Backup & Recovery Drill - Recipes and the Quarterly Restore Test

Status: Active
Last Updated: 2026-08-26
Category: Databases - Operations
Prerequisites: postgres-tuning-basics, sqlite-production-patterns
Time: 3 hours (initial setup) + 1 hour/quarter (drill)
Tags: backup, restore, pg-dump, litestream, minio, disaster-recovery, runbook

Summary

Concrete backup recipes for PostgreSQL (pg_dump/pg_restore) and SQLite (.backup/Litestream), scheduled dumps pushed to MinIO object storage, and a quarterly restore drill runbook that proves the backups actually work. A backup that has never been restored is a hypothesis, not a backup.

🎯 What You'll Learn

By the end of this article, you'll be able to:

✅ Run consistent pg_dump backups of the fogserv.cloud CRM/CMS database
✅ Schedule encrypted dumps to MinIO with retention pruning
✅ Restore both Postgres and SQLite under drill conditions
✅ Execute (and document) the quarterly restore drill

Context / Why This Matters

The production dataset (schema-overview) is projected at ~582 MB — small enough to back up in seconds, which means there is no excuse for not having a complete, tested backup story. The failure mode we're preventing isn't disk loss alone; it's bad migrations (prisma-migrations-guide), accidental cascade deletes, ransomware, and host loss. Recovery objectives for our scale: RPO ≤ 24 h (daily full dumps + continuous WAL/Litestream where deployed), RTO ≤ 1 h. This article is the database slice of the wider DR picture in disaster-recovery.

Implementation / Core Content

Principle: 3-2-1 at Our Scale

3 copies: primary DB, local staging copy, offsite object storage (MinIO).
2 media/classes: local volume + S3-compatible bucket.
1 tested restore procedure — this is the part everyone skips; the drill section enforces it.

Encryption before upload is mandatory: dump files contain subscriber emails and password hashes. Use age or GPG symmetric encryption with a key stored separately from the backups (see secrets).

PostgreSQL: pg_dump Recipes

# Consistent logical backup (custom format = compressed + parallel-restorable)
pg_dump \
  --format=custom \
  --jobs=2 \
  --file=/backups/fogserv-$(date +%F).dump \
  "$DATABASE_URL"

Why custom format over plain SQL: compressed, supports selective restore (pg_restore --table), supports parallel jobs, and lets you rename the database on restore.

Restore into a fresh database:

createdb fogserv_restored
pg_restore --dbname=fogserv_restored --jobs=2 /backups/fogserv-2026-08-26.dump
# Then verify:
psql fogserv_restored -c 'SELECT count(*) FROM "Subscriber";'

Notes:

pg_dump takes a consistent snapshot while the server stays online; it does not block reads/writes.
Custom-format dumps must be restored with the same or newer major version of pg_restore.
For point-in-time recovery beyond daily granularity you need WAL archiving — worth it only when hourly RPO becomes a requirement; document the decision either way.

SQLite Recipes

# Consistent online snapshot (never cp the live file)
sqlite3 /data/app.db ".backup '/backups/app-$(date +%F).db'"

If Litestream is running (sqlite-production-patterns), continuous replication replaces the nightly snapshot as the primary mechanism; keep a weekly .backup anyway as an independent format-level check.

Scheduled Dumps to MinIO

#!/usr/bin/env bash
# /opt/db-backups/backup-to-minio.sh
set -euo pipefail
STAMP=$(date +%F)
DIR=/backups/staging
mkdir -p "$DIR"

if [ -n "${DATABASE_URL:-}" ] && [[ "$DATABASE_URL" == postgres* ]]; then
  pg_dump --format=custom --jobs=2 --file="$DIR/fogserv-$STAMP.dump" "$DATABASE_URL"
else
  sqlite3 /data/app.db ".backup '$DIR/app-$STAMP.db'"
fi

AGE_KEY="${BACKUP_AGE_RECIPIENT:?}" 
age -r "$AGE_KEY" -o "$DIR/fogserv-$STAMP.age" "$DIR/fogserv-$STAMP."{dump,db} 2>/dev/null || \
  age -r "$AGE_KEY" -o "$DIR/fogserv-$STAMP.dump.age" "$DIR/fogserv-$STAMP.dump"

