Our vBulletin migration is complete.
Welcome vBulletin users! All content and user information from the Micro Focus Forums (vBulletin) site has been migrated to this site. READ MORE.

Sentinel PostgreSQL Maintenance Script

Sentinel PostgreSQL Maintenance Script

Sentinel's PostgreSQL database can take up a large amount of disk space, especially if you are utilising the Sentinel Identity Tracking solution.

This is a result of the way PostreSQL functions. When a row in a table is deleted or updated, it is only marked as such and the physical disk space is not tagged as re-usable. These are called tuples and are cleaned up by calling the VACUUM function.

The PostgreSQL documentation states:

We recommend that active production databases be vacuumed frequently (at least nightly), in order to remove dead rows. After adding or deleting a large number of rows, it might be a good idea to issue a VACUUM ANALYZE command for the affected table. This will update the system catalogs with the results of all recent changes, and allow the PostgreSQL query planner to make better choices in planning queries.

This script makes it easy to cron this functionality for the Sentinel DB.

NOTE: The VACUUM process needs free disk space to run in FULL mode as it creates a temporary TABLE, copies the active data, and then replaces the old TABLE with the cleaned version. When it comes to some of the reporting tables, these can consist of hundreds of millions of rows (if not billions) which can take 50GB+.

Log files for the VACUMMing are located in the $ESEC_DATA_HOME/log directory.

You may want to cron this process with something in your /etc/crontab file such as:

# PostgreSQL Database Maintenance
0 1 * * * novell /scripts/vacuum_pgdb -d SIEM_WF >/dev/null 2>&1
0 2 * * * novell /scripts/vacuum_pgdb -d SIEM >/dev/null 2>&1
0 5 * * * novell /scripts/vacuum_pgdb -d SIEM_WF -z >/dev/null 2>&1
0 6 * * * novell /scripts/vacuum_pgdb -d SIEM -z >/dev/null 2>&1

The first cron at 1am will perform the VACUUM, the second will run the ANALYZE to optimise.

In our environment (ignore the graph layout, been playing with Primary Storage disk size), you can see the drop in disk space after the VACUUM shown at red arrow:

PostgreSQL-VACUUM-Result.png

More articles on my Website.

Labels (2)
Attachments

DISCLAIMER:

Some content on Community Tips & Information pages is not officially supported by Micro Focus. Please refer to our Terms of Use for more detail.
Top Contributors
Version history
Revision #:
2 of 2
Last update:
‎2019-06-08 05:43
Updated by:
 
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.