Dépannage des erreurs courantes de basculement et de connexion dans les clusters HA PostgreSQL

Les clusters haute disponibilité (HA) PostgreSQL échouent de deux manières différentes. Parfois, le basculement de la base de données lui-même se brise : aucun réplica n'est promu, deux nœuds sont en désaccord sur le primaire, ou le nouveau primaire ne peut pas accepter les écritures. D'autres fois, la base de données fonctionne correctement, mais l'application ne peut toujours pas se connecter car un pooler, un enregistrement DNS, une adresse IP virtuelle, une règle de pare-feu ou une boucle de nouvelle tentative du client n'a pas suivi la promotion.

Lorsque vous êtes en incident, séparez rapidement ces couches. Demandez d'abord : y a-t-il exactement un primaire accessible en écriture ? Ensuite, demandez : le pooler peut-il l'atteindre ? Ensuite, demandez : l'application peut-elle atteindre le pooler ou le point de terminaison du service ? Cet ordre évite de perdre beaucoup de temps à scruter les journaux d'application alors que le gestionnaire de cluster est toujours bloqué dans l'élection du leader.

Comprendre les bases du HA PostgreSQL

Avant de plonger dans le dépannage, il est essentiel de récapituler brièvement les composants principaux d'un cluster HA PostgreSQL :

Architecture Primaire/Réplica : Une base de données primaire gère toutes les opérations d'écriture, tandis qu'un ou plusieurs réplicas reçoivent de manière asynchrone ou synchrone les modifications via la réplication en continu. Les réplicas sont en lecture seule mais servent de candidats à la promotion lors d'un basculement.
Gestionnaire de basculement : Des outils comme Patroni, pg_auto_failover ou Corosync/Pacemaker surveillent la santé du primaire, détectent les pannes, élisent un nouveau primaire parmi les réplicas disponibles et gèrent le processus de promotion. Ils gèrent également la reconfiguration des autres réplicas pour suivre le nouveau primaire.
Pooling de connexions : Les applications se connectent souvent à un pooler de connexions PostgreSQL (par exemple, PgBouncer, Odyssey) plutôt que directement à la base de données. Le pooler achemine ensuite les requêtes vers le primaire actuel, fournissant le multiplexage des connexions, l'équilibrage de charge et potentiellement l'abstraction de l'adresse réseau réelle du primaire vis-à-vis des applications. Cette abstraction est cruciale lors du basculement.

Problèmes courants de basculement et de connexion et leurs solutions

1. Problèmes de pooling de connexions lors du basculement

L'un des problèmes les plus fréquents après un basculement est l'échec des applications à se reconnecter au primaire nouvellement promu, bien que la base de données elle-même soit opérationnelle. Cela indique souvent des problèmes avec le pooler de connexions ou la mise en cache côté client.

Symptômes du problème :

Les applications signalent des erreurs de connexion à la base de données (FATAL: database "mydb" does not exist, connection refused, server closed the connection unexpectedly).
Les connexions existantes via le pooler semblent bloquées ou tentent de se connecter à l'IP de l'ancien primaire.
Les nouvelles connexions échouent également, même après la fin du basculement.

Causes sous-jacentes :

Connexions obsolètes dans le pooler : Le pooler de connexions peut maintenir des connexions ouvertes vers l'ancien primaire et essayer de les réutiliser, entraînant des erreurs lorsque l'ancien primaire est hors service ou est maintenant un réplica.
Configuration incorrecte du pooler : Le pooler peut ne pas être configuré pour détecter et basculer correctement vers le nouveau primaire, ou son server_reset_query peut être manquant/incorrect.
Mise en cache DNS : Si vos applications ou votre pooler utilisent une entrée DNS pour résoudre l'adresse du primaire, des entrées de cache DNS obsolètes (localement ou au niveau du résolveur DNS) peuvent les amener à continuer d'essayer de se connecter à l'ancienne IP.
Absence de logique de nouvelle tentative côté client : Les applications peuvent ne pas être conçues avec des mécanismes de nouvelle tentative robustes pour gérer les problèmes de connexion transitoires lors d'un basculement.

Étapes de débogage :

