How do I reclaim disk space (after purging data) by shrinking the Built-In PostgreSQL database?

Article ID: 241
Category: Database
Applies to: 2.93 and newer
Updated: 2020-06-01

If, after purging data from your EventSentry database, you still have enough disk space (approximately 1.5x the database size is needed) available on the drive, you can open the "PgADMIN" software, double-click "EventSentry (localhost:5432)" to bring up the logon window and log on with the Postgres password, and then follow these steps to shrink the database and reclaim the disk space:

1) Right click on your EventSentry database
2) Select "Maintenance..."
3) Use "Vacuum" with "Vacuum Options" "FULL" checked.

If there is not enough disk space left available on the drive to perform the vacuum maintenance, you'll have to take additional steps to shrink the database and reclaim the disk space:

1) Stop all EventSentry services with the exception of the "EventSentry Database v9.6" or "EventSentry Database" service on all machines

2) Open a command prompt with elevated privileges and navigate to C:\Program Files (x86)\EventSentry\postgresql96\bin (use "C:\Program Files (x86)\EventSentry\postgresql\bin" instead if you still have PostreSQL 9.1) and dump the entire EventSentry database using the pg_dumpall command:
pg_dumpall -f "D:\Backup\eventsentry_db_backup.out" -c -U postgres * Note: make sure to select a drive or folder with ample disk space for -f option above. The destination folder must also exist before executing the command.

3) If the database export was successful, then open "PgADMIN" and double-click "EventSentry (localhost:5432)" to bring up the logon window and log on with the Postgres password. If you need to reset the Postgres password, please use this article: https://www.eventsentry.com/kb/227.

4) Right-click the EventSentry database and choose Delete/Drop. DO NOT drop the "EventSentry (localhost:5432)" object by mistake, this would remove all the pgAdmin connection settings instead, and the database would still exist!

5) In the same elevated command prompt, use psql to import the previously exported data as follows:
psql -f "D:\Backup\eventsentry_db_backup.out" -U postgres

6) Reopen or refresh the list of databases in PgADMIN - the EventSentry database should have been re-created if your import was successful.

7) Start the EventSentry service on all remote hosts