A backup is not real until you restore it

1. Decide what recovery must achieve

Start with two business decisions. Your recovery point objective, or RPO, is the maximum acceptable amount of lost work, expressed as time. Your recovery time objective, or RTO, is how quickly the service must be usable again after disruption. Write down what usable means: customers can sign in, find their records, and complete the core transaction.

Consider a hypothetical outage at 14:00. An RPO of 15 minutes requires a recoverable state at 13:45 or later; a nightly dump from 02:00 cannot meet it. An RTO of 60 minutes requires the service to be usable by 15:00. Finding credentials, downloading files, provisioning infrastructure, restoring data, and checking the application all consume that hour. These numbers illustrate targets, not measured performance.

2. Match the backup method to the promise

A logical dump records database objects and data. PostgreSQL's pg_dump exports one database; custom-format archives are restored with pg_restore. This is useful for a simple restore exercise and selective recovery. It does not capture cluster-wide roles or tablespaces, and a successful export alone does not establish a production backup strategy.

Physical backups capture the database cluster's files. A valid base backup plus the required continuous sequence of archived write-ahead log files, or WAL, can support point-in-time recovery: replay changes to a chosen moment, such as just before an accidental deletion. Missing required WAL can prevent reaching that moment. Physical recovery has server-version constraints; rehearse your exact supported procedure or your managed provider's equivalent.

A replica supports availability, but it can reproduce an accidental deletion. Keep independently retained backups that survive loss of the primary system and its ordinary credentials. Also account for application configuration, file uploads, and external services. WAL does not back up manual changes to PostgreSQL configuration files.

3. Prove you can retrieve and decrypt it

Begin the real drill with a retained backup from the place you would use during an incident. Record its identifier, database, creation time, recoverable point, and retention deadline. Check the transfer's integrity using your backup system's verification mechanism. For applicable physical base backups, pg_verifybackup adds useful checks, but PostgreSQL explicitly recommends test restores even when verification succeeds.

As an operational recommendation, encrypt backups in transit and storage, keep an offsite copy in a separate failure domain, and separate backup deletion authority from everyday application access. Test recovery access without depending on the failed server. A named alternate operator should be able to obtain both the backup and its decryption key through the documented access process. Record secret locations and access steps, never secret values, in the runbook.

4. Restore into a disposable, isolated target

Use a separate scratch PostgreSQL server with no production application traffic. Restrict access to restored customer data and disable outbound email, payments, webhooks, and background workers in its test application. Prepare compatible PostgreSQL tools, extensions, storage, and any test roles required by database policies.

Before running this shell example, configure two libpq service aliases outside the script: backup_source for the authorized source database and restore_scratch for the isolated server. Verify their destinations. The scratch role needs permission to create databases. Supply authentication through your approved secret mechanism or protected password file. No production connection strings or secrets belong in this example.

The example creates a unique scratch database from template0 and stops on errors. It demonstrates a fresh dump and restore; for the real drill, replace the dump step with retrieval and decryption of the selected retained archive. Keep that working file on protected storage.

• The ownership and privilege flags simplify the scratch exercise. They do not verify production permissions; rehearse role, ownership, grant, and policy recovery separately.
• Keep the destination disposable. Do not add --clean or redirect this exercise to production. If a step fails, investigate and use a fresh scratch database for the next attempt.

set -eu
umask 077
drill_dir="$(mktemp -d "${TMPDIR:-/tmp}/restore-drill.XXXXXX")"
scratch_db="restore_drill_$(date -u +%Y%m%dT%H%M%S)_$$"

pg_dump --dbname='service=backup_source' \
  --format=custom --file="$drill_dir/database.dump"

createdb --maintenance-db='service=restore_scratch dbname=postgres' \
  --template=template0 "$scratch_db"

pg_restore --dbname="service=restore_scratch dbname=$scratch_db" \
  --exit-on-error --no-owner --no-privileges \
  "$drill_dir/database.dump"

psql --dbname="service=restore_scratch dbname=$scratch_db" \
  -X --set=ON_ERROR_STOP=1 --command='ANALYZE;'

printf 'Scratch database: %s\nArchive directory: %s\n' \
  "$scratch_db" "$drill_dir"

5. Verify the application, not just the exit code

A completed restore proves that commands ran. It does not prove that the right backup was selected or that customers can use the service. Compare critical record counts and business totals with evidence captured for the backup's recovery point. A live production count can legitimately differ because newer writes occurred.

Run the application against the restored database with representative test permissions. Check a known account, a recent expected record, important relationships, and one complete business workflow. Confirm tenant boundaries and authorization rules. Exercise a scratch-only write to expose missing sequence values or dependencies. Verify referenced uploads and required configuration separately. Capture results without copying customer records into public logs.

6. Measure the complete timeline and assign fixes

Record timestamps for the simulated disruption, operator response, access obtained, backup downloaded, restore started, database ready, and application verified. The last timestamp determines elapsed recovery time. Record the newest verified recoverable transaction time to assess data loss. Label a database-only drill as partial when infrastructure or traffic recovery was skipped.

Give each failure a named owner and due date. Recommend a recurring drill cadence, such as monthly for a frequently changing service, and repeat after major changes to versions, permissions, encryption, or storage. Choose the cadence to match risk and resources.

7. Complete the recovery worksheet

Fill in these fields before the drill, then replace assumptions with observed results. Keep the worksheet with the runbook where both operators can reach it during an outage.

• Service and critical workflow: ____. Recovery owner: ____. Alternate operator: ____.
• RPO: ____ minutes. RTO: ____ minutes. Agreed definition of usable: ____.
• Backup identifier and recoverable timestamp: ____. Retention deadline: ____. Required WAL range, if applicable: ____.
• Backup location: ____. Decryption/access procedure reference: ____. Independent copy location: ____.
• Scratch destination: ____. PostgreSQL version, extensions, roles, and external dependencies: ____.
• Verification queries and expected results: ____. Application checks and evidence location: ____.
• Disruption time: ____. Access ready: ____. Download complete: ____. Restore complete: ____. Application verified: ____.
• Measured recovery time: ____. Recoverable data gap: ____. Target met: ____. Fix, owner, and due date: ____.
• Next drill date: ____. Person responsible for removing scratch data and temporary archives: ____. Cleanup verified: ____.