Fix vrmsdb out of space issue (due to no auto-vacuum) without partition resize

Products

• VMware Live Recovery
• VMware vSphere ESXi

Issue/Introduction

Symptoms:
Replicated VM's getting alerts : Invalid: Virtual machine <name> is no longer protected. Database temporarily unavailable or has network problems.

Environment

VMware vSphere Replication 8.x

Cause

When a vPostgres database is manipulated, the records that are affected are changed, but the old copy is retained for reference. Especially, during a delete operation, the records are only marked as deleted. The databases in HMS virtual machines run a maintenance job automatically to reclaim the space and remove these unused inaccessible records, but sometimes due to a bug of a race condition at boot, it is necessary to run the maintenance manually.

Resolution

Per-requisites:

• To check the database storage space, run this command:

du -h /var/lib/vrmsdb --max--depth=0

• To check the storage usage, run this command

df /var/lib/vrmsdb

• To check the failure of autovacuum, run the command

/usr/bin/pgrep -f autovacuum
If the autovacuum command returns PID, then further investigation is required. However, if no PID is returned follow the resolution steps given below.

 

 To resolve the issue, run the following steps;

1. Add a new hard disk to the VR appliance with the vCenter.
2. Log in to the VR appliance as the root user.
3. Find the newly added disk and format it and mount it.
   1. List all the disks

fdisk -l|grep '^Disk'

2. Format the disk, let's assume the disk is /dev/sdc.

mkfs.ext3 /dev/sdc

3. Mount the disk, let's mount it to the /transition directory.

mkdir /transition
mount /dev/sdc /transition

4. Change the owner of the directory to vrmsdb.

chown vrmsdb:vrmsdb /transition

5. Log in to the psql cli and alter the tablespace for database vrmsdb.

/opt/vmware/vpostgres/current/bin/psql postgres -U vrmsdb

• Execute below commands in the cli

1. Create a new table space on the new disk(named with transition)

CREATE TABLESPACE transition LOCATION '/transition';

2. Disable the connection to the database

UPDATE pg_database SET datallowconn = false WHERE datname = 'vrmsdb';

3. Terminate all the connections to the database.

SELECT pg_terminate_backend(pid) FROM pg_stat_activity

           WHERE datname = 'vrmsdb' AND pid <> pg_backend_pid();

4. Change the database tablespace.

ALTER DATABASE vrmsdb SET TABLESPACE transition;

5. Enable the connection to the database

UPDATE pg_database SET datallowconn = true WHERE datname = 'vrmsdb';

6. Exit the cli

\q

6. Manually execute the vacuumdb.

/opt/vmware/vpostgres/current/bin/vacuumdb -a -e -v -f -U vrmsdb > /tmp/vacuumdb.log

7. Reindex the db.

/opt/vmware/vpostgres/current/bin/reindexdb -a -U vrmsdb > /tmp/reindexdb.log"

8. Log in to the psql cli and restore the tablespace for database vrmsdb.

/opt/vmware/vpostgres/current/bin/psql postgres -U vrmsdb

• Execute below commands in the cli

1. Disable the connection to the database.

UPDATE pg_database SET datallowconn = false WHERE datname = 'vrmsdb';

2. Terminate all the connections to the database.

SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE datname = 'vrmsdb' AND pid <> pg_backend_pid();

3. Change the table space of vrmsdb back to the default  ALTER DATABASE vrmsdb SET TABLESPACE pg_default;
4. Enable the connection to the database

UPDATE pg_database SET datallowconn = true WHERE datname = 'vrmsdb';

5. Drop the transition table space.

DROP TABLESPACE transition;

6. Exit the cli.

9. Check the database storage space and verify space usage decrease, run this command,

du -h /var/lib/vrmsdb --max--depth=0