Dépannage de l'activité WAL élevée et gestion de l'espace disque des journaux d'archivage

Une activité élevée des journaux d'écriture anticipée (WAL) dans PostgreSQL n'est pas automatiquement un problème. Une base de données occupée doit générer du WAL. Le problème survient lorsque le taux de WAL vous surprend, lorsque le WAL archivé n'est jamais nettoyé ou lorsque pg_wal grossit parce que quelque chose empêche PostgreSQL de recycler les anciens segments.

La façon la plus rapide d'aggraver un incident WAL est de supprimer manuellement des fichiers de pg_wal. Ne faites pas cela. Traitez un disque WAL plein comme une situation de récupération : identifiez ce qui retient le WAL, créez une marge de manœuvre si vous le pouvez en toute sécurité, puis corrigez l'archivage défaillant, le standby à la traîne ou le slot abandonné qui a provoqué la croissance.

Comprendre la journalisation d'écriture anticipée (WAL)

PostgreSQL écrit les enregistrements de modification dans le WAL avant que les pages de données associées ne soient écrites en toute sécurité. Après un crash, PostgreSQL rejoue le WAL afin que les modifications validées ne soient pas perdues. Le même flux est également utilisé pour la réplication en continu et la récupération à un instant précis.

Les fichiers WAL sont stockés dans des fichiers de segments de taille fixe. De nombreuses installations utilisent des segments de 16 Mo car c'est la valeur par défaut courante, mais la taille est choisie lors de l'initialisation du cluster. Une charge de travail intensive en écriture peut créer un grand nombre de segments rapidement. Les anciens segments sont recyclés ou supprimés uniquement après que PostgreSQL n'en a plus besoin pour la récupération après crash, les points de contrôle, l'archivage, la réplication ou les slots.

Concepts clés du WAL :

Durabilité : les transactions validées peuvent être récupérées après un crash.
Réplication : les standbys reçoivent les enregistrements WAL du primaire.
Récupération à un instant précis (PITR) : les sauvegardes de base plus le WAL archivé permettent de récupérer à un point choisi dans votre fenêtre de rétention.
Segments WAL : le WAL est stocké dans des fichiers de segments sous pg_wal.

Causes courantes d'une activité WAL élevée

Plusieurs facteurs peuvent contribuer à un volume anormalement élevé de génération de WAL. Identifier la cause racine est la première étape d'un dépannage efficace.

1. Chargement et modifications de données en masse

Les opérations comme INSERT, UPDATE, DELETE, TRUNCATE et COPY peuvent générer des quantités importantes de WAL. Les opérations en masse, en particulier sur les grandes tables, produiront naturellement plus d'enregistrements WAL que les petites transactions individuelles.

Exemple : Une seule commande COPY FROM pour insérer des millions de lignes peut générer des gigaoctets de données WAL.
Exemple : Exécution d'une migration de données à grande échelle ou d'un script de mise à jour par lots.

2. Retard de réplication et problèmes de standby

Si vos serveurs standby ne suivent pas le primaire (retard de réplication), les fichiers WAL s'accumuleront sur le primaire. Le serveur primaire ne peut pas supprimer les segments WAL terminés tant qu'ils n'ont pas été confirmés comme étant envoyés et traités par tous les standbys connectés (si wal_keep_size ou max_slot_wal_keep_size n'est pas configuré, ou si les slots ne sont pas utilisés correctement).

Scénario : Un serveur standby est arrêté, déconnecté ou rencontre des problèmes de performances, l'empêchant de consommer les enregistrements WAL du primaire.

3. Écritures de pages complètes après les points de contrôle

Après un point de contrôle, la première modification d'une page de données peut enregistrer une image de page complète lorsque full_page_writes est activé. Ce paramètre protège la récupération contre les pages déchirées et est normalement laissé activé. Si les points de contrôle se produisent trop souvent, les images de pages complètes peuvent augmenter sensiblement le volume de WAL. La solution consiste généralement à ajuster le comportement des points de contrôle, pas à désactiver les protections de durabilité.

4. Croissance non gérée du répertoire `pg_wal`

Si l'archivage WAL est activé et échoue, PostgreSQL conserve les segments WAL qui doivent encore être archivés. Si l'archivage n'est pas activé, pg_wal devrait toujours recycler les anciens segments lorsqu'ils ne sont plus nécessaires, à moins que la réplication, les slots ou la pression des points de contrôle ne les retiennent.

5. Slots de réplication non récupérés

Les slots de réplication garantissent que les segments WAL ne sont pas supprimés avant d'être consommés par un standby spécifique ou un client de décodage logique. Si un slot est créé mais que le consommateur s'arrête ou se déconnecte sans que le slot soit supprimé, les segments WAL requis par ce slot seront conservés, même si le standby n'est plus actif.

Gestion de l'espace disque WAL : configuration et solutions

