Logs et monitoring – Surveiller et diagnostiquer PostgreSQL

Bonne lecture et bon apprentissage !
Junior TSAFACK – 20/08/2026
⏱️ Temps de lecture estimé : 10 minutes

---

La surveillance et les logs sont des éléments essentiels pour maintenir une base de données PostgreSQL en bonne santé. Ils permettent de détecter les problèmes de performance, de diagnostiquer les erreurs et de suivre l’activité des utilisateurs. Ce cours vous présente les paramètres de logging, les outils de monitoring intégrés et les bonnes pratiques pour une supervision efficace.

---

Distinction : Logs vs WAL

Avant d’entrer dans le détail, clarifions une confusion fréquente :

Type	Rôle
Logs (journaux d’événements)	Enregistrent les événements du serveur : connexions, erreurs, requêtes lentes. Servent à l’administration et au diagnostic.
WAL (Write-Ahead Log)	Journaux de transactions qui garantissent la durabilité des données et permettent la récupération après crash.

💡 Bon à savoir : Les logs sont dans pg_log/ (ou log_directory), les WAL dans pg_wal/. Ne les confondez pas !

---

Configuration des logs

Paramètres essentiels

Tous les paramètres de logs sont dans postgresql.conf (ou postgresql.auto.conf).

Paramètre	Description	Valeur recommandée
log_destination	Destination des logs (stderr, csvlog, syslog, eventlog)	'csvlog' ou 'stderr'
logging_collector	Active le collecteur de logs (indispensable pour logger dans des fichiers)	on
log_directory	Répertoire des logs (relatif à PGDATA)	'pg_log'
log_filename	Format du nom des fichiers (strftime)	'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation	Écrase les fichiers existants lors de la rotation	on
log_rotation_age	Durée avant rotation	1d
log_rotation_size	Taille avant rotation	100MB
log_line_prefix	Préfixe des lignes de log (informations ajoutées)	'%m [%p] %q%u@%d '

Préfixe de ligne (log_line_prefix)

Les codes % permettent d’ajouter des informations contextuelles :

Code	Signification	Exemple
%m	Timestamp avec millisecondes	2024-01-15 14:30:25.123
%p	Process ID (PID)	12345
%u	Nom d’utilisateur	jean_dupont
%d	Nom de la base de données	magasin
%a	Nom de l’application	psql
%h	Adresse IP du client	192.168.1.100
%r	Adresse IP + port	192.168.1.100:54321
%q	Ne produit rien (utile pour le formatage)	-

Exemple complet :

log_line_prefix = '%m [%p] %q%u@%d [%a] %h '
# Résultat : 2024-01-15 14:30:25.123 [12345] jean_dupont@magasin [psql] 192.168.1.100

---

Activer le logging des requêtes

log_statement – Quelles requêtes logger ?

Valeur	Description
none	Ne logge que les erreurs.
ddl	Logge les commandes DDL (CREATE, ALTER, DROP, etc.).
mod	Logge les commandes DDL et les modifications de données (INSERT, UPDATE, DELETE, TRUNCATE).
all	Logge toutes les requêtes (y compris SELECT). ⚠️ Impact sur les performances !

Exemple de configuration :

log_statement = 'mod'

⚠️ Attention : log_statement = 'all' peut générer d’énormes fichiers de logs et ralentir le serveur. Utilisez-le uniquement pour du debugging ponctuel.

Debugging ponctuel

Pour activer temporairement le logging de toutes les requêtes pour une base ou un utilisateur spécifique :

-- Logguer toutes les requêtes sur une base spécifique
ALTER DATABASE magasin SET log_statement = 'all';

-- Logguer toutes les requêtes pour un utilisateur spécifique
ALTER USER xavier IN DATABASE magasin SET log_statement = 'all';

-- Réinitialiser
ALTER DATABASE magasin RESET log_statement;
ALTER USER xavier RESET log_statement;

---

Logging des connexions et déconnexions

log_connections = on
log_disconnections = on

Ces paramètres enregistrent chaque connexion et déconnexion, avec la durée de la session.

Exemple de log :

2024-01-15 14:30:25.123 [12345] LOG:  connection received: host=192.168.1.100 port=54321
2024-01-15 14:30:25.456 [12345] LOG:  connection authorized: user=jean_dupont database=magasin
2024-01-15 14:30:30.789 [12345] LOG:  disconnection: session time: 0:00:05.333 user=jean_dupont database=magasin host=192.168.1.100 port=54321

---

Logging des requêtes lentes

Les requêtes lentes sont une des causes principales de mauvaise performance. PostgreSQL permet de les identifier facilement.

