Repository navigation
Restore drill (R2 → clean machine) #7
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| name: Restore drill (R2 → clean machine) | |
| # Proves the off-site backup can actually be restored — with nothing from the prod box. | |
| # A GitHub-hosted runner pulls the latest R2 snapshots (restic), loads the DB dump into a | |
| # fresh pgvector postgres, and checks that it is complete and that the files the DB points | |
| # at exist in the restored storage. Prints how long each step took: that is the real RTO. | |
| # Nothing is written anywhere; the runner is thrown away. | |
| # The V2 database (ADR-030, #1043) is restored and row-counted too, when the backup has one. | |
| # | |
| # Monthly + on demand. A backup that was never restored is a hope, not a backup. | |
| on: | |
| schedule: | |
| - cron: '0 6 1 * *' # 1st of the month, after the nightly backup | |
| workflow_dispatch: | |
| permissions: | |
| contents: read | |
| jobs: | |
| drill: | |
| runs-on: ubuntu-latest | |
| timeout-minutes: 45 | |
| services: | |
| db: | |
| # Same image as prod: the schema still creates the `vector` extension. | |
| image: pgvector/pgvector:pg16 | |
| env: | |
| POSTGRES_USER: drill | |
| POSTGRES_PASSWORD: drill | |
| POSTGRES_DB: drill | |
| ports: ['5432:5432'] | |
| options: >- | |
| --health-cmd "pg_isready -U drill -h 127.0.0.1" | |
| --health-interval 5s --health-timeout 5s --health-retries 20 | |
| env: | |
| RESTIC_REPOSITORY: ${{ secrets.RESTIC_REPOSITORY }} | |
| RESTIC_PASSWORD: ${{ secrets.RESTIC_PASSWORD }} | |
| AWS_ACCESS_KEY_ID: ${{ secrets.R2_ACCESS_KEY_ID }} | |
| AWS_SECRET_ACCESS_KEY: ${{ secrets.R2_SECRET_ACCESS_KEY }} | |
| PGPASSWORD: drill | |
| PSQL: psql -h 127.0.0.1 -U drill -d drill -v ON_ERROR_STOP=1 -qAt | |
| RESTORE: /tmp/restore | |
| steps: | |
| # Only for database/v2: run.sh creates the scratch V2 database, the migrations name its tables. | |
| - uses: actions/checkout@3d3c42e5aac5ba805825da76410c181273ba90b1 # v7.0.1 | |
| with: | |
| sparse-checkout: database | |
| persist-credentials: false | |
| - name: Install restic + psql | |
| run: | | |
| sudo apt-get update -qq | |
| sudo apt-get install -y -qq restic postgresql-client >/dev/null | |
| restic version | |
| - name: Snapshots in R2 | |
| run: | | |
| restic snapshots --compact | |
| - name: Restore database (timed) | |
| run: | | |
| set -euo pipefail | |
| start=$(date +%s) | |
| # Roles the dump references; the dump owner is the prod user. | |
| $PSQL -c "CREATE ROLE textstack_prod" >/dev/null | |
| # Current format: the dump as plain SQL via stdin (/db.sql, since #700). Before that it | |
| # was /backup/db.sql.gz inside the storage snapshot — still restorable, so still checked. | |
| if restic snapshots --path /db.sql --json | grep -q '"id"'; then | |
| echo "restoring /db.sql" | |
| restic dump --path /db.sql latest /db.sql | $PSQL >/dev/null | |
| else | |
| echo "no /db.sql snapshot yet — restoring /backup/db.sql.gz" | |
| restic dump --path /backup latest /backup/db.sql.gz | gunzip | $PSQL >/dev/null | |
| fi | |
| echo "DB_SECONDS=$(( $(date +%s) - start ))" >> "$GITHUB_ENV" | |
| - name: Database is complete | |
| run: | | |
| set -euo pipefail | |
| read -r tables users editions chapters progress vocab userbooks <<<"$($PSQL -F' ' -c " | |
| SELECT (SELECT count(*) FROM information_schema.tables WHERE table_schema='public'), | |
| (SELECT count(*) FROM users), (SELECT count(*) FROM editions), | |
| (SELECT count(*) FROM chapters), (SELECT count(*) FROM reading_progresses), | |
| (SELECT count(*) FROM vocabulary_words), (SELECT count(*) FROM user_books)")" | |
| echo "tables=$tables users=$users editions=$editions chapters=$chapters progress=$progress vocab=$vocab user_books=$userbooks" | |
| # Floors well below today's numbers: catch a truncated/empty dump, not normal churn. | |
| [ "$tables" -ge 50 ] && [ "$editions" -ge 1000 ] && [ "$chapters" -ge 10000 ] && [ "$users" -ge 10 ] \ | |
| || { echo "::error::restored database looks truncated"; exit 1; } | |
| newest=$($PSQL -c "SELECT max(updated_at) FROM reading_progresses") | |
| echo "newest reading progress in the backup: $newest" | |
| - name: Restore storage + .env (timed) | |
| run: | | |
| set -euo pipefail | |
| start=$(date +%s) | |
| restic restore latest --path /backup --target "$RESTORE" | |
| echo "FILES_SECONDS=$(( $(date +%s) - start ))" >> "$GITHUB_ENV" | |
| echo "storage: $(find "$RESTORE/backup/storage" -type f | wc -l) files, $(du -sh "$RESTORE/backup/storage" | cut -f1)" | |
| - name: Files the database points at exist | |
| run: | | |
| set -euo pipefail | |
| S="$RESTORE/backup/storage" | |
| total=0; missing=0 | |
| while IFS= read -r p; do | |
| [ -z "$p" ] && continue | |
| total=$((total + 1)) | |
| [ -f "$S/$p" ] || { missing=$((missing + 1)); [ $missing -le 5 ] && echo "missing: $p"; } | |
| done < <($PSQL -c "SELECT cover_path FROM editions WHERE cover_path IS NOT NULL ORDER BY random() LIMIT 200") | |
| echo "edition covers checked: $total, missing: $missing" | |
| [ "$total" -gt 0 ] || { echo "::error::no cover paths to check"; exit 1; } | |
| # A few may legitimately be gone (deleted after the dump); most missing = broken backup. | |
| [ $((missing * 10)) -le "$total" ] || { echo "::error::more than 10% of covers missing"; exit 1; } | |
| - name: .env is present (names only, never values) | |
| run: | | |
| set -euo pipefail | |
| E="$RESTORE/backup/env" | |
| [ -s "$E" ] || { echo "::error::.env missing from the backup"; exit 1; } | |
| for k in POSTGRES_USER POSTGRES_PASSWORD POSTGRES_DB JWT_SECRET OPENAI_API_KEY; do | |
| grep -q "^$k=." "$E" && echo "$k: set" || { echo "::error::$k missing in backed-up .env"; exit 1; } | |
| done | |
| # V2: backup.yml sends /db_v2.sql to R2 only when textstack_v2 existed that night. No such | |
| # snapshot = "no V2 in this backup": fine while the backed-up .env has FEATURES_V2API off (V2 is | |
| # empty by design), a failure when it is on (the Api serves V2 data that no backup holds). | |
| # Counted: every table a migration in the restored ledger creates, read from that migration. | |
| - name: Restore + count V2 database (timed) | |
| env: | |
| PGHOST: 127.0.0.1 | |
| PGUSER: drill | |
| PGDATABASE: textstack_v2 | |
| run: | | |
| set -euo pipefail | |
| # As the Api's binder reads it: True/true, quoted or not. | |
| flag=off | |
| grep -qiE '^FEATURES_V2API="?true"?[[:space:]]*$' "$RESTORE/backup/env" && flag=on | |
| if ! restic snapshots --path /db_v2.sql --json | grep -q '"id"'; then | |
| if [ "$flag" = on ]; then | |
| echo "::error::FEATURES_V2API=true in the backed-up .env, but no V2 dump in R2"; exit 1 | |
| fi | |
| echo "no V2 in this backup (FEATURES_V2API off)" | |
| echo "V2_RESULT=no V2 in this backup" >> "$GITHUB_ENV"; exit 0 | |
| fi | |
| # A nightly V2 dump failing for weeks still leaves an old /db_v2.sql as "latest". Older than the | |
| # legacy dump by more than a day: an alarm with the flag on, a warning with it off. | |
| newest() { restic snapshots --path "$1" --json | grep -o '"time":"[^"]*"' | cut -d'"' -f4 | sort | tail -n 1; } | |
| legacy_t=$(newest /db.sql); v2_t=$(newest /db_v2.sql) | |
| if [ -n "$legacy_t" ]; then | |
| lag=$(( $(date -d "$legacy_t" +%s) - $(date -d "$v2_t" +%s) )) | |
| if [ "$lag" -gt 86400 ]; then | |
| msg="V2 snapshot ($v2_t) is $((lag / 3600))h older than the legacy one ($legacy_t)" | |
| [ "$flag" = on ] && { echo "::error::$msg"; exit 1; } | |
| echo "::warning::$msg; FEATURES_V2API off" | |
| fi | |
| fi | |
| start=$(date +%s) | |
| sh database/v2/run.sh create | |
| restic dump --path /db_v2.sql latest /db_v2.sql | psql -X -q -v ON_ERROR_STOP=1 >/dev/null | |
| secs=$(( $(date +%s) - start )) | |
| n=0 | |
| for v in $(psql -XAt -c "SELECT lpad(version::text, 4, '0') || '_' || name FROM schema_migrations ORDER BY version"); do | |
| for t in $(grep -o 'CREATE TABLE public\.[a-z0-9_]*' "database/v2/migrations/$v.up.sql" | cut -d. -f2); do | |
| # Not inside echo: echo's status would hide a table the dump never created. | |
| c=$(psql -XAt -v ON_ERROR_STOP=1 -c "SELECT count(*) FROM public.$t") || exit 1 | |
| echo "$t: $c" | |
| n=$((n + 1)) | |
| done | |
| done | |
| [ "$n" -gt 0 ] || { echo "::error::restored V2 database has no migrated tables"; exit 1; } | |
| echo "V2_RESULT=${secs}s, $n tables counted" >> "$GITHUB_ENV" | |
| - name: Recovery time | |
| run: | | |
| echo "### Restore drill passed" >> "$GITHUB_STEP_SUMMARY" | |
| echo "- database restore: ${DB_SECONDS}s" >> "$GITHUB_STEP_SUMMARY" | |
| echo "- storage + .env restore: ${FILES_SECONDS}s" >> "$GITHUB_STEP_SUMMARY" | |
| echo "- V2 database: ${V2_RESULT}" >> "$GITHUB_STEP_SUMMARY" | |
| echo "DB ${DB_SECONDS}s, files ${FILES_SECONDS}s" |