"Could not open file "pg_clog/00xx": No such file or directory" error when starting vCenter Server service

Products

• VMware vCenter Server

Issue/Introduction

Recreating the pg_clog file on vCenter Server Appliance resolves this issue.

Symptoms:

• Unable to start the vCenter Server service on vCenter Server Appliance
• In the /var/log/vmware/vpx/vpxd.log file, you see entries similar to:
  
  YYYY-MM-DDT<time></time>Z [7F06FF982740 warning 'Default'] [VdbStatement] SQL execution failed: SELECT IP_ADDRESS, DEVICE_ID, ENTITY_ID FROM VPX_IP_ADDRESS
  YYYY-MM-DDT<time></time>Z [7F06FF982740 warning 'Default'] [VdbStatement] Execution elapsed time: 0 ms
  YYYY-MM-DDT<time></time>Z [7F06FF982740 warning 'Default'] [VdbStatement] Diagnostic data from driver is 58P01:1:7:ERROR: could not access status of transaction 57961039
  --> Could not open file "pg_clog/00XX: No such file or directory;
  --> Error while executing the query
  YYYY-MM-DDT<time></time>Z [7F06FF982740 warning 'Default'] [VdbStatement] Bind parameters:
  YYYY-MM-DDT<time></time>Z [7F06FF982740 error 'Default'] [Vdb::IsRecoverableErrorCode] Unable to recover from 58P01:7
  YYYY-MM-DDT<time></time>Z [7F06FF982740 error 'Default'] [VdbStatement] SQLError was thrown: "ODBC error: (58P01) - ERROR: could not access status of transaction 57961039
  --> Could not open file "pg_clog/00XX": No such file or directory.;
  --> Error while executing the query" is returned when executing SQL statement "SELECT IP_ADDRESS, DEVICE_ID, ENTITY_ID FROM VPX_IP_ADDRESS"
  YYYY-MM-DDT<time></time>Z [7F06FF982740 error 'utilvpxdDbBulkLoader'] [VpxdDbBulkLoader::Load] Failed to load tableDef 29 from database: "ODBC error: (58P01) - ERROR: could not access status of transaction 57961039
  --> Could not open file "pg_clog/00XX": No such file or directory.;
  --> Error while executing the query" is returned when executing SQL statement "SELECT IP_ADDRESS, DEVICE_ID, ENTITY_ID FROM VPX_IP_ADDRESS"

  Note: The preceding log excerpts are only examples. Date, time, and environmental variables may vary depending on your environment.
   

Environment

VMware vCenter Server Appliance 5.0.x
VMware vCenter Server Appliance 6.7.x
VMware vCenter Server Appliance 5.1.x
VMware vCenter Server Appliance 6.5.x
VMware vCenter Server Appliance 6.0.x
VMware vCenter Server Appliance 5.5.x

Cause

This issue can occur when vCenter Server Appliance is not powered off correctly causing corruption of the VMware Postgres database transaction log.

Resolution

To resolve this issue, recreate the pg_clog file on vCenter Server Appliance.

Shutdown Postgres:

service-control --stop vmware-vpostgres

To recreate the pg_clog file:
 

1. Take a snapshot of the vCenter server appliance.
    
2. Log in to vCenter Server Appliance as root using SSH or the direct console
    
3. Run this command to change the directories to /storage/db/vpostgres/pg_clog:
   
   cd /storage/db/vpostgres/pg_clog/
    
4. Run this command to recreate the pg_clog file:
   
   dd if=/dev/zero of=/storage/db/vpostgres/pg_clog/00xx bs=256k count=1
   
   Note: In this example, the pg_clog/00XX file name is taken from the error message in vpxd.log file.
    
5. Run this command to change ownership of the file to vpostgres:
   
   chown vpostgres:users 00xx
    
6. Start Postgres and vCenter services:

service-control --start --all

 

 

Start the vCenter Server services on vCenter Server Appliance.

Additional Information

In Postgresql 10 and above the directory pg_clog was renamed to pg_xact. Therfore for vCenter 7 and above pg_xact should be used for the directory name instead of pg_clog.

Impact/Risks:
Multiple transaction may be impacted by the missing file.
With any corruption issue, the best course of action is to restore from backups.