Runbook — DR restore PostgreSQL + TimescaleDB

Tipo: Disaster Recovery procedure Severity: SEV1 (perdita dati DB) RTO target: <4 ore dall'allerta a DB serving traffico RPO target: <6 ore (snapshot Hetzner) / <24 ore (pg_dump notturno) Owner: Massimo Bagnoli (m.bagnoli@asheep.it)

1. Backup strategy

Tipo	Frequenza	Destinazione	Retention	RPO contributo
`pg_dump --format=custom` notturno	1×/giorno (03:00 CET)	Storage Box BX11 SFTP (`/backups/pg/akira-YYYY-MM-DD.dump`)	30 giorni	24h
Hetzner snapshot full VM `db-01`	ogni 6h (auto)	Hetzner snapshot store	7 giorni rolling	6h
WAL archiving (continuous)	continuo	Storage Box BX11 SFTP (`/backups/wal/`)	14 giorni	<5min
`pg_dump` settimanale full + schema-only	1×/settimana (domenica)	Storage Box BX11 SFTP + copia offsite	12 settimane	7gg

Storage Box BX11 = 1TB SFTP, costo ~€3,90/m (vedi docs/finops/budget-and-monitoring.md).

WAL archiving è opzionale per prototipo; obbligatorio prima del go-live pilot per consentire PITR (Point-In-Time Recovery).

2. Scenari di restore

Scenario A — VM `db-01` corrotta, ultimo snapshot OK

RTO: ~30 min. Procedura: Step 3.1.

Scenario B — Dati corrotti logici (es. `DELETE` accidentale), snapshot stale

RTO: ~1-2h. Procedura: Step 3.2 (pg_restore selettivo).

Scenario C — Disastro totale (VM persa, snapshot perso)

RTO: ~2-4h. Procedura: Step 3.2 + Step 3.3 (replay WAL se disponibili).

Scenario D — Migrazione PG version major upgrade

Procedura separata, non DR. Vedi futuro docs/runbooks/pg-major-upgrade.md.

3. Procedure

3.1 Restore da snapshot Hetzner (Scenario A)

# Da workstation Massimo, autenticato a Hetzner CLI (hcloud)
hcloud server list --selector role=db
hcloud snapshot list --selector vm=akira-db-01 --sort created:desc

# Crea nuovo server da snapshot più recente
SNAPSHOT_ID=<id>
hcloud server create \
  --name akira-db-01-restore \
  --image $SNAPSHOT_ID \
  --type ccx13 \
  --location nbg1 \
  --network akira-private \
  --ssh-key akira_ed25519

# Riassegna IP privato dello stale db-01 al nuovo (se old è morto)
# OPPURE aggiorna Patroni / pgbouncer config sui consumer per puntare al nuovo IP
hcloud server detach-from-network akira-db-01 --network akira-private
hcloud server attach-to-network akira-db-01-restore --network akira-private --ip 10.0.1.11

# Verifica DB up
ssh deploy@10.0.1.11 'sudo systemctl status postgresql'
psql -h 10.0.1.11 -U akira_app -d akira -c "SELECT now(), version();"

Verifica integrità post-restore: vedi §4.

3.2 Restore logico da `pg_dump` (Scenario B)

# Dal Storage Box BX11 (SFTP), scarica il dump della data target
sftp <storage_user>@<storage_host>
sftp> get /backups/pg/akira-2026-05-13.dump /tmp/akira-restore.dump
sftp> quit

# Su db-01 (o su istanza dedicata staging-restore per safety)
# OPZIONE 1: restore in DB nuovo per validare, poi promote
createdb -U postgres akira_restore
pg_restore -U postgres -d akira_restore -j 4 --no-owner --no-privileges /tmp/akira-restore.dump

# Verifica TimescaleDB hypertable + continuous aggregates
psql -U postgres -d akira_restore -c "SELECT * FROM timescaledb_information.hypertables;"
psql -U postgres -d akira_restore -c "SELECT view_name, materialization_hypertable_name FROM timescaledb_information.continuous_aggregates;"

# Se OK: rename atomico
psql -U postgres -c "ALTER DATABASE akira RENAME TO akira_corrupted_$(date +%Y%m%d);"
psql -U postgres -c "ALTER DATABASE akira_restore RENAME TO akira;"

# Restart consumer
ssh akira-mgmt-01 'sudo docker compose -f /opt/akira/docker-compose.yml restart backend agentcore-bridge'

⚠️ TimescaleDB 2.10+ supporta pg_dump/pg_restore nativi (no più timescaledb-backup separato). Verificare versione Timescale nel dump.

3.3 Replay WAL (Scenario C, opzionale se WAL archiving attivo)

