# 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.