The following steps are applicable to VMware Cloud Foundation 3.x and not 4.x and I’m also assuming you are familiar with databases and CLI file editing on Linux.
SDDC Manager 3.x uses PostgreSQL to store all information related to the SDDC deployment.
SSH to SDDC Manager as vcf user and switch to root. PostgreSQL by default only allow local connections so we can connect using:
psql -h localhost -U postgres
To list all the SDDC databases use
\l
As you can see, there are many databases, nearly every VCF service has a dedicated database. And the platform is the main one storing all the infrastructure critical information
Iif you wanted to see the platform database use:
\c platform
Describe all the tables of the databases you are currently connected to
\dt
Now into the unsupported stuff 🙂
What if I wanted to know the database schema to better understand relationships? Well out of the box you can’t connect to PostgreSQL from outside. We need to make some changes.
First allow PostgreSQL to listen on all interfaces not just 127.0.0.1. Change the listen address to * on the PostgreSQL configuration file, here are the steps:
vi /data/pgdata/postgresql.conf inside the file change this line to --->listen_addresses = '*' systemctl status postgres systemctl restart postgres systemctl status postgres
Then we need to allow PostgreSQL to accept connections from the subnet we’re going to connect from, in my case it is 192.168.110.0/24
vi /data/pgdata/pg_hba.conf
add the following line of course using your own subnet CIDR
host all all 192.168.110.0/24 trust
As last, we need to allow the built-in iptables firewall to pass connections to Postgres on TCP port
systemctl status iptables
iptables --list-rules
iptables -I INPUT 1 -p tcp --dport 5432 -j ACCEPT
iptables --list-rules
/usr/sbin/iptables-save
At this point we should be good to go, we also need to make sure we know the postgres password. if you don’t you can always login locally on SDDC Manager and change the password. SDDC Manager by default is storing the encrypted password on /root/.pgpass and therefore won’t ask you for a password. However connecting externally you will be prompted for a password. If you need to change the postgres password use
ALTER USER postgres WITH PASSWORD 'VMware1!';
Next, I’m using pgAdmin 4 to connect, database connection added as following
and here we have it
Now into the even more cool stuff
DataGrip is a (commercial) database management system that supports pretty much any database that is our there. One of the great features is that it can visualise the schema with all the tables relationship.
I have installed a 30 days trail just to show you how easy it is to get the schema.
Please note: I am skipping any step related to installing DataGrip because it is way beyond the scope of this article.
Here you can see that I have added the SDDC Manager Postgres DB to my DataGrip instance
Right click > Visualise will draw the schema that I am attaching here
and here you have, the platform database schema will all its relationships.