Physical Backups with pg_basebackup
Pedal's database has grown to 400 GB. A logical restore now takes six hours, rebuilding every index, and the nightly dump can only give back 02:00. The business wants the server back within an hour, to within minutes of a failure. That needs a physical backup.
The rule
Copy the whole cluster's files, keep the WAL, replay to any moment.
pg_basebackup makes a byte-for-byte copy of the entire cluster: every database, the roles, everything in the data directory. It runs while the server is live and uses the replication protocol, so it needs:
- a role with the
REPLICATIONattribute, and - a
pg_hba.confline withreplicationin the DATABASE column for that role.
pg_basebackup -h db1 -U replicator \
-D /backups/base-2026-09-25 \
-Fp -Xs -P-D: where to write the backup.-Fp: plain format, a ready-to-use copy of the data directory (-Ft -zwould write compressed tar files instead).-Xs: stream the WAL generated during the copy, so the backup is consistent on its own.-P: show progress.
It also writes a backup_manifest listing every file with a checksum, used later to verify the backup.
Because the files are copied as they are, restoring is fast — no index rebuilds — but the backup only works with the same major version and a compatible platform.
Keep the WAL. A base backup alone is one moment again. Turn on WAL archiving and every completed WAL segment is copied somewhere safe:
# postgresql.conf
archive_mode = on # needs a restart
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'Replay to any moment. To recover, restore the base backup, set restore_command to fetch archived WAL and recovery_target_time = '2026-09-25 14:05:00+05:30', create a file named recovery.signal in the data directory and start the server. It replays WAL up to that moment. This is point-in-time recovery (PITR).
In practice, tools such as pgBackRest, Barman and WAL-G wrap these steps with scheduling, retention, compression and cloud storage. They all rest on this same idea. PostgreSQL 17 also added incremental base backups (--incremental), which copy only what changed since an earlier backup.
The shortcut
Picture a timeline. Each base backup is a starting point; the WAL archive is the road forward from it. To reach any moment, you need the last base backup before it and every WAL segment from there to that moment. A missing segment is a gap in the road you cannot cross.
Pick the target
The bad deploy ran at 14:07, so recover to 14:05.
Drills
Answer fast — trust the rule.
What does pg_basebackup copy?
Which two things does pg_basebackup need on the server side?
A base backup taken with PostgreSQL 16 must be restored. Which server can use it?
What makes point-in-time recovery possible?
Remember
- Copy the whole cluster's files, keep the WAL, replay to any moment.
- Needs a REPLICATION role and a replication line in pg_hba.conf; same major version only.
- The last base backup plus every WAL segment after it reaches any moment.