$ cat ~/field-notes/database-backups-restore-drills.md
Database Backups: Practice the Restore Before You Need It
The backup dashboard is green. Then someone deletes the wrong records, and the team discovers that the archive cannot be decrypted with the credentials available during an outage.
The backup existed. Recovery did not.
A useful backup plan starts with the questions you will need to answer while the service is broken: which recovery point is safe, who can restore it, where it will run, and how the application will reconnect.
Define the Loss You Can Tolerate
Two targets make the plan concrete:
| Target | Question |
|---|---|
| Recovery point objective, RPO | How much recent data can we lose? |
| Recovery time objective, RTO | How long can service remain unavailable? |
Suppose an application can lose at most 15 minutes of writes and must recover within an hour. A nightly export alone cannot meet that data-loss target. A restore that takes three hours misses the time target even if every byte is present.
These numbers are business choices. Write down who accepted them, then test whether the machinery meets them.
Pick a Recovery Method Deliberately
For PostgreSQL, a logical dump is convenient for a small database or a portable copy. pg_dump exports a database; custom-format archives can be restored selectively with pg_restore. Cluster-wide roles need separate treatment. PostgreSQL explains the scope in its SQL dump documentation.
Here is a simple drill using a dedicated, disposable database server. The connection strings should come from your normal secret handling, not shell history:
pg_dump --format=custom --file=app.dump "$SOURCE_DATABASE_URL"
pg_restore --list app.dump
pg_restore --exit-on-error --single-transaction \
--no-owner --no-acl \
--dbname="$RESTORE_DRILL_DATABASE_URL" app.dump
The destination must already be an empty database. This example deliberately omits original ownership and grants for a functional drill; test production roles and permissions separately before calling the recovery plan complete. The pg_restore reference describes these options.
Listing the archive checks that its catalog can be read. It does not prove every table can be restored.
Recovering Between Backups
Point-in-time recovery uses a physical base backup plus a continuous chain of archived write-ahead log records. A logical dump is not that base backup. A gap in the required WAL chain can prevent recovery to your chosen time. See PostgreSQL's continuous archiving guide.
For a destructive change at 14:05, the goal might be to restore to just before that transaction. Practice choosing that point. "Restore the latest" can faithfully restore the mistake.
A replica serves a different purpose. It may reproduce the unwanted deletion as quickly as the original write. Keep recoverable history independent of live replication.
Verify What Users Need
A database process accepting connections is the start of verification. Build a short acceptance script for your application:
- check a known account, recent order, and historical record
- compare counts and totals against a recorded checkpoint
- verify constraints and required extensions
- run a critical read and a reversible test write
- confirm the matching object files and encryption keys are available
An orders table can restore perfectly while receipt attachments remain missing from object storage. Recovery crosses the same boundaries as the application.
Disable email, billing, webhooks, and scheduled jobs in the drill environment. Restored job rows should not trigger real customer effects while you test.
Measure the Work Around the Restore
Start the timer when an operator receives the incident. Include gaining access, provisioning capacity, fetching archives, decrypting, replaying changes, verifying data, and switching the application.
Record the restored data timestamp too. A backup made recently is not necessarily a recovery point containing every recent commit.
Keep an encrypted, access-controlled copy outside the primary failure boundary. Test access with the credentials the recovery team will actually have. Decide how expired backups, old keys, and legal retention interact rather than letting storage growth choose the policy.
Schedule drills often enough to catch changes in database size, extensions, permissions, and deployment scripts. The useful artifact is a dated record of a successful recovery, its duration, and any repair steps. That record gives the next operator a procedure they can trust under pressure.