PostgreSQL refuse les connexions : diagnostiquer pg_stat_activity avant d’augmenter max_connections

ParKelvin Carretero-Corrales

Le message FATAL: remaining connection slots are reserved ou too many clients already pousse souvent à une réaction immédiate : augmenter max_connections. C’est parfois nécessaire, mais rarement le bon premier geste.

Une saturation peut venir d’un pool applicatif surdimensionné, de connexions jamais rendues, de transactions laissées ouvertes ou d’un pic réel de trafic. Ajouter des slots sans comprendre leur origine peut simplement retarder la prochaine panne, tout en augmentant la mémoire réservée par PostgreSQL.

Ce que limite réellement max_connections

max_connections fixe le nombre maximal de connexions simultanées acceptées par le serveur. PostgreSQL réserve normalement quelques slots aux superutilisateurs afin de conserver un accès d’urgence lorsque les connexions ordinaires sont épuisées. Les versions récentes proposent aussi reserved_connections pour des rôles disposant du privilège pg_use_reserved_connections.

Cette réserve n’est pas une capacité destinée au trafic normal : elle sert à diagnostiquer et corriger l’incident. Si l’authentification locale le permet, une connexion administrative depuis le serveur peut ressembler à ceci :

sudo -u postgres psql

La commande dépend de l’installation et de la méthode d’authentification. Sur un service managé, utilisez plutôt le canal d’administration prévu par le fournisseur.

1. Relever les limites sans rien modifier

SHOW max_connections;
SHOW superuser_reserved_connections;
SHOW reserved_connections;

reserved_connections existe à partir de PostgreSQL 16 ; la dernière commande échouera simplement sur une version antérieure. Relevez aussi la version du serveur :

SELECT version();

Évitez de modifier la configuration pendant que vous rassemblez les faits. max_connections ne se recharge pas à chaud : son changement nécessite un redémarrage du serveur et influence certaines allocations de ressources, dont la mémoire partagée.

2. Compter les connexions par état

SELECT state, count(*) AS connections
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY connections DESC;

Les principaux états à distinguer sont :

  • active : la connexion exécute une requête ;
  • idle : elle attend une nouvelle commande du client ;
  • idle in transaction : une transaction est ouverte, mais aucune requête ne s’exécute ;
  • idle in transaction (aborted) : une transaction a rencontré une erreur et attend toujours un ROLLBACK.

Une connexion idle consomme un slot, mais n’est pas forcément anormale : un pool conserve volontairement des connexions prêtes à servir. En revanche, une longue transaction inactive peut garder des verrous et empêcher VACUUM de supprimer certaines anciennes versions de lignes.

3. Identifier l’application qui ouvre les sessions

SELECT
  datname,
  usename,
  application_name,
  client_addr,
  state,
  count(*) AS connections
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY datname, usename, application_name, client_addr, state
ORDER BY connections DESC;

Cette vue permet de savoir si la majorité des connexions provient d’une API, d’un outil d’administration, d’une tâche planifiée ou d’un nœud applicatif précis. Un application_name bien configuré dans les chaînes de connexion fait gagner beaucoup de temps pendant un incident.

Si dix instances d’une application disposent chacune d’un pool maximal de vingt connexions, elles peuvent théoriquement demander deux cents sessions au serveur. Le réglage du pool doit donc être calculé à l’échelle de tout le déploiement, pas instance par instance.

4. Chercher les transactions anciennes et les attentes

SELECT
  pid,
  datname,
  usename,
  application_name,
  client_addr,
  state,
  now() - xact_start AS transaction_age,
  now() - state_change AS state_age,
  wait_event_type,
  wait_event,
  left(query, 160) AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND pid <> pg_backend_pid()
ORDER BY xact_start NULLS LAST;

Cette requête est en lecture seule. Elle montre les transactions les plus anciennes, le temps passé dans l’état courant et les éventuelles attentes. Interprétez les résultats avec le contexte de l’application :

  • une requête active depuis plusieurs minutes peut être normale pour un traitement analytique ;
  • une transaction inactive depuis longtemps indique souvent un chemin applicatif qui n’a ni validé ni annulé son travail ;
  • des centaines de connexions idle issues du même service suggèrent un pool trop large ou multiplié par trop d’instances ;
  • un événement d’attente de type Lock signale un blocage à analyser, pas forcément un manque de connexions.

5. Ne terminez pas une session au hasard

pg_cancel_backend(pid) demande l’annulation de la requête en cours. pg_terminate_backend(pid) coupe la session entière ; une transaction ouverte sera annulée. Ces fonctions nécessitent les privilèges appropriés et peuvent provoquer une erreur visible par l’application.

