Risoluzione dei problemi di elevata attività WAL e gestione dello spazio su disco dei log di archivio

Un'elevata attività del Write-Ahead Log (WAL) in PostgreSQL non è automaticamente un problema. Un database occupato dovrebbe generare WAL. Il problema si verifica quando il tasso di WAL ti sorprende, quando i WAL archiviati non vengono mai puliti, o quando pg_wal cresce perché qualcosa impedisce a PostgreSQL di riciclare i segmenti vecchi.

Il modo più veloce per peggiorare un incidente WAL è eliminare manualmente i file da pg_wal. Non farlo. Tratta un disco WAL pieno come una situazione di ripristino: identifica cosa sta trattenendo il WAL, crea spazio se puoi farlo in sicurezza, poi risolvi l'archiviazione fallita, lo standby in ritardo o lo slot abbandonato che ha causato la crescita.

Comprendere il Write-Ahead Logging (WAL)

PostgreSQL scrive i record di modifica nel WAL prima che le pagine di dati correlate vengano scritte in modo sicuro. Dopo un crash, PostgreSQL riproduce il WAL in modo che le modifiche commesse non vadano perse. Lo stesso flusso viene utilizzato anche per la replica in streaming e il point-in-time recovery.

I file WAL sono memorizzati in file di segmento di dimensione fissa. Molte installazioni utilizzano segmenti da 16 MB perché è l'impostazione predefinita comune, ma la dimensione viene scelta quando il cluster viene inizializzato. Un carico di lavoro pesante in scrittura può creare un gran numero di segmenti rapidamente. I segmenti vecchi vengono riciclati o rimossi solo dopo che PostgreSQL non ne ha più bisogno per il ripristino da crash, i checkpoint, l'archiviazione, la replica o gli slot.

Concetti chiave del WAL:

Durabilità: le transazioni commesse possono essere recuperate dopo un crash.
Replica: gli standby ricevono i record WAL dal primario.
Point-in-Time Recovery (PITR): i backup di base più il WAL archiviato consentono di recuperare fino a un punto scelto all'interno della finestra di conservazione.
Segmenti WAL: il WAL è memorizzato in file di segmento sotto pg_wal.

Cause comuni di elevata attività WAL

Diversi fattori possono contribuire a un volume insolitamente elevato di generazione WAL. Identificare la causa principale è il primo passo per una risoluzione efficace dei problemi.

1. Caricamento e modifiche di dati bulk

Operazioni come INSERT, UPDATE, DELETE, TRUNCATE e COPY possono generare quantità significative di WAL. Le operazioni bulk, specialmente su tabelle di grandi dimensioni, produrranno naturalmente più record WAL rispetto a piccole transazioni individuali.

Esempio: Un singolo comando COPY FROM per inserire milioni di righe può generare gigabyte di dati WAL.
Esempio: Esecuzione di una migrazione di dati su larga scala o di uno script di aggiornamento batch.

2. Ritardo di replica e problemi degli standby

Se i tuoi server standby non tengono il passo con il primario (ritardo di replica), i file WAL si accumuleranno sul primario. Il server primario non può rimuovere i segmenti WAL completati finché non viene confermato che sono stati inviati ed elaborati da tutti gli standby connessi (se wal_keep_size o max_slot_wal_keep_size non sono configurati, o se gli slot non vengono utilizzati correttamente).

Scenario: Un server standby è spento, disconnesso o sta riscontrando problemi di prestazioni, impedendogli di consumare i record WAL dal primario.

3. Scritture di pagina intera dopo i checkpoint

Dopo un checkpoint, la prima modifica a una pagina di dati può registrare un'immagine di pagina intera quando full_page_writes è abilitato. Questa impostazione protegge il ripristino da pagine danneggiate e di solito viene lasciata attiva. Se i checkpoint si verificano troppo spesso, le immagini di pagina intera possono aumentare notevolmente il volume WAL. La soluzione è solitamente ottimizzare il comportamento del checkpoint, non disabilitare le protezioni di durabilità.

4. Crescita non gestita della directory `pg_wal`

Se l'archiviazione WAL è abilitata e fallisce, PostgreSQL conserva i segmenti WAL che devono ancora essere archiviati. Se l'archiviazione non è abilitata, pg_wal dovrebbe comunque riciclare i segmenti vecchi quando non sono più necessari, a meno che la replica, gli slot o la pressione del checkpoint non li stiano trattenendo.

5. Slot di replica non recuperati

Gli slot di replica garantiscono che i segmenti WAL non vengano rimossi prima di essere consumati da uno standby specifico o da un client di decodifica logica. Se uno slot viene creato ma il consumatore si ferma o si disconnette senza che lo slot venga eliminato, i segmenti WAL richiesti da quello slot verranno conservati, anche se lo standby non è più attivo.

Gestione dello spazio su disco WAL: configurazione e soluzioni

