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: 2024-03-16

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 then there are 2 options available, depending on the amount of disk space that is available.

Option 1: Use the dbvaccuum utility from the EventSentry Sysadmin Tools to shrink& vaccuum individual tables

You can utilize the compression feature of the NTFS file system and compress larger tables prior to performing a vaccuum, which creates temporary disk space that can be utilized by the vaccuum command. After the vaccuum completed successfully, the (old) compressed files are removed and the optimized table is written to disk. If disk space is scarce, then start with smaller tables to gradually free up disk space.

1) Download the EventSentry Sysadmin Tools
2) In the Web Reports, navigate to Settings -> Database Usage
3) Identify tables with a bloated index or unused rows, potentially starting with the smaller ones. Note the table name from the Name column
4) Run dbpgsqlvaccuum.exe and specify the table name with the /t parameter, for example

dbpgsqlvaccuum /t ESEventlogMain /u postgres /p Pa$w0rD /d EventSentry /s hostname
5) Repeat this command for all tables that can be shrunk, until sufficient disk space is available

Option 2: If option 1 failed or 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 v14" or 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\EventSentry\postgresql14\bin (use "C:\Program Files (x86)\EventSentry\postgresql96\bin" or "C:\Program Files (x86)\EventSentry\postgresql\bin" instead if you still have PostreSQL 9.6 or 9.1 respectively) 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