spb/ultra-sharp-agent-skills Public

1<!--2Author: Simon-Pierre Boucher3Contact: contact@spboucher.ai4-->56# Backup & Restore Patterns — Recipes78## Contents9- Nightly logical backup (PostgreSQL)10- Restore from a logical dump11- Point-in-time recovery (PITR) setup12- PITR restore walkthrough13- MySQL equivalents14- Retention pruning15- Dead-man monitoring16- Gotchas1718## Nightly logical backup (PostgreSQL)1920```bash21#!/usr/bin/env bash22# nightly-backup.sh — custom-format dump, encrypted, uploaded off-site.23# Retention: 7 daily / 4 weekly / 12 monthly (prune step below).24set -euo pipefail2526DB="appdb"27STAMP=$(date +%F)28OUT="/var/backups/pg/${DB}-${STAMP}.dump.age"2930# -Fc = custom format: compressed, supports parallel & per-table restore31pg_dump -Fc --no-owner "$DB" \32  | age -r "$BACKUP_PUBLIC_KEY" > "$OUT"3334# off-site copy (different region/provider than the DB)35aws s3 cp "$OUT" "s3://acme-db-backups/${DB}/" --only-show-errors3637# dead-man ping: monitoring alerts if this URL isn't hit every 24h38curl -fsS "https://hc-ping.com/${HEALTHCHECK_ID}" > /dev/null39```4041Schedule with a systemd timer (survives reboots, logs to journal), not user cron.4243## Restore from a logical dump4445```bash46age -d -i backup_key.txt appdb-2026-08-05.dump.age > appdb.dump47createdb appdb_restore48pg_restore -d appdb_restore --no-owner -j 4 appdb.dump   # -j 4: parallel4950# verification minimum51psql appdb_restore -c "SELECT count(*) FROM orders;"52psql appdb_restore -c "SELECT count(*) FROM users;"53psql appdb_restore -c "SELECT email FROM users WHERE id = 1;"  # known record54```5556Single table only: `pg_restore -d appdb_restore -t orders appdb.dump`5758## Point-in-time recovery (PITR) setup5960postgresql.conf:6162```ini63wal_level = replica64archive_mode = on65# archive to object storage; %p = file path, %f = file name66archive_command = 'wal-g wal-push %p'   # or: aws s3 cp %p s3://bucket/wal/%f67```6869Base backup (weekly, plus WAL stream between):7071```bash72wal-g backup-push /var/lib/postgresql/16/main73# or without wal-g:74pg_basebackup -D /var/backups/pg/base-$(date +%F) -Ft -z -Xs -P75```7677## PITR restore walkthrough7879Recover to just before a bad statement at 14:32:10:8081```bash82# 1. stop postgres, move the broken data dir aside83# 2. restore the latest base backup BEFORE the target time into the data dir84wal-g backup-fetch /var/lib/postgresql/16/main LATEST85# 3. tell recovery where to stop86cat > /var/lib/postgresql/16/main/postgresql.auto.conf <<'EOF'87restore_command = 'wal-g wal-fetch %f %p'88recovery_target_time = '2026-08-05 14:32:00+00'89recovery_target_action = 'promote'90EOF91touch /var/lib/postgresql/16/main/recovery.signal92# 4. start postgres; it replays WAL to the target time, then promotes93```9495## MySQL equivalents9697```bash98# logical dump, single transaction = consistent without locking InnoDB99mysqldump --single-transaction --routines --triggers appdb | gzip > appdb.sql.gz100# PITR half: enable binlog (log_bin=ON), archive binlogs off-site101# restore: load dump, then replay binlogs to a point:102mysqlbinlog --stop-datetime="2026-08-05 14:32:00" binlog.0000* | mysql appdb103```104105## Retention pruning106107```bash108# keep 7 daily; weekly (Sunday) kept 28 days; monthly (1st) kept 365 days109find /var/backups/pg -name '*.dump.age' -mtime +7 \110  ! -newermt "$(date -d 'last sunday' +%F)" -delete 2>/dev/null || true111# simplest robust option: let the object store do it — S3 lifecycle rules112# per prefix daily/ weekly/ monthly/, and upload into the matching prefix.113```114115Prefer bucket lifecycle policies over local `find` arithmetic when possible.116117## Dead-man monitoring118119Alert on absence, not just failure:120121- Push a ping (healthchecks.io, Cronitor, PagerDuty heartbeat) as the LAST122  line of the backup script (only reached on success).123- Second check: a daily job that fails if the newest object in the backup124  bucket is older than 26 h (24 h schedule + 2 h grace).125126## Gotchas127128- **`pg_dump` while DDL runs** can fail mid-dump with "relation changed";129  schedule dumps away from migration windows.130- **Physical restores require the same major version and architecture**;131  logical dumps are the portable path across versions.132- **`--no-owner`** on dump/restore avoids failures when the scratch instance133  lacks the original roles.134- **WAL archiving fills the disk if `archive_command` fails** — PostgreSQL keeps135  WAL until archived. Alert on `pg_stat_archiver.failed_count`.136- **Snapshots of a running DB without filesystem/DB coordination** can be137  torn on multi-volume setups; use `pg_basebackup`/provider snapshots instead.138- **Testing restores against the production instance** — never; always a139  scratch instance or container.140