Avant toute intervention, vérifiez le PID, l’utilisateur, l’application, l’adresse cliente, l’âge de la transaction et la requête. Ne ciblez pas les processus internes, la réplication ou l’autovacuum pour « récupérer un slot ».

-- Annuler uniquement la requête active après vérification
SELECT pg_cancel_backend(12345);

-- Couper la session complète uniquement si c'est justifié
SELECT pg_terminate_backend(12345, 5000);

Remplacez 12345 uniquement par un PID contrôlé dans pg_stat_activity. Une terminaison peut soulager l’incident, mais elle ne corrige pas la fuite ou le mauvais dimensionnement qui l’a provoqué.

6. Corriger la cause dans l’application

Les corrections durables se trouvent souvent côté client :

  • rendre systématiquement la connexion au pool, y compris lorsqu’une exception survient ;
  • terminer chaque transaction par COMMIT ou ROLLBACK ;
  • définir une taille maximale, un délai d’acquisition et une durée de vie des connexions cohérents ;
  • additionner les pools de toutes les répliques, workers et tâches planifiées ;
  • appliquer un backoff avec une limite de tentatives au lieu de reconnecter en boucle ;
  • renseigner application_name pour rendre les sessions identifiables.

Un pool trop petit peut créer une file d’attente dans l’application. Un pool trop grand transfère la file d’attente vers PostgreSQL et multiplie les processus concurrents. Le bon réglage se valide avec la latence, le débit, les attentes et la consommation de ressources, pas avec un nombre choisi au hasard.

7. Encadrer les transactions oubliées avec des timeouts ciblés

PostgreSQL fournit notamment idle_in_transaction_session_timeout, qui termine une session restée inactive trop longtemps au milieu d’une transaction. Il est préférable de tester ce comportement puis de l’appliquer au rôle applicatif concerné, plutôt que globalement à toutes les sessions.

ALTER ROLE app_user
SET idle_in_transaction_session_timeout = '60s';

La valeur de 60 secondes n’est qu’un exemple. Elle serait trop courte pour certaines opérations manuelles ou certains traitements. statement_timeout et lock_timeout peuvent également limiter les requêtes trop longues ou les attentes de verrou, mais doivent être définis selon les usages du rôle et testés avant déploiement.

8. Quand ajouter PgBouncer

PgBouncer peut accepter de nombreuses connexions clientes tout en limitant le nombre de connexions réellement ouvertes vers PostgreSQL. En mode session, la connexion serveur n’est rendue au pool qu’à la déconnexion du client. En mode transaction, elle est rendue à la fin de chaque transaction.

Le mode transaction améliore souvent la mutualisation pour des requêtes courtes, mais il change les garanties de session. Avant de l’activer, vérifiez les fonctions de l’application qui dépendent d’un état conservé sur une connexion précise. PgBouncer ne remplace pas non plus la correction d’une transaction oubliée.

9. Augmenter max_connections seulement avec des mesures

Une hausse peut être légitime si la charge simultanée est réelle, les pools sont maîtrisés, les transactions sont propres et le serveur dispose des ressources nécessaires. Elle doit alors être planifiée avec :

  • une estimation du nombre maximal d’instances et de workers ;
  • une marge réservée à l’administration, aux migrations et à la supervision ;
  • une mesure de la mémoire, du CPU, des verrous et des temps d’attente ;
  • un test de charge représentatif ;
  • un redémarrage planifié et une procédure de retour arrière.

Sur une architecture avec réplication, la documentation PostgreSQL demande aussi que le standby utilise une valeur au moins égale à celle du primaire.

Les métriques à surveiller ensuite

  • connexions utilisées par rapport aux slots disponibles ;
  • répartition par état, rôle et application ;
  • âge de la plus ancienne transaction ;
  • connexions en attente dans le pool applicatif ;
  • nombre d’erreurs de connexion et de timeouts ;
  • latence des requêtes et événements d’attente.

Une alerte uniquement déclenchée à 100 % arrive trop tard. Il faut conserver une marge permettant au monitoring et à l’administration de se connecter pendant l’incident.

Conclusion

Quand PostgreSQL refuse des connexions, commencez par compter, regrouper et dater les sessions dans pg_stat_activity. Déterminez ensuite si le problème vient d’un pic réel, d’un pool multiplié par le nombre d’instances, d’une fuite ou d’une transaction abandonnée.

FuretSplick propose des automatisations avec tests, alertes et reprise humaine. Pour un service qui dépend de PostgreSQL, cela implique aussi de prévoir les limites de connexion, les erreurs transitoires et un comportement propre lorsque la base refuse momentanément une session.

Sources

Publications similaires