spb/cancerindex

1#!/usr/bin/env bash2# CancerIndex — restore a pg_dump custom archive into a NEW database (CLAUDE.md §172 "test restore").3#4#   bash deploy/restore.sh <dump-file> [--target <dbname>] [--jobs N]5#6# Creates `cancerindex_restore_<YYYYMMDD-HHMMSS>` (or --target) on the same server as PG_URL /7# DATABASE_URL, restores into it and prints row counts of the core tables. It NEVER touches the8# production database: promoting a restore is a deliberate manual step (rename databases, or point9# DATABASE_URL at the restored one, after stopping the PM2 processes).10set -uo pipefail1112DUMP="${1:-}"; shift || true13[ -n "$DUMP" ] && [ -f "$DUMP" ] || { echo "usage: $0 <dump-file> [--target dbname] [--jobs N]" >&2; exit 2; }14TARGET=""; JOBS="${RESTORE_JOBS:-4}"15while [ $# -gt 0 ]; do16  case "$1" in17    --target) TARGET="$2"; shift 2 ;;18    --jobs) JOBS="$2"; shift 2 ;;19    *) echo "unknown option $1" >&2; exit 2 ;;20  esac21done2223SRC_URL="${DATABASE_URL:-postgres://localhost:5432/cancerindex}"24# Server URL without the database name (…/dbname → …/), used for createdb + the target connection.25SERVER_URL="$(printf '%s' "$SRC_URL" | sed -E 's#^(postgres(ql)?://[^/]*)/[^?]*#\1/#')"26TARGET="${TARGET:-cancerindex_restore_$(date +%Y%m%d-%H%M%S)}"27case "$TARGET" in cancerindex|postgres|template0|template1) echo "refusing to restore into '$TARGET'" >&2; exit 2 ;; esac28TARGET_URL="${SERVER_URL}${TARGET}"2930command -v pg_restore >/dev/null || { echo "pg_restore not found" >&2; exit 1; }31pg_restore --list "$DUMP" >/dev/null || { echo "not a readable pg_dump archive: $DUMP" >&2; exit 1; }3233echo "== restoring $(basename "$DUMP") → $TARGET (server ${SERVER_URL%/})"34if psql -d "${SERVER_URL}postgres" -Atc "SELECT 1 FROM pg_database WHERE datname = '$TARGET'" | grep -q 1; then35  echo "database $TARGET already exists — choose another --target" >&2; exit 136fi37psql -d "${SERVER_URL}postgres" -v ON_ERROR_STOP=1 -qc "CREATE DATABASE \"$TARGET\"" || exit 138psql -d "$TARGET_URL" -v ON_ERROR_STOP=1 -qc "CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE EXTENSION IF NOT EXISTS unaccent;" || exit 139psql -d "$TARGET_URL" -qc "CREATE EXTENSION IF NOT EXISTS vector" 2>/dev/null || echo "note: pgvector not available — vector columns will fail to restore if present"4041t0=$(date +%s)42# --no-owner/--no-acl: the dump was taken that way; -j parallel restore for large tables.43if ! pg_restore --no-owner --no-acl --exit-on-error -j "$JOBS" -d "$TARGET_URL" "$DUMP"; then44  echo "pg_restore FAILED — database $TARGET left in place for inspection (drop it with: dropdb $TARGET)" >&245  exit 146fi47echo "restored in $(( $(date +%s) - t0 )) s"48echo "== row counts ($TARGET)"49psql -d "$TARGET_URL" -Atc "50  SELECT rpad(t, 28) || count FROM (51    SELECT 'cancers' AS t, count(*)::text AS count FROM cancers UNION ALL52    SELECT 'provenance', count(*)::text FROM provenance UNION ALL53    SELECT 'source_records', count(*)::text FROM source_records UNION ALL54    SELECT 'ingest_runs', count(*)::text FROM ingest_runs UNION ALL55    SELECT 'clinical_trials', count(*)::text FROM clinical_trials UNION ALL56    SELECT 'rankings', count(*)::text FROM rankings57  ) x"58echo "== done. Inspect with: psql $TARGET_URL"59echo "   To promote: stop PM2 processes, then rename databases (ALTER DATABASE … RENAME TO …) or point DATABASE_URL at $TARGET."60echo "   To discard: dropdb $TARGET"61