PostgreSQL / Advanced: evidence and reliable operations / Lesson 16

Backup, restore and point-in-time recovery

Choose logical or physical artifacts from recovery objectives and rehearse a complete restore chain. Separate crash recovery from undoing a committed business mistake.

About 4 min readContent reviewed 2026-10-25

On this page

Lesson goals

Explain logical dumps, base backups and archived WAL.
Validate isolated recovery boundaries and business invariants.

Start from acceptable loss and delay

Recovery point objective defines acceptable lost history and recovery time objective defines acceptable restoration delay. A nightly logical dump alone cannot promise a five-minute loss window. pg_dump produces a consistent logical export of one database; cluster-wide roles and other globals require separate handling, such as the appropriate pg_dumpall scope. Physical base backups preserve a cluster-level storage state for compatible recovery with WAL. Choose the method from the incident scenarios, portability needs and measured restore time rather than backup-file size alone.

Inventory what must survive a restore

Tables are only part of a usable service. Inventory schemas, routines, extensions, ownership, grants, large objects, external secrets and application configuration according to the chosen method. A dump can contain code that executes during restore, so treat source artifacts as trusted administrative input. The read-only inventory below lists schemas and extension versions in the authorized training database. It does not create a backup, export credentials or prove that a matching extension package exists on the destination server.

sql Illustrative SQL; expected results are reasoned, not measured.

SELECT schema_name FROM information_schema.schemata
ORDER BY schema_name;
SELECT extname, extversion FROM pg_extension ORDER BY extname;

Results depend on visible schemas and installed extensions; preserve a reviewed manifest with the artifact.

PITR requires a physical base and continuous WAL

Continuous recovery combines an appropriate physical base backup with every required WAL segment through the target boundary. A logical pg_dump file is not a base for WAL replay. An archive gap breaks that recovery chain even if newer segments exist. Preserve timeline information and choose whether to stop before or after the target transaction according to documented settings. PostgreSQL 12 moved recovery configuration away from the old recovery.conf mechanism; PostgreSQL 17 added native incremental base-backup support with its own prerequisites and combination workflow.

Validate business state, not only object count

After an isolated recovery, compare known good transactions, excluded unwanted changes and representative invariants. This query checks that stored booking totals equal their line sums under the explicit training policy that no lines means zero and totals are non-NULL. A real application may include tax or rounding and needs its own invariant. Empty output means this check passed on the inspected data; it does not prove every table, permission or recovery boundary is correct. No restore or query was executed here.

sql Illustrative SQL; expected results are reasoned, not measured.

SELECT b.booking_id, b.total, COALESCE(l.total, 0) AS line_total
FROM recovered_lab.bookings AS b
LEFT JOIN (SELECT booking_id, SUM(amount) AS total
 FROM recovered_lab.booking_lines GROUP BY booking_id) AS l
 ON l.booking_id = b.booking_id
WHERE b.total <> COALESCE(l.total, 0)
ORDER BY b.booking_id;

Expected after a correct synthetic restore: no mismatch rows under the stated invariant.

Recovery needs protected independence

Retain backups and WAL outside the failure domain of the source, with recoverable encryption keys and tested access. Restore to a separate fenced environment before changing production routing; disable or isolate scheduled and outbound effects through the reviewed runbook. Measure the complete recovery duration, including provisioning, replay and application checks. A replica can reproduce an accidental DELETE, and crash recovery faithfully preserves committed work, so neither replaces historical backups. Physical restore and promotion procedures are intentionally not executable snippets in this public theory course.

Common errors and fixes

A logical dump is used as a WAL-replay base

Why it happens: Logical and physical recovery methods were conflated.
How to fix it: Use the documented physical base-backup chain for PITR.
Prevent it next time: Record artifact type, version, timeline and WAL requirements.

Recovery cannot cross a missing WAL segment

Why it happens: Archive retention or transfer left a gap.
How to fix it: Find a complete protected chain or report the latest provable target.
Prevent it next time: Continuously verify archive success and recoverability before retention expires.

A restore starts but the application cannot run

Why it happens: Required extensions, ownership, privileges or external configuration are missing.
How to fix it: Compare the manifest and test the intended service identity in isolation.
Prevent it next time: Include application checks and globals in restore rehearsals.

Check your understanding

1. A base backup exists, but WAL between positions B and C is missing. Do later WAL files guarantee recovery after C?

Show worked solution

No. The missing history prevents replaying the chain from that base. Find a complete independent archive or a later valid base backup with all required WAL, then rehearse it. Otherwise state the last provable recovery point and associated loss window. A list of newer filenames does not reconstruct the absent transaction history.

2. Why does restarting PostgreSQL not undo yesterday’s committed accidental deletion?