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

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

book

Article ID: 340940

calendar_today

Updated On:

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
 
  1. Save and close the file.
  2. Stop and Start vPostgres service.
  3. Open command prompt as administrator.
  4. Go to C:\Program Files\VMware\vCenter Server\vPostgres\bin>
  5. Run psql -d VCDB -U postgres
  6. 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 
  1. Once the password is set, restore the backup file for pg_hba file and restart vPostgres service.
  1. 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
  1. Retry VC upgrade.