PostgreSQL PITR: prove recovery with restore drills

A PostgreSQL backup is useful only when a team can restore the right data within an agreed time. Point-in-time recovery (PITR) combines a physical base backup with a continuous sequence of write-ahead log (WAL) files, allowing PostgreSQL to replay changes and stop at a chosen target. The practical goal is not a green backup job. It is evidence that the available base backup and WAL chain can meet a defined recovery point objective (RPO) and recovery time objective (RTO).

For production systems, treat recovery as a versioned service: define the protected cluster, retention window, acceptable data-loss interval, restore deadline, credentials, storage locations, validation queries and owner. Run the procedure in an isolated environment on a schedule. A restore that has never completed is an assumption, even if every uploaded object has a checksum.

The PostgreSQL documentation explains the core mechanism: restore a file-system-level base backup, then replay archived WAL. Logical dumps such as `pg_dump` are valuable for portability and selective recovery, but they are not base backups for WAL replay. Choose the mechanism according to the incident you need to survive.

Start with an explicit recovery contract

RPO answers how much committed data the business can lose. RTO answers how long the service may remain unavailable or degraded. They are product and operational decisions, not numbers a backup tool can invent. A payment ledger may require a smaller RPO than a derived analytics database; a catalog can sometimes be rebuilt from another system of record.

Translate the targets into measurable signals. For RPO, record the timestamp or LSN of the last WAL segment durably stored outside the failure domain and compare it with the database's current WAL position. For RTO, measure discovery, approval, infrastructure creation, backup retrieval, restore, WAL replay, validation and traffic cutover separately. The slowest phase is often data transfer or replay, not the command that starts PostgreSQL.

Define the recovery scope as well. PITR restores an entire PostgreSQL cluster, not one table. If the incident is an accidental update to a small tenant, restoring the full cluster into an isolated environment and exporting corrected rows may be safer than replacing production. The recovery contract should state who chooses the target, who approves promotion and what evidence allows traffic to move.

Build one complete recovery chain

A PITR chain has four inseparable parts: a usable base backup, every required WAL segment after that backup, configuration and secrets needed to retrieve them, and a compatible PostgreSQL runtime. Missing one WAL segment can make later history unreachable. A newer base backup can shorten replay, but it does not repair a broken archive chain for an older target.

PostgreSQL requires `wal_level=replica` or higher and `archive_mode=on` for continuous archiving. The archive command or library must return success only after the segment is durably stored. It should refuse to overwrite a different file with the same name. Monitor `pg_stat_archiver`, the age of the last successful archive, failed archive attempts, archive-storage errors and growth of `pg_wal`. The official documentation warns that repeated archive failure lets `pg_wal` grow until disk pressure can stop the database.

Keep the repository outside the primary database's failure domain. Separate write credentials used by the archiver from broader deletion or retention privileges. Encrypt transport and storage, audit access, and test retrieval with the identity used by the restore environment. Object existence is not proof that the object is readable after a credential, key or policy rotation.

Choose backup cadence from replay time

Base-backup frequency is a trade-off among storage, backup load and recovery time. WAL generated after the chosen base backup must be replayed, so an old full backup may satisfy retention while missing the RTO. Measure restore and replay rates using production-like data, then choose a cadence that leaves headroom for a slower day, network contention and validation.

PostgreSQL 18 supports incremental physical backups through `pg_basebackup --incremental`, but the restore requires the earlier backups in the chain and `pg_combinebackup`. The official PITR guide explicitly notes that PostgreSQL does not track retention dependencies for you. Do not expire a full backup while retained incrementals still depend on it. For smaller clusters, full backups can remain simpler and safer than a deep incremental chain.

Retention must cover the incident-detection window, not only the desired restore point. If corruption is discovered five days late and the repository retains three days, precise recovery is impossible. Keep more than one independent backup generation, and ensure deletion policy cannot remove the last restorable chain after one failed run.

Make backup production and monitoring observable

A backup pipeline should emit facts that an alert can evaluate: backup start and completion time, type, size, source system identifier, start and stop LSN, first and last required WAL, repository, checksum status and expiration relationship. Do not alert only on the scheduler exit code. Alert when no recent valid backup exists, when archive lag exceeds the RPO budget, when repository capacity approaches its limit or when the current backup lacks required WAL.

The pgBackRest guide exposes repository and WAL archive information as JSON, including the last successful backup and latest archived WAL. That is useful for monitoring, but its freshness must be compared with the database state and business target. A dashboard showing a backup timestamp without the WAL boundary can hide a broken recovery chain.