Vérifier l'état du pooler : Accédez à la console de votre pooler (par exemple, psql -p 6432 pgbouncer -U pgbouncer) et vérifiez les sorties SHOW SERVERS, SHOW CLIENTS, SHOW DATABASES pour voir s'il est conscient du nouveau primaire et s'il a des connexions actives vers la bonne adresse.
Vérifier la connectivité réseau : Depuis l'hôte du pooler, effectuez ping et telnet vers le port PostgreSQL du nouveau primaire (telnet new_primary_ip 5432).
Inspecter les journaux du pooler : Consultez les journaux du pooler pour les messages d'erreur liés à la connexion à la base de données ou à la tentative de résolution de noms d'hôte.
Vérifier la résolution DNS : Utilisez dig ou nslookup sur l'hôte du pooler pour vous assurer que l'enregistrement DNS de votre point de terminaison de service primaire (par exemple, primary.mydomain.com) résout l'adresse IP du nouveau primaire.

Solutions :

Configurer server_reset_query : Assurez-vous que votre pooler a un server_reset_query (par exemple, DISCARD ALL;) pour nettoyer l'état de la session lorsqu'une connexion est renvoyée au pool et avant qu'elle ne soit réutilisée par un autre client. Ceci est crucial pour les environnements utilisant des objets temporaires ou des paramètres spécifiques à la session.
max_db_connections et max_user_connections : Définissez des limites appropriées pour empêcher le pooler d'accaparer toutes les connexions vers le nouveau primaire, ce qui pourrait priver d'autres services.
Recharger/Redémarrer le pooler : Dans certains cas, un rechargement ou un redémarrage gracieux du pooler de connexions peut être nécessaire pour le forcer à prendre en compte les nouvelles configurations ou à résoudre à nouveau le DNS. Cela devrait être un dernier recours et de préférence automatisé par votre gestionnaire de basculement.
TTL DNS plus court : Si vous utilisez la découverte de services basée sur DNS, configurez un Time-To-Live (TTL) très court pour l'enregistrement DNS du primaire (par exemple, 30 à 60 secondes) afin de minimiser l'impact de la mise en cache DNS.
Nouvelles tentatives côté client : Implémentez une logique de backoff exponentiel et de nouvelles tentatives dans le code de votre application. Cela rend les applications plus résilientes aux problèmes de connexion transitoires lors du basculement.
Adresse IP virtuelle (VIP) : Envisagez d'utiliser une adresse IP virtuelle gérée par votre solution HA. La VIP se déplace avec le primaire, de sorte que les applications se connectent à une IP statique et le serveur de base de données sous-jacent change de manière transparente.

# Exemple d'extrait de configuration PgBouncer
[databases]
mydb = host=primary_cluster_service_ip port=5432 dbname=mydb
# Ou en utilisant un nom d'hôte mis à jour par votre gestionnaire de basculement
# mydb = host=primary.mydomain.com port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = session
server_reset_query = DISCARD ALL;
server_fast_close = 1 # Fermer rapidement les connexions si le serveur ne répond pas
server_check_delay = 10 # Vérifier la santé du serveur toutes les 10 secondes

2. Retard excessif du réplica entravant le basculement

Le retard du réplica se produit lorsqu'une base de données de secours prend du retard sur le primaire, ce qui signifie qu'elle n'a pas rejoué tous les enregistrements WAL (Write-Ahead Log) envoyés par le primaire. Lors d'un basculement, la promotion d'un réplica très en retard peut entraîner une perte de données ou retarder considérablement le processus de basculement si le gestionnaire HA attend qu'il rattrape son retard.

Symptômes du problème :

Les alertes de surveillance indiquent un retard élevé du réplica (par exemple, en octets ou en temps).
Les gestionnaires de basculement refusent de promouvoir un réplica en raison du dépassement d'un seuil de retard configuré.
Après le basculement, les applications observent des données manquantes qui étaient présentes sur l'ancien primaire.

Causes sous-jacentes :

