How to use
Quarterly, once per database instance rather than once per host, and the middle of it is not a reading exercise. The first nine items can be answered from configuration and monitoring in well under an hour. The rest require that somebody has actually returned a database to a chosen moment since the last review, and the review either has that record with a date on it or it does not.
Work down in order. The early items establish that a recovery could begin at all: a base backup that exists, and a chain of log segments leading away from it towards now. The later items establish that the recovery lands where it was aimed and that the data it holds is the data the business had. A database that passes the first group and fails the second is what this review exists to find, and it is worse than it looks, because every signal anybody is watching stays green.
Where the numbers come from
Three of them come from the system and one comes from the business.
The archive’s account of itself comes from the engine’s archiver statistics —
on PostgreSQL, pg_stat_archiver — which reports what the server handed off and
what was refused. The destination’s account comes from listing the destination,
which is the only check that reads what a recovery will read. Both are needed,
and they are different statements. Byte-for-byte from this course’s capture on
PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2), recorded in
docs/courses/backup-dr/execution-evidence/pg-application-consistency-and-pitr.txt
at the moment an unqualified DELETE emptied the table:
pg_stat_archiver:
archived=6 failed=0 last=000000010000000000000005
WAL segments in the archive : 5
000000010000000000000001
000000010000000000000002
000000010000000000000003
000000010000000000000003.00000028.backup
000000010000000000000004
000000010000000000000005Six archiving events; five segments and one backup history file. The counter counts hand-offs. Only the listing says the sequence has no hole in it.
The oldest recoverable point comes from the oldest base backup that still has an unbroken chain behind it, established by listing both.
The acceptance number comes from the business, and it has to be written down while the data is still correct. In the same capture, the recovery was accepted on two values agreed beforehand:
rows recovered : 50000 (expected 50000)
sum(amount) : 825025000 (expected 825025000)Access this needs
Read access to the archive destination, to its retention configuration, and to the base backups it belongs with. A monitoring role on each database for the archiver statistics and the current log position. The operating-system account the restore command runs as, on the host a recovery would actually use, because that command is tested by hand rather than assumed to work.
Capacity for a full copy of the largest database in scope, on infrastructure that is not serving the application. Nothing here requires write access to the archive; a reviewer who needs it to finish an item has found a defect in the destination’s permissions rather than a gap in their own.
What the review produces
A dated record naming the reviewer, every database in scope, the age of its newest base backup, the earliest moment it can be returned to, and the date of the last recovery actually performed. Attach the log line naming where replay stopped, the invariant that was compared, and the measured durations.
The headline is one sentence per database: the earliest moment it can be returned to, the latest, and who has been told. Where that window is narrower than the objective the service owner believes they hold, the gap goes to them in writing whether or not it is acceptable, because it is the only honest input to that conversation.
Every critical item must pass. A failing critical item is a blocker, not a note for the next sprint.
Sign-off
- Reviewer: ____________________
- Date: ____________
- Database owner: ______________
- Service owner: _______________
Record the disposition of every item that did not pass, and the name of whoever accepted the residual risk on each.