PostgreSQL Backup, Restore, and Recovery

1. Define what recovery must achieve

Agree a recovery point objective (maximum acceptable data loss) and recovery time objective (maximum acceptable outage) with the service owner. Map each database, role, tablespace, configuration, and dependency to a tested backup method and retention policy. Store backups separately from the database host and protect them with encryption and access controls.

2. Make and inspect a logical backup

For a small database or a portable migration, use a custom-format dump. Commands run from a trusted host with credentials supplied through the configured PostgreSQL service/credential mechanism, not embedded in shell history.

pg_dump --format=custom --file=appdb_2026-10-04.dump appdb
pg_restore --list appdb_2026-10-04.dump

# Restore to a new, isolated test database; do not overwrite production.
createdb appdb_restore_test
pg_restore --exit-on-error --dbname=appdb_restore_test appdb_2026-10-04.dump

Check command exit status, artifact size, storage location, and restore logs. Compare important table counts and application-level invariants between source and restored test data.

3. Physical backups and point-in-time recovery

For full-cluster recovery and point-in-time recovery (PITR), use a supported physical backup process plus continuous WAL archiving. Validate the backup and WAL chain, timeline, encryption keys, configuration, and restore instructions. A successful backup-job status alone does not prove recoverability.

1. Identify a clean recovery target and stop writes to the restore environment.
2. Restore the base backup to an isolated host using the documented tool and version.
3. Configure recovery to stop at the approved timestamp or transaction marker.
4. Start in isolation; inspect recovery logs and confirm the expected timeline.
5. Validate schema, representative rows, and application checks before cutover approval.

4. Schedule restore drills

Run regular restore drills, record measured recovery time and data point, and fix gaps in the runbook. Test both a single-database logical restore and the physical recovery path when those are part of the service design. Never test destructive restore steps against the live production data directory.