SDDC Manager 3.9.x Postgres DB

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.

Be sociable, share!Tweet about this on Twitter
Twitter
Share on LinkedIn
Linkedin
Share on Facebook
Facebook
Email this to someone
email

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.