Test Database Recovery Against a Business Invariant

The database starts. Connections succeed. The incident channel celebrates. Then the application discovers paid orders without fulfillment records, or a recovered sequence collides with an existing identifier. Restoring bytes was necessary. It was not the same as restoring a usable service.

A recovery exercise should end with evidence that the application can preserve its promises. Choose a business invariant, define the recovery boundary, and test both the recovered data and the first operations performed afterward. This turns a backup rehearsal into an operational acceptance test.

Choose an invariant with a precise scope

For a double-entry ledger, a useful invariant is that every posted journal balances within each currency. For inventory, it might be that every committed reservation references an existing order and no available quantity is negative under the application's defined inventory model. Avoid slogans such as “all data is correct.” They cannot produce a failed test.

Consider this illustrative query against a hypothetical signed-amount ledger:

SELECT journal_id, currency
FROM ledger_entries
WHERE posting_state = 'posted'
GROUP BY journal_id, currency
HAVING SUM(amount_minor) <> 0;

Zero returned rows is one acceptance condition, not a complete proof. A completely missing journal also satisfies this check. Pair internal invariants with an independently recorded set of expected committed journal IDs through the selected recovery boundary. Check for missing IDs, unexpected IDs, and duplicate business identifiers.

Use synthetic records for rehearsal whenever possible. If production data is required, the recovery environment must retain the same access restrictions and handling controls. A disposable recovery database is still a copy of sensitive information.

Specify the boundary before the failure

Record a known-good point, then perform a controlled sequence of transactions. Record their acknowledgments and identifiers outside the database under test. Introduce a destructive change only in the exercise environment and restore to the intended point before it.

A timestamp alone can be ambiguous around closely spaced commits and clock differences. Use the recovery mechanism's supported targets and document inclusivity. Record the selected timeline and target configuration in the evidence packet. The expected transaction set should derive from that boundary, not from whatever happened to appear after recovery.

PostgreSQL point-in-time recovery combines a base backup with a continuous sequence of archived WAL. Logical dumps are not interchangeable with those physical base backups for WAL replay. Verify the backup chain rather than treating the latest backup file as the entire recovery plan. PostgreSQL continuous archiving and PITR

Measure the full recovery interval

Start the clock when the exercise declares recovery necessary. Include finding the backup, acquiring credentials, provisioning capacity, retrieving archived logs, replaying changes, validating data, and authorizing traffic. A fast restore command can conceal an hour spent locating the correct encryption key.

Record recovery point separately from recovery time. Recovery point concerns what committed work is absent. Recovery time concerns when the service is safe to use. Neither can be inferred from the size of the backup or a green backup-job status.

Run integrity verification where available, but do not stop there. PostgreSQL explicitly notes that pg_verifybackup cannot replace test restores and checks of the resulting data. PostgreSQL backup verification

Test the first write and the outside world

After read-only checks, exercise application writes in an isolated network with real outbound effects disabled. Create a new order, reserve inventory, and commit a synthetic ledger journal. Verify identifiers, constraints, database extensions, application roles, and required configuration. A restored dataset can be readable while an application role lacks a needed permission.

Database recovery does not roll back the payment provider, message broker, or warehouse. Suppose a charge succeeded after the chosen database recovery point. Replaying the order workflow could charge it again unless the application reconciles provider identifiers and uses stable idempotency keys. The exercise should demonstrate reconciliation with simulated external state, not actually repeat a payment.

Likewise, consumers may retain offsets from a future the recovered database no longer contains. Decide whether to rebuild projections, rewind supported consumers, or reconcile them. Record who approves that decision and which systems remain paused until it is complete.

Make failure useful

Introduce one missing WAL segment, one unavailable credential, or one incompatible extension in separate rehearsals. Verify that the runbook fails clearly and does not promote an incompletely validated database. Assign each failure an owner and retest after repair.

The deliverable is an evidence packet: backup identity, target, recovered transaction set, invariant results, elapsed time, and resumption decision. “Restore succeeded” describes a tool outcome. “The service preserves these business promises at this recovery boundary” describes readiness.