← back to Nas Setup

scripts/pull-dw-dump.sh

167 lines

#!/bin/bash
# pull-dw-dump.sh — pull the newest Kamatera dw_unified pg_dump down to TWO on-prem
# destinations (3-2-1 topology, DTD verdict 2026-06-26):
#   * Henry local drive   (default /Volumes/Henry/dw-backups/dw_unified)
#   * NAS 2TB share        (default /Volumes/DW-Backups/dw_unified — auto-activates on mount)
# giving you TWO on-prem copies + the cloud copy = 3-2-1. This is the fix for the
# 12-day silent backup death (Jun 3–15 2026): one cloud copy is a single point of failure.
#
# Safe-by-design:
#   * READ-ONLY against Kamatera (ssh + rsync pull only — never writes to the server).
#   * Each destination is INDEPENDENT: if one is unmounted/unwritable it's skipped with a
#     logged WARNING, but the other still completes. So the Henry leg works TODAY and the
#     NAS leg auto-activates the moment the share is mounted — no code change needed.
#   * Retention defaults to KEEP-ALL (RETENTION_KEEP=0). Pruning old copies is OFF unless
#     you opt in, honoring Steve's standing rule: NEVER autonomous deletion. When enabled
#     it logs every file it removes.
#   * Verifies each pulled dump with `pg_restore --list` (structure-valid) + a size floor.
#   * FAILs (exit 1 + alert) only if NEITHER destination got a verified copy. A partial
#     success (one leg up, one leg skipped/unmounted) is exit 0 with a logged WARNING.
#
# Env knobs (all optional):
#   HENRY_BACKUP_DIR target dir on the Henry drive  (default /Volumes/Henry/dw-backups/dw_unified)
#   NAS_BACKUP_DIR   target dir on the 2TB share     (default /Volumes/DW-Backups/dw_unified)
#   PGDUMP_HOST      ssh target                       (default root@45.61.58.125)
#   PGDUMP_GLOB      remote dump glob                 (default /root/backups/db/dw_unified_*.dump)
#   PGDUMP_FLOOR_MB  reject dumps smaller than this   (default 100)
#   RETENTION_KEEP   keep N newest local copies, 0 = keep all (default 0)
#   PULL_TO          alert email                      (default steve@designerwallcoverings.com)
set -uo pipefail

HENRY_BACKUP_DIR="${HENRY_BACKUP_DIR:-/Volumes/Henry/dw-backups/dw_unified}"
NAS_BACKUP_DIR="${NAS_BACKUP_DIR:-/Volumes/DW-Backups/dw_unified}"
HOST="${PGDUMP_HOST:-root@45.61.58.125}"
GLOB="${PGDUMP_GLOB:-/root/backups/db/dw_unified_*.dump}"
FLOOR_MB="${PGDUMP_FLOOR_MB:-100}"
RETENTION_KEEP="${RETENTION_KEEP:-0}"
PGBIN="/opt/homebrew/opt/postgresql@14/bin"; export PATH="$PGBIN:$PATH"

SELF_DIR="$(cd "$(dirname "$0")" && pwd)"
LOG="$SELF_DIR/../data/pull.log"; mkdir -p "$(dirname "$LOG")"
LATEST="$SELF_DIR/../data/latest.json"
FLOOR_BYTES=$(( FLOOR_MB * 1024 * 1024 ))
log(){ echo "[$(date -Iseconds)] $1" | tee -a "$LOG"; }

# Resumable-transfer flags vary by rsync build. macOS ships openrsync (2.6.9-compatible)
# which lacks --append-verify; modern (Homebrew/Linux) rsync 3.x has it. Probe once and
# pick the strongest resumable flag set the local rsync actually supports.
RSYNC_FLAGS="-a"
if rsync --help 2>&1 | grep -q -- '--append-verify'; then
  RSYNC_FLAGS="-a --partial --append-verify"   # rsync 3.x: resumable + re-checksum on resume
