spb/ultra-sharp-agent-skills Public

1---2name: backing-up-databases3description: 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).4---56<!--7Author: Simon-Pierre Boucher8Contact: contact@spboucher.ai9-->1011# Backing Up Databases1213## When to use / when NOT to use14- **Use for:** backup scheduling, dump commands, point-in-time recovery (PITR), restore procedures, retention policies, restore drills, backup encryption and monitoring.15- **Do NOT use for:** replication/failover architecture, schema migrations (`managing-database-migrations`), or access control (`securing-databases`).1617## Core rules18191. **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.202. **Automate or it doesn't exist.** Backups run from cron/systemd timers/managed schedules — never "someone runs pg_dump sometimes."21   - ✅ systemd timer + failure alert22   - ❌ README saying "remember to back up before releases"233. **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.244. **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.255. **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.266. **Encrypt at rest, and keep the key OUT of the backup location.** `age`/`gpg` on dump files or server-side encryption on the bucket.277. **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.288. **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.2930## Workflow31321. Establish requirements: database engine, size, acceptable data loss (RPO) and downtime (RTO), compliance retention.332. Choose the mechanism by rule 3; write the backup script/config with encryption (rule 6) and retention pruning (rule 8).343. Schedule it and wire both failure and dead-man alerts (rule 7).354. Write the restore procedure as a runnable script or step list — not prose in someone's head.365. **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.3738## Edge cases & failure modes39- **Database too big to dump within the window** → switch to physical backups + WAL archiving; never let dumps overlap.40- **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.41- **`pg_dump` version mismatch** → always dump with the NEWER client version; restore with `pg_restore` matching the target server.42- **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).43- **Backup contains secrets/PII** → encryption (rule 6) is non-negotiable; restrict bucket access to the backup role only.4445## References46Runnable scripts and PITR walkthrough: see [references/patterns.md](references/patterns.md)47