A Restore You Have Actually Tested

An untested backup is a belief. Everything the previous two topics built, the archive and the base backups and the recovery targets, is machinery, and machinery that has never been operated has an unknown success rate. The only two facts that matter about a backup strategy are how long it takes to bring Cartwheel back and how much data is gone when it comes back, and neither is knowable until somebody has done it with a clock running.

This topic turns the configuration into a rehearsed procedure with numbers attached. It is short on new mechanisms deliberately: there is almost nothing here that has not already appeared, and the value is entirely in the discipline of executing it on a day when nothing is wrong.

RPO and RTO, Written Down

The design rests on two numbers, and both are business decisions rather than technical ones. The recovery point objective is how much data the business accepts losing: with nightly dumps it is up to a full day, and with continuous archiving it is bounded by the unarchived tail, meaning the current segment plus whatever archive_timeout allows, which is seconds to a minute. The recovery time objective is how long the restore may take before the loss stops being about data and starts being about the business being closed.

A backup strategy chosen without those two numbers is guesswork that happens to be expensive. Written down, they do real work: an RPO of one minute rules out a dump-only strategy immediately, and an RTO of one hour on a 712 GB cluster rules out anything that rebuilds indexes. Cartwheel's two numbers went into a document the week after 14:12, which is a week later than they were needed.

The Rehearsal

The rehearsal is deliberately unremarkable: restore last night's base backup plus its WAL onto a scratch host, recover to a chosen moment, start the cluster, and run a fixed checklist of queries against it. Row counts on orders and order_items, the latest occurred_at in delivery_events, a checksum over a known slice of products, and one application-level query that touches three tables at once. What makes it a rehearsal rather than a restore is that every phase is timed and the times are written down where somebody who was not there can read them.

$ time tar -xf /backup/base/2026-08-13/base.tar -C $PGDATA
real    68m41s                   # 712 GB pulled from object storage

# restore_command and recovery_target_time set, recovery.signal created
$ pg_ctlcluster 18 main start
$ psql -c "SELECT pg_get_wal_replay_pause_state()"
 paused                          # 44 minutes of replay

$ psql -c "SELECT count(*) FROM orders"            # 40,118,904
$ psql -c "SELECT max(occurred_at) FROM delivery_events"
                                 # 2h04m total, against an RTO of 1h

The finding in that transcript is not a bug anywhere. Everything worked: the base restored, the replay reached the target and paused, the row counts were right. Two hours and four minutes against a stated objective of one hour is still a failure, and it is a failure that can only be found this way. The fixes it points at are concrete: a faster path out of object storage, more frequent base backups so there is less WAL to replay, or a renegotiated hour. Sixty-eight of the 124 minutes were the download.

What a Rehearsal Catches

The list is depressingly consistent across organizations. A missing pg_dumpall --globals-only, so nothing has an owner. A tablespace whose symlink target does not exist on the restore host, which stops the startup dead. An extension the schema depends on that is not installed on the scratch machine. An archive gap from a month ago that nobody noticed because nobody was alerting on pg_stat_archiver. A restore_command that works beautifully on the original host because it relies on an SSH key or a cloud role that exists only there.

Every one of those is invisible to a monitoring system that checks whether backups completed, because every one of them did complete. They are properties of the restore path rather than of the backup, and nothing on the backup side reads that path at all.

Restoring One Table

The common real case is "these 90,000 rows are gone and everything else is fine", not "the cluster is gone". Restoring the whole production cluster to 14:11 would throw away every legitimate order placed between 14:12 and now, which is a second incident bolted onto the first. The procedure that answers it correctly is a scratch restore: recover a separate cluster to 14:11, verify it during the pause, extract the rows from there, and put them back into the live database with a statement that cannot touch anything else.

-- on the scratch cluster, recovered to 14:11 and verified
COPY (SELECT * FROM orders WHERE placed_at >= '2026-07-01')
  TO '/tmp/orders-1411.csv' CSV;

-- on pg-primary: land it beside the live table, never on top of it
CREATE TABLE orders_recovered (LIKE orders);
COPY orders_recovered FROM '/tmp/orders-1411.csv' CSV;

INSERT INTO orders
SELECT r.* FROM orders_recovered r
  LEFT JOIN orders o ON o.id = r.id
 WHERE o.id IS NULL;            -- 90,000 rows, nothing else touched

Bounding the export by placed_at keeps the copy proportionate; there is no reason to move 40 million rows across two hosts to recover 90,000. The LEFT JOIN is what makes the insert safe to run twice: it adds only rows whose id is absent, so a partial run can simply be repeated. Two things still need checking afterwards, and neither is automatic. The children of those orders in order_items and delivery_events either survived, in which case their foreign keys become valid again, or went with the parents and need the same treatment. And the staging table is dropped when the count is confirmed, not left behind for the next person to find and wonder about.

Corruption and the Quieter Failures

Not every failure announces itself. Data checksums, on by default since 18, surface a bad page when something reads it, so a page no query has touched this year stays wrong and gets copied into every base backup taken since. A failing archive shows up in pg_stat_archiver and nowhere else. The worst version of this is a corrupt page that has been faithfully copied into every base backup in the retention window, which is why pg_verifybackup or the equivalent check in pgBackRest, Barman or WAL-G belongs in the schedule rather than in the incident: it reads the manifest, notices missing and extra files, verifies the checksums, and parses the WAL the backup will need before anybody is depending on the answer.

The Cadence

Quarterly is a defensible rhythm for a full rehearsal on production-sized data, with the elapsed time of each phase recorded and compared against the previous run: a restore that took two hours in March and three in September is telling you the database grew and the objective did not move with it. Rehearsing on a copy a hundredth of the size is worse than not rehearsing, because it produces a number that feels earned and extrapolates wrongly: index rebuilds, storage throughput and WAL replay do not scale linearly with anything convenient. Where the tooling supports it, an automated restore-and-verify job turns the quarterly event into a weekly one and a drill for the people rather than the machinery.

The last artefact is the runbook, and its acceptance test is specific: a colleague who has never done this follows it without the person who built the system in the room. If they cannot, the capability lives in one head, and no amount of object-storage redundancy addresses that. Chapter 13 takes the same WAL stream and points it at a second machine that is always replaying it, which solves a different problem from the one solved here and gets confused with it constantly.