Physical Backups and Point-in-Time Recovery

A physical backup is a byte-level copy of the data directory taken while the cluster keeps serving traffic, plus every WAL segment written from that moment onward. Neither half is a backup on its own. The copied files are inconsistent with each other by construction, because they were read over minutes while the database changed underneath; the WAL is what makes them consistent again, and then carries them forward to any moment you name.

That pair is the entire difference between "restore last night and lose the day" and "restore to 14:11". Point-in-time recovery is roughly a dozen lines of configuration and the baseline for any database holding orders or money. Cartwheel did not have it for the same reason most teams do not: nothing had gone wrong yet, so nothing had proved it was missing.

The Two Halves

The base backup comes from pg_basebackup, or from a tool that speaks the same replication protocol to the server. It copies the whole cluster, always the whole cluster and never a single database, without affecting other clients, and the result can start a recovery or become a standby. By default it streams the WAL generated during the run alongside the files, so the backup is self-contained enough to reach consistency on its own. It connects as a role with the REPLICATION attribute or as a superuser, needs a matching pg_hba.conf line, and needs max_wal_senders high enough for the backup plus the WAL stream.

pg_basebackup -h pg-primary -U backup_role \
    -D /backup/base/2026-08-13 \
    -Ft -z \
    -X stream \
    --checkpoint=fast \
    --progress

# -Ft -z            tar format, compressed
# -X stream         the WAL written during the run comes with it
# --checkpoint=fast start now rather than spreading the checkpoint

The second half is continuous archiving: every completed 16 MB segment copied somewhere durable as soon as the server finishes with it. Recovery is then one sentence: restore the base, replay the archive up to the chosen moment. Every moment other than the end of the backup itself is reachable only because those segments exist. A base backup with no archive gives you last night and nothing else. An archive with no base gives you a very detailed description of changes to a database you no longer have.

Archiving the WAL

Archiving is turned on with archive_mode = on, which takes effect only at server start and cannot be enabled at all while wal_level is minimal. What actually moves the file is either an archive_command, a shell command receiving %p for the segment's path and %f for its name, or an archive_library — the loadable archive modules that arrived in 15, and if you set one the command must be empty. Both must satisfy two rules that a careless one-liner breaks.

The first rule is that the command must return zero if and only if it succeeded. A zero tells Postgres the segment is safe, and Postgres then recycles the file; a nonzero tells it to try again later. A script that ends in an unconditional exit 0, or a pipeline whose exit status belongs to the last stage rather than to the copy, reports success for every failure, and the segment is recycled with no copy of it anywhere. The second rule is that the command should refuse to overwrite a file already in the archive, which is why the manual's own example tests for the file's absence before copying: it is what protects the archive from a mistaken re-run.

archive_mode = on          # takes effect at server start only
archive_command = 'test ! -f /archive/wal/%f && cp %p /archive/wal/%f'
archive_timeout = 60       # switch segments even when writes are slow

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

 archived_count | last_archived_time  | failed_count | last_failed_wal | ...
----------------+---------------------+--------------+-----------------+----
        4188271 | 2026-08-13 14:09:41 |            0 |                 |

archive_timeout forces a switch to a new segment after the given interval even when the current one is nowhere near full, which bounds how much recent work is sitting unarchived on a quiet cluster, a real consideration at 04:00 and irrelevant at Saturday peak. pg_stat_archiver is where a broken archive is caught, and two alerts are enough: a failed_count that is climbing, and a last_archived_time that has stopped moving. Neither of those numbers appears anywhere on the write path, so nothing else in the cluster changes behaviour when the archive stops.

Performing a Recovery

The procedure has a fixed shape. Stop the server. Keep a copy of the damaged data directory, because it holds the WAL segments that were never archived, and those are the difference between recovering to 14:11 and recovering to the last segment that made it out. Empty the target directories, restore the base backup into them, clear pg_wal, and copy the unarchived segments back in. Then set the recovery parameters, create the signal file, and start the server.

# in postgresql.conf of the restored directory
restore_command = 'cp /archive/wal/%f %p'
recovery_target_time = '2026-08-13 14:11:00+00'
recovery_target_action = 'pause'      # the default: stop and let a human look