Charge d'écriture primaire élevée : Un volume élevé et soutenu d'opérations d'écriture sur le primaire peut submerger la capacité du réplica à suivre, surtout si le matériel du réplica (E/S, CPU) est inférieur.
Latence/Bande passante réseau : Des liens réseau lents ou congestionnés entre le primaire et le réplica peuvent retarder l'expédition du WAL.
E/S lentes du réplica : Le sous-système de disque du réplica peut ne pas être assez rapide pour écrire et rejouer efficacement les enregistrements WAL.
Paramètre wal_level : Si wal_level n'est pas défini sur replica ou plus, les informations nécessaires à la réplication en continu ne seront pas générées.
max_wal_senders : Un max_wal_senders insuffisant sur le primaire peut limiter le nombre d'emplacements de réplication actifs ou de connexions de réplication simultanées, ce qui a un impact sur le débit.
Problèmes de archive_command / restore_command : Si vous utilisez l'archivage et la récupération WAL, des problèmes avec ces commandes (par exemple, un stockage d'archives lent) peuvent entraîner des retards.

Étapes de débogage :

Surveiller pg_stat_replication : Cette vue fournit des informations en temps réel sur l'état de la réplication, y compris write_lag, flush_lag et replay_lag.
Comparer les LSN : Comparez manuellement le LSN WAL actuel sur le primaire avec le dernier LSN rejoué sur le réplica.
Vérifier les ressources système : Utilisez iostat, vmstat, top sur le primaire et le réplica pour identifier les goulots d'étranglement d'E/S, la saturation du CPU ou la pression mémoire.
Diagnostics réseau : Testez les performances réseau entre le primaire et le réplica à l'aide de iperf.

Solutions :

Augmenter max_wal_senders : Sur le primaire, augmentez max_wal_senders (par exemple, max_wal_senders = 10) pour permettre plus de connexions de réplication simultanées. Un redémarrage est nécessaire.
Améliorer le matériel du réplica : Si les E/S ou le CPU sont un goulot d'étranglement, envisagez de mettre à niveau le matériel du réplica ou d'optimiser sa configuration de stockage (par exemple, des SSD plus rapides, un disque WAL séparé).
Régler wal_compression : Sur le primaire, définir wal_compression = on peut réduire le volume du WAL, améliorant potentiellement la vitesse de réplication sur des liens réseau contraints, mais au prix d'une utilisation du CPU du primaire.
Ajuster wal_keep_size ou wal_keep_segments : Assurez-vous que suffisamment de fichiers WAL sont conservés sur le primaire pour empêcher les réplicas de se désynchroniser et de nécessiter une sauvegarde de base complète.
synchronous_commit : Bien que synchronous_commit = on fournisse des garanties de durabilité des données plus fortes, il introduit une latence pour les écritures sur le primaire. Utilisez remote_write ou remote_apply pour des tables ou transactions spécifiques si une réplication synchrone stricte est nécessaire, mais évaluez soigneusement l'impact sur les performances.
Surveillance et alertes : Implémentez une surveillance robuste pour pg_stat_replication et configurez des alertes lorsque le retard dépasse les seuils acceptables.

-- Sur le primaire : Vérifier le LSN WAL actuel
SELECT pg_current_wal_lsn();

-- Sur le primaire : Vérifier les serveurs de secours connectés et le retard
SELECT
    usename, application_name, client_addr, state, sync_state,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes,
    write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

Sur un serveur de secours, utilisez :

SELECT
    pg_is_in_recovery() AS is_standby,
    pg_last_wal_receive_lsn(),
    pg_last_wal_replay_lsn(),
    now() - pg_last_xact_replay_timestamp() AS time_since_last_replay;

La requête d'horodatage peut être trompeuse sur un système inactif car aucune transaction récente n'a pu être rejouée. Utilisez-la avec des comparaisons LSN et le contexte de la charge de travail.

3. Transition primaire échouée ou bloquée

Un basculement automatisé doit promouvoir un réplica rapidement et de manière fiable. Lorsque ce processus s'arrête ou échoue complètement, cela peut entraîner une panne prolongée et nécessiter une intervention manuelle.

Symptômes du problème :

