Restore a Production Backup and Prove It Works

Restore a production backup into an isolated database, verify row counts and application queries, and measure the real recovery time before an incident demands it.

intermediate~45 mindatabasebackupreliability

Objective

Prove that a production backup restores into a usable database within the recovery objectives, and record the measured recovery time and data loss window.

Environment

A recent production backup plus WAL or transaction log archive for point-in-time recovery
An isolated network and database instance with no route to production
Database superuser access on the restore instance only
Application smoke tests configured to run read-only against a restored database
A defined recovery time objective and recovery point objective

Steps

1. Choose the backup and recovery target

Select a full backup and a point-in-time target, record the backup timestamp and the expected recovery point objective for the exercise.

Expected state: The chosen backup and target time are documented and match the recovery policy.

2. Provision an isolated restore environment

Create a database instance in a separate network with no production credentials, sized to match production so the timing is representative.

Expected state: The restore instance cannot reach production and production cannot reach it.

3. Restore the backup

Restore the full backup and replay WAL or transaction logs to the chosen target time, capturing start and finish timestamps for each phase.

Expected state: The restore completes without errors and the database reports the expected recovery target time.

4. Validate data integrity

Compare critical table row counts, check sequence values, run constraint validation and compare checksums for a sample of large tables against the source.

Expected state: Row counts and checksums match within the expected tolerance and no constraints are invalid.

5. Run application smoke tests

Point a read-only copy of the application at the restored database and run login, search and reporting flows that exercise the main tables.

Expected state: Application smoke tests pass and no query fails because of missing indexes, extensions or permissions.

6. Measure and record recovery

Calculate recovery time from the incident start to usable application state, compare it with the objectives, and write the result into the restore report.

Expected state: The report states measured recovery time, data loss window and any gaps against the objectives.

Decision points

Should the exercise restore over production or into an isolated environment?

Restore into an isolated environment
Restore in place during a maintenance window

Restore into an isolated environment; an in-place restore is destructive and belongs to an actual recovery, not a drill.

Should the test use a full restore or a single table?

Full database restore
Table-level restore for speed

Measure a full restore for the real recovery time, and test table-level restore separately for accidental deletion scenarios.

How should the restore instance be sized?

Match production size
Use the smallest instance that accepts the data

Match production size for the timed exercise; a smaller instance produces a recovery time that will not hold during an incident.

Expected outcome

A restored database passes integrity checks and application smoke tests, and the measured recovery time is documented against the recovery objectives.

Verification

Confirm the restored database reports the target recovery time, compare row counts and checksums with production, run the read-only smoke suite and review the recorded restore timings.

Cleanup

Destroy the isolated instance after exporting the restore report, delete any temporary credentials and snapshots, and keep the measured timings in the runbook.

A backup is a hypothesis until it restores

The only backup that exists is one you have restored and queried. This exercise restores production data into an isolated environment, runs the checks the application actually depends on, and records the elapsed time. It is both a validation and a rehearsal: the next time an incident requires a restore, the team follows a path it has already walked under calm conditions.

Restore, do not overwrite

Never restore on top of production as a test. Export the backup to an isolated instance, then validate the data before involving the application.

pg_restore --dbname=postgres --create --clean --if-exists \
  --jobs=4 /backups/acme-prod-2026-10-21.dump

psql -d acme_prod_restore -c \
  "SELECT count(*) FROM orders WHERE created_at >= '2026-10-20';"

psql -d acme_prod_restore -c \
  "SELECT last_value FROM orders_id_seq;"

Safety checks during validation

Compare row counts on the largest tables and check sequence values, because a restore that resets a sequence causes duplicate key errors only after writes resume. Verify that extensions, roles and grants exist on the restore instance; a restore that succeeds but leaves the application role without privileges is not a successful recovery. Keep the restore instance off the production network and use read-only credentials for the smoke tests.

Measure the real recovery time

Record four timestamps: detection, restore start, database available, and application smoke tests passed. The last one is the recovery time that matters. Compare the gap between the backup timestamp and the chosen target with the recovery point objective, and note any data that cannot be replayed.

Rollback and cleanup

The isolated restore is fully reversible: destroy the instance and its snapshots, and production is untouched. If an in-place restore was performed during a real incident, the pre-restore snapshot is the rollback, so take and verify it first. Destroying the restored instance is safe only after the report is exported; deleting production backups to save space is the irreversible step and must never be part of this exercise.