geointel/deploy/unraid/restore-predeploy-database.sh

#!/usr/bin/env bash
set -euo pipefail

# Restore the production database from a verified pre-deploy dump while the
# normal GeoIntel container is stopped. This is intentionally a separate,
# explicitly confirmed operation: starting an older image against a schema
# migrated by a newer image is not a safe rollback strategy.

ROOT="$(cd "$(dirname "${BASH_SOURCE[0]}")/../.." && pwd)"
cd "$ROOT"

GEOINTEL_DEPLOY_LOCK_FILE="${GEOINTEL_DEPLOY_LOCK_FILE:-/tmp/geointel-release-deploy.lock}"
if [ "${GEOINTEL_DEPLOY_LOCK_HELD:-false}" != "true" ]; then
  command -v flock >/dev/null 2>&1 || {
    echo "GeoIntel database restore requires flock to prevent concurrent deployment." >&2
    exit 2
  }
  exec 9>"$GEOINTEL_DEPLOY_LOCK_FILE"
  if ! flock -n 9; then
    echo "Another GeoIntel deployment or rollback is already running." >&2
    exit 3
  fi
fi

BACKUP_DIR=""
CONFIRMED="false"
RESTORE_IMAGE="${GEOINTEL_ROLLBACK_IMAGE:-}"
GEOINTEL_CONTAINER_NAME="${GEOINTEL_CONTAINER_NAME:-geointel}"

usage() {
  cat <<'EOF'
Usage: bash deploy/unraid/restore-predeploy-database.sh \
  --backup-dir PATH --confirm-production-database-restore [options]

Stops the normal GeoIntel container, starts an isolated PostGIS recovery
container on the same persistent database path, restores the checksum-verified
custom-format dump, validates Alembic/table counts, and stops recovery again.
The caller must start the rollback image after this command succeeds.

Options:
  --image IMAGE    Recovery image containing PostgreSQL/PostGIS tools
  --container NAME Normal application container (default: geointel)
EOF
}

while [ "$#" -gt 0 ]; do
  case "$1" in
    --backup-dir) BACKUP_DIR="$2"; shift 2 ;;
    --confirm-production-database-restore) CONFIRMED="true"; shift ;;
    --image) RESTORE_IMAGE="$2"; shift 2 ;;
    --container) GEOINTEL_CONTAINER_NAME="$2"; shift 2 ;;
    --help|-h) usage; exit 0 ;;
    *) echo "Unknown argument: $1" >&2; usage >&2; exit 2 ;;
  esac
done

if [ "$CONFIRMED" != "true" ] || [ -z "$BACKUP_DIR" ]; then
  echo "Explicit --confirm-production-database-restore and --backup-dir are required." >&2
  exit 2
fi
for required in docker python3 sha256sum; do
  command -v "$required" >/dev/null 2>&1 || {
    echo "Missing required command: $required" >&2
    exit 2
  }
done

if [ -f .env ]; then
  set -a
  # shellcheck disable=SC1091
  . ./.env
  set +a
fi

GEOINTEL_BACKUPS_PATH="${GEOINTEL_BACKUPS_PATH:-/mnt/user/appdata/geointel/backups}"
GEOINTEL_POSTGIS_DATA_PATH="${GEOINTEL_POSTGIS_DATA_PATH:-/mnt/user/appdata/geointel/postgres-data}"
GEOINTEL_POSTGRES_DB="${GEOINTEL_POSTGRES_DB:-geointel}"
GEOINTEL_POSTGRES_USER="${GEOINTEL_POSTGRES_USER:-geointel}"
GEOINTEL_POSTGRES_PASSWORD="${GEOINTEL_POSTGRES_PASSWORD:-}"

if ! [[ "$GEOINTEL_POSTGRES_DB" =~ ^[A-Za-z_][A-Za-z0-9_]*$ ]] \
  || ! [[ "$GEOINTEL_POSTGRES_USER" =~ ^[A-Za-z_][A-Za-z0-9_]*$ ]]; then
  echo "Configured PostGIS database and user names must be simple SQL identifiers." >&2
  exit 2
fi
case "$GEOINTEL_POSTGRES_PASSWORD" in
  ''|geointel|postgres|password|changeme|change-me-before-shared-use)
    echo "Refusing database restore with an empty or known-default PostGIS password." >&2
    exit 2
    ;;
