[Internal] Truncate vCenter server database tables on vPostgres database queuing up

Products

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

Issue/Introduction

Symptoms:

• 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