Configuration de check_pgactivity avec Nagios¶
Introduction¶
Ce document présente la configuration de Nagios pour la supervision de serveurs PostgreSQL.
Le script de supervision check_pgactivity, écrit en Perl, permet d’intégrer la supervision de bases de données PostgreSQL dans un système de supervision piloté par Nagios et consorts.
Il est disponible sur Github ou par paquet Debian ou RPM (voir Installation)
Liste des sondes¶
La supervision d’un serveur PostgreSQL passe par la surveillance de sa disponibilité, des indicateurs sur son activité, l’identification des besoins de maintenance et le suivi de la réplication le cas échéant.
Description¶
Voici la liste de toutes les sondes disponibles dans check_pgactivity, y compris dans la version en développement (depuis le dépôt du projet). Une sélection des plus importantes figure plus bas.
NB : Certaines sondes nécessitent une version récente de PostgreSQL, des droits étendus ou d’être exécutées sur le serveur postgres même. Les niveaux d’alerte ci-dessous sont souvent très généraux ou de simples exemples, car très dépendants des utilisations et besoins. Dans la plupart des cas, on se basera sur l’activité observée lors de périodes chargées mais « normales ». (Voir la documentation de chaque sonde pour les détails.) De même, la périodicité des appels n’est qu’un ordre de grandeur, et diffère grandement selon les instances, leur utilisation, la criticité, le besoin pour la métrologie.
Disponibilité¶
| Sonde | Description | But | Périodicité | Seuil(s) d’alerte |
|---|---|---|---|---|
| connection | Test de connexion pour vérifier que le serveur est accessible | Vérification accessibilité de l’instance | 5-10 min | N/A |
| backends | Compte les connexions au serveur | Alerte en cas d’approche de max_connections | 5-10 min | Ex: -w 80% -c 90% (en % de max_connections) |
| backends_status | Compte les connexions par état (waiting, idle in transaction…) | Détecter l’excès de connexions d’un certain type. | 5-10 min | Dépend des besoins, souvent aucun seuil |
| uptime | Repère les redémarrages et les rechargements de configuration | Être alerté d’un redémarrage ou d’une modification de configuration | 5-10 min | Ex: -w 10m -c 30m (avant retour spontané à la normale) |
Vacuum¶
| Sonde | Description | But | Périodicité | Seuil(s) d’alerte |
|---|---|---|---|---|
| autovacuum | Nombre de workers de l’autovacuum | Suivi de l’autovacuum et des tâches en cours (VACUUM, ANALYZE, FREEZE…) ; métrologie | 5-10 min | N/A |
| table_bloat | Estime le nombre tables avec trop d’espaces morts | Repérer le bloat excessif et les candidats au VACUUM FULL | 1 j | Seuils haut (en % et taille) pour ne cibler que les vrais problèmes Ex: –exclude ‘temp.*’ -w “80%,500MB” -c “90%,950MB” |
| btree_bloat | Idem, pour les index B-tree | Repérer des candidats à un REINDEX INDEX | 1 j | Idem |
| last_analyze | Temps depuis le dernier ANALYZE (relevé des statistiques pour le planificateur) | Repérer un autovacuum bloqué ou pas assez actif | 1 h | Dépendants de l’activité Ex: -w ‘7d’ -c ‘15d’ |
| last_vacuum | Idem pour le VACUUM | Idem | 1 h | Idem |
Activité¶
| Sonde | Description | But | Périodicité | Seuil(s) d’alerte |
|---|---|---|---|---|
| locks | Statistiques sur les verrous | Repérer des accumulations de verrous ; analyses après incident | 5-10 min | Choix délicat : faux positifs fréquents (pg_dump…) \ Ex: -w 8000 -c 10000 |
| wal_files | Nombre de journaux de transaction dans pg_xlog/pg_wal | Repérer une accumulation des journaux (problème d’archivage, secondaire déconnecté…) | 5-10 min | En fonction du nombre en fonctionnement normal et selon la place disque disponible pour pg_wal Ex : -w 200% -c 500% (de max_wal_size) |
| longest_query | Plus longue requête en cours | Repérer les requêtes durant trop longtemps | 5-10 min | Durée dépendante du comportement en temps normal (penser à la durée de la sauvegarde) \ Ex : -w 6h -c 8h –exclude ^autovacuum:“ |
| oldest_idlexact | Calcule l’âge de la plus ancienne transaction idle in transaction (ouverte mais, ni committée ni annulée et inactive) | Repérer ces transactions qui peuvent générer du bloat | 5-10 min | Généralement bref Ex : -w 1h -c 2h |
| oldest_2pc | Calcule l’âge de la plus ancienne transaction préparée (two-phase commit transaction) | Vérifier que des transactions préparées n’ont pas été oubliées | 5-10 min | Idem |
| oldest_xmin | Calcule l’âge de la plus ancienne transaction conservée | Prévenir fragmentation, voire pics de vacuum, à cause d’une transaction préparée, d’un secondaire ou d’une session “idle in transaction” | 1 h | Dépend de l’activité |
| bgwriter | Données de performance des différents processus d’écritures de PostgreSQL | Métrologie | 5-10 min | N/A |
| hit_ratio | Calcul du hit ratio sur l’instance | Vérifier l’utilisation du cache ; métrologie | 5-10 min | Rarement utiilisé comme alerte, faux positifs fréquents |
| commit_ratio | Calcul du commit ratio | Comparaison | 5-10 min | Rarement utilisé |
| checksum_errors | (v12+) Nombre d’erreurs de sommes de contrôle rencontrées | Détection rapide d’une corruption | 1-6 h | 1 (défaut) : problème critique |
| database_size | Taille des bases et leurs variations | Repérer les variations brutales ou la montée de la volumétrie ; métrologie | 1-6 h | Variation : dépendante de l’activité Taille : selon place disque ou cible estimée Ex: -w ‘size=500GB,delta=1%’ -c ‘size=600GB,delta=10GB’ |
| max_freeze_age | Âge des plus vieilles lignes stockées dans chaque base | Suivre le bon passage des VACUUM FREEZE ; avertir du passage d’un VACUUM FREEZE imminent | 1-6 h | > 100% pour le suivi, <100% pour l’approche du FREEZE |
| stat_snapshot_age | Âge des statistiques d’activité | Repérer un blocage du stats collector | 1 h | Valeur élevée, selon activité |
| temp_files | Nombre et taille des fichiers temporaires sur disque | Repérer les fichiers temporaires ; métrologie | 5-10 min | Selon activité en temps normal |
Configuration¶
| Sonde | Description | But | Périodicité | Seuil(s) d’alerte |
|---|---|---|---|---|
| minor_version | Renvoie la version mineure en cours | Repérer les instances à mettre à jour | 1 j | Pas de seuil pour une détection automatique de la version mineure publiée |
| configuration | Permet de vérifier que les principaux paramètres mémoire n’ont pas leur valeur par défaut | 1 j | N/A | |
| settings | Repère un changement des paramètres | Alarme en cas de modification | 1 j | N/A |
| invalid_indexes | Renvoie le nombre d’index invalides | Repérer tout index invalide | 1 j | N/A |
| pgdata_permission | Vérifie les droits sur PGDATA | Éviter un blocage au redémarrage | 1 j | N/A |
| table_unlogged | Remonte le nombre de tables unlogged | Repérer l’utilisation non prévue de tables unlogged | 1 j | Défaut : 0 (préférer les exclusions des tables existantes légitimes) |
Réplication, Sauvegarde PITR & Archivage¶
| Sonde | Description | But | Périodicité | Seuil(s) d’alerte |
|---|---|---|---|---|
| archive_folder | Vérifie que dans les archives de sauvegarde PITR il ne manque pas de WAL entre le plus ancien et le dernier et calcule l’âge du plus récent journal archivé | Repérer des trous dans les archives et un archivage bloqué (inutile si vous déployez check_pgbackrest, check_barman ou pitrery check -n) | 1 j | Âge limite selon utilisation habituelle Ex: -w 15m -c 30m |
| archiver | Compte le nombre de segments du journal de transaction en attente d’archivage | Repérer un archivage défaillant | 5-10 min | Nombre selon les pics d’activité fréquents Défaut souvent suffisant si les appels sont assez rapprochés |
| hot_standby_delta | Calcule le délai de réplication entre un serveur maître et un esclave Préférer la sonde streaming_delta pour une réplication en streaming | Repérer un secondaire en retard | 5-10 min | Selon latence repérée lors des pics d’activité |
| is_master | Vérifie que l’instance est bien démarrée en lecture/écriture | 5-10 min | N/A | |
| is_hot_standby | Vérifie que l’instance est un serveur secondaire (en recovery et accepte bien les requêtes en lecture) | 5-10 min | N/A | |
| streaming_delta | Calcule l’écart de données entre un serveur principal et ses serveurs secondaires (la réplication en flux) | Repérer des secondaires en retard | 5-10 min | Selon latence observée lors des pics d’activité Ex: -w 4GB -c 6GB |
| is_replay_paused | Vérifie si la réplication est en pause | 5-10 min | N/A | |
| replication_slots | Calcule le nombre de journaux de transaction et dans pg_repslot conservés par chaque slot | Repérer l’accumulation de données dus aux slots de réplication | 5-10 min | Selon volumétrie habituelle des pics d’activité |
Sauvegarde physique et logique¶
| Sonde | Description | But | Périodicité | Seuil(s) d’alerte |
|---|---|---|---|---|
| backup_label_age | Calcule l’âge du fichier backup_label (sauvegardes PITR exclusives) | Repérer un pg_stop_backup oublié | 1 j | Selon durée habituelle d’un backup PITR |
| pg_dump_backup | Contrôle l’âge et la variation de taille des sauvegardes logique | 1 j | Selon la durée et la taille des sauvegardes habituelles | Ex: –path ‘/backups_logiques/*’ –pattern ‘(\w+)\d{4}-\d{2}-\d{2}\d{2}-\d{2}.dump’ –dbexclude ‘template[01] |
Métier¶
| Sonde | Description |
|---|---|
| custom_query | Exécute une requête personnalisée |
Autres¶
| Sonde | Description | But | Périodicité | Seuil(s) d’alerte |
|---|---|---|---|---|
| pga_version | Renvoie la version de la sonde check_pgactivity elle-même | Vérifier la mise à jour de la valeur | 1 j | N/A |
Recommandations pour la mise en place d’une supervision¶
En première approche, et sous réserve que leur utilisation soit pertinente dans le contexte, voici les premiers services de check_pgactivity que nous vous recommandons de mettre en place. D’autres services décrits plus haut pourraient être ajoutés selon les besoins.
Pour tous les serveurs :
autovacuum
backends
backends_status
btree_bloat
checksum_errors
database_size
hit_ratio
invalid_indexes
last_analyze
last_vacuum
locks
longest_query
max_freeze_age
oldest_idlexact
table_bloat
temp_files
uptime
wal_files
En cas d’utilisation (même exceptionnelle) de la sauvegarde physique non concurrente :
En cas d’utilisation de l’archivage :Sur un serveur primaire répliqué :
Sur un serveur secondaire :Certains de ces services ne nécessitent pas la mise en place de valeur de WARNING ou CRITICAL (ex: autovacuum) donc pas d’alerte sur celle-ci ; mais permettent d’archiver des indicateurs systèmes qui permettront par la suite un affinage de configuration plus poussé.
Installation¶
La dernière version du script check_pgactivity est à télécharger à cette URL : https://github.com/OPMDG/check_pgactivity
La dernière version stable est la 2.7 (fin 2023), aussi disponible en RPM (dans yum.postgresql.org et sur Github), et en .DEB (dans Debian et apt.postgresql.org).
Cependant, les versions de la branche ‘master’ du dépôt Github sont à priori directement utilisables.
Connexion directe¶
Pour lancer check_pgactivity depuis la machine de supervision, il faut y installer check_pgactivity dans un répertoire accessible pour l’utilisateur système qui lance les commandes Nagios, l’idéal étant de le placer dans le répertoire correspondant à la macro $USER1$. Lancer le programme sans options pour vérifier si des dépendances ne sont pas nécessaires au niveau Perl.
Configuration de PostgreSQL¶
L’accès aux serveurs PostgreSQL pour la supervision doit se faire par l’intermédiaire d’un utilisateur dédié dans l’instance. Dans la procédure qui suit, x.y.z.t est à remplacer par l’adresse IP de la machine de supervision.
- Créer un utilisateur nagios avec mot de passe, se connecter avec l’utilisateur postgres et lancer l’ordre SQL suivant sur le serveur :
pour les versions PostgreSQL < 10 :
pour les versions PostgreSQL >= 10 :
Puis, en fonction du service demandé à check_pgactivity, ajouter les autorisations nécessaires pour permettre sa bonne exécution. Le tableau ci-dessous donne quelques exemples en fonction de la sonde appelée et de la version de l’instance PostgreSQL :
| Sonde | Version PostgreSQL >= 10 | Autorisations nécessaires | exemple |
|---|---|---|---|
| btree_bloat | >= 10 | accès en lecture à la table pg_statistic | GRANT SELECT ON pg_statistic TO nagios; |
| table_bloat | >= 10 | accès en lecture à la table pg_statistic | GRANT SELECT ON pg_statistic TO nagios; |
| temp_files | 10 et 11 | exécution des fonctions pg_stat_file(), pg_ls_dir(), pg_read_file() | GRANT EXECUTE ON FUNCTION pg_stat_file(text) TO nagios; -- etc. |
| wal_files | >= 10 | exécution de la fonction pg_ls_waldir() | GRANT EXECUTE ON FUNCTION pg_ls_waldir() TO nagios; |
Si les 4 sondes du tableau ci-dessus sont utilisées pour surveiller une instance v11, alors le DCL suivant sera à exécuter :
-- pour btree_bloat et table_bloat et sur chaque base ou doit passer la sonde
GRANT SELECT ON pg_statistic TO nagios;
-- pour wal_files
GRANT EXECUTE ON FUNCTION pg_ls_waldir() TO nagios ;
-- pour temp_files
GRANT EXECUTE ON FUNCTION pg_stat_file(text) TO nagios ;
GRANT EXECUTE ON FUNCTION pg_stat_file(text, BOOLEAN) TO nagios ; -- appelée dans check_temp_files pour v10 et 11
GRANT EXECUTE ON FUNCTION pg_ls_dir(text) TO nagios ; -- appelée dans check_temp_files pour v10 et 11
GRANT EXECUTE ON FUNCTION pg_ls_dir(text, BOOLEAN, BOOLEAN) TO nagios ;
GRANT EXECUTE ON FUNCTION pg_read_file(text) TO nagios ; -- appelée dans check_temp_files pour v10 et 11
GRANT EXECUTE ON FUNCTION pg_read_file(text, BIGINT, BIGINT) TO nagios ;
GRANT EXECUTE ON FUNCTION pg_read_file(text, BIGINT, BIGINT, BOOLEAN) TO nagios ;
Par souci de simplification, l’autorisation d’exécution est donnée sur l’ensemble des signatures d’une fonction portant le même nom, cependant il est possible de restreindre l’exécution en spécifiant uniquement la “bonne signature” de la fonction appelée.
- Ajouter la ligne suivante au fichier pg_hba.conf du serveur juste avant la première ligne commençant par “host” :
host all nagios x.y.z.t/32 md5
Chaque machine de supervision doit avoir sa ligne de configuration pour lui permettre l’accès.
- Recharger la configuration par un « reload » du service
- Ajouter une ligne dans le fichier $HOME/.pgpass (les permissions de ce fichier doivent être en mode 600) de l’utilisateur qui le lancera, soit la machine de supervision soit les serveurs PostgreSQL selon le type de connexion choisi :
host:port:nagios:postgres:xxxxxhostetportsont à remplacer par le nom ou l’adresse IP et le port du serveur PostgreSQL.
Note
check_pgactivity utilise le client psql pour se connecter, donc in fine la bibliothèque standard de connexion. Ainsi, un fichier de mot de passe (.pgpass) ou une variable d’environnement ($PGPASSWORD) peuvent être utilisés afin de spécifier un mot de passe si le serveur nécessite d’en fournir un.
Test de connexion¶
Connexion directe¶
Pour valider le fonctionnement correct de check_pgactivity, adapter et lancer la commande suivante depuis la machine de supervision, avec l’utilisateur qui lance les commandes Nagios :
Un résultat équivalent à celui-ci doit être affiché :