spb/cancerindex

1#!/usr/bin/env bash2# CancerIndex — PostgreSQL backup (CLAUDE.md §172).3#4#   pg_dump -Fc <database> → $BACKUP_DIR/cancerindex-YYYYMMDD-HHMM.dump, verified with pg_restore --list,5#   retention: last 14 daily dumps + 8 weekly (the newest dump of each ISO week), everything else pruned.6#   Logs to $LOG_DIR/backup.log; exits non-zero on any failure (PM2 shows the process as errored).7#8# Usage (on the node): cd ~/apps/cancerindex && bash deploy/backup.sh9#   DATABASE_URL   postgres://localhost:5432/cancerindex   (default)10#   BACKUP_DIR     ~/apps/cancerindex/backups              (default)11#   LOG_DIR        <repo>/logs                             (default)12#   KEEP_DAILY=14  KEEP_WEEKLY=813#14# Scheduled by PM2 (`cancerindex-backup`, cron_restart "20 5 * * *", autorestart false) — see the mld manifest.15# The raw data lake (data/raw) is append-only and NOT part of this dump: rsync it separately (deploy/README.md).16set -uo pipefail1718cd "$(dirname "$0")/.." || exit 119DATABASE_URL="${DATABASE_URL:-postgres://localhost:5432/cancerindex}"20BACKUP_DIR="${BACKUP_DIR:-$HOME/apps/cancerindex/backups}"21LOG_DIR="${LOG_DIR:-$PWD/logs}"22KEEP_DAILY="${KEEP_DAILY:-14}"23KEEP_WEEKLY="${KEEP_WEEKLY:-8}"24PREFIX="cancerindex-"2526mkdir -p "$BACKUP_DIR" "$LOG_DIR" || { echo "cannot create $BACKUP_DIR / $LOG_DIR" >&2; exit 1; }27LOG="$LOG_DIR/backup.log"28ts() { date -u +%Y-%m-%dT%H:%M:%SZ; }29log() { echo "[$(ts)] $*" | tee -a "$LOG"; }30fail() { log "ERROR: $*"; exit 1; }3132command -v pg_dump >/dev/null || fail "pg_dump not found in PATH"33command -v pg_restore >/dev/null || fail "pg_restore not found in PATH"3435STAMP="$(date +%Y%m%d-%H%M)"36OUT="$BACKUP_DIR/${PREFIX}${STAMP}.dump"37TMP="$OUT.partial"38DB_LABEL="$(printf '%s' "$DATABASE_URL" | sed -E 's#//([^:@/]+):[^@/]+@#//\1:***@#')"3940log "== backup start db=$DB_LABEL → $OUT"41t0=$(date +%s)42if ! pg_dump -Fc --no-owner --no-acl -d "$DATABASE_URL" -f "$TMP" 2>>"$LOG"; then43  rm -f "$TMP"44  fail "pg_dump failed (see $LOG)"45fi46mv "$TMP" "$OUT" || fail "cannot move $TMP → $OUT"4748# Verification: the archive must be a readable custom-format dump listing our core tables.49if ! LIST="$(pg_restore --list "$OUT" 2>>"$LOG")"; then50  fail "pg_restore --list failed on $OUT — dump is corrupt"51fi52TABLES="$(printf '%s\n' "$LIST" | grep -c 'TABLE DATA' || true)"53for t in cancers provenance ingest_runs; do54  printf '%s\n' "$LIST" | grep -q "TABLE DATA public $t " || fail "table $t missing from dump listing"55done56SIZE="$(du -h "$OUT" | cut -f1)"57log "dump ok: $SIZE, $TABLES tables with data, $(( $(date +%s) - t0 )) s"5859# Retention: keep the newest $KEEP_DAILY dumps, plus the newest dump of each of the last $KEEP_WEEKLY ISO weeks.60KEEP_FILE="$(mktemp)"61# shellcheck disable=SC201262ls -1 "$BACKUP_DIR"/${PREFIX}*.dump 2>/dev/null | sort -r | head -n "$KEEP_DAILY" >"$KEEP_FILE"63# (no associative arrays: macOS /bin/bash is 3.2)64SEEN_WEEKS=" "65weeks=066for f in $(ls -1 "$BACKUP_DIR"/${PREFIX}*.dump 2>/dev/null | sort -r); do67  base="$(basename "$f" .dump)"; d="${base#"$PREFIX"}"; d="${d%%-*}"   # YYYYMMDD68  if date -j -f %Y%m%d "$d" +%G-%V >/dev/null 2>&1; then wk="$(date -j -f %Y%m%d "$d" +%G-%V)"; else wk="$(date -d "$d" +%G-%V 2>/dev/null || echo "$d")"; fi69  case "$SEEN_WEEKS" in *" $wk "*) continue ;; esac70  if [ "$weeks" -lt "$KEEP_WEEKLY" ]; then71    SEEN_WEEKS="$SEEN_WEEKS$wk "; weeks=$((weeks + 1)); echo "$f" >>"$KEEP_FILE"72  fi73done74pruned=075for f in "$BACKUP_DIR"/${PREFIX}*.dump; do76  [ -e "$f" ] || continue77  if ! grep -qxF "$f" "$KEEP_FILE"; then rm -f "$f" && pruned=$((pruned + 1)) && log "pruned $(basename "$f")"; fi78done79rm -f "$KEEP_FILE" "$BACKUP_DIR"/${PREFIX}*.dump.partial80TOTAL="$(du -sh "$BACKUP_DIR" | cut -f1)"81log "retention: kept $(ls -1 "$BACKUP_DIR"/${PREFIX}*.dump | wc -l | tr -d ' ') dumps ($TOTAL), pruned $pruned"82log "== backup done"83