PostgreSQL Disaster Recovery: Backup, Restore, and PITR

A PostgreSQL backup plan is complete only when it can restore the required data within an agreed recovery time and loss window. Logical dumps, physical base backups, archived WAL, replicas, and third-party backup managers solve different problems. Use more than one layer where the failure modes demand it, and prove the design with isolated restore drills.

Define recovery requirements and failure scope

RTO is the maximum acceptable time to restore service. RPO is the maximum acceptable loss of committed work. Define both with the business owner, then list the failures the design covers: a dropped table, bad deployment, storage loss, Region loss, credential compromise, backup-repository loss, and loss of operators or control-plane access. One number rarely fits every database.

Measure recovery from incident declaration through application validation, not merely the time a PostgreSQL process starts. RPO must be verified from restored transactions and archive continuity, not inferred from a schedule name.

Choose the right backup layer

Logical backups with pg_dump

pg_dump creates a consistent logical export of one database while it is in use. Directory format supports parallel dump and parallel restore. Include cluster globals separately when recovery needs roles or tablespace definitions.

# Write to a new protected directory; let pg_dump prompt or use a secured service file
pg_dump --format=directory --jobs=4 \
  --file=/secure/backups/appdb-dir appdb

# Inspect the archive without restoring
pg_restore --list /secure/backups/appdb-dir

# Capture cluster globals separately
pg_dumpall --globals-only --file=/secure/backups/cluster-globals.sql

Parallel jobs open multiple connections and use synchronized snapshots on supported servers. Coordinate schema changes during the dump because conflicting locks can abort a worker. A row count or successful file write does not prove restorability. Restore into an isolated database, check extensions and ownership, run integrity and application queries, and measure the result.

Physical base backups and manifests

pg_basebackup uses the replication protocol to copy an entire cluster. With streamed WAL, its output can be standalone for crash recovery; with a continuous archive, the base backup can be the starting point for PITR.

pg_basebackup \
  --host=primary.example.internal \
  --username=backup_agent \
  --pgdata=/secure/backups/base-restore-test \
  --format=plain \
  --wal-method=stream \
  --progress

pg_verifybackup /secure/backups/base-restore-test

Use least-privilege replication credentials, TLS, repository encryption, and protected credential handling. A fast checkpoint can increase I/O pressure, so do not request one automatically. Monitor pg_stat_progress_basebackup, WAL retention, storage, and the impact on replicas and the primary.

Current PostgreSQL can create backup manifests and supports built-in incremental physical backups when WAL summarization and the required backup chain are available. Incremental recovery adds dependencies and requires pg_combinebackup; it is not a reason to delete the full backups or manifests on which later increments depend.

Continuous WAL archiving for PITR

Point-in-time recovery combines a physical base backup with every required WAL segment generated from the start of that backup through the target. Configure archiving before taking the base backup and prove that it works.

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'backup_wal "%p" "%f"'

The archive program must copy atomically to durable storage, be idempotent for an already archived segment, verify success, and return zero only when the destination copy is safe. Prefer a maintained backup tool or a reviewed script over a compound shell one-liner. PostgreSQL retries failed archive operations; prolonged failures can fill pg_wal, so alert on local space and archive health.

SELECT archived_count,
       last_archived_wal,
       last_archived_time,
       failed_count,
       last_failed_wal,
       last_failed_time
FROM pg_stat_archiver;

A quiet database may not complete a WAL segment promptly. archive_timeout can encourage periodic segment switches, but it can also increase archive volume, and it does not by itself guarantee an RPO. Measure the end-to-end time until WAL is durable and retrievable from the recovery location.

Recover to an isolated target first

1. Declare the incident, preserve evidence, and stop writes or fence the failed primary as the runbook requires.
2. Select a known-good base backup and confirm that its full dependency chain, manifests, tablespaces, encryption keys, and WAL are available.
3. Provision a clean, compatible target. Do not erase the only copy of the failed data directory.
4. Restore the base backup, create recovery.signal, and configure a tested restore_command plus the required recovery_target_time, transaction ID, LSN, or named restore point.
5. Start recovery and watch the PostgreSQL log. Validate the reached timeline and target before promotion.
6. Run schema, extension, ownership, row, business-invariant, and application smoke checks.
7. Promote and redirect clients only after the incident owner accepts the recovery point. Fence any former primary to prevent split brain.

Replication improves availability, not backup independence

Streaming replication is asynchronous by default, so transactions acknowledged on the primary may be absent from a promoted standby. Synchronous replication waits for configured standbys and improves durability at the cost of commit latency and possible loss of write availability. synchronous_commit=remote_apply additionally waits for replay and visibility on the standby; it is not a universal requirement and does not protect against application deletion, compromised credentials, or a mistake replicated to every node.

Test failover, client reconnection, fencing, timeline following, and rebuilding the old primary. Retain off-cluster, access-controlled backups even when synchronous standbys exist.

Verification and retention

• Verify checksums and manifests on receipt and during repository scrubs.
• Restore regularly into an isolated network with production-like PostgreSQL binaries, extensions, tablespaces, and encryption access.
• Validate the newest required transaction, critical constraints, extensions, sequences, ownership, and application behavior.
• Measure achieved RTO/RPO and archive gaps; record the exact backup and WAL set used.
• Use immutable or separately administered retention where the threat model requires it.
• Keep the runbook and credentials available when the primary account or control plane is unavailable.

Official primary sources

• PostgreSQL backup and restore
• pg_dump
• pg_basebackup
• pg_verifybackup
• Continuous archiving and PITR
• Streaming and synchronous replication
• pgBackRest user guide
• WAL-G PostgreSQL documentation

Working with JusDB on PostgreSQL recovery

JusDB helps teams map recovery requirements to backup layers, harden WAL archives, automate verification, and rehearse restores and failovers.

Explore JusDB PostgreSQL services →  |  Talk to a PostgreSQL engineer