Error: “No space left on device" when running VCDB commands such as reindex, truncate, and vacuumdb. The vCenter /storage/seat partition is 100% full

Products

VMware vCenter Server 8.0

Issue/Introduction

df -h displays the following /storage/seat/ partition is 100%
vCenter services fail to start.

Unable to run vacuumdb or truncate table:

# /opt/vmware/vpostgres/current/bin/vacuumdb -d VCDB -e -v -f -U postgres > /tmp/vacuumdb.log

       vacuumdb: error: processing of database "VCDB" failed: ERROR: could not extend file "pg_tblspc/16397/PG_14_202107181/16400/9146569": No space left on device

Unable to run the "reindex" command in database:

VCDB=# reindex database "VCDB";
            ERROR:  could not extend file "pg_tblspc/16422/PG_14_202107181/16386/20552989": No space left on device
            HINT:  Check free disk space.

On running this query from VCDB to determine the source of events:

VCDB=# SELECT COUNT(EVENT_ID) AS NUMEVENTS, EVENT_TYPE, USERNAME FROM VPXV_EVENT_ALL GROUP BY EVENT_TYPE, USERNAME ORDER BY NUMEVENTS DESC LIMIT 10;

                        20116558 | com.vmware.vc.guestOperations.GuestOperation | VSPHERE.LOCAL \Administrator

In vCenter server directory /storage/seat/vpostgres/eventtblsp or tasktblsp observed numerous event files which are significantly old.

The following events were observed in /var/log/vmware/vpxd/vpxd.log on the vCenter server:

