Reclaiming space on VMware Postgres (vPostgres) databases in the VMware vRealize Operations Manager vApp
search cancel

Reclaiming space on VMware Postgres (vPostgres) databases in the VMware vRealize Operations Manager vApp

book

Article ID: 343916

calendar_today

Updated On:

Products

VMware Aria Suite

Issue/Introduction

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. Although the databases in the vRealize Operations Manager (formerly known as vCenter Operations Manager) virtual machines run a maintenance job automatically to reclaim the space and remove these unused inaccessible records, sometimes it is necessary to run the maintenance manually.

The maintenance process is called Vacuum and the database is configured for Auto-Vacuum. However, if the changes to the database are high in number and quick, it may be necessary to run the process manually to reclaim space.

This article provides steps to manually run the Vacuum command from the command line.


Symptoms:



Environment

VMware vCenter Operations Manager (vApp) 5.8.x
VMware vCenter Operations Manager (vApp) 5.7.x

Resolution

To manually run the Vacuum command:

  1. Log in to the UI virtual machine as the root user.
  2. To stop the vRealize Operations Manager (formerly known as vCenter Operations Manager) services, run this command:
su - admin -c "vcops-admin stop"

  1. To check the database storage space, run this command:
du -h /data/pgsql --max-depth=0

  1. To reclaim space, run the vaccum command:
su - postgres -c "/opt/vmware/vpostgres/current/bin/vacuumdb -a -e -v -f -U postgres > /tmp/vacuumdb.log"

  1. Once the Vaccum command is complete reindex the database using this command:
su - postgres -c "reindexdb -a > /tmp/reindexdb.log"

  1. To check the database storage space and verify space usage decrease, run this command:
du -h /data/pgsql --max-depth=0

  1. Log in to the Analytics virtual machine as the root user.
  2. To check the database storage space, run this command:
du -h /data/pgsql --max-depth=0

  1. To reclaim space, run this command:
su - postgres -c "/opt/vmware/vpostgres/current/bin/vacuumdb -a -e -v -f -U postgres > /tmp/vacuumdb.log"

  1. Once the Vaccum command is complete, reindex the database using this command:
su - postgres -c "reindexdb -a > /tmp/reindexdb.log"

  1. Check the database storage space and verify space usage decrease, run this command:
du -h /data/pgsql --max-depth=0

  1. Log in to the UI virtual machine as the root user.
  2. Start the vRealize Operations manager services, run this command:

su - admin -c "vcops-admin start"



Additional Information

Note: If the vacuum command was unable to complete due to lack of space errors, follow these steps:
  1. Add temporary storage space to the appropriate vRealize Operations Manager (formerly known as vCenter Operations Manager) virtual machine. For more information, see Adding additional temporary storage space to a VMware vRealize Operations Manager virtual machine for VMware vFabric Postgres maintenance (2080646).
  2. Log in to the appropriate virtual machine as the root user.
  3. To stop the postgres service, run this command:
service postgresql stop
  1. To copy the database files to the new disk mount, run this command:
cp -ar /data/pgsql/data /mnt/db1/
  1. To start the database from the new location, run this command:
su - postgres -c "/opt/vmware/vpostgres/current/bin/pg_ctl -D /mnt/db1/data start"
  1. To verify that the service has started and is running from the correct location, run this command:
su - postgres -c "/opt/vmware/vpostgres/current/bin/pg_ctl -D /mnt/db1/data status"
  1. To reclaim space, run this command:
su - postgres -c "/opt/vmware/vpostgres/current/bin/vacuumdb -a -e -v -f -U postgres > /tmp/vacuumdb.log"
  1. Once the vacuum command is complete, reindex the database using this command:

su - postgres -c "reindexdb -a > /tmp/reindexdb.log"

  1. To check the database storage space and verify space usage decrease, run this command:
du -h /mnt/db1 --max-depth=0
  1. To stop the database from the new location, run this command:
su - postgres -c "/opt/vmware/vpostgres/current/bin/pg_ctl -D /mnt/db1/data stop"
  1. To remove the original vPostgres database directory, run this command:
rm -rf /data/pgsql/data
  1. To copy the new directory to the original location, run this command:
cp -ar /mnt/db1/data /data/pgsql/
  1. To start the vPostgres service, run this command:
service postgresql start
Note: To remove the temporary disk mount, shutdown the appropriate vRealize Operations Manager (formerly known as vCenter Operations Manager) virtual machine and then remove the added virtual disk from the same virtual machine through the vSphere Client and then restart the virtual machine.
[Internal] Adding additional temporary storage space to a VMware vRealize Operations Manager virtual machine for VMware vFabric Postgres maintenance