mc cp "$DIR"/fogserv-$STAMP.*.age minio/db-backups/
mc rm --older-than 30d --recursive minio/db-backups/     # retention
find "$DIR" -mtime +7 -delete                             # local staging cleanup

Systemd timer (prefer over cron for logging/jitter):

# /etc/systemd/system/db-backup.timer
[Timer]
OnCalendar=*-*-* 03:30:00
Persistent=true
[Install]
WantedBy=timers.target

Verify success by checking the remote listing, not just exit codes:

mc ls minio/db-backups/ | tail -5

Alert on absence: a scheduled job in uptime-kuma-setup or simple-alerts should page if today's object doesn't exist by 05:00.

The Quarterly Restore Drill Runbook

Run every quarter; target one hour. Rotate who runs it so knowledge isn't siloed.

Announce & pick artifact (5 min): choose yesterday's backup from MinIO. Record its name and checksum.
Isolated restore (20 min):
- Postgres: spin up a scratch container (docker run -d --name drill-pg postgres:16-alpine), create fogserv_drill, pg_restore into it. Never restore over production.
- SQLite: litestream restore (or decrypt + open the .backup file).

Integrity verification (10 min):

-- Postgres
SELECT count(*) FROM "User";           -- matches prod counts ± expected drift
SELECT count(*) FROM "CampaignLog";
PRAGMA integrity_check;                 -- SQLite variant

Spot-check one known record (e.g., a specific published Post).

Application smoke test (15 min): point a local app instance at DATABASE_URL=...fogserv_drill and log in, load /kb, render one post. Data that restores but doesn't serve is still failed backup.
Timing & gaps (5 min): record wall-clock duration vs RTO; note any missing objects, decryption issues, stale credentials.
Write-up (5 min): append results to this article's Change Log area or a dated drill note in Forgejo issues. Fix any breakage this quarter, not "eventually".

Pass criteria: restore completes < 60 min, integrity checks pass, app serves real data, zero manual improvisation beyond the runbook.

Practical Examples

Example 1: Selective table restore after a bad migration

pg_restore --dbname=fogserv --table=CampaignLog --clean --if-exists \
  /backups/fogserv-2026-08-25.dump

Recovers one wrecked table without touching the rest — only possible because we chose custom format.

Example 2: Full drill command sequence (Postgres)

docker run -d --name drill-pg -e POSTGRES_PASSWORD=drill postgres:16-alpine
sleep 3
docker exec drill-pg createdb -U postgres fogserv_drill
cat /backups/fogserv-2026-08-25.dump | docker exec -i drill-pg \
  pg_restore -U postgres -d fogserv_drill --jobs=2
docker exec drill-pg psql -U postgres -d fogserv_drill \
  -c 'SELECT count(*) FROM "Post";'
docker rm -f drill-pg   # always tear down

Common Pitfalls & Troubleshooting

Problem	Cause	Fix
Backup "succeeds" but can't be restored	Never tested; wrong format/version	Quarterly drill catches this; pin pg_dump/pg_restore major versions
Dump contains plaintext PII on shared storage	Uploaded without encryption	age/GPG before upload; key held per secrets
Corrupt SQLite backup	Copied live file with `cp`	Use `.backup` command or Litestream exclusively
Retention deleted everything old during incident	Retention window shorter than detection time	Keep ≥ 30 days; monthly archive tier for year
Restore fails: `role "appuser" does not exist`	Dump references roles not present in fresh cluster	Recreate roles first or use `--no-owner --no-privileges`
Nobody noticed backups silently failing for months	No absence alerting	Alert on missing daily object; see observability articles

Next Steps / Ops Actions

Deploy backup-to-minio.sh + timer this week; confirm tomorrow's object appears.
Add the backup-absence alert to monitoring (why-monitor).
Put next quarter's drill on the team calendar now; record last drill date here after each run.
Coordinate database restores with full-host recovery plans in disaster-recovery and file-level backups in restic-backups.

Sources & Related

External references consulted:

Change Log

2026-08-26

Initial creation covering pg_dump/sqlite recipes, MinIO scheduling, and the quarterly drill runbook.