My favorite tool to query the database I found to be pgAdmin III but this is installed on my jump server so here are the steps i following to allow pgAdmin to connect to the internal postgresql database on vCSA 6!
- Enable SSH for vCSA.
- Login as root
- shell.set --enabled True
- View following 2 files for information on database installation
- General server information and password for superuser.
- Stores connection information for vCenter server database VCDB (password for vc user
- Edit /storage/db/vpostgres/pg_hba.conf
- Add following lines to add your own subnets to be able to connect to PG
- Host all all <ip address range>/<subnet> md5
- Edit /storage/db/vpostgres/postgresql.conf
- Add the line to end of file: listen_addresses = '*'
- Restart posgresql /etc/init.d/vmware-vpostgres restart
- Open port on vcsa appliance firewall:
- iptables -A INPUT -p tcp -m tcp --dport 5432 -j ACCEPT
This should do it and allow you to connect externally through the nice pgAdmin GUI to your database.