konnosDashboard
restore-drill.sh109 lines · main
1#!/bin/sh
2# briven — restore drill. pulls the most recent backup off-site, restores
3# it into a throwaway postgres database, runs sanity counts, then drops
4# everything. intended to run monthly on the same kvm that takes the
5# nightly backup so we never discover at 3am that a backup is unusable.
6#
7# the drill exercises the full path: download → checksum → restore →
8# query → drop. failure on ANY step exits non-zero so the cron's discord
9# webhook fires.
10#
11# required env:
12# BRIVEN_BACKUP_B2_KEY_ID
13# BRIVEN_BACKUP_B2_APP_KEY
14# BRIVEN_BACKUP_B2_BUCKET
15# BRIVEN_DRILL_PG_HOST — host running a restore-only postgres
16# BRIVEN_DRILL_PG_PORT — default 5432
17# BRIVEN_DRILL_PG_USER — superuser that can CREATE/DROP DATABASE
18# BRIVEN_DRILL_PG_PASSWORD
19#
20# optional env:
21# BRIVEN_BACKUP_PREFIX — default: prod
22#
23# the script picks the lexicographically-latest dated folder under the
24# prefix, which works because pg-dump.sh names folders with the timestamp.
25
26set -eu
27
28prefix=${BRIVEN_BACKUP_PREFIX:-prod}
29ts=$(date -u +%Y%m%d-%H%M%S)
30work=/tmp/briven-restore-drill-$ts
31trap 'rm -rf "$work"' EXIT
32
33mkdir -p "$work"
34
35export B2_APPLICATION_KEY_ID="$BRIVEN_BACKUP_B2_KEY_ID"
36export B2_APPLICATION_KEY="$BRIVEN_BACKUP_B2_APP_KEY"
37
38echo "[restore-drill $ts] picking latest backup folder"
39latest=$(b2 ls "$BRIVEN_BACKUP_B2_BUCKET/$prefix/" --json \
40 | jq -r '.[].fileName' \
41 | sed 's|/$||' \
42 | sort \
43 | tail -n 1)
44if [ -z "$latest" ]; then
45 echo "[restore-drill $ts] no backups under prefix=$prefix — drill cannot proceed" >&2
46 exit 1
47fi
48echo "[restore-drill $ts] latest=$latest"
49
50echo "[restore-drill $ts] downloading"
51b2 sync "b2://$BRIVEN_BACKUP_B2_BUCKET/$latest" "$work"
52
53echo "[restore-drill $ts] checksum verify"
54( cd "$work" && sha256sum -c sha256sums.txt ) || {
55 echo "[restore-drill $ts] checksum mismatch — refusing to restore" >&2
56 exit 2
57}
58
59restore_db="briven_drill_$(date -u +%s)"
60export PGHOST="$BRIVEN_DRILL_PG_HOST"
61export PGPORT="${BRIVEN_DRILL_PG_PORT:-5432}"
62export PGUSER="$BRIVEN_DRILL_PG_USER"
63export PGPASSWORD="$BRIVEN_DRILL_PG_PASSWORD"
64
65echo "[restore-drill $ts] CREATE DATABASE $restore_db"
66psql -d postgres -c "CREATE DATABASE $restore_db" >/dev/null
67
68# Trap the drop so a partial restore still cleans up.
69trap '
70 rm -rf "$work";
71 psql -d postgres -c "DROP DATABASE IF EXISTS $restore_db" >/dev/null 2>&1 || true
72' EXIT
73
74echo "[restore-drill $ts] restoring control.dump → $restore_db"
75pg_restore --no-owner --no-privileges --dbname="$restore_db" "$work/control.dump"
76
77echo "[restore-drill $ts] sanity counts on the meta-db"
78# These tables exist on every shipped revision of the control schema. If
79# any of them are zero, the dump didn't capture the rows we expect —
80# fail the drill loudly so j gets paged.
81fail=0
82for t in users projects deployments organizations subscriptions; do
83 n=$(psql -d "$restore_db" -tAc "SELECT count(*) FROM $t" 2>/dev/null || echo "ERROR")
84 echo "[restore-drill $ts] $t = $n"
85 if [ "$n" = "ERROR" ]; then
86 echo "[restore-drill $ts] $t MISSING — drill failed" >&2
87 fail=1
88 fi
89done
90
91echo "[restore-drill $ts] restoring data.dump → $restore_db"
92pg_restore --no-owner --no-privileges --dbname="$restore_db" "$work/data.dump"
93
94# At least one project schema must be present in the data dump (else
95# we restored an empty cluster and the drill is meaningless).
96schemas=$(psql -d "$restore_db" -tAc \
97 "SELECT count(*) FROM information_schema.schemata WHERE schema_name LIKE 'proj_%'")
98echo "[restore-drill $ts] data-plane project schemas restored: $schemas"
99if [ "$schemas" -lt 1 ]; then
100 echo "[restore-drill $ts] no proj_* schemas — drill failed" >&2
101 fail=1
102fi
103
104if [ "$fail" -ne 0 ]; then
105 echo "[restore-drill $ts] FAILED" >&2
106 exit 3
107fi
108
109echo "[restore-drill $ts] OK — backup taken at $latest restores cleanly"