Proving the backup restores, nightly, with nobody watching

The problem

Backups run. The job is green, the bucket fills up, the retention policy prunes the old ones, and everybody's disaster-recovery documentation says the recovery time objective is four hours.

Nobody on the team has restored one.

The failure mode is not that the backup job crashes; that would page someone. It is that the backup job succeeds and produces something that cannot be restored. A schema migration changed a type the dump tool serialises incorrectly. The pg_dump version drifted from the server version, the tool refused to run, and nobody checked the job's exit status. The encryption key rotated and the restore path still references the old one. An extension the database needs was never included, so the restore fails at the first CREATE INDEX. The archive is written to a bucket with a lifecycle rule that moves it to a storage class the restore tooling cannot read without a twelve-hour thaw.

Every one of these produces a green backup job and an unusable backup. The gap between the two is discovered at the moment it is most expensive: during an incident, when the person running the restore is doing it for the first time, under pressure, from documentation nobody has tested.

This is not a hypothetical failure class. GitLab's public postmortem of its 31 January 2017 data-loss incident describes an engineer removing the wrong data directory and then discovering, in sequence, that several of the mechanisms that were supposed to make that survivable did not work as believed. The lasting lesson from that write-up was not about the deletion. It was that the state of the recovery path was unknown until it was needed, and that a backup nobody has restored is an assumption wearing a job's green tick.

The reason nobody restores regularly is not negligence. It is that a real restore test is a half-day of work — provision somewhere to restore to, fetch several hundred gigabytes, run the restore, connect, poke at it, decide whether what came back is right, tear it all down and make sure the scratch environment cannot reach production. It competes with a roadmap, and it loses every time.

The method

Restore into a throwaway container, then ask it questions. Fetch the backup, start a scratch container, load the backup into it, run smoke queries, compare against expectations, tear it down, record the result. Since 0.5.0 a plan can also leave the image out and restore in place, for when the process running the harness is already a throwaway environment (a Kubernetes CronJob pod, a CI job, a scratch VM); the load and the checks then run in that process, with no Docker involved.

Four sections of plan, one per stage. The plan file is declarative and maps one-to-one onto the pipeline:

Isolate the scratch container completely. It runs on its own Docker network created with --internal, which means no external egress at all. A restored database is full of live credentials, and the container cannot reach anything to use them on. Isolation is how the harness creates the network, not an instruction in a runbook. In place, the isolation is whatever the pod or VM provides; the harness adds none.

Fetchers run as argv, not through a shell. restic and pgbackrest are invoked as argument vectors, so a repository path or a stanza name containing shell metacharacters is data rather than syntax. A bare command fetcher is provided as the escape hatch for the backup tools that are not built in, and it does run through sh -c; the documentation states that keeping it injection-safe is the plan author's responsibility. The common paths are safe by construction, and the one that is not says so.

Poll for readiness. A database container is not ready when Docker says it is running; it is ready when it accepts connections and has finished its own initialisation. The plan supplies a ready_command, polled every two seconds through docker exec until it succeeds or ready_timeout (default 60 seconds) expires. sleep 30 is the alternative, and it is both slower on a good day and flaky on a bad one.

Declare expectations in the plan. Three comparison forms cover the cases that matter, evaluated against the check's trimmed standard output:

checks:
  - name: users table populated
    command: psql -tAc 'select count(*) from users'
    expect_min: 1000          # numeric floor — data present, not just schema
  - name: schema version current
    command: psql -tAc 'select max(version) from schema_migrations'
    expect: "20240115120000"  # exact string
  - name: no orphaned rows
    command: psql -tAc 'select count(*) from orders where user_id not in (select id from users)'
    expect_max: 0             # numeric ceiling — referential sanity

Figure 1 — Three expectation forms doing three different jobs. expect_min catches an empty restore that technically succeeded; expect pins the schema version so a stale backup is a failure rather than a surprise; expect_max: 0 asserts a referential invariant that a checksum cannot see.

Exit codes are the interface. The harness is meant to be driven by cron and CI, so its result has to be legible to a scheduler that cannot read prose:

Every run leaves a record. history_file appends one JSON line per run: {timestamp, duration_seconds, ok}, plus an error field when the run threw. That file is the audit evidence, and it accumulates without anyone maintaining it.

The headline results

• "Restored" means the checks passed, and the checks are yours.
• What the tests assert.
• Duration is recorded on every run

The full results, with the tables and the reasoning behind each figure, are in the PDF.