Backup & RecoveryBackup & Recovery

Part of the PostgreSQL DBA Roadmap knowledge base.

Overview

Backup and recovery is arguably the single most consequential responsibility a PostgreSQL DBA carries. Indexing mistakes cost query latency; a botched vacuum strategy costs disk space and bloat; a bad backup strategy costs the business its data, permanently, at the exact moment it can least afford it. Every other topic in this knowledge base is about making a healthy database faster, safer, or more available — this one is about what happens when all of that fails anyway: a dropped table, a bad DELETE without a WHERE clause, a failed storage volume, a ransomware attack, a botched migration. If you cannot restore from that moment, no amount of replication or tuning elsewhere matters.

The central lesson experienced DBAs repeat is that an untested backup is not a backup — it’s an unverified file that might restore. Backups fail silently far more often than they fail loudly: a pg_dump that completes with exit code 0 but was taken against a connection that got interrupted mid-stream, a WAL archive with a silent gap, a base backup taken from a filesystem snapshot that wasn’t actually crash-consistent. The only way to know a backup strategy works is to routinely, mechanically restore from it and verify the result — not to assume that a green checkmark in a cron job log means recoverability.

This note covers the two fundamentally different backup mechanisms PostgreSQL offers — logical (pg_dump/pg_dumpall/pg_restore) and physical (pg_basebackup) — how physical backups combine with continuous WAL archiving to deliver Point-in-Time Recovery (PITR), the mature ecosystem of tools that manage this for you in production (Barman, pgBackRest, WAL-G, pg_probackup), how to actually validate that backups restore, and how pg_upgrade fits into this picture for major version upgrades. It closes with a decision table mapping backup type to RPO/RTO characteristics and tooling. Bulk data loading is covered in ./06-modifying-and-bulk-loading-data.md, the storage layer that WAL and vacuum operate on in ./11-storage-internals-and-vacuum.md, and how backups relate to (and differ from) replication/HA failover in ./13-replication-and-high-availability.md. For the organizational side of what happens when recovery becomes a live incident — communication, decision authority, keeping the business running during the restore — see ../engineering-manager/en/12-crisis-leadership-and-business-continuity.md.

Fundamentals

Logical backups: pg_dump

pg_dump produces a logical backup of a single database: a reconstructable sequence of SQL statements (or an equivalent binary encoding of them) that, when replayed, recreates the schema and data. Because it works at the SQL level rather than the disk-block level, a pg_dump output is portable — it can be restored into a different PostgreSQL major version, a different OS, even a different CPU architecture, which physical backups categorically cannot do.

pg_dump supports several output formats, and the choice matters operationally:

# Plain SQL format — human-readable, restorable with psql, easiest to inspect/edit
pg_dump -h localhost -U postgres -d shop_db -F p -f shop_db.sql

# Custom format (-Fc) — compressed, and the only format compatible with pg_restore's
# selective/parallel restore features; the recommended default for most use cases
pg_dump -h localhost -U postgres -d shop_db -F c -f shop_db.dump

# Directory format (-Fd) — one file per table plus a TOC, enables parallel DUMP
# via -j in addition to parallel restore
pg_dump -h localhost -U postgres -d shop_db -F d -j 4 -f shop_db_dir/

Plain SQL format is the simplest to reason about and diff, but it dumps and restores single-threaded and offers no selective-restore capability beyond editing the SQL text by hand. Custom format (-Fc) is compressed by default, does not immediately execute anything (it’s an archive pg_restore interprets), and is the format most DBAs reach for as the default: it supports selective restore (specific tables, specific schemas), reordering, and parallel restore. Directory format (-Fd) goes one step further and also parallelizes the dump itself (-j N), which matters when the database is large enough that a single-threaded dump takes hours — each table is dumped to its own file concurrently.

A pg_dump of a single large table or database can be slow, and there’s a subtlety worth understanding: pg_dump takes a single consistent snapshot of the database at the start of the dump (it runs inside one transaction using REPEATABLE READ isolation internally), so the dump is transactionally consistent even though it may take hours to complete — later writes to the database during the dump simply aren’t included, and the dump doesn’t see a half-committed intermediate state. What it does not give you is a specific recoverable point in time after the fact; you get exactly one snapshot, the one taken when the dump began, and nothing more granular.

Logical backups: pg_dumpall

pg_dump operates on a single database and, deliberately, does not capture cluster-wide (instance-wide) objects — roles, tablespaces, and other objects that live outside any one database. pg_dumpall fills that gap:

