Comment surveiller une base de données PostgreSQL

Une base de données est rarement le premier composant supervisé, et souvent le premier à poser problème. PostgreSQL a la particularité de se dégrader progressivement : les symptômes apparaissent des semaines avant l'incident, sous forme de ralentissements que l'on attribue d'abord à l'application.

Cet article détaille les métriques qui annoncent réellement une dégradation, avec les requêtes de diagnostic correspondantes.

D'abord : l'espace disque

Avant toute métrique interne, la panne la plus brutale reste la saturation du volume hébergeant les données. PostgreSQL passe alors en arrêt ou refuse toute écriture.

Deux répertoires à surveiller séparément :

• le répertoire de données (PGDATA), qui grossit avec les tables et index
• le répertoire des journaux de transaction (pg_wal), qui peut grossir indépendamment

Le second est le plus vicieux. Si un emplacement de réplication (replication slot) devient inactif — un serveur secondaire arrêté, par exemple — PostgreSQL conserve indéfiniment les journaux dont ce secondaire pourrait avoir besoin. Le répertoire pg_wal grossit alors sans limite, jusqu'à remplir le volume, alors même que l'activité de la base est normale.

SELECT slot_name, active, pg_size_pretty(
  pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retenu
FROM pg_replication_slots;

Un emplacement inactif avec une rétention qui croît est une alerte à traiter le jour même.

Les connexions

PostgreSQL alloue un processus par connexion. Atteindre max_connections provoque le refus de toute nouvelle connexion — l'application tombe intégralement, alors que la base fonctionne parfaitement.

SELECT count(*) AS actives,
       (SELECT setting::int FROM pg_settings WHERE name='max_connections') AS maximum
FROM pg_stat_activity;

Seuil : alerte à 80 % de max_connections.

Surveillez aussi les connexions inactives dans une transaction (idle in transaction). Une transaction laissée ouverte par une application bloque le nettoyage des lignes mortes et peut immobiliser des verrous. Une connexion dans cet état depuis plus de quelques minutes est presque toujours un défaut applicatif.

SELECT pid, state, now() - state_change AS duree, query
FROM pg_stat_activity
WHERE state = 'idle in transaction' AND now() - state_change > interval '5 minutes';

Les requêtes lentes et les verrous

Requêtes longues

SELECT pid, now() - query_start AS duree, state, query
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > interval '1 minute'
ORDER BY duree DESC;

Une requête qui dure plusieurs minutes n'est pas nécessairement anormale (rapport, maintenance), mais leur nombre l'est. Alertez sur le compte de requêtes dépassant un seuil de durée, pas sur leur existence.

Verrous bloquants

C'est le symptôme le plus visible pour les utilisateurs : l'application se fige sans erreur. Une requête en attend une autre, qui en attend une troisième.

SELECT blocked.pid AS bloque, blocking.pid AS bloquant,
       blocked.query AS requete_bloquee
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

Toute chaîne de blocage durant plus d'une minute mérite une alerte.

Le taux de succès du cache

Indicateur global de santé : la proportion de lectures servies depuis la mémoire plutôt que depuis le disque.

SELECT datname,
  round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS taux_cache
FROM pg_stat_database
WHERE datname NOT IN ('template0','template1');

Sur une base correctement dimensionnée, ce taux dépasse 95 %. Une valeur durablement inférieure à 90 % signale que shared_buffers est trop petit pour le volume de données actif, ou qu'une requête parcourt régulièrement des tables entières.

C'est un indicateur de tendance, pas d'alerte immédiate : sa dégradation progressive annonce un besoin de dimensionnement.

Autovacuum et le gonflement des tables

PostgreSQL ne supprime pas physiquement les lignes modifiées ou effacées : il les marque comme mortes, et le processus autovacuum les récupère plus tard. Si ce processus ne suit pas le rythme des écritures, les tables gonflent : elles occupent de plus en plus d'espace pour la même quantité de données utiles, et les parcours ralentissent.

SELECT relname, n_live_tup, n_dead_tup,
  round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS pct_mortes,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Seuil : une table dépassant durablement 20 % de lignes mortes mérite attention. Une table dont last_autovacuum est vide ou très ancien alors qu'elle est fortement écrite signale un autovacuum en difficulté.

Le compteur de transactions

Cas rare mais critique : PostgreSQL utilise des identifiants de transaction sur un espace fini. Si autovacuum n'a pas pu figer les anciennes transactions, la base s'arrête pour se protéger d'une corruption.

SELECT datname, age(datfrozenxid) AS age_transactions
FROM pg_database ORDER BY age_transactions DESC;

Une valeur approchant 200 millions demande une intervention. Cette panne est rarissime, mais son impact est total et sa remise en état longue — elle mérite une surveillance passive.

La réplication

Sur une architecture avec serveur secondaire, deux métriques :

-- Sur le primaire
SELECT client_addr, state,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS retard
FROM pg_stat_replication;

Un retard croissant signale un secondaire qui ne suit plus. S'il devient inutilisable pour une bascule, votre plan de reprise ne fonctionne plus — sans qu'aucune alerte applicative ne se déclenche.

Ce qu'il faut retenir en priorité

Si vous ne surveillez que cinq choses :

1. L'espace disque de PGDATA et pg_wal, avec les emplacements de réplication inactifs
2. Le nombre de connexions, rapporté à max_connections
3. Les verrous bloquants durant plus d'une minute
4. Le service PostgreSQL lui-même
5. Le retard de réplication, si vous en avez une

Ces cinq métriques couvrent l'essentiel des incidents qui provoquent une interruption réelle. Le reste — cache, gonflement, requêtes lentes — relève du suivi de tendance et du dimensionnement.

Comment collecter ces métriques

L'agent Zabbix ne collecte pas ces métriques par défaut : elles supposent une connexion à la base. Zabbix Agent 2 dispose d'un plugin PostgreSQL qui les expose, moyennant la création d'un utilisateur de supervision dédié.

CREATE USER zbx_monitor WITH PASSWORD '...' INHERIT;
GRANT pg_monitor TO zbx_monitor;

Le rôle pg_monitor accorde exactement les droits de lecture des vues statistiques, sans accès aux données. C'est le principe de moindre privilège appliqué correctement : n'utilisez jamais un compte applicatif ni postgres pour la supervision.

Le paquet correspondant s'installe séparément :

sudo apt install zabbix-agent2-plugin-postgresql   # Debian, Ubuntu
sudo dnf install zabbix-agent2-plugin-postgresql   # Rocky, AlmaLinux

Avec Logiserv

Le serveur hébergeant PostgreSQL est supervisé comme un serveur Linux : espace disque avec découverte automatique des volumes, mémoire, charge, état du service postgresql et réseau. Cela couvre la panne la plus brutale — la saturation du volume de données — et l'arrêt du service.

Les métriques internes à la base (connexions, verrous, gonflement, réplication) ne sont pas incluses dans les templates fournis. Le plugin PostgreSQL de l'Agent 2 étant disponible, leur activation relève d'une configuration spécifique à votre installation.

Supervision du serveur

Native

Disque, mémoire, charge, service postgresql et réseau, avec découverte des volumes.

Métriques internes

Non incluses

Connexions, verrous et réplication demandent le plugin PostgreSQL et un utilisateur dédié.

Panne la plus brutale

Saturation disque

Couverte par la supervision standard, y compris sur un volume pg_wal séparé.

Pour aller plus loin

• Surveiller l'espace disque d'un serveur — la panne la plus brutale pour une base
• Pourquoi superviser un serveur Linux — la supervision du système hôte
• Quelles métriques surveiller sur un serveur — le plan général
• Installer Zabbix Agent sur Ubuntu ou Debian
• Supervision serveur Windows et Linux : le guide — la démarche complète