## Introduction PostgreSQL est un système de gestion de base de données relationnelle open source puissant, mais sa fiabilité dépend de la qualité de sa surveillance et de votre capacité à réagir aux alertes. De nombreuses équipes considèrent la surveillance comme une réflexion après coup jusqu'à ce qu'une panne les oblige à se démener. Cet article fournit des conseils pratiques et exploitables pour mettre en place la surveillance et les alertes de PostgreSQL, destinés aux développeurs, aux ingénieurs DevOps et aux équipes techniques de startups qui gèrent leurs propres bases de données. Vous apprendrez quelles métriques collecter, comment les interpréter, comment configurer des alertes pertinentes et comment créer un tableau de bord qui vous aide à diagnostiquer rapidement les problèmes. Chaque section comprend des commandes concrètes, des sorties attendues et des étapes de récupération pour que vous puissiez appliquer ces pratiques immédiatement. Nous nous concentrons sur la sécurité opérationnelle : observer avant d'apporter des modifications, limiter le rayon d'impact de toute intervention, utiliser des espaces réservés plutôt que des secrets dans la configuration, vérifier le résultat de chaque action et documenter les chemins de récupération à l'avance. Tout au long de cet article, nous supposons un environnement PostgreSQL sans services de surveillance cloud gérés, donc les exemples utilisent des outils natifs comme psql, des requêtes SQL et des commandes shell. L'objectif est de vous donner une base solide qui fonctionne sur n'importe quel déploiement PostgreSQL. ## Inventaire de la version et de l'environnement Avant de pouvoir surveiller efficacement PostgreSQL, vous devez savoir exactement à quoi vous avez affaire. Commencez par identifier la version installée, la topologie de déploiement et les prérequis. Cet inventaire guidera toutes les étapes suivantes, car les commandes et les options de configuration varient selon les versions de PostgreSQL. Par exemple, l'extension pg_stat_statements nécessite des étapes de configuration différentes sur PostgreSQL 13 et 14, et les poolers de connexions comme PgBouncer ajoutent une couche supplémentaire à prendre en compte. Exécutez la commande en lecture seule suivante pour obtenir la version et quelques paramètres de base : SELECT version(); La sortie attendue ressemble à : PostgreSQL 15.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 12.2.1 20221121 (Red Hat 12.2.1-4), 64-bit Cela vous indique la version majeure (15), la version mineure (3), le système d'exploitation et le compilateur. C'est essentiel car les requêtes de surveillance reposent parfois sur des colonnes ou des vues ajoutées dans des versions ultérieures. Par exemple, pg_stat_activity inclut backend_type depuis PostgreSQL 10, mais les versions plus anciennes ne l'ont pas. Ensuite, vérifiez la configuration actuelle pertinente pour la surveillance : SHOW shared_preload_libraries; SHOW track_activities; SHOW track_counts; SHOW track_functions; SHOW log_min_duration_statement; Exemple de sortie : shared_preload_libraries ----------------------------------------------- pg_stat_statements, auto_explain (1 row) track_activities ------------------ on (1 row) track_counts -------------- on (1 row) track_functions ----------------- none (1 row) log_min_duration_statement ---------------------------- -1 (1 row) Ici, shared_preload_libraries inclut pg_stat_statements , ce qui est essentiel pour la surveillance des performances des requêtes. track_activities et track_counts sont activés, donc les statistiques de session et cumulatives sont collectées. track_functions est défini sur none , ce qui signifie que les statistiques d'appels de fonctions ne sont pas suivies ; vous voudrez peut-être le changer en pl ou all si vous utilisez des procédures stockées. log_min_duration_statement est -1 , ce qui désactive la journalisation des requêtes longues ; le définir sur une valeur positive comme 1000 (millisecondes) journaliserait toute requête prenant plus d'une seconde, utile pour identifier les requêtes lentes. Pour un déploiement sous Docker, vous pouvez également vérifier : docker exec psql -U -c "SELECT version();" Remplacez et par les valeurs réelles. Cette commande vous permet d'exécuter psql à l'intérieur d'un conteneur sans y entrer de manière interactive. Prérequis pour cette section : vous avez besoin d'accéder à l'instance PostgreSQL via psql ou un autre client, avec des privilèges en lecture seule sur les catalogues système. Les commandes d'observation ci-dessus sont en lecture seule et sûres à exécuter sur un système de production. Elles ne modifient aucun état. Si vous rencontrez une erreur de permission comme : ERROR: permission denied for function version Cela signifie que votre utilisateur ne dispose pas des privilèges nécessaires. Dans ce cas, accordez USAGE sur les fonctions concernées ou utilisez un superutilisateur ou un rôle de surveillance dédié. Pour récupérer, ajustez les permissions du rôle et réexécutez la commande. ## Chemin de configuration sécurisé Configurer la surveillance dans PostgreSQL nécessite souvent de modifier des paramètres qui exigent un redémarrage ou un rechargement du serveur. La voie sécurisée consiste à observer d'abord la configuration actuelle, à comprendre l'impact de chaque changement, à apporter la modification la plus petite possible et à vérifier le résultat. N'appliquez jamais plusieurs modifications à la fois, car si quelque chose tourne mal, il est difficile d'isoler la cause. Un changement de surveillance courant est l'activation de l'extension pg_stat_statements . Cette extension enregistre des statistiques sur toutes les instructions SQL exécutées, y compris le texte de la requête, le nombre d'exécutions, le temps total et l'utilisation des ressources. Elle est inestimable pour identifier les requêtes lentes ou fréquemment exécutées. Tout d'abord, vérifiez si l'extension est disponible et installée : SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements'; SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements'; Si la première requête renvoie une ligne avec installed_version à NULL, l'extension n'est pas installée. Si la deuxième requête ne renvoie aucune ligne, cela confirme l'absence. Pour activer pg_stat_statements , vous devez l'ajouter à shared_preload_libraries dans postgresql.conf . Cela nécessite un redémarrage. Les étapes sécurisées sont : shared_preload_libraries = 'pg_stat_statements' - Sauvegardez votre fichier postgresql.conf actuel (ou au moins notez la valeur actuelle de shared_preload_libraries ). - Éditez postgresql.conf et ajoutez pg_stat_statements à la liste, par exemple : - Redémarrez PostgreSQL. Sur un Linux basé sur systemd, c'est souvent : sudo systemctl restart postgresql - Après le redémarrage, connectez-vous et exécutez : CREATE EXTENSION pg_stat_statements; - Vérifiez en exécutant : SELECT * FROM pg_stat_statements LIMIT 1; Si la requête renvoie une ligne (ou même aucune ligne mais pas d'erreur), l'extension est active. Sortie attendue après la commande CREATE EXTENSION : CREATE EXTENSION Et une requête de vérification : SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_stat_statements'; Sortie : extname | extversion -------------------+------------ pg_stat_statements| 1.10 (1 row) Si vous utilisez un service géré ou ne pouvez pas redémarrer, des méthodes de surveillance alternatives comme pg_stat_activity et pg_stat_database sont disponibles, mais elles fournissent moins de détails au niveau des requêtes. Un autre exemple de configuration sécurisée est l'ajustement de log_min_duration_statement . Cela définit le seuil de journalisation des requêtes lentes. Par exemple : ALTER SYSTEM SET log_min_duration_statement = '1000'; SELECT pg_reload_conf(); Ce changement ne nécessite pas de redémarrage ; pg_reload_conf() envoie un signal pour recharger les fichiers de configuration. Vérifiez avec : SHOW log_min_duration_statement; Attendu : log_min_duration_statement ---------------------------- 1s (1 row) Remarque : PostgreSQL accepte les unités de temps, donc '1000' est interprété comme 1000 millisecondes, affiché comme 1s. Testez toujours les modifications de configuration dans un environnement de préproduction avant de les appliquer en production. Documentez chaque changement et sa raison, et gardez un plan de retour en arrière (par exemple, restaurer le postgresql.conf d'origine et redémarrer). ## Vérification et diagnostics Une fois la surveillance mise en place, vous devez vérifier que les données sont collectées et utiliser ces données pour les diagnostics. Cette section fournit des requêtes en lecture seule pour inspecter la santé et les performances de votre instance PostgreSQL. Commencez par une vérification simple de connectivité et d'activité : SELECT count(*) FROM pg_stat_activity; Cela renvoie le nombre de connexions actuelles, y compris les inactives. Un système sain a généralement un nombre bien inférieur à max_connections . S'il est proche de la limite, vous pouvez avoir des fuites de connexions ou avoir besoin d'un pooler de connexions. Pour voir les requêtes actives qui s'exécutent depuis plus de 5 secondes : SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state FROM pg_stat_activity WHERE state = 'active' AND now() - pg_stat_activity.query_start > interval '5 seconds' ORDER BY duration DESC; Exemple de sortie : pid | duration | query | state ------+-----------------+-----------------------------------------------------------------+-------- 1234 | 00:12:34.56789 | SELECT * FROM large_table WHERE unindexed_column = 'value'; | active (1 row) Cela montre une requête qui s'exécute depuis plus de 12 minutes, probablement bloquée à cause d'un index manquant ou d'un verrou. Pour approfondir, vous pouvez vérifier les verrous bloquants : SELECT blocked_locks.pid AS blocked_pid, blocking_locks.pid AS blocking_pid, blocked_activity.query AS blocked_query, blocking_activity.query AS blocking_query FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.granted; Cette requête identifie les sessions en attente de verrous et les sessions qui détiennent ces verrous. La sortie inclut les PID et les textes des requêtes, ce qui vous aide à décider d'annuler ou de terminer une requête bloquante. Pour annuler une requête proprement : SELECT pg_cancel_backend(); Ou si cela échoue, terminez le backend : SELECT pg_terminate_backend(); Utilisez la terminaison avec prudence, car elle peut faire échouer la requête bloquée. Un autre diagnostic consiste à vérifier le retard de réplication, essentiel dans les configurations de réplication en continu : SELECT application_name, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes FROM pg_stat_replication; Sortie sur un réplica en bonne santé avec un retard minimal : application_name | lag_bytes ------------------+----------- standby1 | 0 (1 row) Si lag_bytes est constamment élevé (par exemple, plusieurs mégaoctets ou plus), le réplica peut prendre du retard, risquant une perte de données en cas de bascule. Examinez la latence réseau, la contention des ressources sur le réplica ou les transactions longues sur le primaire. Pour les performances des requêtes, pg_stat_statements fournit des statistiques agrégées : SELECT query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5; Exemple de sortie : -[ RECORD 1 ]----+--------------------------------------------------------------- query | SELECT * FROM orders WHERE customer_id = $1; calls | 15000 total_exec_time | 120000.5 mean_exec_time | 8.0 rows | 15000 -[ RECORD 2 ]----+--------------------------------------------------------------- query | UPDATE inventory SET quantity = quantity - $1 WHERE product_id = $2; calls | 8000 total_exec_time | 60000.2 mean_exec_time | 7.5 rows | 8000 Ici, la requête principale est exécutée fréquemment et consomme un temps significatif. Le temps d'exécution moyen de 8 ms peut être acceptable, mais comme elle s'exécute 15 000 fois, son optimisation (par exemple, ajouter un index sur customer_id ) pourrait apporter des avantages substantiels. Vérifiez que les statistiques sont collectées correctement en vérifiant l'horodatage de la vue : SELECT stats_reset FROM pg_stat_database WHERE datname = current_database(); Si stats_reset est récent de manière inattendue, les statistiques ont peut-être été réinitialisées manuellement ou à cause d'un plantage. ## Modes de défaillance et récupération La surveillance elle-même peut échouer, tout comme l'instance PostgreSQL. Cette section couvre les modes de défaillance courants, comment les détecter via la surveillance et les étapes de récupération. Défaillance : les requêtes de surveillance cessent de renvoyer des données. Si votre tableau de bord n'affiche soudainement plus de données, vérifiez d'abord si la connexion de surveillance fonctionne. Exécutez une requête simple depuis l'hôte de l'outil de surveillance : psql -h -U -d postgres -c "SELECT 1;" Si cela échoue avec une erreur de connexion, le problème peut être le réseau, l'authentification ou PostgreSQL est arrêté. Vérifiez l'état du service PostgreSQL : sudo systemctl status postgresql Si le service est actif mais que les connexions sont refusées, vérifiez pg_hba.conf pour les règles d'authentification de l'utilisateur de surveillance et assurez-vous que l'utilisateur a le privilège CONNECT sur la base de données. Par exemple, une règle pourrait être : host postgres monitoring_user 192.168.1.0/24 md5 Si la règle est manquante, ajoutez-la et rechargez : sudo systemctl reload postgresql Défaillance : espace disque plein. Un disque plein peut empêcher PostgreSQL d'accepter des écritures ou provoquer un plantage. Surveillez l'utilisation du disque avec : df -h /var/lib/postgresql/data Si l'utilisation dépasse 80 %, définissez une alerte. Pour récupérer, vous pouvez archiver ou supprimer d'anciens fichiers WAL (après vous être assuré qu'ils ne sont plus nécessaires pour la réplication ou la récupération à un point dans le temps), nettoyer les anciens journaux ou ajouter du stockage. Veillez à ne pas supprimer de fichiers critiques ; utilisez pg_archivecleanup si vous utilisez l'archivage continu. Défaillance : retard de réplication trop élevé. Comme mentionné, un retard élevé peut être dangereux. Surveillez pg_stat_replication régulièrement et définissez une alerte si le retard dépasse, disons, 100 Mo. Pour récupérer, identifiez d'abord la cause. Vérifiez les transactions longues sur le primaire : SELECT pid, now() - xact_start AS duration, query FROM pg_stat_activity WHERE state = 'idle in transaction' AND now() - xact_start > interval '1 minute'; Ces transactions inactives peuvent détenir des verrous et empêcher la relecture des WAL. Terminez-les si c'est sûr. Assurez-vous également que le réplica dispose de suffisamment de ressources (CPU, E/S). Si le retard persiste, vous devrez peut-être reconstruire le réplica avec pg_basebackup . Défaillance : trop de connexions. Si le nombre de connexions dans pg_stat_activity approche max_connections , les nouvelles connexions échoueront avec « FATAL : désolé, trop de clients déjà ». Pour récupérer temporairement, vous pouvez augmenter max_connections (nécessite un redémarrage) mais cela consomme plus de mémoire. Une meilleure solution à long terme : implémenter un pool de connexions avec PgBouncer ou ajuster les paramètres de connexion de l'application. Surveillez le nombre de connexions et définissez une alerte à 80 % du maximum. Pour chaque mode de défaillance, documentez un manuel avec les étapes de détection, de diagnostic et de récupération. Testez le manuel dans un environnement de préproduction. ## Liste de contrôle des opérations Cette liste récapitule les tâches clés pour la surveillance et les alertes continues de PostgreSQL. Attribuez un responsable unique pour chaque tâche et définissez une fréquence de révision. Exemple : Priya Shah, responsable de l'ingénierie, révise chaque semaine.
TâcheResponsableFréquenceVérification
Vérifier la cohérence de la version et de la configurationPriya ShahMensuelleExécuter SHOW shared_preload_libraries; et comparer avec la référence
Examiner les journaux de requêtes lentesAdministrateur de base de données (Alex)QuotidienneInterroger pg_stat_statements pour les 10 requêtes principales par temps total
Surveiller le retard de réplicationIngénieur DevOps (Sam)Horaire (automatisé)Alerter si retard > 100 Mo pendant plus de 5 minutes
Vérifier l'utilisation de l'espace disqueResponsable de l'équipe infrastructure (Jules)Quotidiennedf -h sur le répertoire de données ; alerter si >80 %
Vérifier les règles d'alerte et les canaux de notificationPriya ShahTrimestrielleEnvoyer une alerte de test à la rotation d'astreinte
Mettre à jour les manuels pour les modes de défaillanceAlexAprès tout incident ou changementVérifier l'exactitude du manuel
Utilisez cette liste comme point de départ et adaptez-la à votre environnement. Automatisez autant que possible à l'aide d'un système de surveillance comme Prometheus avec postgres_exporter, ou Zabbix avec son modèle PostgreSQL. La clé est la cohérence : la surveillance n'est pas une configuration unique. ## Pièges courants et comment les éviter - Ignorer les différences de version : Utiliser des requêtes ou des paramètres d'une ancienne version sur une version plus récente peut produire des erreurs ou des données trompeuses. Vérifiez toujours votre version de PostgreSQL et consultez la documentation officielle de cette version. - Collecter trop de métriques sans but : Il est tentant d'activer toutes les métriques disponibles, mais cela crée du bruit et une surcharge de stockage. Commencez par un ensemble ciblé : connexions, performances des requêtes, retard de réplication, utilisation du disque et taux d'erreurs. Ajoutez-en plus seulement lorsque nécessaire. - Définir des seuils d'alerte trop stricts ou trop lâches : Si les alertes se déclenchent pour chaque petit écart, les gens commencent à les ignorer (fatigue des alertes). Si les seuils sont trop élevés, vous manquez de vrais problèmes. Définissez les seuils en fonction des performances de référence au fil du temps et ajustez-les à mesure que votre charge de travail évolue. - Ne pas tester les alertes : Une alerte qui ne notifie personne est inutile. Testez régulièrement votre pipeline d'alertes en déclenchant manuellement une alerte de test. - Utiliser un superutilisateur pour les connexions de surveillance : Cela accorde des privilèges inutiles et augmente le risque de sécurité. Créez un rôle de surveillance dédié avec un accès en lecture seule aux vues de statistiques et des permissions minimales. - Négliger la surveillance des journaux : Les journaux PostgreSQL contiennent des informations précieuses sur les erreurs, les requêtes longues et les problèmes de vacuum automatique. Configurez log_min_duration_statement , log_checkpoints et log_connections de manière appropriée, et envoyez les journaux à un système centralisé pour analyse. - Ne pas documenter les étapes de récupération : En cas d'urgence, vous ne voulez pas avoir à improviser. Rédigez des manuels pour les problèmes courants comme le disque plein, une CPU élevée et le retard de réplication, et gardez-les accessibles. ## Conclusion La surveillance et les alertes PostgreSQL sont essentielles pour maintenir un service de base de données fiable. En suivant les pratiques de cet article, vous pouvez détecter les problèmes tôt, les diagnostiquer efficacement et récupérer avec un temps d'arrêt minimal. N'oubliez pas de commencer par un inventaire clair de votre environnement, d'apporter des modifications de configuration en toute sécurité, de vérifier que la surveillance fonctionne et de vous préparer aux modes de défaillance avec des procédures de récupération documentées. Révisez régulièrement votre configuration de surveillance et ajustez-la à mesure que votre application évolue. Avec ces exemples pratiques, vous pouvez construire une base d'observabilité solide pour votre déploiement PostgreSQL. Comme prochaine étape, choisissez une vérification à faible risque de cet article, par exemple confirmer que l'extension pg_stat_statements est activée ou vérifier le retard de réplication. Exécutez la commande, notez l'état actuel et comparez le résultat avec la sortie attendue. Ensuite, configurez une alerte simple pour une métrique clé et testez qu'elle atteint votre équipe. Au fil du temps, étendez votre surveillance pour couvrir plus de métriques et intégrez-la à votre processus de réponse aux incidents. Un système de surveillance fiable rend les défaillances visibles avant qu'elles ne deviennent des pannes, protège vos données et vous donne confiance dans vos opérations.