A Backup Is Only an Input to Recovery

The backup job is green. The archive exists, its checksum matches, and pg_restore exits successfully. A shipment lookup still returns nothing.

That is not a hypothetical combination. The OPS-03 fixture contains a schema-only archive with a stable SHA-256 identity. PostgreSQL restores it without error. The tables and constraints exist. The application probe then asks for shipment-100, receives no row, and rejects the recovery.

The checksum answered which bytes we selected. The restore command answered whether PostgreSQL could apply them. Neither answered whether the recovered database could do useful work.

A backup is therefore an input to recovery, not evidence that recovery works. It becomes credible when its exact bytes restore a clean environment to a declared point, within a declared duration, and pass the application behavior that the data must support.

Give the archive an identity before trusting it

“The latest backup” is not a useful recovery instruction. Latest according to which producer, database, successful job, retention system, or clock?

The fixture selects one PostgreSQL custom-format archive and retains:

• its filename, byte size, and SHA-256 digest;
• the source server and pg_dump versions;
• the database and archive format;
• the synthetic cutoff time;
• the highest business sequence included; and
• the archive table of contents reported by pg_restore --list.

This is enough to identify the candidate precisely. It is not enough to call the candidate recoverable.

The truncated control makes the distinction blunt. It takes the first half of the valid archive and computes stable bytes for that file too. pg_restore fails with could not read from input file: end of file. A digest can prove that the broken file did not change. It cannot prove the file was complete in the first place.

PostgreSQL describes pg_dump as a consistent export of one database. Its documentation also notes that roles, tablespaces, and other cluster-wide objects require separate treatment. The custom format supports inspection and selective restoration through pg_restore; it does not widen the contents beyond what the dump captured.

Artifact identity belongs at the start of the recovery record because every later result depends on it. It should not be mistaken for the end of the test.

A recovery point is a business fact

The fixture has a small recovery ledger. Sequence 100 books shipment-100; sequence 101 books shipment-101. The archive is selected at 10:02 with sequence 101 as its last committed business record.

At 10:05, the source commits shipment-102 as sequence 102. A destructive operation then removes shipment-100 from the source. The resulting states disagree in useful ways:

The restore recovers the pre-backup shipment which the damaged source lost. It does not recover the later booking which the archive never contained. Both outcomes are correct for the selected artifact.

That gives the exercise an observed recovery-point loss of one booking and three synthetic minutes. Its declared tolerance is no more than one booking and five minutes, so the selected point satisfies this laboratory contract. Another system may measure orders, payments, messages, event positions, or committed timestamps instead. The useful unit is the business work which will be absent, not merely the age printed on a backup file.

This is also why RPO should be declared before an incident. AWS’s recovery-objective definition calls it the maximum acceptable time since the last recovery point, defined by the organization. It is not whatever gap happens to remain after the available archive is chosen.

Stop the recovery clock after acceptance

The clean target in the retained fixture became ready to accept PostgreSQL connections before the archive was restored. If the recovery clock stopped there, the result would look better and mean almost nothing.

The fixture keeps the clock running through:

start clean PostgreSQL target
create empty recovery database
restore the selected archive with exit-on-error
check schema, constraints, rows, and event order
load a known shipment and its event history
exercise a real uniqueness failure
book and read a probe shipment atomically
roll the probe transaction back

The committed-tree run completed that path in 1,694 milliseconds on one local machine. That number proves only that this tiny fixture stayed inside its 30-second laboratory limit. It says nothing about production recovery time. Database size, storage throughput, network transfer, indexes, WAL replay, provider provisioning, credentials, and dependent services can all dominate a real recovery.

The boundary is the useful part of the measurement. Server readiness is an infrastructure milestone. Restore completion is a database milestone. The recovery-time observation ends only after the required application behavior succeeds.

Database integrity and application usefulness can disagree

The valid restore checks structural properties first:

• schema version one is present;
• 13 primary-key, unique, foreign-key, and check constraints exist;
• two shipments, four events, and two ledger entries were restored;
• shipment identities are unique;
• no event is orphaned;
• each shipment’s event sequence is contiguous; and
• the highest ledger sequence is 101.

Those checks matter. A database with missing constraints or broken references can answer a simple query and still be unsafe to reopen.

They are not application acceptance. The next probes load shipment-100, verify its ordered booked and label_ready history, and confirm that its current stored state is usable. A duplicate external identity must fail through the restored unique constraint. A new probe shipment and first event must commit atomically inside the test transaction, read back through the same query contract, and disappear after rollback.

The row-count control explains why the named lookup matters. It deletes shipment-100 and inserts shipment-999. The database still contains two shipments. A check which knows only the expected count passes; the customer journey represented by the known shipment does not.

An acceptance suite does not need to replay every application test. It needs to cover the smallest set of identities, invariants, reads, and writes that establish the recovered boundary is useful. For this fixture, that boundary is one shipment workflow. It does not prove that credentials, queues, caches, files, or external integrations have recovered.

A clean target removes accidental help

Restoring over the damaged environment leaves too many explanations for a green result. A missing role may survive from the old cluster. A required extension may already be installed. Tables omitted from the archive may still exist. Old rows may make a partial restore look complete.

OPS-03 starts a second digest-pinned PostgreSQL container without the source data directory or a pre-populated application database. The runner creates an empty target database and treats the first restore error as fatal.

The conflicting-target control does the opposite. It creates a shipments table before applying the archive. pg_restore stops when that relation already exists. Silently merging an archive with unknown target state would make it impossible to say which artifact supplied the recovered contract.

PostgreSQL’s own pg_restore examples use a freshly created database for this path. For continuous archive recovery, the official procedure goes further: restore the cluster files and WAL into controlled state, prevent ordinary access during recovery, and then inspect the database before admitting users . Different mechanisms prepare the target differently, but both need a boundary which excludes accidental help from the failed system.

Choose the mechanism from the recovery contract

This fixture uses a logical archive because it makes a small database and its failure controls easy to inspect. That does not make logical dumps the default answer for every PostgreSQL system.

A logical archive is attractive when selected databases or schemas need an inspectable representation and reconstruction fits the recovery window. It also leaves cluster-wide objects and application acceptance as explicit obligations.

A physical base backup plus a continuous WAL archive can recover a compatible cluster and stop at a selected point. PostgreSQL’s continuous-archiving documentation requires a valid base backup and an unbroken sequence of required WAL files. That can improve recovery-point selection and avoid rebuilding every row logically, while adding archive-chain, timeline, compatibility, storage, and replay obligations.

A provider-managed point-in-time restore can own much of the capture, retention, and target-provisioning machinery. Amazon RDS, for example, creates a new instance for a point-in-time restore and exposes a latest restorable time. The application still has to select the point, restore the required configuration and network access, observe the target, and decide whether its data is useful. That last sentence is an inference from the boundary: a database provider cannot know which shipment, payment, or user journey your application must accept.

The choice changes with database size, acceptable loss, acceptable downtime, required point selection, PostgreSQL compatibility, operator capability, provider constraints, and the application behavior that must return. A table of generic advantages cannot choose those values.

The practical recovery record is smaller and stricter:

selected artifact and identity
declared recovery point and tolerated loss
clean target and exact restore procedure
integrity checks
application acceptance probes
observed loss and elapsed time
retained failure evidence

Keep producing backups. Then regularly pick one, remove the original environment from the test, and make the recovered application prove what those bytes can do. Until that path passes, the backup is inventory—not recovery.