How to restore an EventSentry database backup to a separate server for archival or testing purposes

1. Install PostgreSQL on the new server 2. Allow your production EventSentry/Web Reports server to connect 3. Create the Web Reports database role 4. Create the target database 5. Restore the backup 6. Connect the archive as a new Web Reports profile

How to restore an EventSentry database backup to a separate server for

This guide shows how to restore an EventSentry .backup file (created with pg_dump, see KB235) onto a separate, dedicated PostgreSQL server - for example, to access archived/historical data without risking disk space or performance issues on your production EventSentry server. Once restored, the database can be connected to the Web Reports as an additional profile, letting you switch between your live and archive databases from the same Web Reports interface.

Prerequisites

An EventSentry database backup file (.backup), created via pg_dump as described in KB235.
A separate Windows server to host the restored database. This does not require EventSentry to be installed - a standalone PostgreSQL installation is sufficient.
Enough free disk space on the new server. See the sizing note below before provisioning.
Network connectivity between your production EventSentry/Web Reports server and the new dedicated server.

Important Note on Database Size: A pg_dump backup file is compressed, so the restored database will be significantly larger than the backup file itself - often 10-40x or more, depending on how compressible your data is. To estimate your own expansion ratio, compare a recent backup file's size to your live database's actual size (visible in the Web Reports > Tools > Database Usage), and apply that same ratio to the archive file you're restoring. Provision the new server with headroom above your estimate.

Steps

1. Install PostgreSQL on the new server

Download the standalone PostgreSQL installer (matching the major version used to create your backup) from the official site: Download PostgreSQL

Run a default installation. Make note of the password you set for the postgres superuser - you'll need it for every command below.

2. Allow your production EventSentry/Web Reports server to connect

By default, a fresh PostgreSQL install only accepts local connections. Edit these two files located in C:\Program Files\PostgreSQL<version>\data:

postgresql.conf > Confirm this setting allows listening beyond localhost:
listen_addresses = '*'

pg_hba.conf > Add lines at the end of the file allowing your production server's IP to connect (replace PRODUCTION_ES_SERVER_IP and match the auth method already used elsewhere in the file, typically scram-sha-256 or md5):

1
2
3

host    all    eventsentry_web     PRODUCTION_ES_SERVER_IP/32    scram-sha-256
host    all    eventsentry_svc     PRODUCTION_ES_SERVER_IP/32    scram-sha-256
host    all    postgres            PRODUCTION_ES_SERVER_IP/32    scram-sha-256

Restart the PostgreSQL service (postgresql-x64-version) so both changes take effect.

3. Create the Web Reports database role

Creating this role before restoring the backup means the permissions embedded in the backup file apply automatically during restore - saving you a manual permissions step afterward. In an elevated Command Prompt:

1
2

"C:\Program Files\PostgreSQL\<version>\bin\psql.exe" -h localhost -U postgres -c "CREATE ROLE eventsentry_web LOGIN PASSWORD 'Your_Password_Here'"
"C:\Program Files\PostgreSQL\<version>\bin\psql.exe" -h localhost -U postgres -c "CREATE ROLE eventsentry_svc LOGIN PASSWORD 'Your_Password_Here'"

Note: The eventsentry_web user is what the Web Reports use to read data, and the eventsentry_svc user is what the EventSentry collector service uses to write data.

4. Create the target database

Choose a name that won't collide with your production database (this example uses EventSentry_Archive):

1	"C:\Program Files\PostgreSQL\<version>\bin\psql.exe" -h localhost -U postgres -c "CREATE DATABASE \"EventSentry_Archive\""

5. Restore the backup

1	"C:\Program Files\PostgreSQL\<version>\bin\pg_restore.exe" -h localhost -U postgres -d EventSentry_Archive -v C:\path\to\your\es_database.backup

Restore time depends on the backup size - for large archives this can take a while. You'll see a long stream of creating TABLE, creating INDEX, and creating FK CONSTRAINT messages; this is normal. Do not interrupt this process.

6. Connect the archive as a new Web Reports profile

In the Web Reports, go to Settings > Profile Editor > New Profile and enter:

Server: the new dedicated server's IP or hostname
Port: 5432
Database: EventSentry_Archive or your database created in step 4
Schema: eventsentry
Username: eventsentry_web
Password: the password set in Step 3
Database Admin: postgres (with its password)

Click Test Connection, then Submit. You can now switch between your live and archive databases using the profile dropdown in the upper-left corner of the Web Reports. Check that the database is being successfully accessed in the Web Reports by going to Tools > Database Usage.

Troubleshooting

FATAL: no pg_hba.conf entry for host...
The new server hasn't been configured to accept connections from your production server yet. Revisit Step 2 - and confirm the IP in pg_hba.conf matches your production server exactly, and that you restarted the PostgreSQL service after editing both files. If you still can't connect after this, check that Windows Firewall on the new server allows inbound TCP 5432 from your production server's IP.

Permission denied for schema eventsentry
This means the eventsentry_web/svc role didn't exist when the backup was restored, so the permission grants embedded in the backup were skipped. Grant permissions manually:

"C:\Program Files\PostgreSQL\<version>\bin\psql.exe" -h localhost -U postgres -d EventSentry_Archive -c "GRANT USAGE ON SCHEMA eventsentry TO eventsentry_web; GRANT SELECT ON ALL TABLES IN SCHEMA eventsentry TO eventsentry_web; GRANT SELECT ON ALL SEQUENCES IN SCHEMA eventsentry TO eventsentry_web;"

To avoid this next time, create the role before restoring (Step 3 before Step 5).

Pg_restore: error: could not execute query: ERROR: role "eventsentry_web/svc" does not exist during restore
This is expected if the database roles were not created. Check step 3.

Column ... does not exist when browsing the archive profile in Web Reports > Tools > Database Usage.
This means the archived database's schema is older than what your current Web Reports version expects since the schema evolves across EventSentry releases. To fix it, update the archive's schema using EventSentry's built-in Database Wizard:

Open the Management Console in your production EventSentry server, right-click Actions > Add Action > Database, and name it.
In the new database action, select Edit and fill in your backup database settings. Make sure to use the eventsentry_svc username and password.
Click Initialize or Update to start the Database Wizard. Enter the postgres password for your new database server, click Next, and follow the wizard instructions.