Backups, WAL, and usable recovery | PostgreSQL: operations and recovery

Concept and mechanism

A logical backup describes objects and data for recreation; a physical backup preserves the structure needed for cluster recovery with WAL. A single-database pg_dump is not a PITR base backup and does not create every global role. Plan those objects separately, for example through pg_dumpall --globals-only, protecting sensitive material. A physical plan needs an appropriate base backup and the required WAL sequence with coherent retention and timeline. Incrementals add dependencies on earlier backups and require appropriate combination before recovery. Keep an inventory of those dependencies so that a base still needed by later copies is not removed.

Guided application

In a fictional position-update error, define the intended point, isolate the target, and preserve the source to reconcile valid later movements. pg_verifybackup detects integrity problems within its scope but does not replace starting the server, validating data, and measuring service. Configuration, certificates, and manually edited rules are not recovered by WAL replay. A failing archive_command must continue reporting failure until records are saved correctly; false success destroys trust in the chain. The rehearsal report separates data-restore time, time to a usable application, and observed loss, with actions covering access and other dependencies.

IN PRACTICE

Data restored in 18 minutes and application ready at 52: observed service recovery is 52 minutes.

Common pitfalls

Dump as physical base; checksum as DR; zero exit without copying; configuration outside the plan.

Related topics: Connections, identities, and privileges · Transactions, blocking, and resumption · Plans, indexes, and memory

Take this idea with you

Recoverability requires artifacts, dependencies, and a complete rehearsal.

Create account

Reference: Base backups and WAL recovery · PostgreSQL 18 reference semantics;18.6 current stable at review