La surveillance des bases de données est l'une des tâches les plus critiques pour tout administrateur de bases de données.
PostgreSQL offre une large gamme d'outils intégrés et d'extensions pour vous aider à suivre les performances, à résoudre les problèmes et à garantir la fiabilité de vos systèmes.
Dans cet article, nous allons explorer l'essentiel de la surveillance de PostgreSQL, depuis les vues du système jusqu'aux outils avancés.
Pourquoi surveiller PostgreSQL ?
La surveillance vous permet de :
- Détecter et résoudre les goulets d'étranglement en matière de performances.
- Suivre les sessions actives et les verrous.
- Capturer les statistiques d'exécution des requêtes.
- Assurer une disponibilité et une stabilité élevées.
Sans surveillance proactive, les requêtes lentes, les sessions bloquées et la mauvaise gestion des ressources peuvent dégrader silencieusement les performances.
Outils de surveillance intégrés
PostgreSQL est livré avec plusieurs tables et vues de catalogue qui fournissent des informations précieuses :
- pg_stat_activité - Affiche les sessions et les requêtes SQL en cours.
- pg_locks - Affiche les verrous détenus par les transactions en cours.
- pg_stat_database - Fournit des statistiques sur l'activité de la base de données.
- pg_stat_user_tables - Surveille l'activité au niveau des tables, comme les insertions, les mises à jour et les suppressions.
- pg_stat_user_indexes - Suivi de l'utilisation de l'index.
- pg_stat_progress_vacuum / create_index / cluster - Indique l'état d'avancement des tâches de maintenance.
👉 Ces vues vous donnent un aperçu en temps réel de ce qui se passe dans votre cluster.
Extensions pour une meilleure compréhension
- pg_stat_statements - Suivi des statistiques d'exécution de toutes les requêtes, pour vous aider à identifier les requêtes SQL lentes.
- auto_explain - Enregistre les plans d'exécution des requêtes dont la durée dépasse un seuil configuré.
- pg_badger - Analyse les journaux PostgreSQL pour générer des rapports de performance.
Ces outils sont particulièrement utiles pour l'analyse et l'optimisation à long terme.
PostgreSQL Enterprise Manager (PEM)
Pour les entreprises qui ont besoin d'une surveillance centralisée, Postgres Enterprise Manager (PEM) fournit :
- Des tableaux de bord avec des mesures de la santé du système.
- Débogueur de requêtes et profileur.
- Alertes et planification des tâches.
- Gestion de la sauvegarde et du basculement.
- Intégration avec d'autres systèmes de surveillance via des API et des webhooks.
PEM est idéal pour les administrateurs de bases de données qui gèrent plusieurs clusters à grande échelle.
Principaux paramètres d'enregistrement
Vous pouvez configurer PostgreSQL pour qu'il enregistre des informations utiles en modifiant les paramètres de la section postgresql.conf:
- déclaration_min_durée_log - Enregistre les requêtes de plus de X millisecondes.
- log_connections / log_disconnections - Suivi de l'activité de la session.
- log_lock_waits - Permet d'identifier les requêtes bloquantes.
- Fichiers log_temp - Détecte l'utilisation excessive de fichiers temporaires.
👉 L'ajustement de ces paramètres vous permet de détecter rapidement les requêtes problématiques.
Questions pratiques sur le contrôle
Voici quelques exemples rapides que les administrateurs de bases de données peuvent utiliser quotidiennement :
-- Check current running queries
SELECT pid, usename, state, query
FROM pg_stat_activity;
-- Identify blocking sessions
SELECT pid, pg_blocking_pids(pid) AS blocked_by, query
FROM pg_stat_activity;
-- Find top queries by execution time (requires pg_stat_statements)
SELECT query, calls, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;
Réflexions finales
La surveillance ne consiste pas seulement à détecter les problèmes, mais aussi à s'assurer que vos bases de données PostgreSQL fonctionnent au maximum de leurs performances.
PostgreSQL 16 fournit de puissantes statistiques et extensions intégrées, tandis que des outils tels que PEM ajoutent des capacités de niveau entreprise.