# Illustrative checks; adapt stanza, paths and alert thresholds.
sudo -u postgres pgbackrest --stanza=app --output=json info
sudo -u postgres pgbackrest --stanza=app check

# Verify a plain pg_basebackup against its manifest.
pg_verifybackup --progress /restore/base

`pg_verifybackup` compares files and checksums with the backup manifest and can parse required WAL for plain-format backups. PostgreSQL's own documentation says this is not a substitute for a test restore: verification cannot reproduce every check performed by a running server or prove that the recovered application data is correct. Use it as one gate, not the final definition of recoverability.

Run a restore drill as a deployment

Create an isolated network and fresh storage. Pin the PostgreSQL major version and extensions expected by the backup. Fetch a selected base backup and record its identity. Configure `restore_command`, create `recovery.signal`, and select a recovery target by timestamp, named restore point or another supported target. Block normal application traffic until validation completes.

Recover first to a time just before a known marker transaction, then to just after it. This tests target selection instead of merely replaying to the newest WAL. Record when the drill starts, when the server accepts recovery, when replay reaches the target, when validation finishes and when a hypothetical cutover could occur. Compare the measured RPO and RTO with the contract.

Do not promote the drill environment automatically because PostgreSQL started. A syntactically successful recovery may still use the wrong target, omit an extension, reference an unavailable tablespace or contain data that violates application expectations. Preserve logs and the chosen backup/WAL identifiers so another operator can reproduce the result. Destroy the environment only after evidence has been stored.

Validate from storage to product behavior

Use layered checks. First validate the backup manifest, file ownership, permissions, tablespaces and PostgreSQL startup. Then inspect recovery logs, timeline, final replay position and target action. Run `amcheck` or other database-level integrity checks where appropriate and within an understood performance budget. Confirm extensions, roles and required configuration separately because WAL archiving does not preserve every external configuration file.

Finally test business invariants. Sample important orders, ledger totals, tenant isolation, latest accepted event and background-job checkpoints. Run read-only application smoke tests through the same queries the product uses. If the incident target is known, verify that the harmful transaction is absent while earlier valid transactions are present. A row count alone cannot prove the right state was recovered.

Keep validation deterministic and versioned alongside the application. When the schema changes, update restore assertions in the same release. The drill should fail closed: an unknown extension, missing secret or ambiguous recovery target requires investigation, not an automatic production promotion.

Separate backup recovery from high availability

A replica and a backup solve different failures. Streaming replication can reduce failover time for host loss, but it can quickly reproduce an accidental delete or corrupted application write. PITR provides historical recovery if the archive remains intact. Conversely, PITR may take too long to satisfy a seconds-level availability target. Reliable systems often need both, with separate credentials and failure domains.

A volume snapshot can accelerate restoration, but crash-consistent storage alone does not establish an application-consistent PostgreSQL backup or a complete WAL archive. Confirm the storage provider's semantics and PostgreSQL procedure. Never describe a snapshot schedule as PITR unless the base state, WAL continuity and tested recovery target are all present.

Anti-patterns and when PITR is not enough

Do not store backups on the same disk or account as the primary. Do not allow the database archiver to delete every recovery copy. Do not set a very small `archive_timeout` without understanding storage growth, and do not treat replication lag as archive durability. Avoid untested incremental chains, undocumented tablespaces, mutable backup objects and restore scripts that download “latest” without pinning an identity.

PITR is not the best answer for every restore. Use logical dumps when you need portable, selective objects or cross-version migration. Use an isolated PITR restore plus logical export when repairing a subset after an application error. Use replication or managed failover for rapid infrastructure failure. None replaces a tested plan for credentials, DNS or connection cutover, application consistency and post-recovery observation.

Executive recovery checklist

Name the cluster, owners, RPO, RTO and retention window. Capture base-backup and WAL identities. Monitor last valid backup and archive lag. Protect the repository with separate, least-privilege credentials and immutable retention where appropriate. Verify manifests, but also restore into fresh infrastructure. Test a precise recovery target and business invariants. Measure every phase, preserve evidence and turn the result into an action list.

The strongest PostgreSQL backup metric is not jobs completed. It is the age and duration of the last independently verified recovery. When teams rehearse that path, RPO and RTO become observable engineering properties instead of promises discovered during an incident.

Prepared by: Noor Yasser