esac
test -f "$GEOINTEL_POSTGIS_DATA_PATH/PG_VERSION" || {
  echo "Persistent PostGIS data path is not initialized: $GEOINTEL_POSTGIS_DATA_PATH" >&2
  exit 3
}

GEOINTEL_BACKUPS_PATH="$(python3 -c 'import pathlib,sys; print(pathlib.Path(sys.argv[1]).expanduser().resolve())' "$GEOINTEL_BACKUPS_PATH")"
BACKUP_DIR="$(python3 -c 'import pathlib,sys; print(pathlib.Path(sys.argv[1]).expanduser().resolve())' "$BACKUP_DIR")"
python3 - "$GEOINTEL_BACKUPS_PATH" "$BACKUP_DIR" <<'PY'
import pathlib
import sys

root = pathlib.Path(sys.argv[1])
backup = pathlib.Path(sys.argv[2])
try:
    backup.relative_to(root)
except ValueError as exc:
    raise SystemExit(f"Backup directory must be below {root}") from exc
if backup == root:
    raise SystemExit("Backup directory must identify one immutable backup")
PY

for required_file in manifest.json database.dump database.list database-metadata.tsv table-counts.tsv CHECKSUMS.sha256; do
  test -s "$BACKUP_DIR/$required_file" || {
    echo "Missing or empty backup artifact: $required_file" >&2
    exit 3
  }
done
(
  cd "$BACKUP_DIR"
  sha256sum -c CHECKSUMS.sha256
)
python3 "$ROOT/scripts/release_backup_snapshot.py" verify-backup --backup-dir "$BACKUP_DIR"

IFS=$'\t' read -r BACKUP_DB BACKUP_USER BACKUP_IMAGE_ID BACKUP_RELEASE_ID < <(
  python3 - "$BACKUP_DIR/manifest.json" <<'PY'
import json
import pathlib
import sys

payload = json.loads(pathlib.Path(sys.argv[1]).read_text(encoding="utf-8"))
if payload.get("schema_version") != 1 or payload.get("read_only_source") is not True:
    raise SystemExit("Unsupported or unsafe backup manifest")
print(
    f"{payload.get('database_name', '')}\t{payload.get('database_user', '')}\t"
    f"{payload.get('image_id', '')}\t{payload.get('release_id', '')}"
)
PY
)
if [ "$BACKUP_DB" != "$GEOINTEL_POSTGRES_DB" ] || [ "$BACKUP_USER" != "$GEOINTEL_POSTGRES_USER" ]; then
  echo "Backup database identity does not match the configured production database." >&2
  exit 3
fi
if ! [[ "$BACKUP_IMAGE_ID" =~ ^sha256:[0-9a-f]{64}$ ]]; then
  echo "Backup manifest does not contain one immutable Docker image ID." >&2
  exit 3
fi
if [ -z "$RESTORE_IMAGE" ]; then
  RESTORE_IMAGE="$BACKUP_IMAGE_ID"
fi
docker image inspect "$RESTORE_IMAGE" >/dev/null
RESTORE_IMAGE_ID="$(docker image inspect --format '{{.Id}}' "$RESTORE_IMAGE")"
if [ -z "$BACKUP_IMAGE_ID" ] || [ "$BACKUP_IMAGE_ID" != "$RESTORE_IMAGE_ID" ]; then
  echo "Backup image identity does not match the retained rollback image." >&2
  exit 3
fi
case "$BACKUP_RELEASE_ID" in
  predeploy-*) ;;
  *) echo "Production rollback requires a predeploy backup." >&2; exit 3 ;;
esac

if docker ps -a --format '{{.Names}}' | grep -Fxq "$GEOINTEL_CONTAINER_NAME"; then
  docker rm -f "$GEOINTEL_CONTAINER_NAME" >/dev/null
fi

RECOVERY_CONTAINER="geointel-db-restore-$(date -u +%Y%m%d%H%M%S)-$$"
RESTORE_PROOF_DB="geointel_restore_proof_$(date -u +%Y%m%d%H%M%S)_$$"
RECOVERY_DB="geointel_pre_restore_$(date -u +%Y%m%d%H%M%S)_$$"
FAILED_RESTORE_DB="geointel_failed_restore_$(date -u +%Y%m%d%H%M%S)_$$"
SWAP_COMPLETE="false"
cleanup_recovery() {
  if [ "$SWAP_COMPLETE" != "true" ] \
    && [ "$(docker inspect -f '{{.State.Running}}' "$RECOVERY_CONTAINER" 2>/dev/null || true)" = "true" ]; then
    docker exec "$RECOVERY_CONTAINER" dropdb --if-exists --force \
      -U "$GEOINTEL_POSTGRES_USER" "$RESTORE_PROOF_DB" >/dev/null 2>&1 || true
  fi
  docker rm -f "$RECOVERY_CONTAINER" >/dev/null 2>&1 || true
}
trap cleanup_recovery EXIT

