PostgreSQL PITR Restore Drills: Your Backup Is a Rumor Until You Restore It

The migration that forced our first real point-in-time recovery was eleven lines long. It dropped a column that turned out to be load-bearing for a billing integration, and by the time the integration's error rate told us so, forty minutes of live traffic had been written on top. The fix was textbook: restore the cluster to 14:31, one minute before the deploy, replay forward, promote, reconnect. We had run nightly base backups for years, archived every WAL segment, and passed every audit. What we had never done — not once — was restore any of it. The base backup took seventy minutes to copy back. The WAL replay took the rest of the day: 1.8 terabytes of archived WAL, applied by a single-threaded recovery process, at a rate nobody had ever measured, finishing nine and a half hours after we started against a recovery time objective that said two. The data came back perfectly. The RTO did not, and that gap is what this post is about.

Every PostgreSQL team I talk to has backups. Far fewer have restores, in the only sense that matters: a practiced, timed, verified procedure whose duration is a known number. Here is the drill routine that closed that gap for us.

What is a restore, mechanically?

Two ingredients, always: a base backup — a consistent snapshot of the data files, taken with pg_basebackup or a tool built on the same protocol — plus every WAL segment generated since that backup began. Recovery applies the base backup, then replays WAL forward through it until the database reaches a consistent point, and optionally keeps replaying to a target you name. Since PostgreSQL 13, pg_basebackup also writes a backup manifest, a machine-readable list of every file with its checksum, and pg_verifybackup can validate an entire backup against that manifest without restoring a single byte. That tool quietly changed our backup posture: verification used to mean "the backup completed without an error," which proves the network worked, not that the bytes are sound. A manifest check proves the bytes. We run pg_verifybackup on every backup now, and it has caught exactly one corrupted backup in two years — one more than we would have caught otherwise, discovered on a quiet Tuesday instead of during an incident. The strategy-level choices — full versus incremental, retention, where the archive lives — are covered in the backup strategies guide; everything below assumes those choices are made and asks the harder question: does it work, and how long does it take?

How does point-in-time recovery choose where to stop?

You tell it, with recovery target settings, and the precision available is better than most people realize: recovery_target_time for a timestamp, recovery_target_xid for a transaction, recovery_target_lsn for an exact WAL position, or recovery_target_name for a named restore point you created in advance with pg_create_restore_point. The setting that changed how we do drills is recovery_target_action. Set it to pause — the default on recent versions — and the server stops and waits when it reaches the target instead of promoting itself, giving you a read-only, transactionally consistent database at exactly 14:31 to inspect before committing to it:

ALTER SYSTEM SET restore_command = 'cp /mnt/wal_archive/%f %p';
ALTER SYSTEM SET recovery_target_time = '2026-07-14 14:31:00+00';
ALTER SYSTEM SET recovery_target_action = 'pause';
SELECT pg_reload_conf();

On the recovering instance you then check whether replay is waiting at your target, look at the data, and only then resume into promotion:

SELECT pg_is_in_recovery(),
       pg_is_wal_replay_paused(),
       pg_last_wal_replay_lsn(),
       pg_last_xact_replay_timestamp();

SELECT max(created_at) FROM public.orders;  -- does the world look like 14:31?

SELECT pg_wal_replay_resume();  -- commit to the target and promote

That pause is your one chance to catch a wrong target — a timestamp in the wrong time zone, an LSN from the wrong timeline — before promotion forks history. We once paused a drill restore, found max(created_at) reading 03:31 instead of 14:31, and traced it to a recovery_target_time written without a time zone offset against a server whose TimeZone was UTC. In a drill, that is a funny story and a checklist line. During an incident it would have been a second outage.

Why does WAL replay decide your RTO?

Because replay is single-threaded, and its speed is a property of your workload and storage that you either know or discover at the worst possible time. One process reads each WAL record and applies it; there is no parallel recovery. The rate varies enormously — sequential index-heavy writes replay far faster than random-page update storms — and the only honest number is the one from your own drills, read out of the recovery log's progress messages. Ours averaged roughly 55 MB of WAL per second on the storage we had, and the arithmetic from there is brutal and simple: 1.8 TB of WAL divided by 55 MB per second is about nine hours, which is where our day went. Since then, RTO arithmetic is a standing agenda item. The levers are few and all of them are about reducing how much WAL stands between the last base backup and disaster: more frequent base backups shorten the replay directly; keeping the archive on fast, local storage during a restore removes read latency from the critical path; and a streaming replica — monitored properly, as the replication monitoring guide describes — absorbs the majority of incidents before PITR is even on the table, because a promotion is minutes while a restore is hours. PITR is the deep fallback for logical corruption, and its price is replay time. Measure yours before you need it.

What does a real restore drill look like?

Ours runs monthly, unattended, and produces a report a human actually reads. The automation takes the most recent verified base backup, restores it to a throwaway instance on production-class storage, picks a random timestamp from the previous day, replays to it with recovery_target_action at pause, and then runs three checks: the paused state is at the requested target, row counts on three sentinel tables match the primary's counts for tables that size permits, and max(created_at) on a hot table is within minutes of the target. Every phase is timed — copy, replay to consistency, replay to target — and the durations go into a spreadsheet next to the WAL volume replayed, because the trend of those numbers is the RTO forecast. A drill that is not timed is a backup test; a drill that is timed is capacity planning for the worst day of your year. Twice a year the drill goes further and a human runs the full incident procedure by hand from the runbook, including the part where the application points at the restored cluster, because the runbook itself is perishable and the only test of it is use.

What failure modes only a drill will show you?

The ones that are not in any error log until the day you need them. The signal-file confusion from the recovery configuration rework in PostgreSQL 12 is our favorite: an empty standby.signal file in the data directory makes the restored instance start as a standby trying to follow a primary, while recovery.signal makes it perform PITR and then promote — swap the two, or copy your standby provisioning into a PITR runbook, and your restored "primary" boots into a waiting state and tells no one. Timeline forks are the second classic: after any promotion, the new timeline's WAL must be archived too, or a future PITR that needs to cross the fork runs out of history mid-replay — verify the archive receives the new timeline after every failover, not just after drills. Archive gaps are the third: a single missing or zero-length segment between the backup and the target ends replay right there, which is why archive-command failures deserve their own alerting and why the drill should occasionally target a timestamp well inside the retention window, not just last night's. And restore_command itself — wrong archive path, expired credentials on the object store, a host that cannot reach the archive at all. Every one of these is a five-minute fix on a quiet day and an hour of dread during an incident, and the only mechanism that reliably flushes them out is doing the restore for real, on a schedule, when nothing is on fire.

Watching backup and recovery health with MonPG

Backup health is a set of time series that fail silently by default: age of the last verified base backup, WAL archive lag behind the primary's current position, archived segment count per hour, and — from the drill routine — replay rate and phase durations trending over months so the RTO forecast moves when your data does. MonPG tracks exactly these PostgreSQL operational series as part of its PostgreSQL monitoring, alongside the replication and WAL metrics that decide whether an incident becomes a PITR at all. The backups are the rumor; the drills are the evidence; the dashboard is how you know both are still true next month.