Pour traiter une activité WAL élevée, une approche multidimensionnelle est nécessaire, impliquant la surveillance, le réglage de la configuration et des procédures de maintenance appropriées.

1. Activer et surveiller l'archivage WAL

L'archivage WAL est le mécanisme le plus critique pour gérer l'espace disque et permettre la PITR. Lorsque l'archivage est activé, les fichiers WAL terminés sont copiés vers un emplacement séparé (par exemple, un partage de fichiers réseau, un bucket S3 ou un disque différent).

Configuration :

Modifiez votre fichier postgresql.conf :

wal_level = replica         # Ou logical pour la réplication logique
archive_mode = on           # Activer l'archivage
archive_command = 'cp %p /path/to/archive/%f'

# Exemple pour S3 avec wal-g ou un outil similaire :
# archive_command = 'wal-g wal-push %p'

%p : Espace réservé pour le chemin complet du fichier WAL à archiver.
%f : Espace réservé pour le nom du fichier WAL.

Important : La archive_command doit pouvoir s'exécuter avec succès. Si elle renvoie un code de sortie non nul, PostgreSQL considérera que l'archivage a échoué, ce qui peut empêcher la suppression des fichiers WAL. Assurez-vous que le répertoire de destination dispose de suffisamment d'espace et que l'utilisateur exécutant PostgreSQL dispose des autorisations d'écriture.

Surveillance de l'archivage

Utilisez des requêtes SQL pour vérifier l'état de l'archivage :

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

Si failed_count augmente constamment ou si last_archived_time est ancien alors que la base de données écrit encore, corrigez la destination d'archivage avant de régler les paramètres de taille WAL.

2. Gestion de la taille du répertoire `pg_wal`

Même avec l'archivage activé, le répertoire pg_wal sur le primaire peut grossir si les segments WAL ne sont pas supprimés après l'archivage. Cela se produit si :

Les standbys ne suivent pas et le primaire conserve du WAL supplémentaire pour la réplication.
Les slots de réplication retiennent les fichiers WAL.

`wal_keep_size`

Ce paramètre conserve du WAL supplémentaire sur le primaire pour la réplication en continu. Il a remplacé l'ancien paramètre wal_keep_segments dans PostgreSQL 13. Il est utile pour les standbys qui n'utilisent pas de slots, mais il ne garantit pas qu'un standby très en retard puisse toujours rattraper son retard.

# postgresql.conf sur le primaire
wal_keep_size = 1024 # Conserver 1 Go de WAL sur le disque

Les slots de réplication sont souvent préférés lorsque vous avez besoin que le primaire conserve le WAL pour un consommateur spécifique, mais les slots doivent être surveillés car ils peuvent conserver le WAL indéfiniment.

`max_slot_wal_keep_size` (PostgreSQL 13+)

Ce paramètre limite la quantité de WAL qu'un slot de réplication peut conserver. C'est une protection contre la croissance illimitée due à un slot défectueux, mais cela peut également entraîner la perte du WAL nécessaire pour un consommateur à la traîne et nécessiter une réinitialisation.

# postgresql.conf sur le primaire
max_slot_wal_keep_size = 2048 # Limiter les slots à conserver 2 Go de WAL

# Considérez également : wal_keep_size -- toujours pertinent pour le streaming sans slot
# wal_keep_size = 1024 # Conserver 1 Go pour le streaming sans slot

Si un slot prend trop de retard et dépasse cette limite, PostgreSQL peut supprimer le WAL nécessaire au moment du point de contrôle. Cela protège l'espace disque, mais le standby affecté ou le client de réplication logique peut ne plus être en mesure de continuer à partir de son ancienne position.

Slots de réplication

Les slots de réplication sont cruciaux pour éviter la perte de WAL et assurer une réplication fiable. Cependant, ils peuvent entraîner une accumulation de fichiers WAL s'ils ne sont pas gérés correctement.

Problème : Un slot de réplication est créé, mais le consommateur (standby ou client logique) se déconnecte ou échoue, et le slot n'est jamais supprimé. Le serveur primaire conservera tous les fichiers WAL que le slot attend.
Solution : Surveillez régulièrement les slots de réplication et supprimez ceux qui ne sont plus utilisés.

-- Lister les slots de réplication
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;

-- Supprimer un slot inutilisé
SELECT pg_drop_replication_slot('slot_name_to_drop');

Avertissement : La suppression d'un slot de réplication entraînera la perte de sa position par tout consommateur connecté. Assurez-vous que le consommateur n'est plus nécessaire ou a été correctement réinitialisé avant de supprimer le slot.

3. Réglage de `min_wal_size` et `max_wal_size`

Ces paramètres influencent la fréquence des points de contrôle et la quantité de WAL que PostgreSQL essaie de conserver pour la réutilisation. Ils ne limitent pas le WAL conservé pour l'archivage ou la réplication.

