Come PostgreSQL salva i dati: struttura del database e transaction log (WAL)

Quando lavori con un database, sembra tutto piuttosto semplice: hai aggiunto una riga, aggiornato un record, cancellato un cliente. Ma dietro questa semplicità c’è un meccanismo complesso e ben pensato. Dove sono davvero salvati questi dati? Come fa PostgreSQL a non perdere nulla, anche se il server si spegne all’improvviso?

Per capirlo, bisogna afferrare due cose chiave: dove stanno fisicamente i dati (tabelle, indici, info di servizio) e come funziona il meccanismo di protezione di questi dati — il transaction log, cioè il WAL (Write-Ahead Logging).

Tutto il database PostgreSQL è salvato come un insieme di file in una directory speciale — data_directory. Di solito si trova qui:

/var/lib/postgresql/17/main

In questa cartella c’è il cuore del database: tabelle, indici, metainfo e configurazioni. Qui trovi anche il WAL — il meccanismo che riceve per primo tutte le modifiche. Prima che i dati finiscano nella tabella su disco, vengono scritti nel WAL. È come una bozza dove il database fissa ogni passo, così che in caso di crash si possa recuperare tutto fino all’ultima operazione.

Grazie a questo approccio PostgreSQL garantisce affidabilità e robustezza, anche nelle condizioni più instabili.

Tabelle

Ogni tabella è fisicamente un file separato o un gruppo di file. Questi file stanno nella sottocartella base/. La struttura è tipo così:

$PGDATA/base/
├── 16384/
│   ├── 12345   ← tabella
│   ├── 12346   ← indice
│   └── ...

• 16384 — è l’identificatore interno del database (OID).
• 12345 — identificatore della tabella specifica.

Se la tabella è grande, PostgreSQL la divide in segmenti da 1 GB:

12345
12345.1
12345.2
...

I file non contengono "righe" come in un CSV di testo — è un formato di "pagine binarie" da 8 KB.

WAL: Write-Ahead Logging — non è solo un "log"

Ora passiamo a una delle parti più importanti e spesso fraintese di PostgreSQL — WAL, cioè Write-Ahead Logging. Nonostante la parola log nel nome, il WAL non è un normale file di log di testo, tipo i log di errori o delle query. È un meccanismo vitale di coerenza e recovery dei dati, che lavora a livello di modifiche low-level del filesystem.

Il WAL non è un report di eventi, ma la registrazione preventiva di tutte le modifiche che PostgreSQL sta per fare ai dati. Questa scrittura avviene prima della modifica effettiva delle tabelle su disco. Ecco perché si chiama write-ahead — "scrittura anticipata".

Quando, per esempio, inserisci una nuova riga in una tabella, PostgreSQL:

1. NON aggiorna subito la tabella su disco — sarebbe lento e poco sicuro.
2. Prima scrive nel WAL che quella riga verrà aggiunta.
3. Solo dopo, quando conviene (tipo in background), i dati finiscono davvero nella tabella.

Funziona come un assegno in banca: prima lo firmi (WAL), e solo dopo la banca aggiorna il conto (tabella). Se qualcosa va storto — l’assegno ce l’hai ancora, puoi ripetere l’operazione.

Formato e struttura del WAL

• I file WAL sono in formato binario.
• Ogni file è uno stream ordinato di operazioni che descrivono modifiche interne delle pagine dati, strutture di indici, commit, ecc.
• Un file WAL ha dimensione fissa — di default 16 MB.

Importante: il WAL non contiene "comandi SQL" o "righe di tabella" come siamo abituati. Contiene istruzioni per il motore PostgreSQL su come riprodurre le modifiche, pagina per pagina.

Cosa succede se c’è un crash?

Se il server PostgreSQL si spegne all’improvviso — tipo per un blackout — non è tutto perso. Al prossimo avvio il database non va in panico, ma carica dal disco l’ultima versione "stabile" dei dati. Poi prende il transaction log (WAL), dove sono rimaste tutte le ultime modifiche, e le applica con calma — cioè aggiorna quello che non era ancora finito nei file principali. Così il database torna in uno stato coerente, come se non fosse successo nulla.

Funzionalità extra del WAL

Point-In-Time Recovery (PITR). Salvare i file WAL permette di ripristinare il database a qualsiasi punto nel tempo tra due backup completi.

Replica streaming. PostgreSQL può inviare le entry WAL a un altro server in tempo reale. Così puoi avere una hot replica — una copia del database sincronizzata con la principale.

Ripristino incrementale. Insieme a un backup completo, il WAL permette di ripristinare solo le modifiche, senza dover copiare tutto il database da zero.

Creare backup binari: pg_basebackup

Se hai già preso confidenza con pg_dump, sai che va benissimo per fare backup logici (cioè copiare struttura e dati del database come query SQL). Ma se ti serve un backup fisico? Tipo una copia speculare di tutti i file del database? Qui entra in gioco pg_basebackup.

pg_basebackup è una utility che ti permette di creare copie fisiche dei dati PostgreSQL. È super utile per database grandi, dove serve gestire il recovery in modo efficiente. Il vantaggio principale di pg_basebackup è che è davvero veloce.

Sintassi base del comando pg_basebackup

Lavorare con pg_basebackup parte dal capire il comando. Lo lanci dal terminale e la forma base è questa:

pg_basebackup -D /backup_directory -F tar -z -P

Vediamo cosa fa ognuno:

• -D /backup_directory — indica la directory dove salvare i file di backup.
• -F tar — formato dei dati. L’opzione tar crea un archivio .tar. Puoi anche usare plain per avere la struttura a file del database.
• -z — comprime il backup, così risparmi spazio su disco. Sempre bello quando il backup pesa meno!
• -P — mostra l’avanzamento in tempo reale. È come una dose extra di fiducia: vedi che il processo va avanti e il server non è "bloccato".

Esempio d’uso:

pg_basebackup -D /backups/university_backup -F tar -z -P

Dopo il comando, nella directory /backups/university_backup trovi il backup in formato .tar.

Vantaggi di usare pg_basebackup

Efficienza: i backup incrementali evitano di duplicare dati invariati, risparmiando tempo e spazio.

Semplicità: lo strumento pg_basebackup gestisce tutto in automatico, inclusi i file WAL.

Affidabilità: grazie all’integrazione con la meccanica di PostgreSQL, pg_basebackup crea copie fedeli dell’intero database, che puoi ripristinare facilmente.

Esempi d’uso

Ora — un po’ di pratica. Qui sotto trovi esempi reali di come usare pg_basebackup per fare backup di PostgreSQL. Vediamo come fare un backup base, aggiungere la compressione e abilitare l’archiviazione del transaction log (WAL) per il recovery "point-in-time". Questi comandi vanno bene sia per chi inizia che per scenari avanzati.

Creare un backup base

pg_basebackup -D /backups/full_backup -F tar -z -P

Risultato: backup completo del database in archivio .tar.

Impostare compressione e formato

Facciamo un backup con livello di compressione alto:

pg_basebackup -D /backups/full_backup -F tar -z -Z 9 -P

Qui -Z 9 indica il livello di compressione (massimo — 9).

Archiviazione WAL

Se abiliti l’archiviazione del WAL, puoi ripristinare il database a qualsiasi punto nel tempo. Comando per abilitare il backup del WAL:

pg_basebackup -D /backups/incremental_backup -F tar -z -P --wal-method=archive