Database Backup Strategies That Pass a Disaster Drill

Every team has a backup script. It runs nightly, writes a compressed dump to an S3 bucket, and logs “Backup completed successfully.” Nobody checks whether the file can actually be restored until the database is gone and the restore fails with a cryptic error about a missing WAL segment from three days ago.

A backup that has not been restored is a theory. A backup that has been restored, verified, and measured is infrastructure. The difference is a disaster drill — a scheduled, documented process where you rebuild the database from scratch using nothing but the artifacts your backup pipeline produces. If the drill passes, you have backups. If it fails, you have a file that happens to be large.

Why your backup script probably lies to you

pg_dump writes a consistent snapshot. mysqldump does the same. mongodump captures the oplog position. All three produce valid output files, and all three log success. None of them verify that the output is restorable.

The standard failure modes are predictable once you have seen them:

• The dump is incomplete. The export ran but the S3 upload was interrupted. The file on disk is 4 GB. The file in S3 is 2.1 GB. The script logged success because the pg_dump exit code was zero. The upload failure scrolled past in stdout and nobody read it.
• The dump references a WAL segment that was already recycled. Postgres recycles WAL files on a schedule. Your nightly dump captures a snapshot at 2 a.m. and references WAL segment 000000010000000A0000003E. By 3 a.m., that segment has been recycled because wal_keep_size was set too low and the archive command was not configured. The dump is a paperweight.
• The dump requires extensions that are not installed on the restore target. Your production database has pg_trgm, uuid-ossp, and postgis. The restore target is a fresh Postgres container that has none of these. pg_restore fails on the first CREATE EXTENSION statement.
• The dump contains roles and tablespaces that do not exist on the target. pg_dumpall --globals-only captures roles. Your restore script does not. The restored data is there but no user can log in.

The three things a backup must survive

A backup is not a file. It is a capability: the ability to return the database to a known state within an acceptable time window. Testing that capability means proving three things.

One: the backup restores at all. This is the bar most teams miss. Schedule a weekly or nightly restore to a scratch instance. It does not need to be a full production clone — a small VM or container with enough disk to hold the uncompressed dump is sufficient. The restore script must run pg_restore --exit-on-error (or the equivalent for your database) and exit non-zero on failure. If the restore fails, alert.

Two: the data is consistent. A dump that restores without errors can still contain corrupted data — a partially written row, an index that references a missing tuple, a sequence that reset to zero. Run pg_restore with --schema-only on a separate pass to verify the DDL is intact, then run a set of sanity queries against the restored database: row counts for key tables, foreign key integrity checks, a sample of recent rows compared against production timestamps.

Three: the restore finishes within your recovery time objective. A backup that takes 6 hours to restore when your RTO is 2 hours is not a backup — it is a historical archive. Measure every restore. If the time is trending upward as data grows, you need either incremental restore (WAL replay from a base backup) or a faster restore target (larger instance with more I/O throughput). The time to discover this is during a drill, not during an outage.

What a real disaster drill looks like

A disaster drill is not “someone runs a restore command.” It is a documented, scheduled exercise with a clear pass/fail criterion and a post-drill writeup.

Pick a Thursday afternoon. Announce it in the team channel. The process:

1. Provision a fresh database instance. Same major version as production, comparable disk I/O, no existing data. A t3.medium on EC2 or a $10 Hetzner instance is fine for a drill.
2. Fetch the most recent backup from your object storage. Do not use a backup you generated five minutes ago. Use the one the pipeline produced last night, because that is the one you would have during a real incident.
3. Restore the dump. Time it. Log every command and every error.
4. Apply WAL segments if using point-in-time recovery. Verify the WAL archive is complete between the backup timestamp and now. A gap means your PITR chain is broken.
5. Run verification queries. Row counts match production (within the backup lag). Recent records are present. Application can connect and run its startup queries without errors.
6. Write down what happened. What took longer than expected? What step had an undocumented dependency? What script ran a command you had to look up?

If step 5 passes, the drill passes. If any step fails, you have a backup gap, and you fix it before the next drill. Do not skip drills because the last one passed — the backup pipeline changes whenever the schema changes, the Postgres version changes, or the WAL archiving configuration changes. A drill that passed in March says nothing about the backup pipeline in July.

WAL archiving and point-in-time recovery

A nightly pg_dump gives you a restore point with up to 24 hours of data loss. For databases where losing a day of transactions is unacceptable, you need continuous WAL archiving.

Postgres writes every transaction to a write-ahead log before applying it to data files. If you continuously archive those WAL segments to an external location — S3, an NFS mount, a dedicated archive server — you can replay them against a base backup to restore the database to any point in time between the base backup and the last archived segment.

The setup requires three Postgres configuration parameters:

wal_level = replica           # or logical if you also need CDC
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'

The archive_command is the critical piece. It runs for every completed WAL segment. If it fails, Postgres retries. If it fails persistently, Postgres keeps the WAL segment on disk and eventually runs out of disk space. The archive command must be reliable and fast — pgBackRest, wal-g, and barman are the established tools, and each has handled the edge cases you do not want to rediscover.

With WAL archiving in place, a base backup plus all archived segments since that backup gives you point-in-time recovery to any second within the archive window. The restore command looks like:

pgbackrest --stanza=main restore --type=time "--target=2026-07-20 14:22:00"

This restores the base backup and replays WAL up to the specified timestamp. The database comes back in a consistent state with all transactions committed before that moment.

The tradeoff is operational complexity. WAL archiving adds a daemon to monitor, an S3 bucket to manage retention on, and a restore process that requires understanding Postgres timeline mechanics. For a database where 24 hours of data loss is acceptable — a blog, an internal dashboard, a read-only analytics replica — a nightly dump is simpler and good enough. For a database where data loss means financial liability — orders, payments, medical records — WAL archiving is not optional.