Postgres Backup / Restore Runbook (Hetzner)

Status: Operativ. Quartalsweiser Test verpflichtend. Owner: Toby. Stand 2026-06-03.

Prod läuft auf self-hosted Supabase EU auf Hetzner (stratify-db Container, 128.140.8.187). Backup/Restore ist pg_dump/psql gegen diesen Container — kein Supabase-Cloud-Dashboard. (Cloud-PITR nur noch relevant, *falls* ein Stack doch noch auf Supabase Cloud liegt — siehe §3.5.)

1. Backup-Strategie

• Nightly full dump (stratify-db only) via scripts/hetzner-backup-postgres.sh

→ /data/stratify/backups/stratify-<UTC-stamp>.sql.gz auf dem Server.

• Retention: 14 Dumps (STRATIFY_BACKUP_KEEP, default 14), rotiert im Script.
• Schedule: nächtlich 05:30 UTC via /etc/cron.d/stratify

(infra/hetzner/cron-stratify.example), Log /var/log/stratify-backup.log.

• Touches nur stratify-db — nicht Loki (supabase-db/5432) oder Ecosystem.

# Manuell anstoßen (von diesem Mac, SSH zum Server):
./scripts/hetzner-backup-postgres.sh

2. Setup-Checklist

• [ ] scripts/hetzner-backup-postgres.sh einmal manuell getestet (Dump > 0 bytes).
• [ ] Cron in /etc/cron.d/stratify installiert via scripts/hetzner-install-crons.sh.
• [ ] SUPABASE_POSTGRES_PASSWORD (account stratify-hetzner) im Keychain vorhanden.
• [ ] Quartals-Restore-Drill in einer Wegwerf-DB validiert (gzip → psql).

3. Recovery-Szenarien

3.1 Versehentliches DROP TABLE

Symptome: Tabelle weg, 5xx in Production, Sentry-Spike.

Steps:

1. Stoppe alle Writes: stratify-web Container stoppen

(docker stop stratify-web) oder STRATIFY_READ_ONLY=1 setzen + redeploy.

1. Letzten guten Dump wählen: /data/stratify/backups/stratify-*.sql.gz

(ls -t → jüngster Dump vor dem DROP).

1. Restore in Wegwerf-DB: Dump in eine temporäre DB einspielen, Schema +

Zieltabelle verifizieren:

   # auf dem Server:
   gunzip -c /data/stratify/backups/stratify-<stamp>.sql.gz \
     | docker exec -i stratify-db psql -U postgres -d postgres_restore

1. Tabelle zurückspielen: pg_dump --table=<name> aus der Wegwerf-DB →

psql/pg_restore --data-only --table=<name> gegen prod stratify-db.

1. Smoke-Test: /, /walkthrough, /feed,

/api/v1/mandates (mit Sandbox-Key).

1. Audit-Eintrag: Recovery-Event manuell in audit_log schreiben (Service-Role).

SLO: RPO ≤ 24 h (nightly dump), RTO ≤ 60 Minuten. Für RPO ≤ 1 Min vor einem geplanten Risiko-Eingriff vorher manuell hetzner-backup-postgres.sh laufen lassen.

3.2 Datenverlust einer einzelnen Tabelle (z. B. accidental TRUNCATE)

Steps:

1. Stoppe Writes auf diese Tabelle (Hot-Fix-Deploy mit Feature-Flag aus).
2. Jüngsten guten /data/stratify/backups/*.sql.gz in Wegwerf-DB einspielen

(nicht prod überschreiben).

1. pg_dump --table=<name> aus der Wegwerf-DB → psql --data-only --table=<name>

gegen prod stratify-db.

1. Audit-Eintrag.

3.3 Korrupte audit_log-Chain (P0 Compliance)

Symptome: verifyChain liefert { ok: false, firstBadId: N }.

Steps:

1. Snapshot des audit_log: pg_dump --table=audit_log (gegen stratify-db) → Forensic Storage.
2. Schreibe Service-Role Lockdown: Postgres Trigger temporarily blockt alle Mutations auf audit_log.
3. Inkident-Channel öffnen (Slack #incident-audit-XXXX). Antonios + Toby.
4. Counsel-Benachrichtigung binnen 24 h, sobald wir Phase C sind.
5. Restore aus jüngstem guten Dump (Stand unmittelbar vor firstBadId) in Wegwerf-DB. Chain neu validieren, dann gezielt zurückspielen.
6. Forensik-Storage-Snapshot zur DPO archivieren (DSGVO Art. 33 ggf.).

3.4 Hetzner / Supabase-Outage (Host down)

Steps:

1. Read-only mode toggle in App (env flag STRATIFY_READ_ONLY=1 → Server-Actions failen weich mit "Wir sind kurz nicht erreichbar").
2. Status-Update an Subscribers + Partners via Resend Broadcast (separate Failover-Liste).
3. Bei verlängertem Outage (>4 h): jüngsten *.sql.gz Dump in eine temporäre Postgres-Instanz EU einspielen und dahin umschwenken.

3.5 Falls noch auf Supabase Cloud (Legacy)

Nur relevant, falls ein Stack doch noch auf Supabase Cloud läuft (Stratify-Prod ist es nicht mehr — Hetzner-Cutover abgeschlossen):

• Supabase Dashboard → Database → Backups → PITR "Restore to point in time"

~1 Min vor dem Fehler → Klon-DB → Connection-String swap.

4. Quartalsweiser Test

Logge jeden Test mit Datum + RTO/RPO ins audit_log (actorRole: 'system', eventType: 'ops.recovery_drill').

5. Decision Log

v0.2 — 2026-06-03.