Backup and Recovery
Designing a backup strategy from a stated recovery objective, and the verification without which a backup is only an assumption.
Backups exist to recover from things replication cannot fix: an accidental DROP, a bad
migration, corruption, ransomware, or the loss of a whole region.
Start from the objectives
Two numbers drive every other decision:
- Recovery point objective (RPO) — how much data you can afford to lose, in time.
- Recovery time objective (RTO) — how long you can afford to be down.
An RPO of five minutes rules out nightly logical dumps and requires continuous archiving of the transaction log. An RTO of one hour on a 4 TB database rules out a restore path that reads everything from cold object storage at 200 MB/s. Measure, do not assume — the restore rate you actually achieve is the number that matters.
Backup types
Most production systems need a physical backup plus continuous transaction log archiving, which together give point-in-time recovery. Logical dumps remain useful for moving a single table or database between versions.
The 3-2-1 shape
Three copies, on two kinds of media, one off-site. In cloud terms: the primary's storage, a backup in the same region for fast restore, and a copy in another region or another account for the failure that takes out the first two.
The separate account matters more than the separate region. A credential compromise or a faulty automation that can delete your database can usually also delete backups stored under the same identity. Use write-once retention or object lock where available.
Verification
A verification routine that catches real problems:
- Restore the most recent backup into an isolated environment, automatically and on a schedule.
- Record the elapsed time and compare it against the RTO.
- Run integrity checks: row counts per table, at least one business invariant, and the engine's own consistency check if it has one.
- Alert if a verification has not succeeded within the expected interval — not only when one fails.
See Restore Testing.
What to back up besides the data
A restore that produces a database nobody can connect to is not a recovery. Include roles and grants, extensions and their versions, configuration files, and the encryption keys. Store the restore procedure itself somewhere that does not depend on the system being restored.