Opscraft
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 diagrambase backup02:00 nightlyreplay WAL02:00 → 14:31:59stopone secondbefore DROPTABLEnew instanceexport rows orswitch app
Figure 1. Point-in-time recovery: restore the last base backup, then replay archived logs up to just before the mistake.
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 diagramprod accountRDS automatedbackupsother regionsnapshot copiesbackup accountAWS Backup vaultVault Lockcan’t be deletedearly
Figure 2. Backups that survive a lost region or a compromised account.
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.