Validating a recovered database

Recovering from a logical mistake ended with a copy of the database running beside production and one comparison standing between that copy and the decision to trust it. That comparison is the part worth generalising, because most database recoveries are declared finished several rungs below it, at the moment a connection succeeds. A cluster that starts has shown its files are coherent enough for the startup process to complete, which is a statement about PostgreSQL rather than about your data. What turns a running cluster into an accepted recovery is a short ladder of checks, and the rungs that decide anything have to be prepared before the incident that makes you need them.

Six rungs, from pg_ctl start to a completed order

Validation of a database is not a yes or a no. It is an ordered set of claims, each strictly stronger than the one below it, and each the first to catch a different class of wrong.

Rung one: the process started. pg_ctl returned, a postmaster is running. This is satisfied by a cluster recovered from the wrong backup, one recovered to the wrong point, and — as Part IV measured — by a naive cp -a of a live data directory that went through crash recovery and looked fine.

Rung two: the database accepts connections. A client connects and a trivial query returns. This catches a cluster that started and then failed to reach a usable state — a real outcome when a recovery host is short of memory or the socket directory is wrong. It says nothing about content.

Rung three: the expected objects exist. The tables, indexes, sequences, extensions and roles the application needs are present in the catalogue. This is the first rung that catches a successful recovery of the wrong thing: an empty cluster from an initdb that ran because the restore path was wrong, one database out of the several the service uses, or a schema from before a migration.

Rung four: the row counts match an independently recorded figure. The tables hold the number of rows they held at the instant being recovered to, compared against a number written down where the recovery did not produce it. This is the first rung that catches a faithful recovery of the wrong instant, and the word independently carries all of the weight.

Rung five: an application-level invariant holds. A total, a balance, a count of open records — a property the data must satisfy if it is the data. This is the first rung that catches the right number of rows with the wrong contents inside them, which is what a recovery stopped a few seconds early or late produces.

Rung six: a real business transaction completes. The application places an order, the order appears, the ledger moves. This is the first rung that catches everything outside the tables: a missing extension, a role the connection string cannot authenticate as, a sequence handing out a key already in use.

The ladder has one property worth stating on its own, because it is the whole practice: rungs four and five can only test a property that somebody recorded. They are not techniques applied after an incident. They are commitments made before one, and a property you did not capture in advance cannot be checked afterwards at any price.

The cluster announces readiness twice, 32 milliseconds apart

Rung two is weaker than it looks on a database, and the recovery log from the point-in-time capture shows why. These lines are copied from that capture, executed on PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2).

  2026-08-28 13:35:12.713 UTC [631] LOG:  consistent recovery state reached at 0/3000120
  2026-08-28 13:35:12.713 UTC [625] LOG:  database system is ready to accept read-only connections
  2026-08-28 13:35:12.726 UTC [631] LOG:  restored log file "000000010000000000000005" from archive
  2026-08-28 13:35:12.735 UTC [631] LOG:  recovery stopping before commit of transaction 836, time 2026-08-28 13:34:42.096745+00
  2026-08-28 13:35:12.735 UTC [631] LOG:  last completed transaction was at log time 2026-08-28 13:34:38.041366+00
  2026-08-28 13:35:12.736 UTC [631] LOG:  selected new timeline ID: 2
  2026-08-28 13:35:12.740 UTC [631] LOG:  archive recovery complete
  2026-08-28 13:35:12.745 UTC [625] LOG:  database system is ready to accept connections

Readiness is announced at 13:35:12.713 and again at 13:35:12.745. The first announcement arrives one line after consistent recovery state reached, which means the cluster is internally coherent and can serve reads — and it arrives before the fifth write-ahead log segment has been replayed, before the stop point is decided, and 32 milliseconds before recovery is actually finished. A monitoring check that connects and runs select 1 turns green there. So does a human who opens psql, sees a prompt, and reports that the database is up.

