Passa al contenuto principale

ADR-0012 - Backup strategy Postgres + Timescale

  • Status: Accepted (2026-05-15)
  • Deciders: Massimo Bagnoli (architect), Claude (sintesi Architect+DBA+Security)
  • Implementation tasks: TASK-36 (pg_dump nightly), TASK-37 (restore test mensile), TASK-38 (WAL archiving PITR)
  • Supersedes: nessuno
  • Superseded by: nessuno

Context​

Akira e' una piattaforma VoIP wholesale Class-4 con CDR continuativi. Il target di produzione e' 200 CPS, pari a circa 17M CDR/giorno. Il database TimescaleDB ospita l'hypertable cdr e le tabelle relazionali di supporto per companies, tariffs, routing, utenti, RBAC e configurazione operativa.

I task TASK-36, TASK-37 e TASK-38 definiscono rispettivamente backup nightly, restore drill mensile e WAL archiving. Questa ADR rende esplicite le motivazioni della scelta, i trade-off rispetto a strumenti dedicati e i target operativi.

Target iniziali:

  • Staging: RPO 5 minuti, RTO 15 minuti.
  • Produzione: RPO 1 minuto, RTO 30 minuti, da confermare in una ADR dedicata di fase produzione.

Vincoli operativi:

  • Budget infra: sotto 10 EUR/mese per staging, sotto 50 EUR/mese per produzione.
  • Network: server Hetzner in fsn1; backup storage preferibilmente nella stessa region o nello stesso provider.
  • Skillset team: 1 developer principale piu' agenti AI, senza DBA dedicato.
  • Compliance Fase 1: staging senza PII reale e senza requisito GDPR-specifico sui backup.
  • Debbugabilita': la procedura deve essere comprensibile da un generalist e ripetibile in emergenza.

Decision​

Adottiamo una strategia triple-layer basata su pg_dump custom, WAL archiving via SFTP e restore test periodico.

Layer 1 - Dump notturno con pg_dump​

  • Frequenza: 1 dump/notte alle 01:00 UTC.
  • Comando: pg_dump --format=custom --compress=9.
  • Formato: custom PostgreSQL, compresso e ripristinabile con pg_restore.
  • Scope: schema e dati del database, incluse hypertable TimescaleDB e oggetti applicativi.
  • Destinazione locale: /var/backups/akira/.
  • Destinazione remota: Hetzner Storage Box BX11, directory /backups/pg/, accesso SFTP.
  • Retention locale: 7 giorni.
  • Retention remota: 30 giorni.

Il formato custom e' scelto perche' resta uno standard PostgreSQL, consente restore parallelo e rende il backup ispezionabile senza introdurre tool esterni.

Layer 2 - WAL archiving continuo per PITR​

  • archive_mode = on.
  • archive_command = /usr/local/bin/wal-archive.sh.
  • archive_timeout = 300s, per forzare uno switch WAL almeno ogni 5 minuti anche con traffico basso.
  • Destinazione: stesso Storage Box, directory /backups/wal/.
  • Retention WAL: 14 giorni.
  • RPO effettivo staging: 5 minuti.

La retention WAL di 14 giorni copre il replay esteso dopo il restore di un dump fino a 7 giorni vecchio, lasciando margine operativo per ritardi o investigazioni.

Layer 3 - Restore test mensile​

  • Schedulazione: primo giorno del mese alle 04:00 UTC.
  • Procedura: creare VM Hetzner ephemeral, installare PG16 + TimescaleDB, ripristinare l'ultimo dump, eseguire sanity check, appendere il drill log e distruggere la VM.
  • Sanity check minimi: conteggio tabelle, ENUM, hypertable cdr, funzioni e oggetti critici.
  • Costo stimato: trascurabile, circa millesimi di euro per drill.

Il restore test e' parte della strategia, non un controllo opzionale: senza verifica periodica il dump non dimostra recuperabilita' reale.

Consequences​

Positive​

  • RPO staging 5 minuti raggiungibile con costo storage molto basso.
  • Procedura debuggabile: pg_dump, pg_restore e WAL PostgreSQL sono strumenti standard.
  • Nessun lock-in applicativo: dump custom PostgreSQL e WAL standard possono essere migrati verso S3, Backblaze, GCS o altra destinazione.
  • Restore validato: il drill mensile intercetta dump corrotti, incompatibilita' di versione, oggetti TimescaleDB mancanti e regressioni procedurali.
  • Schema evolution-friendly: il dump fotografa lo schema corrente senza richiedere replay manuale di migration storiche.
  • Buon rapporto costo/beneficio: Storage Box BX11 e VM ephemeral bastano per Fase 1 staging.

