Truncate task hangs or queues on vCenter vPostgres database

Products

• VMware vCenter Server

Issue/Introduction

• During any vCenter troubleshooting activity when a table truncate is issued to the vPostgres database, and the task gets queued without completion.
• In /var/log/vmware/vpostgres/postgresql.log, you see entries similar to:

yyyy-mm-dd hh:mm:ss.UTC 5b63835b.e01a 0 VCDB vc DETAIL:  Process holding the lock: 57185. Wait queue: 57370.
yyyy-mm-dd hh:mm:ss.UTC 5b63835b.e01d 0 VCDB vc DETAIL:  Process holding the lock: 57185. Wait queue: 57370, 57373.

Resolution

Note: Take a backup or snapshot of the vCenter before proceeding. Take offline snapshot if the vCenter is in Enhanced Linked Mode. Refer Snapshot Best practices for vCenter Server Virtual Machines

1. SSH in to vCenter
2. Stop vpxd service:
   
   service-control --stop vpxd
   
   
3. Log in to the VCDB using command:

/opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres

2. To find the process ID of autovacuum process, execute the following query:
   
   select * from pg_stat_activity where query like 'autovacuum:%';
    
3. To terminate the autovacuum process, execute the following query with the PID noted from step 2:
   
   select pg_terminate_backend(PID) from pg_stat_activity;
    
4. Retry the truncate task
5. Start vpxd service once the truncate task is completed
   
   service-control --start vpxd