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

 

Copy 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)

netstat -anop | grep postgres

From the above, we know the port is 5432

 

Launch pgadmin and add a new server

 

Give in a connection name and switch to the connection tab, Enter the Host name and port Number

Type in a username and a password (Note that I set the configuration to allow connection without the password in pg_hba.conf)

Save ad you are good to go!

Also note that in most cases, the db credentials is stored in certain configuration files like

  • VCDB.properties

find / -iname vcdb.properties

Cat  /etc/vmware-vpx/vcdb.properties

  • .pgpass from the home directory

ls -ltha ~/

cat ~/.pgpass

 

Related Posts
%d bloggers like this: