Runbook — Emergency Restore

Disaster-recovery procedure for the scidex_v2 Postgres database.

Source: docs/runbooks/emergency_restore.md

Legacy note: this runbook predates the current side-by-side v1/v2 deployment and describes the old single-database scidex restore path. For current v2 database safety, start with docs/dev/test-databases.md and docs/runbooks/substrate_v2_rescue.md. Do not use this runbook as authority to point v2 tests or services at scidex.

Also superseded 2026-05-01: backup production now runs under Reprise (https://github.com/SciDEX-AI/Reprise) per .reprise.yaml and Reprise’s hosts/sandbox-02.yaml. The new layout is project-scoped at /data/backups/<host>/scidex-substrate/postgres/ (was the flat /data/backups/<host>/postgres/ produced by scripts/ops/backup.sh). The script + procedure below remain accurate for legacy artifacts.

Emergency Restore Runbook

Overview

This runbook covers the substrate production Postgres backup and emergency restore path for RC checklist §9.6. The current v2 substrate database is scidex_v2; the default local DSN is postgresql:///scidex_v2. The frozen v1 database remains scidex and must not be used for v2 tests or restores.

The backup policy mirrors the v1 production pattern:

  • Cadence: hourly at minute :20.

  • Retention: keep the 100 most recent full database dumps and the 100 most recent federation checkpoint dumps.

  • Layout: machine-id-prefixed paths under /data/backups/${BACKUP_MACHINE_ID}/postgres/.

  • Full dump stream: ${BACKUP_MACHINE_ID}-scidex-full-<UTC timestamp>.sql.gz.

  • Federation checkpoint stream: ${BACKUP_MACHINE_ID}-scidex-atproto_commit_log-<UTC timestamp>.sql.gz.

Run a full restore drill against a non-production Postgres instance at least once every 30 days before DNS cutover. Record the date, backup path, target DSN, operator, and verification result in the incident log or release checklist.

Prerequisites

  • Shell access to the substrate host or a restore host with pg_dump, psql, dropdb, createdb, gzip, and this repository checkout.

  • Permission to stop and start the substrate service: scidex-substrate in the cutover contract, or scidex-substrate-v2 on existing v2 hosts that still use the v2 service name.

  • A target Postgres cluster with enough free disk for the uncompressed dump.

  • The intended target DB name. Production cutover uses scidex; older staging hosts may still use scidex_v2.

  • The current application checkout and environment needed to run migrations: PYTHONPATH=src python -m scidex_substrate.migrations.runner.

  • A selected backup file from /data/backups/${BACKUP_MACHINE_ID}/postgres/.

Set these defaults before running commands:

export SCIDEX_DB_NAME=scidex_v2
export SCIDEX_DSN=postgresql:///scidex_v2
export BACKUP_MACHINE_ID="${BACKUP_MACHINE_ID:-$(hostname -s)}"
export SCIDEX_BACKUP_DIR="/data/backups/${BACKUP_MACHINE_ID}/postgres"

Backup Procedure

Install the backup command from the repo:

sudo install -m 0755 scripts/ops/backup.sh /usr/local/bin/scidex-substrate-backup

Create a systemd unit and timer so the command runs hourly at :20:

# /etc/systemd/system/scidex-substrate-backup.service
[Unit]
Description=SciDEX Substrate Postgres backup

[Service]
Type=oneshot
Environment=SCIDEX_DB_NAME=scidex_v2
Environment=SCIDEX_DSN=postgresql:///scidex_v2
Environment=BACKUP_MACHINE_ID=%H
ExecStart=/usr/local/bin/scidex-substrate-backup --check-health

# /etc/systemd/system/scidex-substrate-backup.timer
[Unit]
Description=Run SciDEX Substrate Postgres backup hourly at :20

[Timer]
OnCalendar=*-*-* *:20:00
Persistent=true

[Install]
WantedBy=timers.target

Enable it:

sudo systemctl daemon-reload
sudo systemctl enable --now scidex-substrate-backup.timer
systemctl list-timers scidex-substrate-backup.timer

For cron-based hosts, use the equivalent schedule:

20 * * * * SCIDEX_DB_NAME=scidex_v2 SCIDEX_DSN=postgresql:///scidex_v2 /usr/local/bin/scidex-substrate-backup --check-health

Manual backup:

bash scripts/ops/backup.sh

Dry run, including destination paths and retention actions:

bash scripts/ops/backup.sh --dry-run

The script writes both backup streams, then deletes the oldest files once each stream has more than 100 files.

Restore Procedure

  1. Declare the incident and freeze writes. Pause scheduled workers and tell operators that the substrate API is entering restore mode.

  2. Identify the backup to restore:

    export BACKUP_MACHINE_ID="${BACKUP_MACHINE_ID:-$(hostname -s)}"
export SCIDEX_BACKUP_DIR="/data/backups/${BACKUP_MACHINE_ID}/postgres"
ls -lt "${SCIDEX_BACKUP_DIR}"/"${BACKUP_MACHINE_ID}"-scidex-full-*.sql.gz | head -10
export RESTORE_BACKUP="${SCIDEX_BACKUP_DIR}/${BACKUP_MACHINE_ID}-scidex-full-<timestamp>.sql.gz"

  3. Stop the substrate API:

    sudo systemctl stop scidex-substrate || sudo systemctl stop scidex-substrate-v2

  4. Create a last-chance pre-restore dump of the current damaged database:

    pg_dump "${SCIDEX_DSN:-postgresql:///scidex_v2}" | gzip -c > \
  "${SCIDEX_BACKUP_DIR}/${BACKUP_MACHINE_ID}-scidex-pre-restore-$(date -u +%Y%m%dT%H%M%SZ).sql.gz"

  5. Drop and recreate the target database. This is destructive:

    dropdb scidex
createdb scidex

  6. Restore the selected backup:

    { echo "SET statement_timeout = 0;"; gunzip -c "${RESTORE_BACKUP}"; } \
  | psql scidex -v ON_ERROR_STOP=1

    Important (drill finding 2026-05-16): The production Postgres cluster has statement_timeout = 30s. Without overriding this, the COPY of large tables (e.g. db_write_journal at ~723 MB) will be killed mid-copy with: FATAL: terminating connection due to administrator command. Prepending SET statement_timeout = 0; to the dump stream disables the timeout for the restore session only, allowing all rows to load. The full table comparison was confirmed in the 2026-05-16 drill: all four checked tables matched production exactly after applying this fix. See docs/operations/restore-drill-2026-05-16.md.

  7. Apply any migrations that landed after the selected backup:

    PYTHONPATH=src python -m scidex_substrate.migrations.runner

  8. Restart the substrate service:

    sudo systemctl start scidex-substrate || sudo systemctl start scidex-substrate-v2

  9. Run post-restore verification before unfreezing writes:

    scripts/pre_merge_smoke.sh
PYTHONPATH=src python scripts/ops/check_substrate_health.py

  10. Record the restore in the incident log: backup path, target DB, restore start/end time, migration result, smoke result, and operator.

Federation Checkpoint Backup

Federation state includes the AT Protocol peer commit and publish log. The backup script captures a checkpoint on the same hourly cadence. Note: the production AT Protocol tables are named atproto_publishes and atproto_bsky_rate_limits (not atproto_commit_log — see drill finding F-2 in docs/operations/restore-drill-2026-05-16.md). Update the table name in scripts/ops/backup.sh to match if it diverges:

pg_dump --table=atproto_publishes postgresql:///scidex_v2 | gzip -c > \
  /data/backups/${BACKUP_MACHINE_ID}/postgres/${BACKUP_MACHINE_ID}-scidex-atproto_publishes-<timestamp>.sql.gz

Use this stream when investigating cross-substrate envelope replay, peer sync divergence, or publish-log corruption. A normal full database restore already contains AT Protocol tables; the table-specific stream is a smaller federation checkpoint with the same 100-file retention policy.

To inspect a checkpoint without modifying production:

createdb scidex_atproto_restore
gunzip -c "${SCIDEX_BACKUP_DIR}/${BACKUP_MACHINE_ID}-scidex-atproto_publishes-<timestamp>.sql.gz" \
  | psql scidex_atproto_restore -v ON_ERROR_STOP=1
psql scidex_atproto_restore -c 'select count(*), max(created_at) from atproto_publishes;'
dropdb scidex_atproto_restore

Monitoring

The backup health check is “newest full backup is no older than 90 minutes.” Run it from the timer via --check-health, or separately from a watchdog without creating a new dump:

bash scripts/ops/backup.sh --health-only

For a live check without creating a new backup, query the directory directly:

latest=$(ls -t /data/backups/${BACKUP_MACHINE_ID}/postgres/${BACKUP_MACHINE_ID}-scidex-full-*.sql.gz | head -1)
test -n "${latest}"
age_minutes=$(( ($(date +%s) - $(stat -c %Y "${latest}")) / 60 ))
test "${age_minutes}" -le 90

Alert when any of these are true:

  • No full backup exists in /data/backups/${BACKUP_MACHINE_ID}/postgres/.

  • The newest full backup is older than 90 minutes.

  • The newest atproto_commit_log checkpoint is older than 90 minutes.

  • The timer has not fired recently: systemctl list-timers scidex-substrate-backup.timer.

  • The journal contains failed pg_dump, gzip, or retention operations: journalctl -u scidex-substrate-backup.service --since '3 hours ago'.

Post-Restore Verification

Before reopening writes, verify:

  • scripts/pre_merge_smoke.sh passes.

  • PYTHONPATH=src python scripts/ops/check_substrate_health.py passes.

  • The API health and readiness endpoints return success on the target host.

  • select count(*) from artifacts; returns the expected order of magnitude.

  • select count(*) from atproto_commit_log; is nonzero when federation has been enabled on the restored environment.

  • The backup timer is enabled and the next fire is scheduled at minute :20.

  • The restore drill has been recorded within the last 30 days for RC checklist §9.6.