Negative​

  • Dump full, non incrementale: ogni backup notturno contiene l'intero database. Con crescita storica dei CDR sara' necessario monitorare capienza e durata.
  • Failure mode WAL: se lo Storage Box non e' raggiungibile, PostgreSQL accumula WAL in pg_wal/; senza alert il disco puo' saturarsi.
  • Nessuna replica fisica in Fase 1: se db-01 e' indisponibile, il ripristino richiede una procedura di restore, non un failover immediato.
  • Lock condivisi durante dump: pg_dump non blocca DML ordinario, ma puo' interferire con DDL e va schedulato fuori dalle finestre di maintenance.
  • Cifratura non obbligatoria in staging: accettabile per Fase 1 senza PII reale, ma da rivalutare prima della produzione.

Neutral​

  • Storage Box e' una scelta economica e semplice per Fase 1; non e' una dichiarazione definitiva per la produzione.
  • La replica streaming futura resta complementare: migliora RTO e availability, ma non sostituisce backup e PITR.

Alternatives considered​

A1 - Barman​

Barman e' un backup manager PostgreSQL dedicato con gestione di WAL, retention e restore PITR.

Pro: soluzione matura, feature complete, standard in molte installazioni PostgreSQL.

Contro: richiede setup e gestione di un server Barman, curva di apprendimento maggiore e runbook specifici. Per staging con team piccolo e budget ridotto e' piu' complesso del necessario.

Verdict: rejected per Fase 1. Da rivalutare in produzione avanzata o quando esistera' un presidio DBA/ops piu' strutturato.

A2 - WAL-G con storage S3-compatible​

WAL-G gestisce backup e WAL streaming verso object storage S3-compatible, con compressione e cifratura.

Pro: robusto, performante, adatto a storage object e scenari cloud-native.

Contro: introduce dipendenza da S3-compatible storage, credenziali e configurazione aggiuntiva. Non sfrutta direttamente Storage Box SFTP, che e' la scelta economica di Fase 1.

Verdict: rejected per Fase 1. Da riconsiderare se Storage Box diventa insufficiente o se la cifratura at-rest gestita dal tool diventa requisito.

A3 - pg_basebackup + WAL streaming​

Approccio nativo PostgreSQL basato su backup fisico e WAL.

Pro: nessun tool esterno, supporta PITR nativo, allineato al modello di replica fisica.

Contro: backup binario legato alla major version e all'installazione PostgreSQL. Non produce un dump logico facilmente ispezionabile o ripristinabile in parallelo con pg_restore.

Verdict: rejected come soluzione primaria di Fase 1. Resta utile come base per una futura replica streaming db-02.

A4 - Snapshot Hetzner Cloud​

Snapshot a livello block del disco o volume della VM.

Pro: rapido, semplice da avviare, utile per rollback infrastrutturale grossolano.

Contro: non garantisce consistenza applicativa se preso durante scritture, vincola al provider, non offre PITR minuto-per-minuto e non sostituisce un restore PostgreSQL verificato.

Verdict: rejected come backup primario. Possibile complemento settimanale in produzione per rollback emergenziale dell'intera VM.

Implementation references​

  • TASK-36: infra/scripts/pg-backup-nightly.sh, cron 01:00 UTC e Ansible in roles/timescaledb/tasks/backup.yml.
  • TASK-37: infra/scripts/dr-restore-test.sh, cron mensile, cloud-init VM ephemeral e sanity check.
  • TASK-38: infra/scripts/wal-archive.sh, configurazione postgresql.conf con archive_mode=on e archive_timeout=300.
  • TASK-43: alert WALArchiveFailures e controlli su pg_stat_archiver.
  • Storage Box prerequisito: ordinare BX11, creare sub-user akira-staging e configurare le variabili vault dedicate.

Monitoring & success metrics​

  • Restore test mensile: target 100% success; ogni fail apre post-mortem e fix.
  • Storage usage: alert se Storage Box supera 85% di utilizzo.
  • WAL archive lag: alert se pg_stat_archiver.last_archived_time resta oltre 10 minuti nel passato.
  • WAL archive failures: alert se pg_stat_archiver.failed_count aumenta.
  • Costo mensile staging: target sotto 10 EUR/mese.
  • Durata restore drill: target sotto RTO staging di 15 minuti.

Open questions​

  • Prima della produzione decidere se cifrare i dump con GPG e chiave in vault (vault_backup_encryption_key) o passare a un tool con encryption integrata.
  • Definire la procedura GDPR-compliant per cancellazione dati e retention backup quando entreranno dati reali multi-tenant.
  • Confermare in una ADR successiva i target produzione RPO 1 minuto / RTO 30 minuti e l'eventuale introduzione di replica streaming.