Guida passo-passo: eseguire il Point-in-Time-Recovery (PITR) di PostgreSQL su database di produzione

Il PostgreSQL Point-in-Time-Recovery (PITR) è la capacità di riportare un’istanza fisica del database a un istante preciso nel passato. Per gli operatori di installazioni in produzione, il PITR è spesso l’unico modo per annullare in modo mirato transazioni errate, cancellazioni accidentali o danni da ransomware. Questa guida estende le basi con passaggi di verifica collaudati, gestione dei cambi di timeline, controlli di integrità, esempi di automazione e passaggi concreti di troubleshooting, in modo che possiate agire con sicurezza in emergenze reali.

Kurzüberblick: Was ist PITR und wie funktioniert es?

Il PITR combina due componenti fisiche di PostgreSQL: i Base Backups (istantanee coerenti della directory dati PGDATA) e i Write‑Ahead‑Logs (WAL), che documentano sequenzialmente ogni modifica dei dati. Per un ripristino riuscito è necessario un Base Backup e tutti i segmenti WAL fino al punto temporale desiderato. Se manca un segmento WAL, il ripristino fino a quel punto non è possibile e sarà necessario ricorrere a un momento precedente o recuperare gli archivi mancanti da repository secondari.

Wann ist PITR die richtige Methode?

Il PITR è il metodo appropriato quando si vuole annullare modifiche fino a un istante preciso; non è invece indicato per ricostruire selettivamente singole tabelle (in questi casi sono più adatti backup logici come pg_dump o la Logical Replication). Casi d’uso tipici:

• Aggiornamenti di massa errati / cancellazione accidentale di grandi quantità di dati.
• Analisi forense: ricostruire lo stato del DB a uno specifico timestamp.
• Corruzione parziale dei dati, quando è interessato solo un intervallo temporale ristretto.
• Scenari di ransomware, quando si deve ripristinare uno stato pulito anteriore all’infezione.

Grundvoraussetzungen und Architektur

Prima di un PITR devono essere soddisfatti in modo affidabile i seguenti requisiti:

• WAL‑archiving attivo (archive_mode = on) e un archive_command testato.
• Base Backups regolari e coerenti (ad es. con pg_basebackup o snapshot di storage coerenti).
• Una destinazione di archivio affidabile con ridondanza (percorso di archivio locale, NFS o object store come S3) e permessi di accesso tutelati.
• Documentazione: correlazione dei timestamp dei Base Backup, degli intervalli WAL e delle Timeline‑ID.

Verificate rapidamente la configurazione con psql:

psql -At -c "SHOW archive_mode; SHOW wal_level; SHOW archive_command; SHOW archive_timeout;"

Wichtige Parameter kurz erklärt

In una frase:

• archive_mode: abilita l’archiviazione dei WAL.
• archive_command: comando shell/script che trasferisce i segmenti WAL nell’archivio.
• wal_level: deve essere almeno replica affinché i dati WAL completi necessari per il PITR siano disponibili.
• archive_timeout: forza l’archiviazione periodica anche con bassa attività.

PostgreSQL Point-in-Time-Recovery (PITR): Timeline, WAL‑Retention und Betrieb

In ambienti di produzione emergono due aspetti operativi particolarmente critici: il cambio di timeline e la conservazione dei WAL. Le Timeline‑ID si generano in occasione di promozioni o failover; i nomi dei file WAL e i backup‑label le contengono. Se si è verificato un salto di timeline, i WAL della timeline corretta devono essere disponibili, altrimenti il replay si interrompe.