min_wal_size : encourage PostgreSQL à conserver au moins cette quantité de WAL pour la réutilisation au lieu de la supprimer immédiatement.
max_wal_size : le volume de WAL qui tend à déclencher un point de contrôle. Ce n'est pas un maximum absolu lorsque le WAL est conservé pour d'autres raisons.

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

Augmenter max_wal_size peut donner au système plus de marge pendant les pics de charge d'écriture, mais cela signifie également que plus d'espace disque sera occupé par des fichiers WAL pré-alloués.

4. Nettoyage régulier des fichiers WAL archivés

L'archivage WAL, bien qu'essentiel pour la récupération, peut également entraîner des problèmes d'espace disque si les fichiers archivés ne sont jamais nettoyés. Vous devez avoir une stratégie pour gérer la rétention de vos fichiers WAL archivés.

Stratégie : Implémentez un script ou utilisez un outil dédié (comme pg_archivecleanup, pgBackRest, wal-g, barman) pour supprimer les anciens fichiers WAL de l'emplacement d'archivage une fois qu'ils ne sont plus nécessaires pour la PITR ou la réplication.
Utilisation de pg_archivecleanup : Cet utilitaire peut être exécuté sur le serveur primaire pour supprimer les anciens fichiers WAL du répertoire d'archivage.
```
pg_archivecleanup /path/to/archive/location 0000000100000037000000AF
```
Le deuxième argument est le nom du plus ancien fichier WAL qui doit encore être conservé, pas un âge arbitraire. En pratique, les outils de sauvegarde tels que pgBackRest, Barman et WAL-G sont plus sûrs car ils comprennent la rétention des sauvegardes et le WAL nécessaire à la récupération.

Important : Assurez-vous toujours que votre stratégie de nettoyage correspond à vos exigences de sauvegarde et de récupération à un instant précis (PITR). Vous devez conserver les fichiers WAL suffisamment longtemps pour couvrir votre fenêtre de récupération souhaitée.

5. Surveillance de l'espace disque et du taux de génération WAL

Une surveillance proactive est essentielle pour éviter l'épuisement de l'espace disque.

Surveiller l'espace disque : Suivez l'espace libre dans le répertoire de données, pg_wal, les emplacements de fichiers temporaires et les destinations d'archivage.
Surveiller la génération WAL : Utilisez les différences LSN au fil du temps pour estimer le taux de génération.
```
SELECT now() AS sample_time,
       pg_current_wal_lsn() AS current_lsn;
```
Stockez cette valeur périodiquement et comparez les échantillons avec pg_wal_lsn_diff(new_lsn, old_lsn). Pour un aperçu rapide de la taille actuelle du répertoire pg_wal :
```
SELECT pg_size_pretty(sum(size)) AS pg_wal_size
FROM pg_ls_waldir();
```

Étapes de dépannage pour les disques pleins

Si votre disque est déjà plein en raison de l'activité WAL, une action immédiate est requise :

Identifier la cause : Vérifiez pg_stat_archiver pour les échecs d'archivage. Examinez pg_replication_slots pour les slots inutilisés ou problématiques. Vérifiez le retard de réplication sur les standbys.
Libérer de l'espace sans endommager la récupération :
- Ne supprimez pas manuellement les fichiers de pg_wal.
- Si la destination d'archivage est pleine, supprimez les anciens WAL archivés uniquement lorsqu'ils sont en dehors de votre fenêtre de rétention de sauvegarde.
- Si possible, ajoutez du stockage ou déplacez la destination d'archivage, puis laissez PostgreSQL archiver et recycler normalement.
Traiter la cause racine :
- Corriger l'archivage : Assurez-vous que archive_command est correcte et que la destination a de l'espace.
- Gérer les slots : Supprimez les slots de réplication inutilisés.
- Corriger la réplication : Résolvez les problèmes à l'origine du retard du standby.
- Augmenter l'espace disque : Ajoutez temporairement ou définitivement plus de stockage.
Stimuler l'archiveur : Après avoir corrigé la commande d'archivage ou la destination, PostgreSQL devrait réessayer. Un rechargement peut suffire pour les modifications de configuration ; un redémarrage complet devrait être un dernier recours lors d'un incident de disque.

Un modèle mental plus sûr

Lorsque pg_wal grossit, posez trois questions dans l'ordre :

Est-ce que PostgreSQL génère plus de WAL que d'habitude parce que la charge de travail a changé ?
Est-ce que PostgreSQL est incapable d'archiver le WAL ?
Est-ce qu'on demande à PostgreSQL de conserver le WAL pour la réplication ou un slot ?

Ces réponses pointent vers des correctifs différents. Les écritures en masse peuvent nécessiter une planification, un traitement par lots ou un réglage des points de contrôle. Les échecs d'archivage nécessitent des correctifs de stockage et de commande. La rétention des slots nécessite la récupération du consommateur, le nettoyage du slot ou une limite de rétention. Deviner la valeur de max_wal_size résout rarement le vrai problème à lui seul.