🐘 6) Journau& observabilité

Paramètres de base

log_line_prefix

Mettre les infos utiles pour tracer : date, pid, user, db, application, client.

log_line_prefix = '%m [%p] %u@%d app=%a host=%h '

Tu peux ajouter %r pour IP:port ou %e pour SQLSTATE.

Quoi logguer ?

• prod : log_statement = 'none'
• mais log_min_duration_statement = 200ms (OLTP) ou 500ms/1s (OLAP)
• log_lock_waits = on → détection blocages
• log_autovacuum_min_duration = 1000 ms
• log_checkpoints = on

Extrait postgresql.conf

logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_line_prefix = '%m [%p] %u@%d app=%a host=%h '
log_min_duration_statement = 200      # OLTP
log_lock_waits = on
log_checkpoints = on
log_autovacuum_min_duration = 1000

Collecte centralisée

• expédier les logs vers journald / syslog
• ou filebeat → Logstash / OpenSearch
• garder le format PG (pas JSON) si tu veux la compatibilité out-of-box

Pièges

• log_statement = 'all' en prod → disque saturé
• rotation trop faible → trop de petits fichiers
• pas de log_line_prefix → impossible de savoir quelle app

pg_stat_statements

Indispensable : tu sais quelles requêtes consomment le plus (temps total, nb. appels, temps moyen).

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

-- ensuite dans la base
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

auto_explain (QA/pré-prod)

À activer sur QA pour logguer automatiquement les EXPLAIN (ANALYZE, BUFFERS) des requêtes lentes.

shared_preload_libraries = 'pg_stat_statements,auto_explain'

auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_timing = off  -- si overhead

⚠️ en prod, activer seulement pour des durées élevées.

pg_wait_sampling

Permet d’échantillonner les waits (locks, LWLocks, I/O). Très utile pour comprendre les blocages aléatoires.

CREATE EXTENSION IF NOT EXISTS pg_wait_sampling;

SELECT * FROM pg_wait_sampling_profile;

pg_stat_io (PostgreSQL 15+)

Nouvelle vue pour décortiquer les I/O (relation, backend, temp, etc.).

SELECT * FROM pg_stat_io
ORDER BY blk_read_time DESC
LIMIT 20;

Exporter Prometheus

• postgres_exporter (ou pg_exporter)
• en plus : node_exporter sur l’hôte pour corréler CPU/RAM/I/O

Ce qu’il faut voir en 1er

Alertes de base

• P95 requêtes > 1s pendant 5 min
• temp files > 0 pendant 5 min
• WAL lag réplication augmente → alerte
• autovacuum en retard sur certaines tables

Exemples de panneaux Grafana

• “Top 10 slow queries” (requêtes pg_stat_statements par temps total)
• “Autovacuum activity” (nb workers, tables en cours)
• “Checkpoints” (trop rapprochés = mauvais param WAL)
• “Replication lag” (secondes ou WAL distance)
• “IO per tablespace” (si DATA/WAL séparés)

-- gap de réplication côté standby
SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay;

Si tu es en cloud (RDS/Aurora/CloudSQL), récupère l’équivalent métrique et mappe-le dans ton Grafana maison.