"Error: pgpass.conf which enables promptless access to PostgreSQL was not found" when upgrading Windows vCenter Server

Products

• VMware vCenter Server

Issue/Introduction

Symptoms:

• Upgrading Windows vCenter Server fails with the error:
  
  Error: pgpass.conf which enables promptless access to PostgreSQL was not found

• Unable to see the pgpass.conf file in location C:\Windows\System32\config\systemprofile\AppData\Roaming\postgresql\

Environment

VMware vCenter Server 6.7.x
VMware vCenter Server 6.0.x
VMware vCenter Server 6.5.x

Cause

This issue occurs when pgpass.conf file is missing.

Resolution

To resolve this issue:

1.  Check if pgpass.conf is available under C:\Windows\System32\config\systemprofile\AppData\Roaming\postgresql.
2. If pgpass.conf file is missing, recreate the file using below steps:
   1. Reset postgres password.
   2. Take a backup of pg_bha config file and edit pg_hbaconfig file under C:\ProgramData\VMware\vCenterServer\data\vpostgres.
   3. Change the md5 to trust (Since you cannot reset postgres password on vPostgres DB as you should connect to VCDB as vcuser which is not a super user) 

# TYPE  DATABASE        USER            ADDRESS                 METHOD
 
# IPv4 local connections:
host    all             all             127.0.0.1/32           md5
# IPv6 local connections:
host    all             all             ::1/128                md5

 

4. Save and close the file.
5. Stop and Start vPostgres service.
6. Open command prompt as administrator.
7. Go to C:\Program Files\VMware\vCenter Server\vPostgres\bin>
8. Run psql -d VCDB -U postgres
9. Once logged into DB, run the below command to update the password

ALTER USER postgres PASSWORD 'new_password';

Note: You can use the same password as vcpassword under vcbd.properties file : C:\ProgramData\VMware\vCenterServer\cfg\vmware-vpx 

10. Once the password is set, restore the backup file for pg_hba file and restart vPostgres service.

3. Create a pgpass.conf file with below contents with updated password for postgres user and save the file to C:\Windows\System32\config\systemprofile\AppData\Roaming\postgresql and C:\Users\XXXX\AppData\Roaming\postgresql\

 

localhost:5432:postgres:postgres:NEWPASSWORD
127.0.0.1:5432:postgres:postgres:NEWPASSWORD
localhost:5432:VCDB:postgres:NEWPASSWORD
127.0.0.1:5432:VCDB:postgres:NEWPASSWORD

4. Retry VC upgrade.