Monday, February 1, 2016

vCSA 6 postgreSQL - connect externally through pgAdmin

I recently had to query the VCDB database of vCenter Server 6 but had an appliance deployed and since not using a MS SQL database server I had to do some digging to figure out how i can get access.

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!
http://www.pgadmin.org/

SOLUTION:

  1. Enable SSH for vCSA.
  2. Login as root
    1. shell.set --enabled True
    2. shell
  3. View following 2 files for information on database installation
  4. /etc/vmware-vpx/embedded_db.cfg
    1. General server information and password for superuser. 
  5. /etc/vmware-vpx/vcdb.properties
    1. Stores connection information for vCenter server database VCDB (password for vc user
  6. Edit /storage/db/vpostgres/pg_hba.conf
    1. Add following lines to add your own subnets to be able to connect to PG
    2. Host                   all              all                  <ip address range>/<subnet>           md5
  7. Edit /storage/db/vpostgres/postgresql.conf
    1. Add the line to end of file:   listen_addresses = '*'
    2. Restart posgresql /etc/init.d/vmware-vpostgres restart
  8. Open port on vcsa appliance firewall:
    1. 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.