Database operations · Lesson 2 of 6 · 4 min read
Backups and recovery
Kinds of backup
| Type | Tools | Pros | Cons |
|---|---|---|---|
| Logical | pg_dump, mysqldump, mydumper |
Portable across versions and platforms; restore one table | Slow for large databases; a point in time only |
| Physical | pg_basebackup, pgBackRest, Percona XtraBackup |
Fast for big databases; basis for PITR | Same major version and platform |
| Storage snapshots | EBS and RDS snapshots | Fast, incremental, simple | Crash-consistent only unless coordinated; tied to the platform |
| Continuous log archiving | WAL archiving, binlog shipping | Recovery to any second | Needs a base backup and every log segment since |
Point-in-time recovery (PITR) = restore the latest base backup before the target time, then replay archived logs up to just before the mistake (for example, one second before DROP TABLE).
Enlarge diagram
Swipe sideways to see all of it, or tap to open it full screen.
Backups on RDS and Aurora
- Automated backups: a daily snapshot plus transaction logs uploaded every few minutes, kept for 1–35 days. They give PITR to any second within the retention window, typically up to about five minutes ago.
- Manual snapshots are kept until you delete them; use them before risky changes and for long-term retention.
- A restore always creates a new instance with a new endpoint; you then point the application at it or rename. Plan this into the runbook.
- Copy snapshots to another region and another account for disaster recovery and protection against account compromise. Encrypted snapshots need the KMS key shared or re-encrypted with a key in the target.
- AWS Backup centralises schedules and retention across services, with Vault Lock for immutable (WORM) backups that even an administrator can't delete before they expire, which protects against ransomware and malicious deletion.
- Deleting an RDS instance removes its automated backups unless you choose to retain them; take a final snapshot.
For self-managed PostgreSQL, pgBackRest or WAL-G handle full and incremental backups to S3 with WAL archiving; on Kubernetes, operators such as CloudNativePG build on them.
Enlarge diagram
Swipe sideways to see all of it, or tap to open it full screen.
Restores are the real test
A backup you haven't restored is a hope, not a backup. Good practice:
- Automate restore tests: regularly restore the latest backup to a scratch instance, run checks (row counts, a sample query, application smoke test), record the time taken, then delete it.
- Measure the real RTO: restoring a 2 TB database can take hours, so know the number before an incident.
- Follow 3-2-1: three copies, on two kinds of storage, one off-site (another region or account), with at least one immutable.
- Write the runbook: who decides to restore, to which point in time, how the app is switched, how to reconcile data written after the restore point.