PostgreSQL propose de nombreuses vues, accessibles en SQL, pour
obtenir des informations sur son fonctionnement interne. Il est possible
d’avoir des informations sur le fonctionnement des bases, des processus
d’arrière-plan, des tables, les requêtes en cours…
Statistiques sur les objets :
Pour les statistiques sur les objets, le système fournit à chaque
fois trois vues différentes :
Une pour tous les objets du type. Elle contient all dans le
nom, pg_statio_all_tables par exemple ;
Une pour uniquement les objets systèmes. Elle contient sys
dans le nom, pg_statio_sys_tables par exemple ;
Une pour uniquement les objets non-systèmes. Elle contient
user dans le nom, pg_statio_user_tables par
exemple.
Les accès logiques aux objets (tables, index et routines) figurent
dans les vues pg_stat_xxx_tables,
pg_stat_xxx_indexes et
pg_stat_user_functions.
SELECT * FROM pg_stat_user_tables WHERE relname = 'pgbench_accounts' \gxLes accès physiques aux objets sont visibles dans les vues
pg_statio_xxx_indexes, pg_statio_xxx_tables et
pg_statio_xxx_sequences. Une vision plus globale est
disponible dans pg_stat_io (apparue avec PostgreSQL 16).
SELECT * FROM pg_statio_user_tables WHERE relname = 'pgbench_accounts' \gxDes statistiques globales par base sont aussi disponibles, dans
pg_stat_database : le nombre de transactions validées et
annulées, quelques statistiques sur les sessions, et quelques
statistiques sur les accès physiques et en cache, ainsi que sur les
opérations logiques.
SELECT * FROM pg_stat_database WHERE datname = 'pgbench' \gxpg_stat_bgwriter stocke les statistiques d’écriture des
buffers des background writer , et du checkpointer
(jusqu’en version 16 incluse) et des sessions elles-mêmes. On peut ainsi
voir si les backends écrivent beaucoup ou peu. À partir de
PostgreSQL 17, apparaît pg_stat_checkpointer qui reprend
les champs sur les checkpoints et en ajoute quelques-uns. Cette
vue permet de vérifier que les checkpoints sont réguliers, donc
peu gênants.
Exemple (version 17) :
TABLE pg_stat_bgwriter \gxTABLE pg_stat_checkpointer \gxÉcritures :
pg_stat_bgwriter stocke les statistiques d’écriture des
buffers des Background Writer, Checkpointer et des sessions
elles-mêmes.
Requêtes
pg_stat_activity est une des vues les plus utilisées et
est souvent le point de départ d’une recherche. Elle donne la liste des
processus en cours sur l’instance, en incluant entre autres :
le numéro de processus sur le serveur (pid) ;
la base de données, le nom dutilisateur, l’adresse et le port du
client ;
les dates de début d’ordre, de transaction ou de session ;
son statut (active ou non) ;
la requête en cours, ou la dernière requête si la session ne fait
rien ;
le nom de l’application s’il a été renseigné avec le paramètre
application_name ;
le type de processus : session d’un utilisateur (client
backend ), processus interne…
SELECT datname, pid, usename, application_name,query FROM pg_stat_activity \gxLes textes des requêtes sont tronqués à 1024 caractères : c’est un
problème courant. Il est conseillé de monter le paramètre
track_activity_query_size à plusieurs kilooctets.
Cette vue fournit aussi les wait events , qui indiquent ce
qu’une session est en train d’attendre. Cela peut être très divers et
inclut la levée d’un verrou sur un objet, celle d’un verrou interne, la
fin d’une entrée-sortie… L’absence de wait event indique que la
requête s’exécute. À noter qu’une session avec un wait event
peut rester en statut active.
Les détails sur les champs wait_event_type (type
d’événement en attente) et wait_event (nom de l’événement
en attente) sont disponibles dans le tableau des événements
d’attente . de la documentation.
À partir de PostgreSQL 17, la vue pg_wait_events peut
être directement jointe à pg_stat_activity, et son champ
description évite d’aller voir la documentation :
SELECT datname, application_name, pid,query , w.descriptionFROM pg_stat_activity aLEFT OUTER JOIN pg_wait_events wON (a.wait_event_type = w.type AND a.wait_event = w.name) WHERE backend_type= 'client backend' AND wait_event IS NOT NULL ORDER BY wait_event DESC LIMIT 4 \gxLe processus de la ligne 2 attend une synchronisation sur disque du
journal de transaction (WAL), et les deux suivants une lecture d’un
fichier de données. (La description vide en ligne 1 est un souci de la
version 17.2).
Pour entrer dans le détail des champs liés aux connexions :
backend_type est le type de processus : on filtrera
généralement sur client backend, mais on y trouvera aussi
des processus de tâche de fond comme checkpointer,
walwriter, autovacuum launcher et autres
processus de PostgreSQL, ou encore des workers lancés par des
extensions ;datname est le nom de la base à laquelle la session est
connectée, et datid est son identifiant (OID) ;pid est le processus du backend , c’est-à-dire
du processus PostgreSQL chargé de discuter avec le client, qui durera le
temps de la session (sauf parallélisation) ;usename est le nom de l’utilisateur connecté, et
usesysid est son OID dans pg_roles ;application_name est un nom facultatif, et il est
recommandé que l’application cliente le renseigne autant que possible
avec SET application_name TO 'nom_outil_client' ;client_addr est l’adresse IP du client connecté
(NULL si connexion sur socket Unix), et
client_hostname est le nom associé à cette IP, renseigné
uniquement si log_hostname a été passé à on
(cela peut ralentir les connexions à cause de la résolution DNS) ;client_port est le numéro de port sur lequel le client
est connecté, toujours s’il s’agit d’une connexion IP.
Une requête parallélisée occupe plusieurs processus, et apparaîtra
sur plusieurs lignes de pid différents. Le champ
leader_pid indique le processus principal. Les autres
processus disparaîtront dès la requête terminée.
Pour les champs liés aux durées de session, transactions et requêtes
:
backend_start est le timestamp de l’établissement de la
session ;xact_start est le timestamp de début de la
transaction ;query_start est le timestamp de début de la requête en
cours, ou de la dernière requête exécutée ;status vaut soit active, soit
idle (la session ne fait rien) soit
idle in transaction (en attente pendant une transaction) ;
backend_xid est l’identifiant de la transaction en
cours, s’il y en a une ;backend_xmin est l’horizon des transactions visibles,
et dépend aussi des autres transactions en cours.
Rappelons qu’une session durablement en statut
idle in transaction bloque le fonctionnement de
l’autovacuum car backend_xmin est bloqué. Cela peut mener à
des tables fragmentées et du gaspillage de place disque.
Depuis PostgreSQL 14, pg_stat_activity peut afficher un
champ query_id, c’est-à-dire un identifiant de requête
normalisée (dépouillée des valeurs de paramètres). Il faut que le
paramètre compute_query_id soit à on ou
auto (le défaut, et alors une extension peut l’activer). Ce
champ est utile pour retrouver une requête dans la vue de l’extension
pg_stat_statements, par exemple.
Certains champs de cette vue ne sont renseignés que si le paramètre
track_activities est à on (valeur par défaut,
qu’il est conseillé de laisser ainsi).
À noter qu’il ne faut pas interroger pg_stat_activity au
sein d’une transaction, son contenu pourrait sembler figé.
Verrous :
pg_locks permet de voir les verrous posés sur les objets
(principalement les relations comme les tables et les index). Le
processus (pid) est la clé commune pour la rapprocher de
pg_stat_activity.
Archivage et réplication :
pg_stat_archiver donne des informations sur l’archivage
des journaux de transaction et notamment sur les erreurs d’archivage.
L’exemple suivant montre un archivage en erreur :
TABLE pg_stat_archiver \gxpg_stat_replication donne des informations sur les
serveurs secondaires connectés. Les statistiques sur les conflits entre
application de la réplication et requêtes en lecture seule sont
disponibles dans pg_stat_database_conflicts.
Si les réplications se font par des slots de réplication (optionnels
en réplication physique), pg_stat_replication_slots donne
des informations sur leur état et leur retard.
Autres vues :
Des vues plus spécialisées existent :
pg_stat_ssl donne des informations sur les connexions
SSL : version SSL, suite de chiffrement, nombre de bits pour
l’algorithme de chiffrement, compression, Distinguished Name (DN) du
certificat client.
pg_stat_progress_vacuum,
pg_stat_progress_analyze,
pg_stat_progress_create_index,
pg_stat_progress_cluster,
pg_stat_progress_basebackup et
pg_stat_progress_copy donnent respectivement des
informations sur la progression des VACUUM, des
ANALYZE, des créations d’index, des commandes de
VACUUM FULL et CLUSTER, de la commande de
réplication BASE BACKUP et des COPY.
pg_stat_slru permet de suivre les accès à différents
petits caches internes de PostgreSQL (à partir de PostgreSQL 17).
Réinitialisation :
Ces vues contiennent des compteurs cumulatifs. L’évolution en
fonction du temps est souvent gérée par les nombreux outils qui font
appel à ces vues. Il existe une fonction pour réinitialiser les
compteurs de certaines vues (pas toutes) :
SELECT pg_stat_reset_shared('archiver' ) ;
