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
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:
More articles on my Website.