Affrontare un'elevata attività WAL richiede un approccio articolato che coinvolge monitoraggio, ottimizzazione della configurazione e procedure di manutenzione adeguate.

1. Abilitare e monitorare l'archiviazione WAL

L'archiviazione WAL è il meccanismo più critico per gestire lo spazio su disco e abilitare il PITR. Quando l'archiviazione è abilitata, i file WAL completati vengono copiati in una posizione separata (ad esempio, una condivisione di file di rete, un bucket S3 o un disco diverso).

Configurazione:

Modifica il tuo file postgresql.conf:

wal_level = replica         # O logical per la replica logica
archive_mode = on           # Abilita l'archiviazione
archive_command = 'cp %p /percorso/dell/archivio/%f'

# Esempio per S3 usando wal-g o strumento simile:
# archive_command = 'wal-g wal-push %p'

%p: Segnaposto per il percorso completo del file WAL da archiviare.
%f: Segnaposto per il nome del file WAL.

Importante: Il archive_command deve essere in grado di essere eseguito con successo. Se restituisce un codice di uscita diverso da zero, PostgreSQL considererà l'archiviazione fallita, il che può portare alla mancata rimozione dei file WAL. Assicurati che la directory di destinazione abbia spazio sufficiente e che l'utente che esegue PostgreSQL abbia i permessi di scrittura.

Monitoraggio dell'archiviazione

Usa query SQL per verificare lo stato dell'archiviazione:

SELECT archived_count,
       failed_count,
       last_archived_wal,
       last_archived_time,
       last_failed_wal,
       last_failed_time
FROM pg_stat_archiver;

Se failed_count continua ad aumentare o last_archived_time è vecchio mentre il database sta ancora scrivendo, risolvi la destinazione dell'archivio prima di ottimizzare i parametri di dimensione WAL.

2. Gestione della dimensione della directory `pg_wal`

Anche con l'archiviazione abilitata, la directory pg_wal sul primario può crescere se i segmenti WAL non vengono rimossi dopo l'archiviazione. Ciò accade se:

Gli standby non tengono il passo e il primario conserva WAL extra per la replica.
Gli slot di replica trattengono i file WAL.

`wal_keep_size`

Questo parametro mantiene WAL extra sul primario per la replica in streaming. Ha sostituito la vecchia impostazione wal_keep_segments in PostgreSQL 13. È utile per gli standby che non utilizzano slot, ma non è una garanzia che uno standby molto in ritardo possa sempre recuperare.

# postgresql.conf sul primario
wal_keep_size = 1024 # Mantieni 1 GB di WAL su disco

Gli slot di replica sono spesso preferiti quando è necessario che il primario conservi il WAL per un consumatore specifico, ma gli slot devono essere monitorati perché possono trattenere il WAL indefinitamente.

`max_slot_wal_keep_size` (PostgreSQL 13+)

Questa impostazione limita la quantità di WAL che uno slot di replica può conservare. È un guardrail contro la crescita illimitata da uno slot rotto, ma può anche causare la perdita del WAL necessario a un consumatore in ritardo e richiederne la reinizializzazione.

# postgresql.conf sul primario
max_slot_wal_keep_size = 2048 # Limita gli slot a conservare 2 GB di WAL

# Considera anche: wal_keep_size -- ancora rilevante per lo streaming non basato su slot
# wal_keep_size = 1024 # Mantieni 1 GB per lo streaming non basato su slot

Se uno slot è troppo indietro e supera questo limite, PostgreSQL potrebbe rimuovere il WAL necessario al momento del checkpoint. Questo protegge lo spazio su disco, ma lo standby interessato o il client di replica logica potrebbero non essere più in grado di continuare dalla loro vecchia posizione.

Slot di replica

Gli slot di replica sono cruciali per prevenire la perdita di WAL e garantire una replica affidabile. Tuttavia, possono causare l'accumulo di file WAL se non gestiti correttamente.

Problema: Uno slot di replica viene creato, ma il consumatore (standby o client logico) si disconnette o fallisce, e lo slot non viene mai eliminato. Il server primario manterrà tutti i file WAL che lo slot sta aspettando.
Soluzione: Monitora regolarmente gli slot di replica ed elimina quelli che non sono più in uso.

-- Elenca gli slot di replica
SELECT slot_name,
       plugin,
       slot_type,
       active,
       restart_lsn,
       wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC NULLS LAST;

-- Elimina uno slot inutilizzato
SELECT pg_drop_replication_slot('nome_slot_da_eliminare');

Avvertenza: Eliminare uno slot di replica causerà la perdita della posizione di qualsiasi consumatore connesso. Assicurati che il consumatore non sia più necessario o sia stato reinizializzato correttamente prima di eliminarlo.

3. Ottimizzazione di `min_wal_size` e `max_wal_size`

Questi parametri influenzano la frequenza dei checkpoint e la quantità di WAL che PostgreSQL cerca di mantenere per il riutilizzo. Non limitano il WAL conservato per l'archiviazione o la replica.

