🔝 Retour au Sommaire

14.2.1. pg_stat_activity : Activité en cours

Introduction

La vue pg_stat_activity est l'une des vues système les plus importantes de PostgreSQL. Elle vous permet de voir en temps réel ce qui se passe dans votre base de données : quelles requêtes sont en cours d'exécution, qui les exécute, depuis combien de temps elles tournent, et bien plus encore.

Pensez à pg_stat_activity comme à un tableau de bord en direct de votre base de données. C'est souvent le premier endroit où vous regarderez lorsque vous voudrez diagnostiquer un problème de performance ou comprendre ce qui se passe actuellement dans PostgreSQL.

Pourquoi pg_stat_activity est importante ?

Dans un environnement de production, vous devez pouvoir répondre à des questions comme :

Combien de connexions actives sont ouvertes en ce moment ?
Quelle requête est en train de bloquer les autres ?
Pourquoi ma base de données est-elle lente ?
Qui est connecté et depuis combien de temps ?
Y a-t-il des requêtes qui tournent anormalement longtemps ?

pg_stat_activity vous donne les réponses à toutes ces questions.

Accéder à pg_stat_activity

Pour consulter cette vue, il suffit d'exécuter une simple requête SQL :

SELECT * FROM pg_stat_activity;

Cependant, cette requête retourne beaucoup de colonnes (plus de 20 !). En pratique, on sélectionne généralement uniquement les colonnes qui nous intéressent.

Les colonnes principales de pg_stat_activity

Voici les colonnes les plus importantes à connaître :

1. datid et datname

datid : L'identifiant numérique de la base de données
datname : Le nom de la base de données à laquelle la session est connectée

Exemple d'usage :

SELECT datname, count(*)  
FROM pg_stat_activity  
GROUP BY datname;

Cette requête vous montre combien de connexions sont actives par base de données.

2. pid

Le Process ID (identifiant du processus backend PostgreSQL). C'est un nombre unique pour chaque connexion active. Si vous devez tuer une connexion problématique, vous utiliserez ce PID.

Exemple :

-- Trouver le PID d'une connexion spécifique
SELECT pid, usename, query  
FROM pg_stat_activity  
WHERE usename = 'mon_utilisateur';

3. usename

Le nom de l'utilisateur PostgreSQL qui a ouvert la connexion.

Cas d'usage : Identifier qui exécute une requête particulière ou combien de connexions un utilisateur spécifique a ouvertes.

4. application_name

Le nom de l'application cliente qui a établi la connexion. Cela peut être configuré par l'application (par exemple : "mon_api", "job_nocturne", "psql").

Pourquoi c'est utile ? Dans un environnement avec plusieurs applications connectées à la même base, cela vous aide à identifier quelle application cause un problème.

5. client_addr et client_port

client_addr : L'adresse IP du client
client_port : Le port utilisé par le client

Cas d'usage : Identifier d'où viennent les connexions, détecter des connexions suspectes.

6. backend_start

L'horodatage indiquant quand la connexion a été établie.

Exemple :

-- Trouver les connexions ouvertes depuis plus de 1 heure
SELECT pid, usename, backend_start, now() - backend_start AS duree_connexion  
FROM pg_stat_activity  
WHERE now() - backend_start > interval '1 hour';

7. state

L'état actuel de la session. C'est l'une des colonnes les plus importantes !

Valeurs possibles :

active : La session exécute actuellement une requête
idle : La session est connectée mais n'exécute rien (en attente de commande)
idle in transaction : La session est dans une transaction mais n'exécute pas de requête (⚠️ peut être problématique !)
idle in transaction (aborted) : Une transaction a échoué mais n'a pas été fermée
fastpath function call : La session exécute une fonction en mode "fast-path"
disabled : Le suivi est désactivé pour ce backend

Exemple d'analyse des états :

SELECT state, count(*)  
FROM pg_stat_activity  
GROUP BY state;

⚠️ Attention à "idle in transaction" !

Une session en idle in transaction garde des verrous et peut bloquer d'autres opérations. Si vous voyez beaucoup de ces sessions, ou qu'elles durent longtemps, c'est un signe de problème dans votre code applicatif (transactions non fermées proprement).

8. query

Le texte de la requête SQL actuellement en cours d'exécution (ou la dernière requête exécutée si la session est idle).

Note : Par défaut, seule la requête en cours est visible. Les requêtes passées ne sont pas conservées dans cette vue.

Exemple :