Aucun nouveau primaire n'est élu ou promu après la panne de l'ancien primaire.
Le cluster entre dans un état de split-brain où deux nœuds croient être le primaire.
Les journaux du gestionnaire de basculement montrent des erreurs liées au quorum, à l'élection du leader ou à la promotion de la base de données.
Les applications restent indisponibles car aucun primaire n'est disponible.

Causes sous-jacentes :

Split-Brain : Se produit lorsque des partitions réseau isolent les nœuds, conduisant à plusieurs primaires ou à une élection ambiguë du primaire. C'est le scénario le plus dangereux, risquant une divergence des données.
Problèmes de quorum : Le gestionnaire de basculement peut ne pas être en mesure d'atteindre un quorum (vote majoritaire) parmi ses nœuds, l'empêchant de prendre une décision concernant la promotion. Ceci est courant dans les clusters avec un nombre pair de nœuds ou trop peu de nœuds.
Isolement réseau : Les nœuds du gestionnaire de basculement ne peuvent pas communiquer entre eux ou avec les instances PostgreSQL, empêchant les contrôles de santé ou l'exécution de commandes.
Privilèges insuffisants : L'utilisateur du gestionnaire de basculement peut manquer des autorisations PostgreSQL nécessaires (par exemple, pg_promote()) ou des autorisations au niveau système (par exemple, pour gérer les VIP).
Erreurs de configuration : restore_command, primary_conninfo ou d'autres paramètres incorrects dans la configuration du gestionnaire de basculement.

Étapes de débogage :

Vérifier les journaux du gestionnaire de basculement : C'est la principale source d'informations. Pour Patroni, regardez ses journaux spécifiques (souvent journalctl -u patroni ou le fichier journal configuré dans patroni.yml). Pour pg_auto_failover, vérifiez journalctl -u pgautofailover_monitor et les journaux de l'agent.
Vérifier l'état du quorum : Pour Patroni, utilisez patronictl list pour voir l'état de tous les membres du cluster et confirmer le leader élu. Pour pg_auto_failover, vérifiez pg_autoctl show state.
Connectivité réseau : Effectuez des vérifications ping, traceroute et telnet entre tous les nœuds HA et le magasin de consensus distribué (par exemple, Etcd, Consul, ZooKeeper pour Patroni).
Journaux système : Vérifiez journalctl -xe ou /var/log/syslog sur tous les nœuds pour toute erreur au niveau système qui pourrait interférer avec le gestionnaire de basculement (par exemple, disque plein, problèmes de mémoire).
Journaux PostgreSQL : Examinez les journaux PostgreSQL sur le réplica candidat à la promotion pour voir s'il signale des problèmes lors de la tentative de promotion.

Solutions :

Implémenter le Fencing/STONITH : Le Fencing est crucial pour empêcher le split-brain en garantissant qu'un primaire défaillant ne peut pas continuer à accepter les écritures avant qu'un nouveau ne soit promu. Ceci est généralement géré par le gestionnaire de basculement ou la couche d'infrastructure.
Nombre impair de nœuds pour le quorum : Déployez toujours un nombre impair de nœuds de vote (par exemple, 3, 5) pour le magasin de consensus distribué de votre gestionnaire de basculement (Etcd, Consul, ZooKeeper) afin de garantir que le quorum puisse toujours être atteint même si un ou deux nœuds tombent en panne.
Configuration réseau robuste : Assurez-vous de disposer de chemins réseau redondants, de règles de pare-feu appropriées autorisant la communication sur les ports nécessaires (PostgreSQL, magasin de consensus, API Patroni) et d'une résolution de nom d'hôte cohérente.
Vérification des autorisations : Vérifiez que le compte utilisateur exécutant le gestionnaire de basculement dispose de tous les privilèges PostgreSQL nécessaires et des autorisations système pour effectuer les tâches de promotion et de reconfiguration.
Revoir la configuration du gestionnaire de basculement : Revérifiez les paramètres patroni.yml ou pg_auto_failover pour les fautes de frappe, les chemins incorrects ou les restore_command mal configurés.
Intervention manuelle (avec prudence) : En cas de basculement bloqué grave, une promotion manuelle ou une réintégration des nœuds peut être nécessaire. Procédez avec une extrême prudence, en vous assurant que l'ancien primaire est complètement arrêté avant d'en promouvoir un nouveau pour éviter une divergence des données.

