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.

PITR walkthrough

A minimal manual restore to a point in time:

sudo systemctl stop postgresql
# restore the base backup into an empty data directory
tar -xzf /backup/2026-08-01/base.tar.gz -C "$PGDATA"
touch "$PGDATA"/recovery.signal

# postgresql.conf (or postgresql.auto.conf)
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-08-01 14:30:00+08'

On start, the server replays WAL up to the target and pauses (default recovery_target_action = 'pause'). Verify the data, then finish with SELECT pg_wal_replay_resume();. Rehearse timeline handling as well: each completed recovery creates a new timeline, and archiving must follow it.

pgBackRest configuration example

A minimal repository setup:

# /etc/pgbackrest/pgbackrest.conf
[global]
repo1-path=/var/lib/pgbackrest
# example; derive retention from RPO and storage
repo1-retention-full=4
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=<secret>

[main]
pg1-path=/var/lib/postgresql/18/main

sudo -u postgres pgbackrest --stanza=main stanza-create
sudo -u postgres pgbackrest --stanza=main check

sudo -u postgres pgbackrest --stanza=main --type=full backup
sudo -u postgres pgbackrest --stanza=main --type=incr backup
sudo -u postgres pgbackrest --stanza=main info

# Point-in-time restore
sudo systemctl stop postgresql
sudo -u postgres pgbackrest --stanza=main \
  --type=time --target='2026-08-01 14:30:00+08' restore
sudo systemctl start postgresql

check verifies WAL archiving end to end; run it after every configuration change. Retention, encryption keys, and repository permissions must all be available at restore time — losing the cipher pass renders the repository useless.

Restore drill

Run every drill on an isolated instance — a spare host or a second port on a test machine, never the production data directory:

1. Restore the most recent backup to a scratch path, for example pgbackrest --stanza=main --pg1-path=/tmp/restore-test restore.
2. Start a throwaway instance on a separate port: postgres -D /tmp/restore-test -p 6543.
3. Run the validation queries and smoke tests below.
4. Confirm the required extensions, roles, and restore keys were available; per-database dumps exclude cluster roles.
5. Stop and delete the scratch instance.

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.