# Dump ALL databases in the cluster plus roles/tablespaces, in plain SQL
pg_dumpall -h localhost -U postgres -f full_cluster.sql

# Common pattern: dump only the globals (roles + tablespaces), then pg_dump
# each database separately in custom format for selective/parallel restore
pg_dumpall -h localhost -U postgres --globals-only -f globals.sql
pg_dump -h localhost -U postgres -d shop_db -F c -f shop_db.dump
pg_dump -h localhost -U postgres -d analytics_db -F c -f analytics_db.dump

pg_dumpall only produces plain SQL output — it has no custom/directory format and therefore no parallel dump or selective restore. In practice, most production setups use the hybrid pattern above: pg_dumpall --globals-only to capture roles and tablespace definitions once, plus a separate pg_dump -Fc per database so each database can be restored independently and in parallel.

Restoring with pg_restore

pg_restore is the counterpart to custom (-Fc) and directory (-Fd) format dumps — plain SQL dumps are restored with psql instead, since they’re just SQL scripts.

# Restore a custom-format dump into a fresh, already-created database
createdb -h localhost -U postgres shop_db_restored
pg_restore -h localhost -U postgres -d shop_db_restored -j 4 shop_db.dump

# Restore only a subset of objects — e.g. one table — from the same dump
pg_restore -h localhost -U postgres -d shop_db_restored \
  -t orders --data-only shop_db.dump

# List the table of contents of a dump without restoring anything
pg_restore -l shop_db.dump

The -j (parallel jobs) flag is the main reason custom/directory format is preferred over plain SQL for anything beyond a small database: pg_restore -j 4 restores multiple tables’ data and rebuilds multiple indexes concurrently, which can turn a restore that would take hours single-threaded into one that takes a fraction of that. Selective restore — pulling one table, one schema, or data-only/schema-only — is also only possible against custom or directory format archives, using the table of contents (pg_restore -l) to pick exactly what you need.

When logical backups are (and aren’t) the right tool

Logical backups are the right choice when: the database is small-to-medium sized and a full dump/restore cycle fits comfortably in your maintenance window; you’re migrating between PostgreSQL major versions (a logical dump/restore is version-independent, unlike a physical copy which is tied to the on-disk format of one major version); you need to restore or copy just one table or schema rather than the whole cluster; or you want a portable, inspectable artifact you can diff or version-control (for schema-only dumps).

Their limitation is restore speed at scale. Restoring a pg_dump means re-executing INSERTs (or COPYs) row by row and then rebuilding every index from scratch — for a multi-hundred-gigabyte database this can take many hours even with -j parallelism, because index builds and constraint validation are CPU- and I/O-bound work that scales with data volume regardless of format. This is the core reason production systems with large databases lean on physical backups for their primary recovery path, and reserve logical backups for portability, migrations, and small/selective use cases.

Physical backups: pg_basebackup

A physical backup is a byte-for-byte copy of the PostgreSQL data directory — the actual files PostgreSQL uses to store heap pages, indexes, and WAL — rather than a logical reconstruction of the data through SQL. pg_basebackup takes such a copy over the replication protocol while the server keeps running and accepting writes, and it handles the tricky part for you: ensuring the copy is consistent even though files are being copied while concurrently being modified.

# Take a physical base backup, in compressed tar format, with a matching
# WAL segment stream so the backup is immediately usable on its own
pg_basebackup -h localhost -U replicator \
  -D /var/backups/pgbase/$(date +%Y%m%d) \
  -Ft -z -Xstream -P

# Plain directory format instead of tar, useful when you want to inspect
# or directly start PostgreSQL against the copied files
pg_basebackup -h localhost -U replicator \
  -D /var/backups/pgbase_plain \
  -Fp -Xstream -P

Key flags: -Ft produces a .tar.gz-style archive per tablespace (compact for transfer/storage); -Fp produces a plain directory tree that mirrors a real data directory (immediately startable). -Xstream streams the WAL generated during the backup alongside the base backup itself, which is what makes the backup usable in isolation without depending on a separately configured WAL archive being reachable at restore time. -P shows progress. The connecting role needs the REPLICATION privilege (or membership in pg_read_all_settings/appropriate roles depending on version), and pg_hba.conf must permit a replication connection from the backup host.