Reading during recovery is a feature rather than a fault; it is how a hot standby serves queries. The consequence for validation is what matters: on a recovering cluster, “accepts connections” describes the postmaster’s state machine and not how much of the archive has been replayed. With recovery_target_action left at its boot value of pause, the cluster sits in precisely this state deliberately, which is useful for inspecting it before finishing — and easy to mistake for a completed recovery when the only check performed is that a connection succeeded.

Measured: the recovery was accepted on two figures and a log line

The capture makes the pre-incident recording explicit. A base backup was taken while the table held 45000 rows, and then business continued.

$ pg_basebackup -D /work/base -X stream -c fast

  >>> exit code: 0
rows contained in the base backup: 45000

--- business continues after the backup: 5,000 more orders arrive ---
rows now                      : 50000
checksum of the business data : sum(amount)=825025000
recovery target time          : 2026-08-28 13:34:40.077562+00

Three things were written down at that moment, none of them produced by the backup tool: the row count, sum(amount)=825025000, and the timestamp later used as the recovery target. Then somebody ran an unqualified DELETE, the table held zero rows, the base backup plus five archived segments was recovered forward, and the acceptance decision reads like this.

  rows recovered  : 50000   (expected 50000)
  sum(amount)     : 825025000   (expected 825025000)
  RECOVERED - row count and business checksum both match the pre-DELETE state

Both halves are load-bearing. 50000 establishes that the table has the shape it had, and a recovery replayed to a slightly different point could deliver the right count with different rows inside it — five hundred orders missing and five hundred later ones present is still fifty thousand rows. sum(amount)=825025000 is a statement about the values, and it separates a plausible table from the table the business had.

Neither figure could have been checked if the pair had not been recorded while the data was still correct. That is the practice this lesson exists to force, and choosing the property is not an engineering decision made alone: ask the people who own the application what number they would check first if they suspected the data was wrong. An outstanding balance, a count of open tickets, the newest timestamp in an audit table — whatever they name is the invariant, and it is computed inside the backup job and stored where the database cannot rewrite it.

What a matching row count still does not cover

Rungs four and five are about the contents of tables. A database is more than its tables, and the objects that surround them fail quietly.

Sequences. After a physical recovery the sequence relations are replayed with everything else, and the risk is a gap rather than a reused value. The dangerous path is the other one: when a table is repaired by loading rows back into a live database — from a logical dump, or from a recovered copy — the sequence there is untouched and still holds the value it reached before. The row count then matches perfectly and the first insert after the recovery fails on a duplicate key. Compare last_value against the maximum key in the table during acceptance, not during the incident review.

Referential integrity. A point-in-time recovery stops at a commit boundary, so committed constraints hold. Integrity breaks when the recovery is partial: a single table restored into a live database, or a data-only load performed with triggers disabled, leaves rows pointing at parents that are not there. A query for orphans costs one left join and is the only way to see it.

Extensions and their versions. The catalogue entry travels with the backup; the shared library does not, because it lives in the package’s library directory outside the data directory. A recovery host with a different package set can start a cluster whose pg_extension rows reference code that is absent or at a different version, and nothing about that mismatch appears while the cluster is merely running. The acceptance check is therefore not whether pg_extension lists the extension but whether a query that calls into it returns — a rung six question wearing rung three clothes.

Roles and permissions. Roles are cluster-level objects, not objects inside a database. A physical base backup carries them because it carries the whole cluster; a dump scoped to a single database does not, so the cluster-wide objects have to be captured by a second, separate export. A recovery with every table and no login roles passes rungs three through five and fails rung six on the application’s first connection.

select count(*) as orders, sum(amount) as amount_total from orders;
select last_value from orders_id_seq;
select max(id) from orders;
select o.id from orders o
  left join customers c on c.id = o.customer_id
 where o.customer_id is not null and c.id is null
 limit 5;
select extname, extversion from pg_extension order by 1;
select rolname, rolcanlogin from pg_roles where rolcanlogin order by 1;

One more gap is worth naming. A row count answers how many tuples a query can see; it does not establish that every stored page is readable and self-consistent, so it is not the database equivalent of verifying bytes at rest. For that, the file-backup analogue is exact: on a healthy restic repository, reading and re-hashing every pack is a different command from validating structure.

