Runbooks & Refs • Operational

Runbook: Nightly Postgres Backups with Integrity Checks and Retention

TL;DR: The backup approach behind my self-hosted Postgres databases — a nightly gzipped dump that is integrity-checked before old backups are pruned, retained for 30 days, and (depending on the stack) shipped to object storage. Plus the restore path, because a backup you haven't restored isn't a backup.

Every self-hosted database I run is backed up nightly with the same shape: dump → verify → retain → (optionally) ship offsite, scheduled by cron and run before risky operations like deploys.

Dump (compressed)

# pg_dump custom format, streamed straight into gzip
docker exec "$PG_CONTAINER" pg_dump -U "$DB_USER" -d "$DB_NAME" -F c \
  | gzip > "$BACKUP_DIR/aicd_backup_$(date +%Y%m%d_%H%M%S).sql.gz"

Verify before you trust it

# A corrupt gzip is worse than no backup — check it immediately.
if gzip -t "$BACKUP_DIR/$BACKUP_FILE"; then
  echo "Backup file integrity verified"
else
  echo "Backup file is corrupted!" >&2; exit 1
fi

Retention (30 days)

On disk, prune by age:

find "$BACKUP_DIR" -name 'aicd_backup_*.sql.gz' -type f -mtime +30 -delete

Or, when backups go to object storage (MinIO), let a lifecycle rule expire them:

mc ilm rule add --expiry-days 30 "$MINIO_ALIAS/$BUCKET"
mc cp /backup/db_$TIMESTAMP.sql.gz "$MINIO_ALIAS/$BUCKET/db/"

Schedule

# Daily at 02:00 (or 03:00 for the MinIO-backed stack)
0 2 * * * /path/to/backup-db.sh >> /var/log/db-backup.log 2>&1

The script also warns when disk usage crosses 80% / 90%, so a full disk surfaces before it silently breaks the next backup.

Restore

# Decompress and restore a custom-format dump
gunzip -c aicd_backup_20260101_020000.sql.gz | \
  docker exec -i "$PG_CONTAINER" pg_restore -U "$DB_USER" -d "$DB_NAME" --clean

This same backup runs automatically before every deploy, so the worst-case recovery point is seconds before the change that broke something.