docker run -d \
  --name "$RECOVERY_CONTAINER" \
  --restart no \
  -e PGDATA=/var/lib/postgresql/data \
  -e PGPASSWORD="$GEOINTEL_POSTGRES_PASSWORD" \
  -v "$GEOINTEL_POSTGIS_DATA_PATH:/var/lib/postgresql/data" \
  -v "$BACKUP_DIR:/restore:ro" \
  --entrypoint /bin/bash \
  "$RESTORE_IMAGE" \
  -c 'set -euo pipefail; chown postgres:postgres "$PGDATA"; exec gosu postgres postgres' \
  >/dev/null

for attempt in $(seq 1 180); do
  if docker exec "$RECOVERY_CONTAINER" pg_isready -h 127.0.0.1 -U "$GEOINTEL_POSTGRES_USER" -d postgres >/dev/null 2>&1; then
    break
  fi
  if [ "$(docker inspect -f '{{.State.Running}}' "$RECOVERY_CONTAINER" 2>/dev/null || true)" != "true" ]; then
    echo "Database recovery container exited before PostGIS became ready." >&2
    docker logs "$RECOVERY_CONTAINER" >&2 || true
    exit 4
  fi
  if [ "$attempt" -eq 180 ]; then
    echo "PostGIS recovery did not become ready within six minutes." >&2
    exit 4
  fi
  sleep 2
done

RESTORED_LIST="$(mktemp)"
trap 'rm -f -- "$RESTORED_LIST"; cleanup_recovery' EXIT
docker exec "$RECOVERY_CONTAINER" pg_restore --list /restore/database.dump > "$RESTORED_LIST"
cmp -s "$RESTORED_LIST" "$BACKUP_DIR/database.list" || {
  echo "Recovery image reads a different PostgreSQL archive listing." >&2
  exit 4
}

if ! docker exec "$RECOVERY_CONTAINER" psql -X -v ON_ERROR_STOP=1 \
  -U "$GEOINTEL_POSTGRES_USER" -d postgres -Atqc \
  "SELECT 1 FROM pg_database WHERE datname = '${GEOINTEL_POSTGRES_DB}';" | grep -Fxq 1; then
  echo "Configured production database does not exist; refusing replacement." >&2
  exit 4
fi
for generated_database in "$RESTORE_PROOF_DB" "$RECOVERY_DB" "$FAILED_RESTORE_DB"; do
  if docker exec "$RECOVERY_CONTAINER" psql -X -v ON_ERROR_STOP=1 \
    -U "$GEOINTEL_POSTGRES_USER" -d postgres -Atqc \
    "SELECT 1 FROM pg_database WHERE datname = '${generated_database}';" | grep -Fxq 1; then
    echo "Generated recovery database already exists: ${generated_database}" >&2
    exit 4
  fi
done

# Prove the complete archive in a separate database before touching production.
docker exec "$RECOVERY_CONTAINER" createdb \
  -U "$GEOINTEL_POSTGRES_USER" "$RESTORE_PROOF_DB"
docker exec "$RECOVERY_CONTAINER" pg_restore \
  --exit-on-error \
  --no-owner \
  --no-privileges \
  -U "$GEOINTEL_POSTGRES_USER" \
  -d "$RESTORE_PROOF_DB" \
  /restore/database.dump