$ touch /var/lib/postgresql/18/main/recovery.signal
$ pg_ctlcluster 18 main start

recovery.signal is the file that puts the cluster into archive recovery. It replaced recovery.conf in 12, and the replacement was not gentle: a data directory that still contains a recovery.conf refuses to start rather than ignore it, which is exactly the behaviour you want from a change this consequential. The server replays forward from the backup's start position until it reaches the target, then does what recovery_target_action says. The default is pause, and the pause is the feature: the cluster is up and read-only, nothing is committed to the decision, and this is the moment to count rows in orders, confirm the 90,000 are back, and confirm the 14:12 statement is not. Only then does pg_wal_replay_resume() finish the recovery and open it for writes.

The procedure comes with two constraints. The target has to fall after the base backup finished, so a nightly base at 02:00 covers everything from 02:00 onward and nothing before it; recovering to 01:30 is last night's base backup's job. And completing a recovery starts a new timeline, recorded in a small history file alongside the segments in the archive. That is why recovery_target_timeline defaults to latest and why a second recovery attempt can branch off the first without either one destroying the other's history.

Choosing the Target

There are five ways to say where to stop, and they are not equally sharp. recovery_target_time is the intuitive one and the fuzziest, because you are guessing at the second a statement ran. recovery_target_lsn and recovery_target_xid are exact when the log or an audit trail identifies the offending transaction. recovery_target_name stops at a marker somebody placed deliberately. And recovery_target = 'immediate' stops as soon as the restored backup is consistent, which is the right answer when all you want is last night's copy on a scratch host. recovery_target_inclusive, on by default, decides whether the target itself is included or excluded.

SELECT pg_create_restore_point('before_orders_status_backfill');

 pg_create_restore_point
-------------------------
 3A/BC0001F8

-- and if the backfill goes the way the 14:12 one did:
recovery_target_name = 'before_orders_status_backfill'

That function writes a named marker into the WAL and returns its LSN; it is restricted to superusers unless the privilege is granted explicitly. One statement at the top of a migration script converts "recover to about 14:11, we think" into "recover to the marker", and it costs nothing on the day nothing goes wrong. The marker is placed while everyone is calm; the recovery parameters get looked up under pressure.

Tools That Do This Properly

pgBackRest, Barman and WAL-G are not part of PostgreSQL and are the standard answer anyway. What they add is everything that turns a mechanism into a strategy: retention policies that expire old backups correctly, parallel and incremental backups, compression and encryption, verification as a scheduled job, and a restore that is one command instead of a nine-step checklist executed by whoever is awake. Hand-rolled archiving works exactly as long as nothing unusual happens, and the first unusual thing is normally the incident.

Core Postgres has closed part of the gap. Since 17, pg_basebackup --incremental takes the manifest of an earlier backup and sends only the blocks that changed, and pg_combinebackup reconstructs a synthetic full backup from the full plus its chain of incrementals, specified oldest to newest. Two traps come with it. The WAL summarizer that makes it possible, summarize_wal, is off by default, and its summaries are kept for ten days unless you say otherwise, so an incremental whose reference backup is older than that simply fails. And every backup in the chain must survive: delete one and everything after it is unrestorable. pg_combinebackup checks only that the backups relate to each other correctly, not that any of them is intact; pg_verifybackup is the one that reads the manifest, notices missing or extra files, checksums everything, and parses the WAL the backup will need.

Retention, Storage, and the Recovery Point

Cartwheel's shape after this chapter is a nightly base backup plus continuous WAL archiving with a seven-day window. Seven is not a technical number. It says that a mistake noticed within a week is recoverable and a mistake noticed in the ninth week is not, which is a decision about the business rather than about the database, and it belongs in writing somewhere the person on call can find it.

Two properties of the archive matter as much as the schedule does. Base backups must stay fresh, because a cluster that has not taken a new one since spring recovers by replaying months of WAL, and that replay takes longer than the outage it was supposed to repair. And the backups must not share a failure domain with the database: a copy on the same volume dies with the volume, and a copy in the same cloud account behind the same credentials survives a disk failure but not a compromised key or a mistaken deletion. Cartwheel's archive lives on separate storage with separate credentials. Nothing described on this page has been restored from yet; topic 66 does that, with a stopwatch.