Validate the Consistency of the vCenter Server Appliance 6.5 and Newer Databases

Products

VMware vCenter Server

Issue/Introduction

The vpgsql-consistency-check.sh script, located under the Attachments section on the bottom of the article, performs a number of functions with it's primary function being a check of the selected database to ensure that all data can be read.
By reading out all of the data from the database we can verify that there are no issues present that will impede the operation of the database.

Along with checking the read consistency of the selected database(s) the vpgsql-consistency-check.sh script also does the following:

Backup selected database(s)
Vacuum the PostgreSQL database
Send an e-mail notification of the results of the selected operation(s)
Correct issue with Crontab to allow for the addition of this script to Crontab
Test SMTP settings to ensure e-mails will be received

Environment

VMware vCenter Server 7.0.x
VMware vCenter Server Appliance 6.7.x
VMware vCenter Server Appliance 6.5.x

Resolution

Configuration of the Script

Log into the vCenter Server Appliance as root via SSH or Console.Toggling the vCenter Server Appliance 6.x default shell
Make a directory for the script:

mkdir /scripts/

Copy the script to the vCenter Server Appliance under /scripts using an SCP utility.
Verify the script has proper rights:

chmod 744 /scripts/vpgsql-consistency-check.sh

(Optional): Configure SMTP to allow script to send e-mail notifications

Follow the directions in the VMware Documentation to configure SMTP on your vCenter Server Appliance.
Log into the vCenter Server Appliance as root via SSH or Console.
Test SMTP functionality by running the following:

/scripts/vpgsql-consistency-check.sh -e e-mail -s

Note: Replace e-mail with the desired destination e-mail address

(Optional): Configure script to be run by Cron

Log into the vCenter Server Appliance as root via SSH or Console.
Then type shell.
Enable Crontab functionality:

/scripts/vpgsql-consistency-check.sh -c

Add the script to Crontab:

crontab -e

Type i to insert into file.
Copy the following into the file.

00 02 * * * /scripts/vpgsql-consistency-check.sh Options

Note: Replace Options with the desired script options found in the Additional Information section of this article..

Type :wq and press the Enter key to write the changes and close the file.

Note: The Crontab time format is Minutes Hour Day_of_the_Month Month Day_of_the_Week

Example: 00 02 * * * would run the script at 02:00 AM system time everyday of the month.

Execution of the Script

There are a number of options that can be simultaneously run.
For instance, you can check the AFD and PostgreSQL databases, backup both databases to a defined location, and vacuum the PostgreSQL database.

Example: /scripts/vpgsql-consistency-check.sh -a -b -f /tmp/ -v
Note: In the above example; any errors will be reported to /var/log/vmware/vpostgres/VPG_Check-date/time_error.log.

The suggested execution of this script is:
/scripts/vpgsql-consistency-check.sh -a -b -f /location -v -e e-mail_address

Note: Replace location with the desired backup location and e-mail_address with the desired e-mail address.

In the above example; script messaging will be recorded to the default log file and an e-mail notification reflecting the status will be sent to the defined address.

Notes:

By default every execution of the script creates a /var/log/vmware/vpostgres/VPG_Check-date/time_error.log file.
When executing a backup of any database the script will ensure there is at least 10% of available disk space on the destination defined. If there is not 10% free space the script will error out reporting this.

Additional Information

Script Options:
-a enable backup of AFD
-b enable backup of VCDB
-f backup file location
-v vacuum VCDB DB
-e notify e-mail address
-s test SMTP (Requires -e option)
-c correct Crontab issue
-h help

Attachments

vpgsql-consistency-check get_app