EXPECTED_HEAD="$(awk -F $'\t' '$1 == "alembic_head" { print $2 }' "$BACKUP_DIR/database-metadata.tsv")"
validate_restored_database() {
  local database_name="$1"
  local restored_head=""
  restored_head="$(docker exec "$RECOVERY_CONTAINER" psql -X -v ON_ERROR_STOP=1 \
    -U "$GEOINTEL_POSTGRES_USER" -d "$database_name" -Atqc \
    'SELECT version_num FROM alembic_version;')"
  if [ -z "$EXPECTED_HEAD" ] || [ "$restored_head" != "$EXPECTED_HEAD" ]; then
    echo "Restored Alembic head '$restored_head' differs from backup head '$EXPECTED_HEAD'." >&2
    return 1
  fi
  while IFS=$'\t' read -r table expected; do
    [[ "$table" =~ ^[a-z_]+$ ]] || {
      echo "Unsafe table name in retained counts: $table" >&2
      return 1
    }
    actual="$(docker exec "$RECOVERY_CONTAINER" psql -X -v ON_ERROR_STOP=1 \
      -U "$GEOINTEL_POSTGRES_USER" -d "$database_name" -Atqc \
      "SELECT count(*) FROM public.${table};")"
    if [ "$actual" != "$expected" ]; then
      echo "Restored count mismatch for $table: expected $expected, got $actual." >&2
      return 1
    fi
  done < "$BACKUP_DIR/table-counts.tsv"
}

validate_restored_database "$RESTORE_PROOF_DB"
echo "Isolated predeploy restore proof passed: ${RESTORE_PROOF_DB}"

docker exec "$RECOVERY_CONTAINER" psql -X -v ON_ERROR_STOP=1 \
  -U "$GEOINTEL_POSTGRES_USER" -d postgres -c \
  "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname IN ('${GEOINTEL_POSTGRES_DB}', '${RESTORE_PROOF_DB}') AND pid <> pg_backend_pid();" \
  >/dev/null
docker exec "$RECOVERY_CONTAINER" psql -X -v ON_ERROR_STOP=1 \
  -U "$GEOINTEL_POSTGRES_USER" -d postgres -c \
  "ALTER DATABASE ${GEOINTEL_POSTGRES_DB} RENAME TO ${RECOVERY_DB};"
if ! docker exec "$RECOVERY_CONTAINER" psql -X -v ON_ERROR_STOP=1 \
  -U "$GEOINTEL_POSTGRES_USER" -d postgres -c \
  "ALTER DATABASE ${RESTORE_PROOF_DB} RENAME TO ${GEOINTEL_POSTGRES_DB};"; then
  echo "Restored database cutover failed; restoring the untouched production database name." >&2
  docker exec "$RECOVERY_CONTAINER" psql -X -v ON_ERROR_STOP=1 \
    -U "$GEOINTEL_POSTGRES_USER" -d postgres -c \
    "ALTER DATABASE ${RECOVERY_DB} RENAME TO ${GEOINTEL_POSTGRES_DB};"
  exit 4
fi
SWAP_COMPLETE="true"

if ! validate_restored_database "$GEOINTEL_POSTGRES_DB"; then
  echo "Post-cutover validation failed; restoring the retained pre-restore database." >&2
  docker exec "$RECOVERY_CONTAINER" psql -X -v ON_ERROR_STOP=1 \
    -U "$GEOINTEL_POSTGRES_USER" -d postgres -c \
    "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '${GEOINTEL_POSTGRES_DB}' AND pid <> pg_backend_pid();" \
    >/dev/null
  docker exec "$RECOVERY_CONTAINER" psql -X -v ON_ERROR_STOP=1 \
    -U "$GEOINTEL_POSTGRES_USER" -d postgres -c \
    "ALTER DATABASE ${GEOINTEL_POSTGRES_DB} RENAME TO ${FAILED_RESTORE_DB};"
  docker exec "$RECOVERY_CONTAINER" psql -X -v ON_ERROR_STOP=1 \
    -U "$GEOINTEL_POSTGRES_USER" -d postgres -c \
    "ALTER DATABASE ${RECOVERY_DB} RENAME TO ${GEOINTEL_POSTGRES_DB};"
  SWAP_COMPLETE="false"
  echo "Original production database was restored; failed restore retained as ${FAILED_RESTORE_DB}." >&2
  exit 4
fi

if [ -z "$RECOVERY_DB" ]; then
  echo "Recovery database identity was not retained." >&2
  exit 4
fi

while IFS=$'\t' read -r table expected; do
  [[ "$table" =~ ^[a-z_]+$ ]] || {
    echo "Unsafe table name in retained counts: $table" >&2
    exit 4
  }
done < "$BACKUP_DIR/table-counts.tsv"

rm -f -- "$RESTORED_LIST"
cleanup_recovery
trap - EXIT
echo "Production database restored and verified from: $BACKUP_DIR"
echo "Pre-restore production database retained for operator recovery as: $RECOVERY_DB"