psql commands fail to connect to the vPostgres database on vCenter even though the vmware-vpostgres service is running

Products

• VMware vCenter Server

Issue/Introduction

• Attempts to execute a psql command fail with the following error:

psql.bin: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?

• Changing solution user certificates using certificate-manager fails when updating the eam extension:

Updating the certificate for VC extension com.vmware.vim.eam
Error while updating the certificate for VC extension com.vmware.vim.eam
Stderr :-[ psql.bin: error: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
] Command Failed!!

In both scenarios above, the error shows that psql is looking in the /tmp/ folder for the Unix domain socket file instead of the correct location /var/run/vpostgres

• The vPostgres service is running:

service-control --status vmware-vpostgres
Running:
 vmware-vpostgres

Environment

vCenter Server 7.0

vCenter Server 8.0

Cause

This can happen if the environment variable PGHOST is not set.

To check this, execute the following command at the BASH shell in vCenter:

• echo $PGHOST

The expected/correct output is: /var/run/vpostgres

If this is empty, psql will use the default value of /tmp

Resolution

This environment variable is set when /etc/profile.d/vmware-vpostgres-config.sh is executed at the start of every SSH session.

If this file was removed or changed, this can result in environment variables not being correctly set.

Copy the file from a working vCenter, or else create a new /etc/profile.d/vmware-vpostgres-config.sh file with the following contents:

export VMWARE_POSTGRES_ROOT=/opt/vmware/vpostgres
export VMWARE_POSTGRES_BASE=$VMWARE_POSTGRES_ROOT/current
export VMWARE_POSTGRES_BIN=$VMWARE_POSTGRES_BASE/bin
export VMWARE_POSTGRES_ETC=$VMWARE_POSTGRES_BASE/etc
export VMWARE_POSTGRES_SCRIPTS=$VMWARE_POSTGRES_BASE/scripts
export VMWARE_POSTGRES_DB_ADMIN=postgres
export VMWARE_POSTGRES_OS_ADMIN=vpostgres
export VMWARE_POSTGRES_VMON_GROUP=vpgmongrp
export VMWARE_POSTGRES_MOUNT_DATA=/storage/db
export VMWARE_POSTGRES_DATA=$VMWARE_POSTGRES_MOUNT_DATA/vpostgres
export VMWARE_POSTGRES_SSL_DATA=$VMWARE_POSTGRES_MOUNT_DATA/vpostgres_ssl
export VMWARE_POSTGRES_MOUNT_XLOG=/storage/dblog
export VMWARE_POSTGRES_XLOG=$VMWARE_POSTGRES_MOUNT_XLOG/vpostgres/pg_xlog
export VMWARE_POSTGRES_LOG=/var/log/vmware/vpostgres
export VMWARE_POSTGRES_MOUNT_ARCHIVE=/storage/archive
export VMWARE_POSTGRES_ARCHIVE=$VMWARE_POSTGRES_MOUNT_ARCHIVE/vpostgres
export VMWARE_POSTGRES_BACKUP=$VMWARE_POSTGRES_MOUNT_ARCHIVE/backup_data
export VMWARE_POSTGRES_MOUNT_SEAT=/storage/seat
export VMWARE_POSTGRES_TBSPACE_SEAT=$VMWARE_POSTGRES_MOUNT_SEAT/vpostgres
export VMWARE_POSTGRES_DB_REPLICATION=replicator
export PGHOST=/var/run/vpostgres
export PGSERVICEFILE=$VMWARE_POSTGRES_DATA/pg_service.conf

This should not require a reboot/restart of services, but you would need to start a new SSH session for the changes to take effect.