PostgreSQL backups that pass a restore drill

pg_dump returning exit code zero is not the end of a backup job. A useful PostgreSQL routine captures roles, transfers archives off-server, and regularly restores them into a clean database.

Use the custom archive format

sudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql
sudo -u postgres pg_dump \
  --format=custom \
  --compress=6 \
  --file=/var/backups/postgresql/app-$(date +%F).dump \
  app_production

The custom format supports parallel restore and selective recovery. pg_dump takes a consistent snapshot without stopping writes, but it backs up one database, not cluster-wide roles.

Capture global objects separately:

sudo -u postgres pg_dumpall --globals-only \
  > /var/backups/postgresql/globals-$(date +%F).sql

Protect these files: they may contain password hashes and ownership information.

Fail loudly

Put backup commands in a script with set -euo pipefail. Write to a temporary filename, validate, then rename:

tmp=/var/backups/postgresql/app-$(date +%F).dump.partial
final=${tmp%.partial}

sudo -u postgres pg_dump -Fc -f "$tmp" app_production
sudo -u postgres pg_restore --list "$tmp" > /dev/null
mv "$tmp" "$final"

Only completed archives receive the final extension. Copy them to encrypted off-site storage and alert when the job or transfer fails.

Rehearse a restore

Create a disposable database on a non-production server or under a temporary name:

sudo -u postgres createdb app_restore_test
sudo -u postgres pg_restore \
  --dbname=app_restore_test \
  --no-owner \
  --exit-on-error \
  --jobs=2 \
  /var/backups/postgresql/app-2026-06-24.dump

Run checks that matter to the application:

SELECT count(*) FROM users;
SELECT max(created_at) FROM orders;
SELECT count(*) FROM schema_migrations;

Start a staging application against the restored database if possible. Row counts catch empty tables; an application smoke test catches missing extensions, permissions, and assumptions.

Know the limits

Logical backups are excellent for small and medium databases, migrations, and individual-table recovery. They do not provide point-in-time recovery. For a busy production system with a low recovery-point objective, add physical base backups and continuous WAL archiving.

Record two numbers after every drill: how much data was lost relative to the failure time, and how long service restoration took. Those are your real RPO and RTO, not the values in a policy document.

Automate retention and verification

A simple wrapper can retain the last 14 daily archives and delete only files that match its own naming pattern:

find /var/backups/postgresql -name 'app-20*.dump' -type f -mtime +14 -delete

Run pg_restore --list after every dump and record its exit code. Transfer the completed archive only after validation, then verify the remote object exists and has a non-zero size. Do not treat a successful scp connection as proof that the right file was copied.

Capture extension requirements and role ownership in the recovery note. A restore can fail even with a good archive when the target lacks an extension, a role, or enough disk for indexes. Restore globals before the database when ownership matters, or use --no-owner and assign ownership explicitly in staging.

For a production recovery, stop application writers, restore into a new database name, run smoke tests, and switch the application connection only after validation. Keep the original database untouched until the recovered path is proven.