# Exemple : Vérification de l'état du cluster Patroni
patronictl -c /etc/patroni/patroni.yml list

# Sortie attendue (exemple) :
# + Cluster: my_ha_cluster (6979219803154942080) ------+----+-----------+----+-----------+
# | Member  | Host         | Role    | State    | TL | Lag |
# +---------+--------------+---------+----------+----+-----+
# | node1   | 192.168.1.10 | Leader  | running  | 2  |     |
# | node2   | 192.168.1.11 | Replica | running  | 2  | 0   |
# | node3   | 192.168.1.12 | Replica | running  | 2  | 0   |
# +---------+--------------+---------+----------+----+-----+

4. Problèmes de connectivité réseau et de résolution DNS

À la racine de nombreux problèmes HA se trouvent des problèmes réseau fondamentaux, empêchant les nœuds de communiquer ou les applications de trouver le point de terminaison de base de données correct.

Symptômes du problème :

Erreurs connection refused ou no route to host provenant des applications ou entre les nœuds du cluster.
Le gestionnaire de basculement signale que des nœuds sont inaccessibles.
Les services reposant sur le DNS ne peuvent pas résoudre correctement le nom d'hôte du primaire.

Causes sous-jacentes :

Règles de pare-feu : Règles de pare-feu mal configurées (par exemple, iptables, groupes de sécurité) bloquant le port PostgreSQL (5432), les ports du gestionnaire de basculement ou les ports du magasin de consensus.
Partition réseau : Division réseau physique ou logique empêchant la communication entre un sous-ensemble de nœuds.
Routage incorrect : Routes réseau mal configurées sur un ou plusieurs nœuds.
Mise en cache DNS/Mauvaise configuration : Comme discuté dans la section 1, des enregistrements DNS obsolètes ou une configuration de serveur DNS incorrecte peuvent mal diriger le trafic.
Échec de la migration de l'adresse IP virtuelle (VIP) : Si vous utilisez une VIP, elle peut ne pas parvenir à migrer vers le nouveau primaire, laissant le service inaccessible.

Étapes de débogage :

Connectivité de base : Utilisez ping <target_ip> entre tous les nœuds.
Connectivité de port : Utilisez telnet <target_ip> <port> (par exemple, telnet 192.168.1.10 5432) pour vérifier que le port PostgreSQL est ouvert et à l'écoute.
Vérification du pare-feu : Sur chaque nœud, vérifiez les règles de pare-feu actives (sudo iptables -L, sudo ufw status, ou les configurations de groupe de sécurité du fournisseur de cloud).
État de l'interface réseau : Utilisez ip addr show ou ifconfig pour vous assurer que les interfaces réseau sont actives et correctement configurées.
Résolution DNS : Utilisez dig <hostname> ou nslookup <hostname> pour vérifier la résolution du nom d'hôte à partir des nœuds concernés (serveurs d'applications, pooler, nœuds HA).

Solutions :