# Timeline-Infos aus dem Base Backup / control
psql -c "SELECT timeline_id, last_wal_replay_lsn() FROM pg_control_checkpoint();"
# Timeline-History Dateien im Archiv prüfen
ls -1 /srv/pg_wal_archive/*.history

Pianificate la conservazione dei WAL in modo da coprire la vostra finestra di ripristino (RPO). Per S3/object‑store è consigliabile una lifecycle‑policy che conservi i WAL per almeno la durata della vostra più ampia finestra di ripristino pianificata.

Step‑by‑Step: Eseguire PITR (descrizione estesa del processo)

I passaggi seguenti si basano sul flusso di base, lo estendono con controlli e verifiche e forniscono raccomandazioni operative concrete:

1) Pianificazione, isolamento e comunicazione

Scegliete il momento di recovery e informate gli stakeholder. Preparate un host di recovery isolato o una copia del PGDATA; non sovrascrivete mai direttamente il PGDATA produttivo. Definite una condizione di rollback nel runbook: es. interruzione se mancano WAL o si rilevano errori di checksum.

2) Base Backup identifizieren und integritätsgeprüft bereitstellen

Verificate il Base Backup per completezza e integrità. Se usate backup in formato tar, controllate backup_label, il manifest e le eventuali checksum memorizzate.

mkdir -p /recovery/pgdata
cd /recovery/pgdata
tar -xzf /backups/basebackup_2026-07-26.tar.gz
cat /recovery/pgdata/backup_label
# Optional: Prüfen einer manifest-Datei mit SHA256-Hashes
sha256sum -c /backups/basebackup_2026-07-26.manifest

3) RESTore_command gründlich testen

Gli script RESTore_command difettosi sono una delle cause più comuni di recovery fallite. Testate l’intera catena manualmente come postgres-user, inclusi credenziali di rete, SELinux/AppArmor‑Kontexte e il percorso verso aws/gsutil.

# Beispiel: S3-Objekt abrufen und auf Lesbarkeit prüfen
sudo -u postgres bash -c "aws s3 cp s3://my-pg-wal-archive/0000000100000000000000A0 /tmp/test_wal.gz && gunzip -c /tmp/test_wal.gz > /tmp/test_wal && file /tmp/test_wal"
# Prüfen auf Exit-Code
if [ $? -ne 0 ]; then echo 'RESTore_command failed'; fi

4) Recovery‑Parameter setzen und Timeline‑Regeln prüfen

Per PostgreSQL 12+ impostate i parametri di recovery in postgresql.conf o in una configurazione separata simile a recovery.conf. PRESTate attenzione a recovery_target_time, recovery_target_lsn e recovery_target_timeline. recovery_target_timeline determina se, in presenza di un branch di timeline, utilizzare il branch più recente (latest), solo la timeline corrente (current) o un ID di timeline esplicito.

# Beispielkonfiguration
RESTore_command = '/usr/local/bin/RESTore_wal_from_s3.sh %f %p'
recovery_target_time = '2026-07-27 14:12:03+00'
recovery_target_timeline = 'latest'
recovery_target_action = 'promote'

5) Start, Monitoring und WAL‑Replay‑Analyse

Avviate il recovery posizionando una recovery.signal (PG12+) nel PGDATA di recovery. Monitorate i log e il replay‑LSN. Usate pg_waldump per analizzare preventivamente i contenuti WAL se sospettate irregolarità (es. fine improvvisa di un segmento):

# WAL-Inhalt prüfen
pg_waldump -f /srv/pg_wal_archive/0000000100000000000000A0 | head -n 50
# Replay-Status prüfen
psql -c "SELECT pg_is_in_recovery(), pg_last_wal_replay_lsn(), pg_last_wal_replay_timestamp();"

Se il replay si interrompe su un determinato file WAL, verifichi quel file per corruzione e lo confronti, se necessario, con una copia alternativa (p. es. archivio secondario o altra regione).

6) Promozione e validazione

Dopo avere raggiunto il punto temporale target, esegua la promozione o lo shutdown secondo il Runbook. Validi con query aziendali a campione, Row‑Counts e controlli sugli indici. Crei quindi un nuovo Base Backup per chiudere correttamente la catena di recovery.

Analisi degli errori: problemi frequenti e contromisure concrete

• WAL mancanti: Controlli altri obiettivi di archivio, repliche o backup. Se non disponibili, riduca il punto di recovery all’ultimo LSN disponibile e comunichi l’entità della perdita di dati.
• Corruzione WAL: Utilizzi pg_waldump per identificare la corruzione. In genere le correzioni della corruzione non sono possibili — è necessaria una copia integra del WAL interessato o bisogna fermarsi prima del segmento corrotto.
• Confusione sulle Timeline: Controlli i file .history nell’archivio e imposti recovery_target_timeline di conseguenza.
• Errori di autorizzazioni e ambiente: Testi tutti gli script come utente postgres; verifichi SELinux/AppArmor e le variabili d’ambiente per aws/gsutil.

Controlli di integrità e tecniche di validazione

Oltre a semplici campionamenti, pianifichi controlli strutturati:

• Confronto dei Row‑Counts per le tabelle chiave tra il log di produzione e il sistema ripristinato.
• Validazione delle checksum nei Base Backups (se create durante il backup) e negli oggetti d’archivio tramite manifest SHA256 memorizzati.
• Controlli funzionali e di integrazione con una copia dell’applicazione in modalità Read‑Only.

Automazione e monitoring: query di controllo consigliate

Configuri controlli di monitoring che segnalino tempestivamente errori di archiviazione:

# Letzte Archivierungs‑Informationen
psql -c "SELECT archived_count, failed_count, last_archived_wal FROM pg_stat_archiver;"
# Letzte WAL-File-Zeitstempel
psql -c "SELECT name, last_modified FROM pg_ls_waldir() LIMIT 10;" -- Abhängig von installierten Helper-Funktionen

Integra alert in Prometheus/Nagios che generino allarmi quando per X ore non vengono più archiviati WAL o un RESTore_command fallisce.

Quando il PITR non è possibile: alternative

Se esistono lacune nei WAL e il PITR completo non è possibile, entrano in gioco le seguenti alternative:

• Ripristino logico delle tabelle con pg_dump/pg_RESTore, se sono disponibili backup logici.
• Ricostruzione da log applicativi o sorgenti ETL.
• Ripristino parziale: recovery fino all’ultima WAL disponibile e correzioni integrative da parte dei team applicativi.

Consigli pratici per l’operazione

• Automatizzi i RESTore di prova in un ambiente isolato e documenti i tempi (RTO) e l’impegno.
• Cataloghi i backup e gli intervalli WAL in un registro/DB centrale con metadati (Start/End LSN, Timeline, checksum).
• Gestisca gli accessi agli obiettivi di archivio in modo minimale (IAM‑Principle of Least Privilege) e usi la memorizzazione cifrata.
• Dopo ogni recovery crei immediatamente un nuovo Base Backup per semplificare la catena futura.

Checkliste: Vor dem Live‑PITR (Erweiterte Version)

1. Controllo asset: Base Backup completo, backup_label e manifest verificati.
2. WAL‑Check: Tutti i file WAL presenti e verificati nell’integrità.
3. RESTore_command: testato manualmente come utente postgres, codici di uscita corretti.
4. Timeline‑Check: file .history e Timeline‑IDs confrontati.
5. Recovery‑Host: risorse, isolamento e I/O dello storage verificati.
6. Kommunikation: Stakeholder informati, catena di escalation pronta.
7. Rückfall: PGDATA attuale salvato, criteri di fallback definiti nel Runbook.

Conclusione

PostgreSQL Point-in-Time-Recovery (PITR) è potente, ma operativamente esigente. Determinanti sono backup base affidabili, archiviazione WAL senza lacune, script RESTore_command testati, documentazione chiara delle timeline e controlli automatizzati. Con RESTore di prova regolari, strategie di archivio ridondanti e una documentazione Runbook pulita, il PITR diventa un elemento affidabile della vostra strategia di disaster recovery. Pianificate risorse per verifiche e automazione — i costi dei test preventivi sono inferiori rispetto a ripristini non pianificati e caotici.

Ulteriori indicazioni per il collegamento interno

Pagine interne da collegare: Backup‑Policy, configurazione dell’archiviazione WAL, Recovery‑Runbook, linee guida IAM per il cloud storage e lista dei contatti dei team responsabili. Questi link facilitano l’assegnazione delle responsabilità e accelerano il recovery in caso di emergenza.

PostgreSQL Point‑in‑Time‑Recovery (PITR): aspetti di architettura e integrazione

Oltre al flusso di RESTore e ai passaggi di verifica, vale la pena considerare decisioni di architettura e integrazioni che in esercizio fanno la differenza tra un ambiente rapidamente ripristinabile e una ricostruzione dell’incidente prolungata.

Archiviazione, object‑storage e lifecycle: regole pratiche

I WAL archive oggi spesso finiscono in object store di tipo S3. Pianificate i lifecycle in modo che i WAL rimangano disponibili almeno per la finestra di recovery maggiore (RPO). PRESTate attenzione al versioning/immutabilità degli oggetti per prevenire sovrascritture accidentali. Considerate i costi di egress di rete in caso di ampio fabbisogno di ripristino da region cloud.

Sicurezza, credenziali e principio del least‑privilege

Il RESTore_command richiede accesso alle destinazioni di archivio. Usate ruoli/token a breve durata (IAM‑Session, presigned URLs) invece di chiavi statiche. I ruoli dovrebbero avere solo permessi di sola lettura sui prefix rilevanti. Documentate la rotazione delle chiavi e gli audit‑trail, in modo che una credenziale di archivio compromessa possa essere revocata rapidamente.

Kubernetes, Snapshots und PITR: Fallen vermeiden

In ambienti containerizzati i PV‑Snapshot sono attraenti, ma non sostituiscono automaticamente un Base Backup coerente + catena WAL. Uno snapshot di un Pod Postgres in esecuzione deve essere coordinato (p. es. pg_start_backup/pg_stop_backup o filesystem‑freeze), altrimenti mancano WAL o il backup risulta incoerente. Per StatefulSets si consiglia una combinazione di CSI‑Snapshots per ripristini rapidi e backup base regolari per garantire la capacità PITR.

Idempotenz und Robustheit des RESTore_command

Il RESTore_command viene chiamato ripetutamente per ogni segmento WAL mancante. Assicuratevi che lo script sia idempotente, che scriva file temporanei in modo atomico e che propaghi correttamente i codici di errore. Testate lo script in condizioni come connessione di rete lenta, 403/404 inattesi e download parziali.

# Überwachung: einfache Prüfqueries, die Sie in Alerts nutzen können
psql -c "SELECT archived_count, failed_count, last_archived_wal FROM pg_stat_archiver;"
psql -c "SELECT status, receive_start_lsn, receive_start_tli FROM pg_stat_wal_receiver;"

Interazione operativa tra replica e failover

Eseguire sempre il ripristino in isolamento — evitare promozioni automatiche delle replica in streaming durante un PITR pianificato. Il branching della timeline dopo una promozione porta altrimenti a situazioni complesse con file .history. Definire nel Runbook quando fermare le repliche, quando consentire promozioni conservative e quando è necessario un intervento manuale.

Validazione come processo: ripristini di prova automatizzati

Automatizzate i ripristini di prova a intervalli regolari e documentate RTO/RPO. Un reporting chiaro su tassi di successo, durata e errori di archiviazione riscontrati rende misurabile la prontezza al PITR e riduce il rischio di sorprese in caso di evento critico.

Per questo tema è importante anche l’archiviazione WAL. Il contributo inquadra questi aspetti in modo chiaro e mostra ciò che conta nella pratica quotidiana.