error vpxd[06552] [Originator@6876 sub=Default opID=EventManagerProcessJobs-6b553e89] [VdbStatement] Execute result code: -1
error vpxd[06552] [Originator@6876 sub=Default opID=EventManagerProcessJobs-6b553e89] [VdbStatement] SQL execution failed: insert into VPX_EVENT_8 (EVENT_ID, CHAIN_ID, EVENT_TYPE, EXTENDED_CLASS, CREATE_TIME,USERNAME, CATEGORY, VM_ID, VM_NAME,                       HOST_ID, HOST_NAME, COMPUTERESOURCE_ID, COMPUTERESOURCE_TYPE, COMPUTERESOURCE_NAME, DATACENTER_ID, DATACENTER_NAME, DATASTORE_ID, DATASTORE_NAME, NETWORK_ID, NETWORK_NAME, NETWORK_TYPE, DVS_ID, DVS_NAME, STORAGEPOD_ID, STORAGEPOD_NAME,CHANGE_TAG_ID) values                               (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

error vpxd[06552] [Originator@6876 sub=Default opID=EventManagerProcessJobs-6b553e89] [VdbStatement] Execution elapsed time: 2 ms
error vpxd[06552] [Originator@6876 sub=Default opID=EventManagerProcessJobs-6b553e89] [VdbStatement] Statement diagnostic data from driver is 53100:0:1:ERROR: could not extend file "pg_tblspc/16397/PG_14_202107181/16400/6662762": No space left on                   device;
     --> Error while executing the query
error vpxd[06552] [Originator@6876 sub=Default opID=EventManagerProcessJobs-6b553e89] [VdbStatement] Bind parameters:
error vpxd[06552] [Originator@6876 sub=Default opID=EventManagerProcessJobs-6b553e89] [VdbStatement] [0]datatype: 1, size: 4, arraySize: 0
error vpxd[06552] [Originator@6876 sub=Default opID=EventManagerProcessJobs-6b553e89] [VdbStatement] value = 29412800

Environment

vCenter Server Appliance 9.x

vCenter Server Appliance 8.x

vCenter Server Appliance 7.x

Cause

This behavior occurs by design. When the available disk space is low, PostgreSQL (vPostgres) prevents the creation or extension of data files to avoid potential database corruption
vCenter services fail to start as they cannot log or process new event entries in the vPostgres database due to space constraints.
The vacuumdb, reindex and TRUNCATE commands also fail in this condition, since these DB operations require additional temporary disk space to rewrite and clean database values before committing changes.

Resolution

Solution 1:

Take offline snapshots of linked vCenters, or an online snapshot if the vCenter is standalone
Create a symlink and move old vpostgres table folders to /storage/updatemgr/backup

1. Create a directory under storage:

# mkdir /storage/updatemgr/backup

2. Move the data to the newly created directory:

# mv /storage/seat/vpostgres/tasktblsp/PG_14_202107181/16400 /storage/updatemgr/backup/16400

Note: The numeric directory above as 16400 may vary in your environment. Please ensure you identify and use the correct directory number as applicable in your setup while following the Plan of Action. Do not rely specifically on the number shown in this example.

3. Create a symlink as follows:

# ln -s /storage/updatemgr/backup/16400 /storage/seat/vpostgres/tasktblsp/PG_14_202107181/16400

4. Post creating symlink, check the database for large table with below command:

#VCDB=# SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND C.relkind <> 'i' AND nspname !~ '^pg_toast' ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 20;

Note: Connect to the VCDB (vCenter Database) by running this command from the vCenter Server Appliance BASH Shell:

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

5. Truncate the large tables with below command:

# truncate table <table-name> cascade;

6. After truncating, the data needs to be removed and moved back to the original folder

rm /storage/seat/vpostgres/tasktblsp/PG_14_202107181/16400
mv /storage/updatemgr/backup/16400 /storage/seat/vpostgres/tasktblsp/PG_14_202107181/16400

7. Restart vCenter services with below command:

 service-control --stop --all && service-control --start --all

If the above steps does not work, there may be some additional database tasks and events to be cleared. Continue to follow below steps:

Solution 2:

Take offline snapshots of linked vCenters, or an online snapshot if the vCenter is standalone
Delete old tasks, events and statistics data in vCenter Server

Delete old tasks, events and statistics data in vCenter Server 5.x, 6.x, 7.x and 8.x

Download the script attached to the above KB "2110031_Postgres_task_event_stat_reset_event_sequence.sql" and place it in the /tmp directory of the vCenter.

Stop vpxd and content-library

service-control --stop vpxd
service-control --stop content-library

Run below command to execute

 # /opt/vmware/vpostgres/current/bin/psql -U postgres -v TaskMaxAgeInDays=<days to keep> -v EventMaxAgeInDays=<days to keep> -v StatMaxAgeInDays=<days to keep> -d VCDB -t -q -f <download-path>/2110031_Postgres_task_event_stat_reset_event_sequence.sql
Note: After execution of the script event id sequence will be reset. Event ids for new events will start from 1.

Stop and Start the vCenter Server services:
```
service-control --stop --all && service-control --start --all
```
If none of the above solution works, then the last option is to increase the disk space for /storage/seat partition following below steps:

Solution 3:

Do not take any vCenter snapshots before increasing the /storage/seat partition.
Increasing the disk space for the vCenter Server Appliance in vSphere

Increasing the disk space for the vCenter Server Appliance in vSphere 6.5, 6.7, 7.0 and 8.0

Power Off the vCenter Server VM. If the VM is inaccessible, then do this from ESXi host level.
Right-click the VM.
Click Edit Settings.
Select Virtual Disk.
Increase the size of the disk. Ensuring there is no snapshot attached to VM.
Power On the vCenter VM.
After the virtual disk is increased, return to the SSH session and run this command to automatically expand any logical volumes for which the physical volumes are increased:

  # /usr/lib/applmgmt/support/scripts/autogrow.sh

Note: Before running the command to expand the volume, check the service status. If services are in a stopped state, specifically vpostgres we need restart the vCenter services. After the autogrow.sh is executed successfully run the restart command -

# service-control --stop --all
# service-control --start --all

Additional Information

If there is enough temporary disk space to rewrite and clean database, then below KB can be followed directly:

vCenter Server services fail to start due to high /storage/seat full