# name: backing-up-databases description: Designs and implements database backup and restore strategies including scheduled dumps, point-in-time recovery, retention, and restore drills. Use when the user asks to back up a database, set up pg_dump or mysqldump, configure WAL archiving or point-in-time recovery, restore a database or a single table, define backup retention, or verify that backups actually work. Do not use for high-availability or replication setup, or for schema migrations (managing-database-migrations).

# Backing Up Databases

# When to use / when NOT to use

• Use for: backup scheduling, dump commands, point-in-time recovery (PITR), restore procedures, retention policies, restore drills, backup encryption and monitoring.
• Do NOT use for: replication/failover architecture, schema migrations (managing-database-migrations), or access control (securing-databases).

# Core rules

1. A backup is only real once it has been restored. An untested backup is a hope, not a backup. Every strategy MUST include a scheduled restore drill (monthly at minimum) into a scratch instance, with row-count spot checks.
2. Automate or it doesn't exist. Backups run from cron/systemd timers/managed schedules — never "someone runs pg_dump sometimes."
   • ✅ systemd timer + failure alert
   • ❌ README saying "remember to back up before releases"
3. Pick logical vs physical by size and RPO. Default: logical dumps (pg_dump -Fc) for databases < ~100 GB or when per-table restore matters; physical base backup + WAL archiving when the database is larger or the recovery-point objective is minutes, not hours.
4. PITR needs both halves. A base backup without continuous WAL/binlog archiving cannot restore to "5 minutes before the bad DELETE." If the user needs that sentence to be true, set up WAL archiving.
5. Follow 3-2-1. Three copies, two media/locations, one off-site (different cloud region or provider). The backup living on the same disk as the database counts as zero copies.
6. Encrypt at rest, and keep the key OUT of the backup location. age/gpg on dump files or server-side encryption on the bucket.
7. Failures must be loud. Alert on job failure AND on "no successful backup in N hours" (dead-man switch) — a silently disabled cron job is the classic disaster.
8. Retention is a policy, not an accident. Default: 7 daily + 4 weekly + 12 monthly; adjust to compliance needs and state it in the backup script header.

# Workflow

1. Establish requirements: database engine, size, acceptable data loss (RPO) and downtime (RTO), compliance retention.
2. Choose the mechanism by rule 3; write the backup script/config with encryption (rule 6) and retention pruning (rule 8).
3. Schedule it and wire both failure and dead-man alerts (rule 7).
4. Write the restore procedure as a runnable script or step list — not prose in someone's head.
5. Validate: perform the restore into a scratch database NOW, verify with row counts on the 3 largest tables and one known record. Record the restore duration (that is your real RTO). The task is not done until this restore succeeds.

# Edge cases & failure modes

• Database too big to dump within the window → switch to physical backups + WAL archiving; never let dumps overlap.
• Restore drill has no scratch environment → a temporary Docker container of the same engine version is the minimum; same major version is mandatory for physical restores.
• pg_dump version mismatch → always dump with the NEWER client version; restore with pg_restore matching the target server.
• Managed databases (RDS/Cloud SQL) → provider snapshots + PITR cover rules 3–4, but still run logical dumps for off-provider copies (rule 5) and still do restore drills (rule 1).
• Backup contains secrets/PII → encryption (rule 6) is non-negotiable; restrict bucket access to the backup role only.

# References

Runnable scripts and PITR walkthrough: see references/patterns.md