Connecting to VMware appliance postgres/PSQL instance from an external computer/pgadmin

By default, the postgres instance on vCenter/vSphere replication..etc.. are configured to not accept connections from a computer on the network. On this post, I will show you how to re-configure this to allow connections from an external box for tools like PGadmin etc.

Note: Depending on the appliance, the postgres, configuration files/paths might be different. On this post, we will search for the configuration and then change the respective file.

Start by ssh into the appliance.

Type the below command to search for the configuration file: postgresql.conf

find / -iname postgresql.conf

take a copy of the configuration.

cp /storage/db/vpostgres/postgresql.conf /storage/db/vpostgres/postgresql.conf.backup

Edit the file

vi /storage/db/vpostgres/postgresql.conf

Look for the line that says “listen_addresses = ‘XXXX”
In some cases, this will be hashed out, remove the hash. and replace local host with *

Save the configuration file (key combination: “Esc” + “:” and then type in “wq!”

Search for the Postgres configuration file

find / -iname pg_hba.conf

backup the configuration file

cp /storage/db/vpostgres/pg_hba.conf /storage/db/vpostgres/pg_hba.conf.bak

Edit the file

vi /storage/db/vpostgres/pg_hba.conf

Look for the below and replace this with the your IP subnet

host all all 192.168.1.0/24 trust <———————————–From the below putty, you can see that I am on a 192.168.1.x subnet

The method is set to trust (not recommended) as I did not want to log into the DB with a password

Save the configuration file (key combination: “Esc” + “:” and then type in “wq!”

restart vmware-postgres service

service vmware-vpostgres restart

For vCenter server 6.5

service-control --vmware-vpostgres restart

Conform postgres port number and if it is listening to (vSPhere Replication appliance listens to a different port! it is best to know which port you need to connect to when accessing from an external box)