Backups and restores with compression (Windows)
A supplement to Backups and restores (Windows) for cases where the uncompressed PostgreSQL backup uses too much disk space, since pg_dumpall cannot compress its output. For on-demand backups, the in-app System Backup is an excellent alternative.
This method uses pg_dump, which needs the Informer database name. If your Informer has more than one database, run pg_dump and pg_restore for each. Most Informers have a single database.
The method produces two files, both required for a restore: one with the PostgreSQL user roles, and one with a compressed backup of the Informer database.
Frequently asked questions
Why does pg_dumpall use so much disk space? It creates a flat, uncompressed file of the entire PostgreSQL database.
Why have my backups grown so much? Several Informer components write to the database. The most common sources of growth are System Auditing, Workspaces, and file-based Datasets.
Why isn't pg_dump the default? pg_dumpall is comprehensive, capturing all PostgreSQL users, roles, and databases.
Commands and options
This procedure uses two PostgreSQL tools. The values for -U, -d, and -p, and the user password, are in the db block of the Informer config.json.
pg_dump (documentation) extracts a database into an archive file:
| Option | Meaning |
|---|---|
-Fc | Custom (compressed) archive format, for use with pg_restore. It supports selective, reordered, parallel restores and compresses by default (compression level 6). |
-U | PostgreSQL username (postgres is the default). |
-d | Informer database name (informer5 is the default). |
-p | PostgreSQL listening port (5432 is the default). |
-f | Output filename. |
pg_dumpall (documentation) extracts a database cluster into a script file:
| Option | Meaning |
|---|---|
--globals-only | Dump only global objects (roles and tablespaces), no databases. |
This article uses the filename dumproles.sql for the pg_dumpall output and dumpdata.dump for the pg_dump output. Using these names (with a date stamp) keeps things consistent.
Back up with compression
All Informer components except PostgreSQL must be stopped before backing up, or you risk corrupt data.
In an elevated PowerShell prompt, navigate to the folder where you want the backups to reside.
Back up the PostgreSQL user roles:
pg_dumpall -U <username> -p <port> --globals-only -f dumproles.sql
Back up the Informer database (compressed):
pg_dump -U <username> -p <port> -d <databaseName> -Fc -f dumpdata.dump
Verify the backup files have contents, by inspecting them or checking the file sizes.
Restore from a compressed backup
-
Make sure the Informer process is stopped.
-
In an elevated PowerShell prompt, navigate to the folder with the backup files.
-
Restore the user roles first:
psql -U <username> -p <port> -f dumproles.sql -
Then restore the Informer database:
pg_restore -U <username> -p <port> -d <databaseName> dumpdata.dump