elif rsync --help 2>&1 | grep -q -- '--partial'; then
  RSYNC_FLAGS="-a --partial"                    # partial-resume without verify
fi

alert(){ # CNCP card + George email on FAIL. args: <reason>
  local reason="$1"; local CNCP="${CNCP_URL:-http://localhost:3333}"
  local note="[DW DUMP MIRROR $(date +%F)] dw_unified on-prem pull FAILED: ${reason}. The on-prem copies (Henry + NAS) are the 3-2-1 safety net for the cloud pg_dump — investigate."
  curl -sS --max-time 10 "$CNCP/api/parking-lot" -H 'Content-Type: application/json' \
    -d "$(jq -n --arg u "onprem://dw_unified-mirror" --arg note "$note" '{url:$u,note:$note}')" >/dev/null 2>&1 || true
  if [ -f "$HOME/.claude/skills/_shared/george-send.sh" ]; then
    . "$HOME/.claude/skills/_shared/george-send.sh"
    local body="<div style=\"font-family:-apple-system,sans-serif;color:#222\"><h3 style=\"color:#b23b3b\">⚠ dw_unified on-prem mirror FAILED</h3><div>${reason}</div><div style=\"color:#666;font-size:13px\">This job pulls the nightly Kamatera dump to BOTH Henry and the NAS 2TB share so there's a local restore source (3-2-1). Without it you're back to one cloud copy.</div></div>"
    george_send info "${PULL_TO:-steve@designerwallcoverings.com}" "⚠ dw_unified on-prem mirror FAIL — $(date +%F)" "$body" >/dev/null 2>&1 || true
  fi
}

# ── 0. newest remote dump (read-only) — resolved ONCE, reused per destination ──
REMOTE=$(ssh -o ConnectTimeout=15 -o BatchMode=yes "$HOST" "ls -t $GLOB 2>/dev/null | head -1")
if [ -z "$REMOTE" ]; then log "FAIL: no remote dump found ($GLOB)"; alert "no remote dump matching $GLOB"; exit 1; fi
BASE="$(basename "$REMOTE")"
log "newest remote dump: $REMOTE"

# Track per-destination outcome for the final verdict + latest.json.
OK_COUNT=0
SKIP_COUNT=0
declare -a DEST_JSON=()