Internally, pg_basebackup works by calling pg_start_backup()-equivalent logic (via the replication protocol, not the deprecated SQL functions) to record a starting WAL location, streaming the actual data files, and then calling the stop-backup equivalent to record an ending WAL location — because files are copied while writes continue, some pages may be copied in an inconsistent, torn state, and it’s the WAL segments generated between start and stop that let PostgreSQL replay changes at startup and heal any such torn pages into a fully consistent state. This is why a physical backup is only valid together with the WAL generated during (and, for PITR, after) the backup — you cannot treat the raw file copy alone as trustworthy without that WAL.

Why physical backups restore so much faster

Restoring a physical backup is mechanically simple: copy the files onto disk in the right location, adjust configuration (postgresql.conf, recovery.signal if replaying WAL), and start PostgreSQL. There is no need to re-run millions of INSERT statements or rebuild indexes from scratch — the on-disk B-tree pages, heap pages, and everything else are already in their final physical form, exactly as they existed on the source server. For a large database, this is the difference between a restore measured in minutes (dominated by file copy I/O throughput) versus one measured in many hours (dominated by CPU-bound SQL replay and index construction). This speed advantage is the primary reason physical backups — not logical ones — are the backbone of recovery strategy for any database large enough that restore time matters.

Key Concepts

Point-in-Time Recovery (PITR)

A single base backup only gets you back to the moment it was taken. Point-in-Time Recovery combines a base backup with a continuous, unbroken stream of archived WAL segments to let you replay forward from that base backup to any point in time after it — down to a specific timestamp, a specific transaction ID, or a specific LSN (log sequence number). This is the capability that answers the DBA’s most dreaded page: “someone ran DELETE FROM orders WHERE 1=1 five minutes ago — can we get everything back from right before that?” With PITR configured, the answer is yes: restore the most recent base backup, replay WAL up to (but not including) the offending transaction, and you have a database exactly as it stood one transaction earlier.

The mechanism has two moving parts that must both be present and unbroken:

1. A base backup (from pg_basebackup, or from a backup tool built on the same underlying mechanism) taken at some point in time.
2. Continuous WAL archiving from that point forward — every WAL segment PostgreSQL generates is copied to durable, separate storage as it’s produced, with no gaps.

Configure continuous archiving via archive_mode and archive_command in postgresql.conf:

# postgresql.conf
wal_level = replica          # or logical; must be at least 'replica' to archive
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'
# a real deployment would use a tool-managed command instead of a bare cp,
# e.g. pgBackRest's or Barman's own archive_command wrapper

To perform a PITR restore: copy the base backup into the data directory, create a recovery.signal file (PostgreSQL 12+; older versions used recovery.conf), and configure the recovery target in postgresql.conf:

# postgresql.conf, after restoring the base backup files
restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2026-07-19 14:32:00+07'
recovery_target_action = 'promote'   # or 'pause' to inspect before continuing

touch /var/lib/postgresql/17/main/recovery.signal
systemctl start postgresql
# PostgreSQL replays archived WAL from the base backup forward until it
# reaches recovery_target_time, then stops and promotes to a normal server

You can target recovery by timestamp (recovery_target_time), by a specific transaction ID (recovery_target_xid), by a named restore point created in advance with pg_create_restore_point(), or by a specific LSN (recovery_target_lsn). This flexibility — replay to any point, not just the moment of a backup — is why PITR is considered the gold-standard recovery capability, and why every production PostgreSQL deployment that cares about its data should have it configured, not merely periodic full backups. Without WAL archiving, your recovery granularity is limited to “whichever full backup happens to exist,” which for a database backed up nightly could mean losing up to 24 hours of transactions; with PITR, your recovery granularity is limited only by how far back your retained WAL archive reaches.

Barman

Barman (“Backup and Recovery Manager”) is a mature, Python-based tool from 2i2c/EnterpriseDB that runs on a dedicated backup server and manages the entire backup lifecycle for one or more PostgreSQL servers: scheduling and taking base backups (via pg_basebackup-equivalent streaming or rsync), continuously receiving/archiving WAL (via pg_receivewal streaming or an archive_command push), enforcing retention policies, and orchestrating PITR restores.

# barman.conf server stanza (on the Barman host)
# [pg-primary]
# conninfo = host=primary-db user=barman dbname=postgres
# streaming_conninfo = host=primary-db user=streaming_barman
# backup_method = postgres
# archiver = on
# streaming_archiver = on
# path_prefix = /usr/lib/postgresql/17/bin

# Take a full base backup
barman backup pg-primary

# List available backups for a server
barman list-backup pg-primary

# Verify WAL archiving is uninterrupted and check overall server health
barman check pg-primary