-- Voir toutes les requêtes actives
SELECT pid, usename, state, query  
FROM pg_stat_activity  
WHERE state = 'active';

9. query_start

L'horodatage indiquant quand la requête actuelle a commencé.

Cas d'usage crucial : Identifier les requêtes qui tournent trop longtemps.

Exemple - Trouver les requêtes lentes :

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

10. xact_start

L'horodatage indiquant quand la transaction actuelle a commencé.

Différence avec query_start : Une transaction peut contenir plusieurs requêtes. xact_start vous indique le début de la transaction, tandis que query_start indique le début de la requête en cours.

11. wait_event_type et wait_event

Ces colonnes vous indiquent si la session est en attente et pourquoi.

wait_event_type peut être :

LWLock : Attente d'un verrou léger (light-weight lock)
Lock : Attente d'un verrou classique
BufferPin : Attente pour accéder à un buffer
IO : Attente d'une opération disque
Client : En attente du client
Extension : En attente dans une extension
Timeout : En attente d'un timeout

wait_event donne plus de détails sur l'événement précis.

Exemple - Identifier les sessions bloquées :

SELECT
    pid,
    usename,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity  
WHERE wait_event_type IS NOT NULL  
  AND state = 'active';

12. backend_type

Le type de processus backend. Cela permet de distinguer les connexions clients des processus internes.

Valeurs possibles :

client backend : Connexion cliente normale
autovacuum worker : Processus autovacuum
logical replication worker : Travailleur de réplication logique
parallel worker : Travailleur parallèle
background writer : Processus d'écriture en arrière-plan
checkpointer : Processus de checkpoint
walwriter : Processus d'écriture WAL
etc.

Exemple - Voir uniquement les connexions clients :

SELECT pid, usename, state, query  
FROM pg_stat_activity  
WHERE backend_type = 'client backend';

Requêtes pratiques courantes

1. Voir toutes les connexions actives avec requêtes en cours

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    query_start,
    query
FROM pg_stat_activity  
WHERE state = 'active'  
ORDER BY query_start;

2. Compter le nombre de connexions par état

SELECT
    state,
    count(*) AS nombre_connexions
FROM pg_stat_activity  
GROUP BY state  
ORDER BY count(*) DESC;

3. Identifier les requêtes longues (> 10 minutes)

SELECT
    pid,
    usename,
    application_name,
    now() - query_start AS duree,
    state,
    left(query, 100) AS requete_tronquee -- On tronque pour la lisibilité
FROM pg_stat_activity  
WHERE state = 'active'  
  AND now() - query_start > interval '10 minutes'
ORDER BY duree DESC;

4. Trouver les transactions en attente (idle in transaction)

SELECT
    pid,
    usename,
    application_name,
    now() - xact_start AS duree_transaction,
    state,
    query
FROM pg_stat_activity  
WHERE state LIKE 'idle in transaction%'  
  AND xact_start IS NOT NULL
ORDER BY xact_start;

⚠️ Ces transactions peuvent causer des problèmes de performance !

5. Compter les connexions par utilisateur

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

6. Compter les connexions par application

SELECT
    application_name,
    count(*) AS nombre_connexions
FROM pg_stat_activity  
GROUP BY application_name  
ORDER BY count(*) DESC;

7. Identifier les sessions bloquées et bloquantes

Cette requête est plus avancée mais très utile :

SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocking.pid AS blocking_pid,
    blocking.usename AS blocking_user,
    blocked.query AS blocked_query,
    blocking.query AS blocking_query
FROM pg_stat_activity AS blocked  
JOIN pg_locks AS blocked_locks ON blocked.pid = blocked_locks.pid  
JOIN pg_locks AS blocking_locks ON  
    blocked_locks.locktype = blocking_locks.locktype
    AND blocked_locks.database IS NOT DISTINCT FROM blocking_locks.database
    AND blocked_locks.relation IS NOT DISTINCT FROM blocking_locks.relation
    AND blocked_locks.page IS NOT DISTINCT FROM blocking_locks.page
    AND blocked_locks.tuple IS NOT DISTINCT FROM blocking_locks.tuple
    AND blocked_locks.virtualxid IS NOT DISTINCT FROM blocking_locks.virtualxid
    AND blocked_locks.transactionid IS NOT DISTINCT FROM blocking_locks.transactionid
    AND blocked_locks.classid IS NOT DISTINCT FROM blocking_locks.classid
    AND blocked_locks.objid IS NOT DISTINCT FROM blocking_locks.objid
    AND blocked_locks.objsubid IS NOT DISTINCT FROM blocking_locks.objsubid
    AND blocked_locks.pid != blocking_locks.pid
