Guide

pg_dump vs pg_basebackup vs PITR

Three different backups that recover three different disasters. Most setups need two of them, and almost nobody has thought about which two.

The comparison

Two rows do most of the deciding. Logical is the only one that crosses versions and machines — a dump taken from Postgres 15 restores into 17, on a different architecture, on a different provider. Physical is the only one that gets your RPO under a day, because only WAL can replay what happened between backups.

pg_dump — the portable copy

A logical dump connects like any other client and writes out the statements needed to rebuild one database. It runs against a live server, takes a consistent snapshot of the instant it starts, and does not block writes.

pg_dump -Fc -d "$DATABASE_URL" -f mydb.dump
pg_dumpall --globals-only -d "$DATABASE_URL" -f globals.sql

Three properties matter, and they are the reason this is the backup you should have even when you have another one:

• It is portable. Restorable into a newer major version, another host, another cloud, your laptop. Physical backups are none of those things.
• It is granular. One table back, without touching the rest of the cluster: pg_restore -t orders.
• It is independent of your provider. A managed platform's PITR lives inside that platform. If the account is suspended, the region is down, or the project is deleted, the dump in your own bucket is what you still have.

What it does not do: pg_dump covers a single database and skips cluster-wide objects. Roles, and the passwords and grants attached to them, come from pg_dumpall --globals-only — leave it out and the restore fails with "role does not exist". And the client version rule is strict: pg_dump must be at least the server's major version (the version rule in detail). Which archive format to pick is its own decision.

pg_basebackup — the cluster clone

A physical backup is a byte-level copy of the data directory, taken over the replication protocol. It needs a role with the REPLICATION attribute and a server with wal_level at replica or higher and a spare max_wal_senders slot:

pg_basebackup -h db.internal -U replicator -D /backup/base -Ft -z -P

# -X stream (the default) ships the WAL generated during the copy,
# which is what makes the result self-consistent and startable

The output is a cluster, not a database. You restore it by pointing a Postgres server of the same major version, built for the same platform at those files and starting it. There is no partial restore: no single table, no single database, no cross-version jump.

On its own, a base backup gives you exactly the same RPO as a nightly dump — you lose everything since it was taken. Its real job is to be the starting point for the third option, and to seed replicas.

PITR — base backup plus every WAL segment since

Point-in-time recovery is a base backup plus a continuous archive of the write-ahead log. Postgres restores the files, then replays WAL forward and stops exactly where you tell it:

# postgresql.conf on the primary
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'

# on the restored copy: recovery settings + a recovery.signal file
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-09-07 14:32:00'

That last line is the whole point. It is the only backup that answers "put the database back to one minute before the migration ran" — the failure mode that actually kills companies, because a bad DELETE at 14:33 is faithfully copied to every replica within milliseconds.

Do not hand-roll this in production. pgBackRest, Barman and WAL-G exist because retention, compression, parallelism, encryption and archive verification are all things the two-line cp above does badly. They take the base backup and manage the archive for you, and pgBackRest and Barman will verify checksums on the stored backup.

Note that recovery_target_time and friends belong in the configuration file on PostgreSQL 12 and later; the old recovery.conf was removed in 12, replaced by a recovery.signal file next to the normal config.

Two things that are not backups

• A replica. Streaming replication protects against a server dying. It replicates your mistakes perfectly and instantly. High availability and backup are different problems with different solutions.
• A filesystem snapshot of a running $PGDATA, unless the snapshot is atomic across every volume the cluster touches. Copy the data directory with cp or tar while the server is running and you get a torn, unstartable copy — the file-level route requires pg_backup_start() / pg_backup_stop() and the WAL generated in between. This is the trap behind most "we back up the Docker volume" setups.

How to choose

• Managed host (Supabase, Neon, Railway, Render), database under ~50 GB: nightly pg_dump -Fc plus --globals-only, shipped to a bucket you own. Your provider's PITR, where the plan includes it, is a fine primary — the dump is the copy that survives losing the account.
• Self-hosted, RPO measured in hours is acceptable: nightly logical dumps, offsite, tested. Simplest thing that works.
• Self-hosted, RPO in minutes, or a cluster large enough that a logical restore would take longer than the outage you can afford: pgBackRest or Barman for PITR, plus a periodic logical dump. The dump is your version-upgrade path and your single-table rescue; PITR is your clock.
• Choosing only one and you are not sure: take the dump. It recovers the fewest scenarios but it is the one that still works when everything else about your infrastructure has changed.

Whichever you pick, the file or the archive is a hypothesis until something has restored it. PITR setups fail on a broken archive_command silently filling pg_wal; dumps fail on missing roles and version drift. The only way to know is the drill.

Related fixes: How to actually test a Postgres backup pg_dump formats compared pg_dumpall vs pg_dump: what a per-database dump leaves behind pg_restore: role "..." does not exist pg_dump hangs — and takes your application with it