swf-monitor/scripts/backup-swfdb.sh

0001 #!/usr/bin/env bash
0002 # Nightly swfdb backup with built-in integrity verification.
0003 #
0004 # Dumps the system database (compressed pg_dump custom format) to the
0005 # durable shared area on the large /data volume, verifies the fresh dump
0006 # by reading its table of contents, and ages out old dumps (nightlies
0007 # after KEEP_DAILY days; first-of-month dumps after KEEP_MONTHLY days).
0008 # Runs standalone, from cron, or by hand; every run appends one line to
0009 # backup.log, ERROR-prefixed on failure. Restore with:
0010 #   pg_restore -h <host> -U <user> -d <db> --clean --if-exists <dump>
0011 #
0012 # On-volume dumps protect against logical loss (bad migration, table
0013 # deletion, corruption), not against loss of /data itself — PGDATA
0014 # shares the volume. Off-host replication is a separate step.
0015 set -uo pipefail
0016
0017 ENV_FILE=${SWF_ENV_FILE:-/opt/swf-monitor/config/env/production.env}
0018 BACKUP_DIR=${SWF_DB_BACKUP_DIR:-/data/swf-shared/db-backups}
0019 LOG_FILE="$BACKUP_DIR/backup.log"
0020 KEEP_DAILY=14     # days a nightly dump is kept
0021 KEEP_MONTHLY=400  # days a first-of-month dump is kept
0022 # The 1.1 GB database compresses to ~48 MB (2026-07); alert if a dump
0023 # arrives at less than half that.
0024 MIN_BYTES=20000000
0025 MIN_TABLES=20
0026
0027 mkdir -p "$BACKUP_DIR"
0028 log() { echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*" | tee -a "$LOG_FILE"; }
0029 fail() { log "ERROR: $*"; exit 1; }
0030
0031 # Literal KEY=VALUE parse — the env file carries unquoted shell
0032 # metacharacters in secret values, so it is never bash-sourced.
0033 env_get() { grep -E "^$1=" "$ENV_FILE" | head -1 | cut -d= -f2-; }
0034 [ -r "$ENV_FILE" ] || fail "cannot read $ENV_FILE"
0035 DB_NAME=$(env_get DB_NAME)
0036 DB_USER=$(env_get DB_USER)
0037 DB_HOST=$(env_get DB_HOST)
0038 PGPASSWORD=$(env_get DB_PASSWORD)
0039 export PGPASSWORD
0040 [ -n "$DB_NAME" ] && [ -n "$DB_USER" ] || fail "DB_NAME/DB_USER missing from $ENV_FILE"
0041 DB_HOST=${DB_HOST:-localhost}
0042
0043 STAMP=$(date +%Y%m%d)
0044 DUMP="$BACKUP_DIR/swfdb-$STAMP.dump"
0045 TMP="$DUMP.inprogress"
0046
0047 pg_dump -Fc -h "$DB_HOST" -U "$DB_USER" -d "$DB_NAME" -f "$TMP" \
0048     || fail "pg_dump failed for $DB_NAME@$DB_HOST"
0049
0050 # Integrity: the dump must carry a plausible table of contents and bulk.
0051 ITEMS=$(pg_restore --list "$TMP" 2>/dev/null | grep -c "TABLE DATA")
0052 [ "${ITEMS:-0}" -ge "$MIN_TABLES" ] \
0053     || fail "verification failed: only ${ITEMS:-0} TABLE DATA entries in $TMP"
0054 SIZE=$(stat -c%s "$TMP")
0055 [ "$SIZE" -ge "$MIN_BYTES" ] \
0056     || fail "dump suspiciously small: $SIZE bytes in $TMP"
0057
0058 mv "$TMP" "$DUMP"
0059
0060 # Retention: nightlies age out; first-of-month dumps are kept longer.
0061 find "$BACKUP_DIR" -name 'swfdb-*.dump' ! -name 'swfdb-????01.dump' -mtime +"$KEEP_DAILY" -delete
0062 find "$BACKUP_DIR" -name 'swfdb-????01.dump' -mtime +"$KEEP_MONTHLY" -delete
0063 find "$BACKUP_DIR" -name '*.inprogress' -mtime +1 -delete
0064
0065 log "OK swfdb-$STAMP.dump: $SIZE bytes, $ITEMS tables verified"