# Restore a specific backup (optionally to a PITR target) onto a target host
barman recover pg-primary 20260719T060000 /var/lib/postgresql/17/main \
  --target-time "2026-07-19 14:32:00+07" --remote-ssh-command "ssh postgres@restore-host"

Barman centralizes backup storage on a separate host from the database server itself, which is a real resilience advantage — the backup survives even if the primary’s own disks are lost entirely. It’s been in production use for well over a decade and has a straightforward, well-documented retention policy model (retention_policy = RECOVERY WINDOW OF 30 DAYS, for example).

pgBackRest

pgBackRest is the tool most new production PostgreSQL deployments reach for today, and its adoption has been steadily overtaking Barman’s for a few concrete reasons: true parallel backup and restore across multiple processes/threads (not just parallel WAL push), built-in compression (multiple algorithms, including zstd) and built-in encryption at rest, native incremental and differential backups that only capture changed blocks/files since a prior backup (dramatically shrinking both backup time and storage footprint on large databases), and first-class support for pushing backups directly to S3, Azure Blob Storage, and GCS without a separate sync step.

# /etc/pgbackrest/pgbackrest.conf
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
process-max=4
compress-type=zst

[main]
pg1-path=/var/lib/postgresql/17/main
pg1-port=5432

# One-time repository initialization
pgbackrest --stanza=main stanza-create

# Full backup
pgbackrest --stanza=main --type=full backup

# Subsequent incremental backup (only changed blocks since the last backup)
pgbackrest --stanza=main --type=incr backup

# Restore, optionally to a specific point in time
pgbackrest --stanza=main --type=time \
  --target="2026-07-19 14:32:00+07" restore

process-max controls how many parallel workers pgBackRest uses for backup/restore/archive-push, which is where most of its speed advantage over Barman comes from on multi-core hosts with fast storage. Incremental backups in particular change the operational math for large databases: instead of a full multi-hundred-gigabyte backup every night, you can run one full backup weekly and small, fast incrementals daily, cutting both backup window and storage cost substantially.

Barman vs. pgBackRest

Both tools are legitimate, production-grade choices and both fully support PITR against the same underlying PostgreSQL mechanism described above; the difference is mostly about performance at scale, feature breadth (encryption, native cloud storage, incrementals), and which ecosystem your team is already invested in. For new deployments, especially anything cloud-hosted or large enough that backup/restore time is a real constraint, pgBackRest is the more common recommendation today.

WAL-G

WAL-G is a lighter-weight, Go-based backup tool built specifically with cloud-native deployments in mind. It focuses on a narrower scope than Barman or pgBackRest — efficient physical base backups and WAL archiving with direct, first-class integration into S3, Google Cloud Storage, and Azure Blob Storage as the archive target, with no intermediate local staging required.

# Environment-based configuration (S3 example)
export WALG_S3_PREFIX=s3://my-backups-bucket/pg
export AWS_ACCESS_KEY_ID=...
export AWS_SECRET_ACCESS_KEY=...

# Push a base backup directly to S3
wal-g backup-push /var/lib/postgresql/17/main

# archive_command in postgresql.conf pushes each WAL segment as it's produced
# archive_command = 'wal-g wal-push %p'

# Restore the latest backup
wal-g backup-fetch /var/lib/postgresql/17/main LATEST
wal-g wal-fetch %f %p   # used as restore_command during PITR

WAL-G is popular in Kubernetes-based and other cloud-native PostgreSQL setups (it’s the backup engine behind several PostgreSQL operators) precisely because it treats object storage as the primary, native destination rather than an add-on, and because its smaller scope keeps operational overhead low. It supports PITR through the same base-backup-plus-WAL-replay mechanism, but offers less in the way of centralized retention-policy management and monitoring than Barman or pgBackRest.

pg_probackup

pg_probackup is another dedicated backup and recovery manager, originally developed by Postgres Professional, aimed at the same core problem as pgBackRest: efficient full/incremental/differential physical backups with built-in compression, parallel operation, and PITR support, plus backup validation and catalog management commands. It has a particularly strong following in ecosystems where Postgres Professional’s PostgreSQL fork and tooling are common (notably some Russian and CIS-region deployments), and it’s a reasonable alternative to evaluate alongside pgBackRest, though it has a smaller international community and ecosystem than pgBackRest today. Functionally the two tools solve overlapping problems; the choice between them is often more about ecosystem fit and existing familiarity than a decisive technical gap.

Best Practices

Never trust a backup you haven’t restored