JOIN pg_stat_activity AS blocking ON blocking_locks.pid = blocking.pid  
WHERE NOT blocked_locks.granted;

Note : Cette requête combine pg_stat_activity avec pg_locks (vue des verrous).

8. Vue simplifiée avec les informations essentielles

SELECT
    pid,
    usename AS utilisateur,
    application_name AS application,
    client_addr AS adresse_ip,
    state AS etat,
    CASE
        WHEN state = 'active' THEN now() - query_start
        ELSE NULL
    END AS duree_requete,
    CASE
        WHEN state LIKE 'idle in transaction%' THEN now() - xact_start
        ELSE NULL
    END AS duree_transaction,
    left(query, 80) AS requete
FROM pg_stat_activity  
WHERE backend_type = 'client backend'  
ORDER BY  
    CASE state
        WHEN 'active' THEN 1
        WHEN 'idle in transaction' THEN 2
        ELSE 3
    END,
    query_start;

Gérer les connexions problématiques

Terminer une connexion spécifique

Si vous identifiez une connexion problématique (requête bloquante, transaction longue), vous pouvez la terminer :

-- Terminer proprement une connexion (permet au client de nettoyer)
SELECT pg_cancel_backend(PID);

-- Terminer immédiatement une connexion (plus brutal)
SELECT pg_terminate_backend(PID);

Différence :

pg_cancel_backend() : Envoie un signal d'annulation. Le client peut intercepter et nettoyer.
pg_terminate_backend() : Ferme immédiatement la connexion. Plus radical.

Exemple pratique :

-- Annuler toutes les requêtes actives de plus de 30 minutes
SELECT pg_cancel_backend(pid)  
FROM pg_stat_activity  
WHERE state = 'active'  
  AND now() - query_start > interval '30 minutes'
  AND pid != pg_backend_pid(); -- Ne pas se tuer soi-même !

Permissions et sécurité

Important : Tous les utilisateurs ne peuvent pas voir toutes les sessions dans pg_stat_activity.

Les superutilisateurs voient toutes les sessions
Les utilisateurs normaux voient uniquement leurs propres sessions
Les membres du rôle pg_read_all_stats peuvent voir toutes les sessions

Pour donner l'accès en lecture à un utilisateur :

GRANT pg_read_all_stats TO mon_utilisateur;

Limitations et considérations

1. Performance

Interroger pg_stat_activity est généralement très rapide, mais dans un système avec des milliers de connexions, cela peut avoir un léger impact. Évitez de l'interroger en boucle trop rapidement.

2. Le paramètre track_activities

Si track_activities est désactivé dans la configuration PostgreSQL, pg_stat_activity ne montrera pas les requêtes. Par défaut, ce paramètre est activé (on).

-- Vérifier la configuration
SHOW track_activities;

3. Visibilité de la colonne query

Par défaut, seule la requête en cours ou la dernière requête exécutée est visible. PostgreSQL ne garde pas d'historique des requêtes dans cette vue.

Pour un historique complet, vous devez utiliser pg_stat_statements (extension à activer séparément).

Cas d'usage en production

Scénario 1 : "Ma base de données est lente !"

Étapes de diagnostic :

Vérifier les requêtes actives longues :

SELECT pid, usename, now() - query_start AS duree, query  
FROM pg_stat_activity  
WHERE state = 'active'  
ORDER BY duree DESC  
LIMIT 10;

Vérifier les transactions idle :

SELECT count(*), max(now() - xact_start) AS max_duree  
FROM pg_stat_activity  
WHERE state LIKE 'idle in transaction%';

Vérifier le nombre de connexions :

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

Scénario 2 : "Combien de connexions puis-je encore ouvrir ?"

SELECT
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_connexions,
    count(*) AS connexions_actives,
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') - count(*) AS connexions_disponibles
FROM pg_stat_activity;

Scénario 3 : "Une application ne répond plus"

-- Identifier les sessions de l'application
SELECT
    pid,
    state,
    now() - query_start AS duree,
    query
FROM pg_stat_activity  
WHERE application_name = 'mon_application'  
ORDER BY query_start;

Si vous voyez des requêtes bloquées, cherchez les verrous avec pg_locks.

Monitoring automatisé