log_min_duration_statement

Définit le seuil (en millisecondes) au-delà duquel une requête est loggée.

-- Loguer les requêtes de plus de 500 ms
log_min_duration_statement = 500

-- Loguer toutes les requêtes (seuil = 0)
log_min_duration_statement = 0

-- Ne rien logger (valeur par défaut)
log_min_duration_statement = -1

💡 Bon à savoir : Ce paramètre est indépendant de log_statement. Même si log_statement = 'none', les requêtes lentes seront loggées.

Définir un seuil personnalisé pour une session

-- Pour la session courante
SET log_min_duration_statement = 1000;

-- Pour un utilisateur spécifique
ALTER USER xavier SET log_min_duration_statement = 1000;

-- Pour une base spécifique
ALTER DATABASE magasin SET log_min_duration_statement = 500;

---

Logging des checkpoints et du VACUUM

-- Logguer les checkpoints (utile pour surveiller l'écriture sur disque)
log_checkpoints = on

-- Logguer les opérations de VACUUM
log_autovacuum_min_duration = 1000  -- Logguer les VACUUM de plus de 1 seconde

-- Logguer les verrous (deadlocks)
log_lock_waits = on
deadlock_timeout = 5s

---

Rotation des logs

Rotation automatique (recommandée)

log_truncate_on_rotation = on
log_rotation_age = 1d           -- Rotation quotidienne
log_rotation_size = 100MB       -- Rotation à 100 Mo

Rotation externe avec logrotate

Si vous utilisez une rotation externe (ex : logrotate), désactivez la rotation interne :

log_rotation_age = 0
log_rotation_size = 0

Exemple de configuration logrotate pour PostgreSQL :

/var/log/postgresql/postgresql-*.log {
    daily
    rotate 30
    compress
    delaycompress
    missingok
    notifempty
    create 640 postgres postgres
    sharedscripts
    postrotate
        /usr/bin/pg_ctlcluster 16 main reload
    endscript
}

---

Analyse des logs

Exemple de log complet

2024-01-15 14:30:25.123 UTC [12345] LOG:  connection received: host=192.168.1.100 port=54321
2024-01-15 14:30:25.456 UTC [12345] LOG:  connection authorized: user=jean_dupont database=magasin
2024-01-15 14:30:25.789 UTC [12345] LOG:  statement: SELECT * FROM commandes WHERE client_id = 12345;
2024-01-15 14:30:25.900 UTC [12345] LOG:  duration: 111.234 ms  statement: SELECT * FROM commandes WHERE client_id = 12345;

Outils d’analyse

Outil	Description
grep et awk	Analyse manuelle des logs (Linux).
pgBadger	Outil d’analyse de logs (rapport HTML complet).
pgaudit	Extension pour un audit détaillé des accès.
Logstash + Elasticsearch	Centralisation des logs pour une analyse avancée.

Exemple avec pgBadger

# Installer pgBadger
sudo apt install pgbadger

# Analyser un fichier de log
pgbadger /var/log/postgresql/postgresql-*.log

# Générer un rapport HTML
pgbadger -o rapport.html /var/log/postgresql/postgresql.log

---

Monitoring avec les vues système

PostgreSQL expose de nombreuses vues système pour surveiller l’état du serveur.

1. pg_stat_activity – Sessions actives

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

Colonnes importantes :

Colonne	Description
pid	Process ID de la session.
usename	Utilisateur connecté.
application_name	Nom de l’application.
client_addr	Adresse IP du client.
state	État de la session : active, idle, idle in transaction.
query	Requête en cours (tronquée).
backend_start	Début de la session.
query_start	Début de la requête.

2. pg_stat_database – Statistiques par base

SELECT
    datname,
    numbackends,
    xact_commit,
    xact_rollback,
    blks_read,
    blks_hit,
    tup_returned,
    tup_fetched,
    tup_inserted,
    tup_updated,
    tup_deleted
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1');

Indicateurs clés :

Colonne	Indicateur
numbackends	Nombre de connexions actives.
xact_commit / xact_rollback	Ratio de validations/annulations (un taux de rollback élevé = problèmes).
blks_read / blks_hit	Ratio de cache (hit ratio). Si blks_read est élevé, augmentez shared_buffers.
tup_returned / tup_fetched	Efficacité des requêtes.

3. pg_stat_user_tables – Statistiques par table

SELECT
    schemaname,
    relname,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    n_tup_ins,
    n_tup_upd,
    n_tup_del,
    n_live_tup,
    n_dead_tup
FROM pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 10;

Indicateurs :

• seq_scan élevé = pas d’index (ou index inutilisable).
• n_dead_tup élevé = besoin de VACUUM.
• n_tup_upd / n_tup_del élevé = tables avec beaucoup de modifications.

4. pg_stat_user_indexes – Statistiques par index

SELECT
    schemaname,
    relname,
    indexrelname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC
LIMIT 10;

5. pg_stat_bgwriter – Statistiques du background writer

SELECT
    checkpoints_timed,
    checkpoints_req,
    buffers_checkpoint,
    buffers_clean,
    maxwritten_clean,
    buffers_backend,
    buffers_backend_fsync,
    buffers_alloc
FROM pg_stat_bgwriter;

Indicateurs :

• checkpoints_req élevé = checkpoints demandés (pas planifiés) = problème.
• buffers_clean / maxwritten_clean = performances de l’écriture.

---

Vues de monitoring avancées

pg_locks – Verrous en cours

SELECT
    locktype,
    database,
    relation,
    page,
    tuple,
    virtualxid,
    transactionid,
    classid,
    objid,
    objsubid,
    virtualtransaction,
    pid,
    mode,
    granted
FROM pg_locks
WHERE NOT granted;

Cette requête affiche les verrous non accordés (en attente), utiles pour diagnostiquer des deadlocks.

pg_wal_lsn – Position dans les WAL

-- Position actuelle
SELECT pg_current_wal_lsn();

-- Comparer la réplication
SELECT
    pg_current_wal_lsn() AS current,
    pg_last_wal_receive_lsn() AS received,
    pg_last_wal_replay_lsn() AS replayed;

pg_stat_replication – État de la réplication

SELECT
    application_name,
    client_addr,
    state,
    sync_state,
    replay_lag
FROM pg_stat_replication;

---

Bonnes pratiques de logging et monitoring

Pratique	Description
Ne pas logger toutes les requêtes en production	Utilisez log_statement = 'none' et log_min_duration_statement pour les requêtes lentes.
Utiliser csvlog	Le format CSV est plus facile à analyser avec des outils externes.
Configurer la rotation des logs	Évitez que les logs n’occupent tout l’espace disque.
Surveiller le ratio de cache	blks_hit / (blks_hit + blks_read) doit être > 90 %.
Vérifier régulièrement les requêtes lentes	Utilisez log_min_duration_statement pour identifier les goulots.
Surveiller les deadlocks	Activez log_lock_waits et deadlock_timeout.
Centraliser les logs	Utilisez des outils comme ELK, Prometheus ou Grafana.
Analysez périodiquement les logs	Utilisez pgBadger pour des rapports automatiques.
Ne pas oublier le monitoring système	Surveillez CPU, RAM, disque, réseau en complément.

---

Exemple de configuration de logs optimisée

postgresql.conf :

# Logging
logging_collector = on
log_destination = 'csvlog'
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
log_truncate_on_rotation = on

# Préfixe des lignes
log_line_prefix = '%m [%p] %q%u@%d '

# Connexions
log_connections = on
log_disconnections = on

# Requêtes lentes
log_min_duration_statement = 1000

# Checkpoints et VACUUM
log_checkpoints = on
log_autovacuum_min_duration = 1000

# Verrous
log_lock_waits = on
deadlock_timeout = 5s

# Ne pas logger toutes les requêtes (économie de performances)
log_statement = 'none'

---

Commandes récapitulatives

Action	Commande
Afficher les sessions actives	SELECT * FROM pg_stat_activity WHERE state = 'active';
Annuler une requête	SELECT pg_cancel_backend(PID);
Terminer une session	SELECT pg_terminate_backend(PID);
Afficher les statistiques des bases	SELECT * FROM pg_stat_database;
Afficher les stats des tables	SELECT * FROM pg_stat_user_tables;
Afficher les stats des index	SELECT * FROM pg_stat_user_indexes;
Afficher les verrous en attente	SELECT * FROM pg_locks WHERE NOT granted;
Vérifier l’état de la réplication	SELECT * FROM pg_stat_replication;
Activer le logging pour une session	SET log_statement = 'all';
Activer le logging pour un utilisateur	ALTER USER nom SET log_statement = 'all';
Lire le fichier de log	tail -f /var/log/postgresql/postgresql-*.log

---

Prochain chapitre

Vous savez maintenant configurer les logs et monitorer votre base de données. Dans le prochain cours, nous aborderons la sauvegarde et la restauration, un élément critique pour la protection de vos données.

👉 Cours 11 : Sauvegarde et restauration

---

Junior TSAFACK – 20/08/2026