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: 2019-07-29

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 III" 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 service on all machines

2) Open a command prompt with elevated privileges and navigate to C:\Program Files (x86)\EventSentry\postgresql\bin (use "C:\Program Files (x86)\EventSentry\postgresql96\bin" instead if you have PostreSQL 9.6) 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 and select a location with ample disk space for -f option above

3) If the database export was successful, then open "pgADMIN III" 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 III - the EventSentry database should have been re-created if your import was successful.

7) Start the EventSentry service on all remote hosts