Case Study: A Database Backup Script

From a one-liner to something you can trust

A team's backup was a cron one-liner: pg_dump shop > /backups/shop.sql. It overwrote yesterday's backup, ignored failures, filled the disk and could run twice at once. The improved script: strict mode and traps; getopts for database name, target directory and retention days; require_command pg_dump gzip; an flock so only one instance runs; a timestamped file name; pg_dump piped into gzip with pipefail so a failed dump is noticed; writing to a temporary file and atomically renaming it into place only after success; verifying the result is non-empty and testing the archive with gzip -t; pruning backups older than N days with find -mtime; logging to stderr with timestamps; and a non-zero exit status on any failure so the scheduler (a systemd timer) and monitoring can alert. Credentials come from ~/.pgpass or environment variables, never the script. Finally, a monthly restore test proves the backups actually work.

The core of the backup script

Every step either succeeds or stops the script with a clear error.

#!/usr/bin/env bash
set -Eeuo pipefail
log() { printf '%s %s\n' "$(date -u +%FT%TZ)" "$*" >&2; }

db="${DB_NAME:?set DB_NAME}" dest="${BACKUP_DIR:-/var/backups/postgres}" keep="${KEEP_DAYS:-14}"

exec 9> "/run/lock/backup-$db.lock"
flock -n 9 || { log "backup of $db already running"; exit 0; }

mkdir -p -- "$dest"
stamp=$(date -u +%Y%m%dT%H%M%SZ)
final="$dest/$db-$stamp.sql.gz"
tmp=$(mktemp "$dest/.$db-$stamp.XXXXXX")
trap 'rm -f -- "$tmp"' EXIT

log "dumping $db"
pg_dump --no-owner "$db" | gzip -c > "$tmp"     # pipefail catches pg_dump errors
[[ -s $tmp ]] || { log "empty backup"; exit 1; }
gzip -t -- "$tmp"
mv -- "$tmp" "$final"
log "wrote $final ($(du -h -- "$final" | cut -f1))"

find "$dest" -name "$db-*.sql.gz" -mtime +"$keep" -print -delete >&2
log "pruned backups older than $keep days"

A backup you have not restored is a hope

Schedule an automated restore into a scratch database and run a few sanity queries. Many teams discover broken backups only on the day they need them.

Quick check: In the case study, why is pg_dump piped into gzip only safe with pipefail?

• Without pipefail, a failed pg_dump would be hidden by gzip's successful exit status
• gzip needs pipefail to compress
• pipefail speeds up pg_dump
• pipefail encrypts the backup