# pull_one <label> <dest_dir>
#   Returns 0 on a verified pull, 1 on a hard failure of THIS destination.
#   Skips (return 2) if the destination's volume root isn't mounted/writable.
pull_one(){
  local LABEL="$1" DIR="$2"
  local ROOT; ROOT="$(echo "$DIR" | awk -F/ '{print "/"$2"/"$3}')"   # e.g. /Volumes/Henry

  # mounted?
  if ! mount | grep -q " on ${ROOT} "; then
    log "[$LABEL] volume not mounted ($ROOT) — skipping this destination (will activate on mount)"
    return 2
  fi
  if ! mkdir -p "$DIR" 2>>"$LOG"; then
    log "[$LABEL] WARN: cannot create $DIR — skipping this destination"
    return 2
  fi
  if [ ! -w "$DIR" ]; then
    log "[$LABEL] WARN: $DIR not writable — skipping this destination"
    return 2
  fi

  local DEST="$DIR/$BASE"

  # rsync down (resumable; skips if already complete & same size). RSYNC_FLAGS is probed
  # above so this works on both macOS openrsync and Homebrew/Linux rsync 3.x.
  if rsync $RSYNC_FLAGS -e "ssh -o ConnectTimeout=15 -o BatchMode=yes" "$HOST:$REMOTE" "$DEST" 2>>"$LOG"; then
    log "[$LABEL] rsync ok → $DEST"
  else
    log "[$LABEL] FAIL: rsync of $BASE failed"; return 1
  fi

  # integrity: size floor + pg_restore --list structure check
  local SIZE SIZE_MB TOC
  SIZE=$(stat -f %z "$DEST" 2>/dev/null || echo 0); SIZE_MB=$(( SIZE / 1024 / 1024 ))
  if [ "$SIZE" -lt "$FLOOR_BYTES" ]; then
    log "[$LABEL] FAIL: pulled dump ${SIZE_MB}MB < floor ${FLOOR_MB}MB (truncated?)"; return 1
  fi
  TOC=$(pg_restore --list "$DEST" 2>>"$LOG" | grep -c ';' || echo 0)
  if [ "$TOC" -lt 100 ]; then
    log "[$LABEL] FAIL: pg_restore --list shows only $TOC entries — dump not structurally valid"; return 1
  fi
  log "[$LABEL] PASS: $BASE — ${SIZE_MB}MB, $TOC TOC entries → $DEST"

  # retention (OFF by default; logs every deletion when enabled)
  if [ "$RETENTION_KEEP" -gt 0 ]; then
    local f; local -a OLD=()
    while IFS= read -r f; do OLD+=("$f"); done < <(ls -t "$DIR"/dw_unified_*.dump 2>/dev/null | tail -n +"$((RETENTION_KEEP+1))")
    for f in "${OLD[@]}"; do log "[$LABEL] retention: removing $(basename "$f")"; rm -f "$f"; done
    [ "${#OLD[@]}" -gt 0 ] && log "[$LABEL] retention: pruned ${#OLD[@]} old copy(ies), kept newest $RETENTION_KEEP"
  else
    local COUNT; COUNT=$(ls "$DIR"/dw_unified_*.dump 2>/dev/null | wc -l | tr -d ' ')
    log "[$LABEL] retention OFF (keep-all) — $COUNT local copies"
  fi

  DEST_JSON+=("$(jq -n --arg label "$LABEL" --arg dir "$DIR" --argjson size_mb "$SIZE_MB" --argjson toc "$TOC" \
    '{label:$label,dir:$dir,size_mb:$size_mb,toc_entries:$toc,verdict:"PASS"}')")
  return 0
}

# ── 1. Henry leg (primary on-prem second medium; works TODAY) ──
pull_one "Henry" "$HENRY_BACKUP_DIR"; rc=$?
case $rc in 0) OK_COUNT=$((OK_COUNT+1));; 2) SKIP_COUNT=$((SKIP_COUNT+1));; esac

# ── 2. NAS leg (auto-activates once /Volumes/DW-Backups is mounted) ──
pull_one "NAS" "$NAS_BACKUP_DIR"; rc=$?
case $rc in 0) OK_COUNT=$((OK_COUNT+1));; 2) SKIP_COUNT=$((SKIP_COUNT+1));; esac

# ── 3. verdict: FAIL only if NEITHER destination got a verified copy ──
DESTS_NDJSON=$(printf '%s\n' "${DEST_JSON[@]:-}")
DESTS_ARR=$(printf '%s' "$DESTS_NDJSON" | jq -s 'map(select(. != null))' 2>/dev/null || echo '[]')

if [ "$OK_COUNT" -eq 0 ]; then
  log "FAIL: no destination got a verified copy of $BASE (ok=$OK_COUNT skip=$SKIP_COUNT)"
  alert "no on-prem destination received a verified copy of $BASE"
  jq -n --arg base "$BASE" --argjson dests "$DESTS_ARR" \
    '{pulled_at:(now|todate),dump:$base,destinations:$dests,verdict:"FAIL"}' > "$LATEST"
  exit 1
fi

VERDICT="PASS"
if [ "$SKIP_COUNT" -gt 0 ]; then
  VERDICT="PARTIAL"
  log "WARN: $OK_COUNT destination(s) verified, $SKIP_COUNT skipped (unmounted/unwritable) — still have an on-prem copy"
fi

jq -n --arg base "$BASE" --argjson dests "$DESTS_ARR" --arg verdict "$VERDICT" \
  '{pulled_at:(now|todate),dump:$base,destinations:$dests,ok:($dests|length),verdict:$verdict}' > "$LATEST"
log "✓ mirror complete ($VERDICT) — $OK_COUNT verified, $SKIP_COUNT skipped"
exit 0