Dans un environnement de production, vous ne devez pas consulter pg_stat_activity manuellement. Utilisez des outils de monitoring qui le font pour vous :

Prometheus + postgres_exporter : Exporte des métriques comme le nombre de connexions, les requêtes longues, etc.
pgBadger : Analyse les logs pour identifier les problèmes
pgAdmin, DBeaver, DataGrip : Interfaces graphiques avec vues temps réel
Solutions cloud : AWS RDS Performance Insights, Azure Database Insights, etc.

Exemple de métrique Prometheus :

-- Nombre de connexions actives par état
SELECT state, count(*)  
FROM pg_stat_activity  
GROUP BY state;

Cette requête peut être exportée et visualisée dans Grafana.

Bonnes pratiques

Ne restez pas connecté inutilement : Fermez les connexions quand vous ne les utilisez plus.
Fermez vos transactions : Les transactions idle in transaction sont un problème majeur. Assurez-vous que votre code applicatif fait toujours un COMMIT ou ROLLBACK.
Utilisez un connection pooler : Des outils comme PgBouncer limitent le nombre de connexions réelles à PostgreSQL et améliorent les performances.
Configurez des timeouts : Le paramètre idle_in_transaction_session_timeout peut automatiquement tuer les transactions idle après un délai.

-- Exemple : Tuer automatiquement les transactions idle après 10 minutes
ALTER SYSTEM SET idle_in_transaction_session_timeout = '10min';  
SELECT pg_reload_conf();

Surveillez régulièrement : Mettez en place des alertes sur :
- Nombre de connexions approchant max_connections
- Requêtes dépassant un certain temps (ex: 1 minute)
- Transactions idle in transaction de plus de 5 minutes

Résumé

pg_stat_activity est votre fenêtre en temps réel sur l'activité de PostgreSQL. Les colonnes principales à retenir :

Colonne	Description
pid	Identifiant du processus (pour tuer une connexion)
usename	Utilisateur connecté
state	État de la session (active, idle, idle in transaction)
query	Requête SQL en cours ou dernière exécutée
query_start	Début de la requête en cours
xact_start	Début de la transaction en cours
wait_event	Événement d'attente (si bloqué)
backend_type	Type de processus (client, autovacuum, etc.)

Points clés :

Identifiez rapidement les requêtes lentes avec query_start
Surveillez les transactions idle in transaction (problématiques !)
Utilisez pg_cancel_backend() ou pg_terminate_backend() pour gérer les connexions problématiques
Combinez avec pg_locks pour analyser les blocages
Automatisez le monitoring en production

Avec pg_stat_activity, vous avez un outil puissant pour comprendre et diagnostiquer ce qui se passe dans votre base de données PostgreSQL. C'est un incontournable pour tout développeur ou administrateur de bases de données.

Prochaines étapes : Explorez les autres vues système comme pg_stat_database, pg_stat_user_tables, et pg_locks pour une vision encore plus complète de votre instance PostgreSQL.

⏭️ pg_stat_database : Métriques par base

FilesExpand file tree

02.1-pg-stat-activity.md

Latest commit

History

02.1-pg-stat-activity.md

File metadata and controls

14.2.1. pg_stat_activity : Activité en cours

Introduction

Pourquoi pg_stat_activity est importante ?

Accéder à pg_stat_activity

Les colonnes principales de pg_stat_activity

1. datid et datname

2. pid

3. usename

4. application_name

5. client_addr et client_port

6. backend_start

7. state

8. query

9. query_start

10. xact_start

11. wait_event_type et wait_event

12. backend_type

Requêtes pratiques courantes

1. Voir toutes les connexions actives avec requêtes en cours

2. Compter le nombre de connexions par état

3. Identifier les requêtes longues (> 10 minutes)

4. Trouver les transactions en attente (idle in transaction)

5. Compter les connexions par utilisateur

6. Compter les connexions par application

7. Identifier les sessions bloquées et bloquantes

8. Vue simplifiée avec les informations essentielles

Gérer les connexions problématiques

Terminer une connexion spécifique

Permissions et sécurité

Limitations et considérations

1. Performance

2. Le paramètre track_activities

3. Visibilité de la colonne query

Cas d'usage en production

Scénario 1 : "Ma base de données est lente !"

Scénario 2 : "Combien de connexions puis-je encore ouvrir ?"

Scénario 3 : "Une application ne répond plus"

Monitoring automatisé

Bonnes pratiques

Résumé