Backup, recovery, and PITR

Choose the mechanism

Choosing pgBackRest, WAL-G, or pg_dump

Do not copy a fixed daily/weekly schedule. Derive backup cadence from RPO, WAL volume, restore bandwidth, retention policy, and measured RTO, and keep at least one copy outside the primary database permission boundary.

Logical backup

Custom format supports parallel restore and object selection:

pg_dump \
  --format=custom \
  --file=commerce-20260802.dump \
  --dbname='postgresql://backup@db.example/commerce'

pg_restore --list commerce-20260802.dump
createdb commerce_restore_test
pg_restore \
  --dbname=commerce_restore_test \
  --jobs=4 \
  --exit-on-error \
  commerce-20260802.dump

pg_dump provides a consistent snapshot during export but covers one database. Back up global objects separately:

pg_dumpall --globals-only > globals-20260802.sql

Do not put globals files containing password hashes in a general artifact store.

Physical backup and PITR

PITR requires a usable base backup, an unbroken WAL stream from that backup, correct recovery configuration, and timeline handling. WAL alone is insufficient; a base backup alone cannot recover to an arbitrary point.

An archive command returns zero only after a safe copy and never overwrites an existing file. For object storage, use a mature tool for concurrency, checksums, retention, and encryption rather than an unmonitored shell one-liner.

Continuously alert on archive failures, missing WAL, repository capacity, and the latest recoverable time. Let the backup tool calculate dependency-aware retention; do not delete physical backup files solely by date.

Restore drill

Record backup id, start/end, recovery target, server version, required keys, actual RTO, latest recoverable transaction time, validation queries, and anomalies.

At minimum inspect:

SELECT count(*) FROM critical_table;
SELECT min(created_at), max(created_at) FROM critical_table;
SELECT conname, convalidated FROM pg_constraint WHERE NOT convalidated;
SELECT indexrelid::regclass, indisvalid FROM pg_index WHERE NOT indisvalid;

Then run application-level read-only smoke tests. Matching row counts do not prove relations and privileges are correct.