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

# Backup & Restore Patterns — Recipes

## Contents
- Nightly logical backup (PostgreSQL)
- Restore from a logical dump
- Point-in-time recovery (PITR) setup
- PITR restore walkthrough
- MySQL equivalents
- Retention pruning
- Dead-man monitoring
- Gotchas

## Nightly logical backup (PostgreSQL)

```bash
#!/usr/bin/env bash
# nightly-backup.sh — custom-format dump, encrypted, uploaded off-site.
# Retention: 7 daily / 4 weekly / 12 monthly (prune step below).
set -euo pipefail

DB="appdb"
STAMP=$(date +%F)
OUT="/var/backups/pg/${DB}-${STAMP}.dump.age"

# -Fc = custom format: compressed, supports parallel & per-table restore
pg_dump -Fc --no-owner "$DB" \
  | age -r "$BACKUP_PUBLIC_KEY" > "$OUT"

# off-site copy (different region/provider than the DB)
aws s3 cp "$OUT" "s3://acme-db-backups/${DB}/" --only-show-errors

# dead-man ping: monitoring alerts if this URL isn't hit every 24h
curl -fsS "https://hc-ping.com/${HEALTHCHECK_ID}" > /dev/null
```

Schedule with a systemd timer (survives reboots, logs to journal), not user cron.

## Restore from a logical dump

```bash
age -d -i backup_key.txt appdb-2026-08-05.dump.age > appdb.dump
createdb appdb_restore
pg_restore -d appdb_restore --no-owner -j 4 appdb.dump   # -j 4: parallel

# verification minimum
psql appdb_restore -c "SELECT count(*) FROM orders;"
psql appdb_restore -c "SELECT count(*) FROM users;"
psql appdb_restore -c "SELECT email FROM users WHERE id = 1;"  # known record
```

Single table only: `pg_restore -d appdb_restore -t orders appdb.dump`

## Point-in-time recovery (PITR) setup

postgresql.conf:

```ini
wal_level = replica
archive_mode = on
# archive to object storage; %p = file path, %f = file name
archive_command = 'wal-g wal-push %p'   # or: aws s3 cp %p s3://bucket/wal/%f
```

Base backup (weekly, plus WAL stream between):

```bash
wal-g backup-push /var/lib/postgresql/16/main
# or without wal-g:
pg_basebackup -D /var/backups/pg/base-$(date +%F) -Ft -z -Xs -P
```

## PITR restore walkthrough

Recover to just before a bad statement at 14:32:10:

```bash
# 1. stop postgres, move the broken data dir aside
# 2. restore the latest base backup BEFORE the target time into the data dir
wal-g backup-fetch /var/lib/postgresql/16/main LATEST
# 3. tell recovery where to stop
cat > /var/lib/postgresql/16/main/postgresql.auto.conf <<'EOF'
restore_command = 'wal-g wal-fetch %f %p'
recovery_target_time = '2026-08-05 14:32:00+00'
recovery_target_action = 'promote'
EOF
touch /var/lib/postgresql/16/main/recovery.signal
# 4. start postgres; it replays WAL to the target time, then promotes
```

## MySQL equivalents

```bash
# logical dump, single transaction = consistent without locking InnoDB
mysqldump --single-transaction --routines --triggers appdb | gzip > appdb.sql.gz
# PITR half: enable binlog (log_bin=ON), archive binlogs off-site
# restore: load dump, then replay binlogs to a point:
mysqlbinlog --stop-datetime="2026-08-05 14:32:00" binlog.0000* | mysql appdb
```

## Retention pruning

```bash
# keep 7 daily; weekly (Sunday) kept 28 days; monthly (1st) kept 365 days
find /var/backups/pg -name '*.dump.age' -mtime +7 \
  ! -newermt "$(date -d 'last sunday' +%F)" -delete 2>/dev/null || true
# simplest robust option: let the object store do it — S3 lifecycle rules
# per prefix daily/ weekly/ monthly/, and upload into the matching prefix.
```

Prefer bucket lifecycle policies over local `find` arithmetic when possible.

## Dead-man monitoring

Alert on absence, not just failure:

- Push a ping (healthchecks.io, Cronitor, PagerDuty heartbeat) as the LAST
  line of the backup script (only reached on success).
- Second check: a daily job that fails if the newest object in the backup
  bucket is older than 26 h (24 h schedule + 2 h grace).

## Gotchas

- **`pg_dump` while DDL runs** can fail mid-dump with "relation changed";
  schedule dumps away from migration windows.
- **Physical restores require the same major version and architecture**;
  logical dumps are the portable path across versions.
- **`--no-owner`** on dump/restore avoids failures when the scratch instance
  lacks the original roles.
- **WAL archiving fills the disk if `archive_command` fails** — PostgreSQL keeps
  WAL until archived. Alert on `pg_stat_archiver.failed_count`.
- **Snapshots of a running DB without filesystem/DB coordination** can be
  torn on multi-volume setups; use `pg_basebackup`/provider snapshots instead.
- **Testing restores against the production instance** — never; always a
  scratch instance or container.