---
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).
---

<!--
Author: Simon-Pierre Boucher
Contact: contact@spboucher.ai
-->

# 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](references/patterns.md)