Truncate vCenter server database tables on vPostgres database queuing up

Products

• VMware vCenter Server 6.0
• VMware vCenter Server 7.0
• VMware vCenter Server 8.0

Issue/Introduction

• During any troubleshooting activity when a table truncate is issued to the vPostgres database, and the task gets queued without completion.
• In 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.

Environment

• VMware vCenter Server 6.7.x
• VMware vCenter Server Appliance 6.0.x
• VMware vCenter Server 7.0.x
• VMware vCenter Server Appliance 6.5.x
• VMware vCenter Server 6.0.x
• VMware vCenter Server Appliance 6.7.x
• VMware vCenter Server 6.5.x
• VMware vCenter Server 8.0.x

Cause

This issue occurs if an autovacuum process is in progress.

Resolution

1. Log in to the VCDB using command: 

For vCenter Server Appliance: /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