Revoir les règles de pare-feu : Assurez-vous que les ports nécessaires sont ouverts pour PostgreSQL (5432), le plan de contrôle du gestionnaire de basculement (par exemple, 8008 pour l'API Patroni, 8000/8001 pour pg_auto_failover) et le magasin de consensus distribué (par exemple, Etcd : 2379/2380, Consul : 8300/8301/8302).
Réseau cohérent : Assurez-vous que tous les nœuds sont sur le même sous-réseau ou disposent d'un routage correct configuré s'ils s'étendent sur plusieurs sous-réseaux.
Mises à jour DNS : Automatisez les mises à jour DNS dans le cadre du processus de basculement, ou utilisez un TTL plus court. Les VIP sont souvent préférées pour cette raison.
Gestion des VIP : Si vous utilisez une VIP, assurez-vous que l'outil de gestion des VIP (par exemple, Keepalived, la gestion IP du fournisseur de cloud) est correctement configuré et fonctionne. Testez explicitement la migration de la VIP.
Accès basé sur l'hôte : Pour plus de simplicité dans les petits clusters, assurez-vous que pg_hba.conf autorise les connexions depuis toutes les adresses IP potentielles du primaire/réplica et l'IP du pooler de connexions.

Outils essentiels pour le dépannage

psql : Pour exécuter des requêtes SQL comme pg_stat_replication, pg_current_wal_lsn(), les commandes SHOW *.
CLI du gestionnaire de basculement : patronictl, pg_autoctl pour interroger l'état du cluster, les journaux et lancer des actions.
Surveillance système : Des outils comme Prometheus + Grafana, Zabbix, Nagios, ou la surveillance du fournisseur de cloud pour des informations en temps réel sur l'utilisation des ressources, le retard de réplication et l'état du service.
journalctl / tail -f : Pour visualiser les journaux système et d'application.
Utilitaires réseau : ping, traceroute, telnet, iperf, netstat, dig, nslookup pour diagnostiquer la connectivité.
dmesg : Pour les erreurs au niveau du noyau, en particulier liées aux E/S disque ou au tueur OOM (Out Of Memory).

Une checklist rapide pour les incidents

Lorsqu'une alerte de basculement HA se déclenche, utilisez une courte checklist avant de modifier quoi que ce soit :

Confirmer la vue du cluster :

patronictl -c /etc/patroni/patroni.yml list

Confirmer le propre rôle de PostgreSQL sur chaque nœud accessible :

SELECT pg_is_in_recovery();

false signifie que le nœud est un primaire accessible en écriture. true signifie qu'il s'agit d'un serveur de secours. Si plus d'un nœud signale false, arrêtez-vous et traitez l'incident comme un possible split-brain.

Confirmer le point de terminaison de l'application :

dig primary-db.internal.example.com
nc -vz primary-db.internal.example.com 5432

Confirmer la cible du pooler, si vous utilisez PgBouncer :

SHOW SERVERS;
SHOW POOLS;

Vérifier si les erreurs sont anciennes ou actuelles. Les journaux d'application contiennent souvent des messages de nouvelle tentative des premières secondes du basculement. Comparez les horodatages avant de supposer que la panne est toujours en cours.

Cette checklist est volontairement simple. Lors d'un véritable basculement, la meilleure commande est celle que tout le monde dans l'équipe comprend et peut exécuter sans deviner.

Bonnes pratiques pour prévenir les problèmes de basculement

Tests de basculement réguliers : Simulez régulièrement des pannes primaires et observez le processus de basculement. Cela renforce la confiance et expose les erreurs de configuration.
Surveillance et alertes robustes : Surveillez les métriques clés comme le retard du réplica, l'état du primaire, la santé du pooler de connexions et les ressources système. Configurez des alertes pour tout écart.
Configuration correcte du pooler de connexions : Assurez-vous que server_reset_query est configuré, que pool_mode est approprié pour votre application et que les contrôles de santé sont activés.
Régler les paramètres de réplication : Configurez wal_level, max_wal_senders, wal_keep_size et synchronous_commit avec soin en fonction de vos exigences de performance et de durabilité.
Documenter votre configuration HA : Documentez clairement votre architecture HA, la configuration du gestionnaire de basculement, les paramètres réseau et les procédures de récupération.
Utiliser un gestionnaire de basculement dédié : Fiez-vous à des solutions éprouvées comme Patroni ou pg_auto_failover plutôt qu'à des scripts personnalisés pour la logique HA critique.
Magasin de consensus dédié : Si vous utilisez un gestionnaire comme Patroni, déployez un cluster séparé et hautement disponible pour son magasin de consensus distribué (Etcd, Consul) afin d'éviter un point de défaillance unique.

Les tests HA les plus utiles ne sont pas des démonstrations propres où le primaire est arrêté poliment. Testez aussi les cas difficiles : l'ancien primaire perd le réseau mais continue de fonctionner, le DNS se met à jour lentement, PgBouncer conserve des connexions serveur obsolètes, un réplica a 30 secondes de retard, ou le magasin de consensus perd un membre. Ces tests montrent si vos runbooks correspondent au système que vous exploitez réellement.