The single most important best practice in this entire domain is routine, automated backup validation: periodically — weekly is a reasonable cadence for most production systems — spin up a scratch instance (a disposable VM, container, or ephemeral cloud instance), restore the latest backup (and, ideally, exercise a PITR restore to a recent point, not just “restore the base backup”) onto it, run integrity checks (pgbackrest ... --stanza=main check, barman check, a simple SELECT count(*) against known tables, or application-level smoke tests), and alert loudly if anything fails. Both Barman (barman check) and pgBackRest (built-in WAL segment verification, plus pgbackrest verify in newer versions) provide tooling to help, but the only test that actually proves recoverability is a real restore followed by a real query. A backup job that “succeeds” every night for a year, discovered to have been silently broken since month two, is a worse outcome than no backup at all — it created false confidence that delayed building a real safety net.

Keep WAL archiving unbroken and monitored

PITR is only as good as its weakest link, and the most common way PITR silently breaks is a gap in the WAL archive — a full disk on the archive destination, a transient network failure to S3 that isn’t retried, a misconfigured archive_command that fails quietly. Monitor pg_stat_archiver for failed archive attempts, alert on archiving lag, and make sure your retention policy never deletes a WAL segment that a still-valid base backup depends on (all the backup tools above manage this dependency for you if configured correctly — a bare hand-rolled archive_command + cp setup does not).

Store backups off the primary, and ideally off-site/off-cloud-account

A backup that lives on the same disk, same host, or even same cloud account/region as the primary database defends against a much narrower set of failures than one stored genuinely elsewhere. Barman’s model of a separate backup host, or pgBackRest/WAL-G pushing to a separate object storage bucket (ideally in a different account or with independent access credentials, to survive a compromised primary), protects against hardware failure, accidental deletion, and a compromised or ransomed primary host alike.

Match retention policy to actual recovery requirements, not habit

Decide retention (how many full backups, how far back PITR should reach) based on real business requirements for RPO (how much data loss is acceptable) and compliance/audit needs, not an arbitrary “keep everything forever” or “keep one week” default. Longer WAL retention costs storage; too-short retention means a slow-to-discover issue (a corruption introduced weeks ago, a compliance audit reaching back further than expected) becomes unrecoverable.

Take a full backup — and test it — before any major version upgrade

pg_upgrade performs an in-place major version upgrade (e.g., PostgreSQL 16 to 17) by linking or copying the underlying data files and converting the system catalogs to the new version’s format, rather than dumping and reloading every row through SQL. In --link mode it uses hard links between the old and new data directories, making the upgrade itself extremely fast — proportional to catalog conversion work, not to data volume — which is why pg_upgrade is standard practice for large databases where a full pg_dumpall/reload would take unacceptably long. The trade-off is that --link mode ties the old and new clusters’ data files together until you explicitly complete and clean up the old cluster, so a failed or interrupted upgrade can leave both clusters unusable if you aren’t careful (--clone or --copy modes avoid this by not sharing files, at the cost of needing double the disk space and more time).

# Always run --check first: validates compatibility without touching data
/usr/lib/postgresql/17/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/16/main \
  --new-datadir=/var/lib/postgresql/17/main \
  --old-bindir=/usr/lib/postgresql/16/bin \
  --new-bindir=/usr/lib/postgresql/17/bin \
  --check

# Then run the real upgrade (add --link for hard-link mode on large databases)
/usr/lib/postgresql/17/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/16/main \
  --new-datadir=/var/lib/postgresql/17/main \
  --old-bindir=/usr/lib/postgresql/16/bin \
  --new-bindir=/usr/lib/postgresql/17/bin \
  --link

Because pg_upgrade operates directly on data files and catalogs, a bug, a missed extension incompatibility, or an interrupted run can corrupt or strand a cluster in a way that’s much harder to reason about than a clean logical dump/restore failure. The standard precaution, without exception, is: take a full, verified physical or logical backup immediately before running pg_upgrade, and rehearse the exact upgrade procedure against a restored copy of production (not a toy test database) before touching the real cluster. Treat pg_upgrade --check as necessary but not sufficient — it catches catalog-level incompatibilities, not every possible operational surprise.

Backup strategy decision table

References

• PostgreSQL Documentation — Chapter 26: Backup and Restore
• PostgreSQL Documentation — Continuous Archiving and Point-in-Time Recovery (PITR)
• PostgreSQL Documentation — pg_basebackup
• PostgreSQL Documentation — pg_dump
• PostgreSQL Documentation — pg_upgrade
• pgBackRest Documentation
• Barman Documentation
• WAL-G GitHub Repository