Backup, Restore and PITR Mental Model

1) The problem before tools

A green backup job proves that a process ran; it does not yet prove that the artifact is restorable, the recovery target is correct, the WAL chain is complete, or the actual loss/time boundary has been measured. This lesson uses one bounded PostgreSQL 18 incident: legitimate credit changes are followed by an accidental bulk update. The deliverable is Backup, Restore & PITR Recovery Evidence Record v1, not a claim of production-wide disaster readiness.

2) Route and lesson boundary

DAT-005 supplies migration discipline and DAT-007 supplies the separation between declared intent and observed database state. This lesson owns recovery reasoning only. It keeps two needs separate: a portable logical export through pg_dump/pg_restore, and point-in-time recovery through a base backup plus WAL. They are not interchangeable.

3) Mental model

Keep two chains visible:

Database → pg_dump archive → pg_restore → isolated restored DB → row/business verification

Base backup + continuous WAL archive → recovery target → isolated recovered cluster → row/business verification

For both chains, the terminal gate is verification, not artifact creation and not server startup. The evidence record captures objective, mechanism, scope, required components, target, restore steps, integrity checks, measured loss boundary, measured recovery time, a failure probe, and residual risk.

4) Keep recovery classes distinct

PostgreSQL distinguishes SQL dump, file-system/physical backup, and continuous archiving. A logical dump is useful for portable object/data restore and archive formats are consumed by pg_restore. A single-database dump does not automatically represent cluster-global objects or a PITR timeline. A base backup provides the physical starting point, while PITR additionally depends on the WAL required to replay toward a target.

5) What logical restore actually proves

The fixture captures a bounded database and creates a logical archive. Restore happens into a separate disposable destination. A return code is not sufficient evidence: row counts, key credit values, schema fingerprint, and scope assumptions must be checked. If an operational dependency lies outside the single-database dump, record the limitation instead of silently treating it as restored.

6) PITR requires a chain, not merely a base backup

PITR starts from a suitable base backup and replays the required WAL. The target must be explicit, such as a time, LSN, or incident marker. The worked case records a marker immediately before the destructive bulk update. Removing a required WAL segment must invalidate the recovery claim. Starting the recovered server is still not enough; row-level/business verification determines whether the intended state was reached.

7) Worked incident and target boundary

Accounts A-100, A-200, and A-300 hold credits. After baseline, two legitimate changes occur, followed by a faulty statement that zeros the credits. A current logical export may satisfy a separate portability/compliance requirement. Incident recovery instead targets the state immediately before the destructive statement. The record binds that target to an expected business state and compares recovered rows against it.

8) Required failure modes

WAL gap: a required segment is absent; PITR success must not be claimed.

Wrong target: the server starts but lands after the destructive update; business verification rejects it.

Logical-scope gap: a database archive restores but an external/global dependency was outside its scope; the scope check records that limitation.

RPO/RTO overclaim: a configured cadence or one fast fixture restore is not an organizational guarantee. Only measured scenario evidence is recorded.

9) Separate RPO/RTO promises from measurements

RPO concerns an acceptable data-loss boundary; RTO concerns the acceptable time to restore capability. Configuration alone proves neither. This lesson records the measured loss boundary and measured restore/recovery time for the actual exercise, then names assumptions such as hardware, data volume, archive availability, operator actions, and verification cost.

10) Guided and independent practice

In the guided path, state the recovery objective before choosing commands. Restore the logical archive into an isolated destination and verify it. For PITR, construct the chain plan and target marker, run the local tool preflight, and use only a disposable local cluster. The starter does not accept production hosts or irreplaceable archives.

Independent transfer: a support database must recover to immediately before an accidental bulk update while a separate compliance task needs a portable logical export. Choose two different artifacts, two verification gates, and one residual limitation for each without copying the worked conclusion.

11) Verification and evidence

Minimum evidence includes the recovery objective, mechanism choice, artifact identity/hash, restore-destination identity, target marker, row/business verification, a failure observation, measured duration, measured data-loss boundary, changed-context transfer, and a residual limitation. Separate observation from inference. If real PostgreSQL or physical recovery tools were not run, status remains NOT_RUN and runtime evidence claims remain false.

12) Assessment and transfer

A passing learner distinguishes logical restore from PITR, preserves the bounded scope of pg_dump, explains the base-backup-plus-WAL dependency, rejects wrong-target and missing-WAL cases, verifies recovered business state directly, and limits RPO/RTO claims to the measurement actually performed.

13) Sources and freshness

The source boundary contains four official PostgreSQL 18 references only: PG18-BACKUP, PG18-PGDUMP, PG18-PGRESTORE, and PG18-RECOVERY-CONFIG. They were retrieved on 2026-09-02 and have review_due=2026-12-31. On 2026-09-15 no mandatory freshness recheck is due and the source boundary is not expanded.