$ restic check --read-data

using temporary cache in /tmp/restic-check-cache-1847567736
create exclusive lock for repository
load indexes
check all packs
check snapshots, trees and blobs
[0:00] 100.00%  2 / 2 snapshots
read all data
[0:00] 100.00%  7 / 7 packs
no errors were found
>>> exit code: 0

The database equivalents are a dump of the recovered cluster discarded to /dev/null, which forces every row to be read, and amcheck, whose functions verify the logical consistency of index structures and, with verify_heapam, of heap pages. Both are for the run you make after recovering from media you have reason to distrust.

The recovered instance was listening on 0.0.0.0 the whole time

The recovery in the capture began by binding a socket, several lines before any of this validation had happened.

  2026-08-28 13:35:12.691 UTC [625] LOG:  listening on IPv4 address "0.0.0.0", port 5434
  2026-08-28 13:35:12.691 UTC [625] LOG:  listening on IPv6 address "::", port 5434
  2026-08-28 13:35:12.696 UTC [625] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5434"

Port 5434 is the only thing separating that instance from production traffic. A cluster recovered from a physical base backup is a byte-level copy of the production cluster, so it carries production’s roles and their stored password verifiers: every credential that works against the real database works against the unvalidated copy. If it lands on a port an application already looks for, acquires a DNS name, or is registered with whatever service discovery the estate uses, application connections will find it. Reads from it return data nobody has accepted yet; writes to it are lost when the copy is discarded, and are invisible in the real database while somebody is still counting what the incident cost.

Bring a recovery up deliberately quarantined: an unused port, listen_addresses restricted to the loopback interface, and no firewall rule admitting it. Widen that only after the invariant has been checked and the recovery accepted.

set -euo pipefail

RECOVERED=/work/recovered
PORT=5434

pg_ctl -D "$RECOVERED" \
  -o "-p $PORT -c listen_addresses=127.0.0.1" \
  -l "$RECOVERED/recovery.log" start

psql -h 127.0.0.1 -p "$PORT" -d orders -At \
  -c 'select count(*), sum(amount) from orders'

Production discipline

1. Record the invariant in the same job that takes the backup. A figure computed by a separate, later job describes a different instant, which produces rehearsal mismatches nobody can explain and rehearsals that get switched off.
2. Agree the invariant with the application owners, in writing. The measured acceptance turned on sum(amount)=825025000 because someone decided in advance that the total was the property that mattered. That is a design decision, not a monitoring detail.
3. Read the recovery log before you read the data. recovery stopping before commit of transaction 836 and last completed transaction was at log time 2026-08-28 13:34:38.041366+00 fix where replay stopped, independently of anything the recovered cluster reports about itself.
4. Check the objects that no row count covers. Sequence values against maximum keys, orphaned references, pg_extension versions against what the host actually provides, and the login roles the application authenticates as.
5. Quarantine the instance until it is accepted. It listens on a socket long before it is trustworthy and carries production’s credentials, so an unused port, a loopback listen_addresses and no firewall rule are the default posture, not a precaution for sensitive recoveries.

Cross-course references

• PostgreSQL for Production Sysadmins — Part V (Authentication, Roles and TLS) covers how roles, pg_hba.conf and connection privileges are defined and where each of them is stored, which is the material behind two checks here: that the login roles survived the recovery at all, and that a recovered copy accepting production credentials is a hazard rather than a convenience.
• Observability for Production Sysadmins — Part LIX (Database Observability) builds the continuous signals that describe a running database, and the relationship here is a division of labour: those signals say a cluster is healthy now, while these acceptance checks answer the question monitoring cannot — whether the data it holds is the data that existed before the incident.
• Kubernetes for Production Sysadmins — Part XXXVIII (Services) explains how a pod becomes an endpoint of a Service purely by matching labels, which is the containerised form of the reachability hazard in the last section: a recovered database carrying the right labels receives production traffic before anyone has checked an invariant against it.

Quiz