min_wal_size: incoraggia PostgreSQL a mantenere almeno questa quantità di WAL per il riutilizzo invece di rimuoverlo immediatamente.
max_wal_size: il volume WAL che tende a innescare un checkpoint. Non è un massimo rigido quando il WAL viene conservato per altri motivi.

# postgresql.conf
min_wal_size = 1GB
max_wal_size = 4GB

Aumentare max_wal_size può dare al sistema più margine durante i picchi di carico di scrittura, ma significa anche che più spazio su disco sarà occupato da file WAL preallocati.

4. Pulizia regolare dei file WAL archiviati

L'archiviazione WAL, sebbene essenziale per il ripristino, può anche portare a problemi di spazio su disco se i file archiviati non vengono mai puliti. Devi avere una strategia per gestire la conservazione dei tuoi file WAL archiviati.

Strategia: Implementa uno script o utilizza uno strumento dedicato (come pg_archivecleanup, pgBackRest, wal-g, barman) per rimuovere i vecchi file WAL dalla posizione di archivio una volta che non sono più necessari per PITR o replica.
Utilizzo di pg_archivecleanup: Questa utility può essere eseguita sul server primario per rimuovere i vecchi file WAL dalla directory di archivio.
```
pg_archivecleanup /percorso/della/posizione/dell/archivio 0000000100000037000000AF
```
Il secondo argomento è il nome del file WAL più vecchio che deve ancora essere conservato, non un'età arbitraria. In pratica, strumenti di backup come pgBackRest, Barman e WAL-G sono più sicuri perché comprendono la conservazione dei backup e il WAL necessario per il ripristino.

Importante: Assicurati sempre che la tua strategia di pulizia sia allineata con i requisiti di backup e Point-In-Time Recovery (PITR). Devi conservare i file WAL abbastanza a lungo da coprire la finestra di ripristino desiderata.

5. Monitoraggio dello spazio su disco e del tasso di generazione WAL

Il monitoraggio proattivo è fondamentale per prevenire l'esaurimento dello spazio su disco.

Monitora lo spazio su disco: Tieni traccia dello spazio libero nella directory dei dati, pg_wal, nelle posizioni dei file temporanei e nelle destinazioni di archivio.
Monitora la generazione WAL: Utilizza le differenze LSN nel tempo per stimare il tasso di generazione.
```
SELECT now() AS sample_time,
       pg_current_wal_lsn() AS current_lsn;
```
Memorizza quel valore periodicamente e confronta i campioni con pg_wal_lsn_diff(new_lsn, old_lsn). Per una visione rapida della dimensione corrente della directory pg_wal:
```
SELECT pg_size_pretty(sum(size)) AS pg_wal_size
FROM pg_ls_waldir();
```

Passaggi per la risoluzione dei problemi per dischi pieni

Se il tuo disco è già pieno a causa dell'attività WAL, è necessaria un'azione immediata:

Identifica la causa: Controlla pg_stat_archiver per errori di archiviazione. Esamina pg_replication_slots per slot inutilizzati o problematici. Controlla il ritardo di replica sugli standby.
Libera spazio senza danneggiare il ripristino:
- Non eliminare manualmente i file da pg_wal.
- Se la destinazione dell'archivio è piena, rimuovi i vecchi WAL archiviati solo quando sono al di fuori della finestra di conservazione del backup.
- Se possibile, aggiungi storage o sposta la destinazione dell'archivio, quindi lascia che PostgreSQL archivi e ricicli normalmente.
Affronta la causa principale:
- Ripara l'archiviazione: Assicurati che archive_command sia corretto e che la destinazione abbia spazio.
- Gestisci gli slot: Elimina eventuali slot di replica inutilizzati.
- Ripara la replica: Affronta i problemi che causano il ritardo dello standby.
- Aumenta lo spazio su disco: Aggiungi temporaneamente o permanentemente più storage.
Stimola l'archiviatore: Dopo aver riparato il comando di archivio o la destinazione, PostgreSQL dovrebbe riprovare. Un ricaricamento potrebbe essere sufficiente per le modifiche di configurazione; un riavvio completo dovrebbe essere l'ultima risorsa durante un incidente del disco.

Un modello mentale più sicuro

Quando pg_wal sta crescendo, poni tre domande in ordine:

PostgreSQL sta generando più WAL del solito perché il carico di lavoro è cambiato?
PostgreSQL non è in grado di archiviare il WAL?
A PostgreSQL viene detto di conservare il WAL per la replica o uno slot?

Queste risposte portano a diverse soluzioni. Le scritture bulk potrebbero richiedere pianificazione, raggruppamento o ottimizzazione del checkpoint. I fallimenti di archivio necessitano di correzioni di storage e comandi. La conservazione degli slot richiede il recupero del consumatore, la pulizia dello slot o un limite di conservazione. Indovinare su max_wal_size raramente risolve il problema reale da solo.