# Dopo restore da snapshot più vecchio (Step 3.1), replay WAL fino al timestamp desiderato
# In recovery.conf / postgresql.auto.conf:
restore_command = 'aws s3 cp s3://akira-wal/%f %p'  # placeholder, in prod via SFTP rclone
recovery_target_time = '2026-05-13 14:30:00 CET'
recovery_target_action = 'promote'

# Avvia PG in recovery mode, attende replay, promote, restart

Procedura PITR completa: out-of-scope per questo runbook minimo; documentare prima del go-live pilot.

4. Verifica integrità post-restore

-- 1. Hypertable CDR esiste e ha chunks recenti
SELECT hypertable_name, num_chunks, compression_enabled
FROM timescaledb_information.hypertables
WHERE hypertable_name = 'cdr';

-- 2. CDR ultimi 7 giorni — sanity count
SELECT COUNT(*), MIN(started_at), MAX(started_at)
FROM cdr
WHERE started_at > now() - INTERVAL '7 days';
-- Atteso: count > 0 e MAX(started_at) recente (<1h dall'ora di restore)

-- 3. Continuous aggregates aggiornate
SELECT view_name, last_run_started_at, last_run_status
FROM timescaledb_information.job_stats
WHERE proc_name LIKE 'policy_refresh%';

-- 4. Tabelle critiche conta righe
SELECT 'companies', COUNT(*) FROM companies UNION ALL
SELECT 'originators', COUNT(*) FROM originators UNION ALL
SELECT 'terminators', COUNT(*) FROM terminators UNION ALL
SELECT 'tariffs', COUNT(*) FROM tariffs UNION ALL
SELECT 'invoices', COUNT(*) FROM invoices;
-- Confrontare con valori attesi (baseline pre-incidente noto)

-- 5. ENUM types presenti
SELECT typname FROM pg_type WHERE typtype='e' AND typname LIKE 'akira_%';
-- Atteso: 24 enum (vedi schema v1)

-- 6. Funzioni PL/pgSQL custom
SELECT proname FROM pg_proc WHERE pronamespace = 'public'::regnamespace AND prokind = 'f'
  AND proname IN ('longest_prefix_match', 'fn_jsonb_diff', 'verify_company_balance');

Se uno di questi check fallisce: NON aprire al traffico. Indagare differenza e decidere se rollback al DB corrupted o continuare.

5. Restart consumer dopo restore

L'ingestion CDR usa NATS JetStream (ADR-0007) — non si parla mai direttamente con il DB.

# Verifica NATS JetStream stream e consumer
ssh akira-sig-01 'nats stream info CDR'
ssh akira-sig-01 'nats consumer info CDR cdr-rating-worker'

# Se restore ha causato gap: il consumer ripartirà dall'ultima ACK
# Eventuale backlog visibile come "Pending" > 0 nel consumer info

# Restart worker rating su backend
ssh akira-mgmt-01 'sudo docker compose -f /opt/akira/docker-compose.yml restart cdr-rating-worker'

# Verifica throughput
watch -n 2 'nats consumer info CDR cdr-rating-worker | grep -E "Pending|Delivered|Acknowledged"'

6. Test mensile

Cadenza: 1×/mese (primo lunedì del mese).

Procedura test:

Spin up VM disposable staging-restore-test (Hetzner CX21, distruzione automatica fine test).
Restore ultimo pg_dump notturno via Step 3.2.
Esegui §4 verifica integrità.
Cronometra durata totale → deve essere <4h.
Distruggi VM.
Log esito in docs/runbooks/_drill-log.md.

Script automatizzato: infra/scripts/dr-test.sh (TBD — implementare prima del go-live).

7. Riferimenti

ADR-0007 CDR pipeline NATS: docs/adr/0007-cdr-pipeline-nats-jetstream.md
ADR-0008 Kamailio HA: docs/adr/0008-kamailio-ha-hetzner-cloud.md
Schema DB baseline: akira_schema_v1.sql
FinOps backup costs: docs/finops/budget-and-monitoring.md
Break-glass Tailscale: docs/runbooks/break-glass-tailscale-down.md
Schema delta v5.15→v1: docs/db/SCHEMA_DELTA_v515_to_v1.md

1. Backup strategy​

2. Scenari di restore​

Scenario A — VM db-01 corrotta, ultimo snapshot OK​

Scenario B — Dati corrotti logici (es. DELETE accidentale), snapshot stale​

Scenario C — Disastro totale (VM persa, snapshot perso)​

Scenario D — Migrazione PG version major upgrade​

3. Procedure​

3.1 Restore da snapshot Hetzner (Scenario A)​

3.2 Restore logico da pg_dump (Scenario B)​

3.3 Replay WAL (Scenario C, opzionale se WAL archiving attivo)​

4. Verifica integrità post-restore​

5. Restart consumer dopo restore​

6. Test mensile​

7. Riferimenti​