If You Haven't Restored It, It Isn't a Backup

Almost every company has database backups. Far fewer have restored one recently, timed it, and checked that the result was complete. Backups fail quietly — a full disk, an expired credential, a changed path — and the failure is usually discovered at the worst possible moment: during the incident in which you need them.

Start from recovery targets, not from tools

Two numbers should drive the whole design:

• RPO (recovery point objective) — how much data you can afford to lose. "Up to 24 hours" and "under a minute" lead to completely different setups.
• RTO (recovery time objective) — how long the database may be unavailable while you restore.

Agree on both with the business, per system. A nightly dump may be perfectly fine for an internal tool and unacceptable for the order database.

Logical and physical backups

Logical backups — pg_dump, mysqldump — export the data as SQL or an archive. They are portable across versions and handy for single tables, but they are slow to restore for large databases and only capture the moment they were taken.

Physical backups copy the data files themselves. For PostgreSQL, tools such as pgBackRest, WAL-G or Barman combine periodic base backups with continuous archiving of the write-ahead log. For MySQL, Percona XtraBackup plus binary logs plays the same role. This combination enables point-in-time recovery.

Point-in-time recovery

Most data loss is not hardware failure — it is a DELETE without a WHERE, a bad migration or a buggy deployment. A nightly backup forces you to choose between losing a day of data and keeping the damage. With point-in-time recovery you restore the latest base backup and replay the log up to one minute before the mistake.

The quiet failures

These are the problems we find most often in backup reviews — none of them raise an alarm by default:

• Log archiving that silently fails. In PostgreSQL, pg_stat_archiver shows failed_count and last_failed_time; if nobody monitors them, point-in-time recovery may have been impossible for weeks.
• Retention that deletes too much. Archived WAL deleted while the oldest base backup still needs it makes that backup unusable for PITR.
• Backups stored with the same credentials as production. Anyone — or any ransomware — that can delete the database can then delete the backups too. Keep at least one copy offsite and immutable, for example in object storage with object lock.
• Encryption keys stored only on the server being backed up. An encrypted backup without its key is not a backup.
• Backups taken from a replica that has stopped replicating. They complete successfully every night — with data from three weeks ago.

Automate restore tests

The only proof that a backup works is a successful restore. Automate it: on a schedule — weekly for critical systems — restore the latest backup into an isolated environment, recover to a recent point in time, and then verify the result:

1. Check that the database starts and recovery reached the expected point in time.
2. Compare row counts of key tables with production.
3. Run integrity checks — for PostgreSQL, pg_amcheck can verify tables and B-tree indexes.
4. Run a short application smoke test against the restored copy.
5. Record how long the whole restore took and compare it with your RTO.

Alert on a failed restore test exactly like you alert on a failed backup.

Write the runbook before the incident

During an incident nobody should be reading tool documentation for the first time. Write a step-by-step restore runbook with real commands, paths and credentials locations, and have someone other than its author follow it during a test. Every point where they get stuck is a point where a real recovery would have stalled.