🔝 Retour au Sommaire

14.7.1. pgBadger : Analyse de logs

Introduction

pgBadger est un outil open-source d'analyse de logs PostgreSQL qui transforme les fichiers journaux (logs) bruts et souvent difficiles à lire en rapports HTML visuels et interactifs. C'est l'un des outils les plus populaires de l'écosystème PostgreSQL pour comprendre ce qui se passe dans votre base de données.

Pourquoi analyser les logs ?

Les logs PostgreSQL contiennent une mine d'informations sur l'activité de votre base de données :

Les requêtes les plus lentes
Les erreurs et problèmes rencontrés
Les connexions et déconnexions
Les opérations de maintenance (VACUUM, ANALYZE)
Les checkpoints et leur durée
Les verrous (locks) et contentions

Cependant, ces logs sont :

Volumineux : Des milliers, voire millions de lignes par jour
Non structurés : Format texte difficile à parcourir
Techniques : Nécessitent une expertise pour être interprétés

pgBadger résout ce problème en analysant automatiquement ces logs et en générant des rapports visuels compréhensibles.

Qu'est-ce que pgBadger ?

Définition

pgBadger (PostgreSQL Badger) est un analyseur de logs écrit en Perl qui :

Lit les fichiers de logs PostgreSQL
Parse (analyse syntaxiquement) les entrées
Agrège les statistiques
Génère un rapport HTML interactif avec graphiques et tableaux

Caractéristiques principales

Caractéristique	Description
Performance	Analyse très rapide, même sur des logs de plusieurs Go
Zéro dépendance	Pas de base de données nécessaire pour l'analyse
Open Source	Licence PostgreSQL (libre et gratuit)
Multi-format	Supporte différents formats de logs PostgreSQL
Incrémental	Peut analyser uniquement les nouvelles entrées
Multi-plateforme	Fonctionne sur Linux, Windows, macOS

Pourquoi "Badger" (blaireau) ?

Le nom fait référence au blaireau, un animal fouisseur qui creuse pour trouver ce qui est caché. De la même manière, pgBadger "fouille" dans les logs pour extraire des informations précieuses cachées dans des milliers de lignes de texte.

Comment fonctionne pgBadger ?

Architecture et processus

┌─────────────────────┐
│  Fichiers de logs   │
│    PostgreSQL       │
│  (postgresql.log)   │
└──────────┬──────────┘
           │
           │ Lecture
           ▼
┌─────────────────────┐
│     pgBadger        │
│   (Analyseur Perl)  │
│                     │
│  • Parsing          │
│  • Agrégation       │
│  • Calcul stats     │
└──────────┬──────────┘
           │
           │ Génération
           ▼
┌─────────────────────┐
│  Rapport HTML       │
│  (out.html)         │
│                     │
│  • Graphiques       │
│  • Tableaux         │
│  • Statistiques     │
└─────────────────────┘

Les étapes d'analyse

Lecture des logs : pgBadger ouvre et lit les fichiers de logs PostgreSQL (peut traiter plusieurs fichiers simultanément)
Parsing des entrées : Chaque ligne de log est analysée et décortiquée pour extraire :
- Le timestamp (date et heure)
- Le type d'événement (requête, erreur, connexion...)
- Le texte de la requête SQL
- La durée d'exécution
- L'utilisateur et la base de données concernés
- Les messages d'erreur éventuels
Agrégation des données : Les informations sont regroupées et calculées :
- Requêtes les plus fréquentes
- Requêtes les plus lentes
- Temps total passé par type de requête
- Distribution temporelle de l'activité
Génération du rapport : Un fichier HTML est créé avec :
- Des graphiques interactifs (courbes, camemberts)
- Des tableaux triables
- Des statistiques détaillées
- Des liens pour naviguer rapidement

Configuration préalable de PostgreSQL

Pour que pgBadger puisse analyser efficacement les logs, PostgreSQL doit être correctement configuré.

Paramètres essentiels dans postgresql.conf

1. Activer la journalisation

# Activer la journalisation
logging_collector = on

# Destination des logs
log_destination = 'stderr'

# Répertoire des logs
log_directory = 'log'

# Nom des fichiers de logs avec timestamp
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

2. Configurer le format des logs

# Format de ligne recommandé pour pgBadger
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

# Alternative plus simple (mais moins complète)
# log_line_prefix = '%t [%p]: '

Explication des marqueurs :

%t : Timestamp (date et heure)
%p : Process ID (PID)
%l : Numéro de ligne de log
%u : Nom d'utilisateur
%d : Nom de la base de données
%a : Nom de l'application
%h : Hostname/IP du client

3. Logger les requêtes lentes

# Logger les requêtes qui prennent plus de 100ms
log_min_duration_statement = 100

# Inclure la durée d'exécution
log_duration = off  # Déjà inclus avec log_min_duration_statement

4. Logger les erreurs et connexions

# Logger les erreurs
log_error_verbosity = default

# Logger les connexions/déconnexions
log_connections = on  
log_disconnections = on  

# Logger les checkpoints
log_checkpoints = on

# Logger les commandes DDL
log_statement = 'ddl'

Compromis entre détail et volume

⚠️ Attention : Plus vous loggez d'informations, plus les fichiers de logs seront volumineux.

Niveau de logging	Volume de logs	Utilité
Minimal	Faible	Erreurs seulement, peu d'insights
Modéré	Moyen	Requêtes lentes (>100ms), erreurs, connexions
Détaillé	Important	Toutes les requêtes, debugging
Verbeux	Très important	Tout logger (développement uniquement)

Recommandation pour la production : Niveau modéré avec log_min_duration_statement entre 100ms et 500ms selon votre contexte.

Les rapports pgBadger

Structure d'un rapport

Un rapport pgBadger typique contient plusieurs sections :

1. Vue d'ensemble (Overall stats)

Statistiques globales sur la période analysée :

Nombre total de requêtes
Nombre de connexions
Nombre d'erreurs
Taille totale des logs analysés
Période couverte

2. Connexions (Connections)

Graphique temporel montrant :

Le nombre de connexions par heure/jour
Les pics d'activité
Les bases de données les plus sollicitées
Les utilisateurs les plus actifs

Exemple d'insight : "Pic de connexions tous les jours à 9h00 → démarrage des applications métier"

3. Sessions

Statistiques sur les sessions :

Durée moyenne des sessions
Sessions les plus longues
Nombre de sessions par utilisateur/base

4. Checkpoints

Informations sur les checkpoints PostgreSQL :

Fréquence des checkpoints
Durée des checkpoints
Checkpoints planifiés vs forcés (trop de checkpoints forcés = problème de configuration)

Indicateur de santé : Un ratio élevé de checkpoints forcés peut indiquer que max_wal_size est trop petit.

5. Requêtes (Queries)

C'est la section la plus importante pour l'optimisation :

Top des requêtes les plus lentes (Slowest queries)

Tableau listant les requêtes triées par temps d'exécution :

Requête SQL normalisée (paramètres remplacés par $1, $2...)
Temps d'exécution moyen
Temps d'exécution maximum
Nombre d'occurrences

Exemple :

Requête                                          | Temps moyen | Temps max | Occurrences
-------------------------------------------------|-------------|-----------|------------
SELECT * FROM orders WHERE user_id = $1         | 2.3s        | 8.1s      | 1,245  
UPDATE products SET stock = stock - $1 WHERE... | 1.8s        | 5.2s      | 892

Top des requêtes les plus fréquentes (Most frequent)

Requêtes exécutées le plus souvent :

Identifie les points chauds de votre application
Permet de prioriser les optimisations (impact maximal)

Top des requêtes consommant le plus de temps (Time consuming)

Calcul du temps cumulé : temps moyen × nombre d'exécutions

Exemple de stratégie d'optimisation :

Requête A : 5s en moyenne, exécutée 10 fois → 50s au total
Requête B : 0.5s en moyenne, exécutée 1000 fois → 500s au total

→ Optimiser la requête B en priorité, même si elle est individuellement plus rapide !

6. Erreurs (Errors)

Liste des erreurs rencontrées :

Erreurs de syntaxe SQL
Violations de contraintes
Deadlocks
Timeouts
Permissions refusées

Permet d'identifier rapidement les problèmes applicatifs.

7. Locks (Verrous)

Si activé dans les logs :

Verrous en attente
Deadlocks détectés
Durée d'attente sur verrous

8. Temporary files

Fichiers temporaires créés :

Identifie les requêtes qui ont besoin de plus de work_mem
Signale un potentiel problème de configuration

9. Vaccum et Autovacuum

Statistiques sur les opérations de maintenance :

Tables les plus souvent "vacuum"
Durée des opérations
Tables en retard de VACUUM

Graphiques et visualisations

pgBadger génère de nombreux graphiques interactifs :

Graphiques temporels : Courbes montrant l'évolution dans le temps
- Nombre de requêtes par heure
- Charge CPU estimée
- Activité de lecture/écriture
Graphiques en camembert : Distribution
- Répartition des requêtes par type (SELECT, INSERT, UPDATE, DELETE)
- Répartition par base de données
- Répartition par utilisateur
Histogrammes : Distribution statistique
- Distribution des temps de réponse
- Distribution des tailles de résultats

Navigation dans le rapport

Le rapport HTML est interactif :

Sommaire cliquable en haut de page
Tableaux triables : Cliquez sur les en-têtes de colonne
Liens vers les requêtes : Cliquez sur une requête pour voir les détails
Filtres : Possibilité de filtrer par base, utilisateur, etc.

Cas d'usage pratiques de pgBadger

1. Diagnostic de performance

Problème : "L'application est lente depuis hier"

Approche avec pgBadger :

Générer un rapport sur les dernières 24h
Consulter la section "Slowest queries"
Identifier les requêtes qui ont un temps d'exécution anormal
Analyser le plan d'exécution de ces requêtes (EXPLAIN ANALYZE)
Ajouter des index ou réécrire les requêtes

Exemple de découverte : Une requête qui faisait 50ms fait maintenant 5s → La table a grossi et un index est manquant.

2. Identification des points chauds

Objectif : Savoir quelles parties de l'application sollicitent le plus la base

Approche :

Regarder "Most frequent queries"
Identifier les patterns répétitifs
Envisager la mise en cache applicative pour ces requêtes
Optimiser en priorité les requêtes les plus fréquentes

Exemple :

-- Cette requête est appelée 50,000 fois par jour
SELECT user_id, username, email FROM users WHERE user_id = $1;

→ Solution : Implémenter un cache Redis avec TTL de 1h

3. Audit de sécurité

Utilisation : Identifier les tentatives de connexion suspectes

Ce que pgBadger révèle :

Connexions échouées répétées (force brute ?)
Connexions depuis des IP inattendues
Requêtes avec erreurs de permission
Tentatives d'injection SQL (erreurs de syntaxe suspectes)

4. Planification de capacité

Question : "Avons-nous besoin de plus de ressources ?"

Analyse avec pgBadger :

Consulter les graphiques d'activité temporelle
Identifier les heures de pointe
Évaluer le taux de croissance du trafic
Anticiper les besoins futurs

Exemple de pattern :

Connexions simultanées :
- 08h-09h : 50 connexions
- 09h-12h : 200 connexions (pic)
- 12h-14h : 80 connexions
- 14h-18h : 180 connexions

→ Le serveur doit supporter 200+ connexions. Si limite actuelle = 150, il faut augmenter max_connections.

5. Validation après optimisation

Processus :

Générer un rapport pgBadger avant optimisation
Appliquer les optimisations (index, réécriture, configuration)
Générer un nouveau rapport pgBadger après optimisation
Comparer les métriques :
- Temps d'exécution moyen
- Temps cumulé
- Nombre de requêtes lentes

Méthode de comparaison :

Sauvegarder les rapports avec des noms datés : rapport_2025_11_21_avant.html, rapport_2025_11_28_apres.html
Comparer visuellement les sections clés
Quantifier l'amélioration : "Temps moyen passé de 2.5s à 0.3s → 88% plus rapide"

6. Troubleshooting des erreurs

Scénario : "Des utilisateurs signalent des erreurs intermittentes"

Investigation :

Ouvrir la section "Errors" du rapport
Trier par nombre d'occurrences
Identifier les erreurs les plus fréquentes

Exemples d'erreurs et solutions :

Erreur : "deadlock detected"
→ Problème de concurrence, revoir la logique transactionnelle

Erreur : "could not serialize access due to concurrent update"
→ Isolation niveau Serializable trop strict, envisager Read Committed

Erreur : "temporary file size exceeds temp_file_limit"
→ Requêtes trop gourmandes, augmenter work_mem ou optimiser

Bonnes pratiques d'utilisation

1. Analyser régulièrement

Fréquence recommandée :

Quotidien : Environnement de production critique
Hebdomadaire : Environnement de production standard
Mensuel : Environnement de développement/staging

Automatisation : Configurer un cron job qui :

Lance pgBadger chaque nuit sur les logs de la veille
Archive le rapport HTML
Envoie une notification si des anomalies sont détectées

2. Conserver un historique

Pourquoi ?

Comparer les performances dans le temps
Identifier les régressions
Correler avec des événements (déploiements, montées de version)

Stratégie de rétention :

Rapports quotidiens : 30 jours
Rapports hebdomadaires : 12 semaines
Rapports mensuels : 12 mois

3. Définir des seuils d'alerte

Créer une checklist de revue :

Métrique	Seuil d'alerte	Action
Requêtes > 1s	> 100/jour	Investigation immédiate
Checkpoints forcés	> 10% du total	Augmenter `max_wal_size`
Erreurs	> 50/jour	Audit de code
Connexions échouées	> 20/jour	Vérifier sécurité
Temp files	> 1GB/jour	Revoir `work_mem`

4. Partager les rapports

Qui doit consulter les rapports ?

Développeurs : Identifier les requêtes à optimiser dans leur code
DevOps/SRE : Surveiller la santé globale du système
DBA : Tuning de configuration et maintenance
Product Managers : Comprendre les patterns d'utilisation

Méthode : Héberger les rapports HTML sur un serveur web interne accessible à l'équipe.

5. Corréler avec d'autres métriques

pgBadger ne fonctionne pas en isolation. Combinez avec :

Métriques système : CPU, RAM, I/O (via pg_stat_kcache, Prometheus)
Métriques applicatives : Temps de réponse, taux d'erreur (APM)
Logs applicatifs : Identifier quelle partie du code génère les requêtes lentes

Exemple de corrélation :

pgBadger → Pic de requêtes lentes à 14h37  
Monitoring système → Pic I/O à 14h37  
Logs applicatifs → Job de synchronisation lancé à 14h35  
→ Conclusion : Le job de synchro sature la base

Limites et considérations

Limites de pgBadger

Dépend de la configuration des logs
- Si PostgreSQL ne log pas assez d'informations, pgBadger ne peut pas les analyser
- Nécessite log_line_prefix bien configuré
Analyse post-mortem uniquement
- pgBadger analyse les logs a posteriori
- Ne fournit pas de monitoring temps réel
- Délai entre l'événement et l'analyse
Pas de recommandations automatiques
- pgBadger montre les problèmes, mais ne les résout pas
- L'interprétation et les actions correctives sont à la charge de l'administrateur
Volumétrie
- Sur de très gros logs (>10GB), l'analyse peut être longue
- Consommation mémoire proportionnelle à la taille des logs
Requêtes normalisées
- Les paramètres sont remplacés par $1, $2...
- On perd la visibilité sur les valeurs spécifiques
- Impossible de savoir quels utilisateurs ou ID posent problème

Quand utiliser des alternatives ?

Besoin	Alternative à pgBadger
Monitoring temps réel	pg_stat_statements, Prometheus + Grafana
Alerting automatique	Prometheus Alertmanager, Datadog
Analyse de requêtes en direct	pg_stat_activity, pg_stat_statements
Profiling détaillé	auto_explain, EXPLAIN ANALYZE
APM complet	New Relic, Datadog APM, Sentry

Complémentarité des outils

pgBadger fait partie d'une stack d'observabilité complète :

┌──────────────────────────────────────────────────┐
│              Stack d'observabilité               │
├──────────────────────────────────────────────────┤
│ Temps réel    → pg_stat_statements, Grafana      │
│ Historique    → pgBadger                         │
│ Profiling     → EXPLAIN, auto_explain            │
│ Système       → pg_stat_kcache, node_exporter    │
│ Applicatif    → APM, Sentry                      │
│ Alerting      → Prometheus Alertmanager          │
└──────────────────────────────────────────────────┘

Utiliser pgBadger en complément, pas en remplacement des autres outils.

Impact sur les performances

La journalisation a-t-elle un coût ?

Oui, mais généralement acceptable en production.

Impact de log_min_duration_statement

Valeur	Impact CPU	Impact I/O	Volume logs
`0` (tout)	~5-10%	Important	Très élevé
`100ms`	~1-2%	Faible	Modéré
`1000ms`	<1%	Très faible	Faible
`off`	0%	0%	Minimal

Recommandation : Commencer à 500ms et ajuster selon le contexte.

Impact de l'analyse par pgBadger

Pas d'impact sur PostgreSQL : pgBadger analyse les fichiers de logs hors ligne
Ressources utilisées : CPU et mémoire de la machine où pgBadger s'exécute
Peut être exécuté sur un serveur séparé : Copier les logs et analyser ailleurs

Optimiser l'analyse pgBadger

Pour de très gros logs :

Mode incrémental : Analyser seulement les nouveaux logs, pas tout le fichier à chaque fois
Analyse parallèle : pgBadger peut utiliser plusieurs cœurs CPU
Filtrage préalable : Analyser seulement certaines bases ou périodes
Compression : Les logs peuvent être compressés, pgBadger supporte gzip, bzip2, xz

pgBadger dans le workflow DevOps

Intégration dans le cycle de développement

1. Développement

Analyser les logs de l'environnement de dev
Identifier les requêtes problématiques avant la mise en production
Valider que les optimisations fonctionnent

2. Staging

Générer des rapports après les tests de charge
Comparer avec les rapports de production
Anticiper les problèmes de performance

3. Production

Rapports automatiques quotidiens
Alertes sur les anomalies
Audit régulier des performances

4. Post-mortem

Après un incident :

Analyser les logs de la période de l'incident
Identifier la cause racine
Documenter dans le post-mortem
Définir des actions préventives

Exemple de workflow automatisé

#!/bin/bash
# Script quotidien de génération de rapport pgBadger

# 1. Définir les chemins
LOG_DIR="/var/log/postgresql"  
REPORT_DIR="/var/www/pgbadger"  
DATE=$(date +%Y-%m-%d)  

# 2. Analyser les logs de la veille
pgbadger \
  --incremental \
  --outdir $REPORT_DIR \
  --prefix '%t [%p] %u@%d ' \
  $LOG_DIR/postgresql-$DATE*.log

# 3. Vérifier les seuils d'alerte
SLOW_QUERIES=$(grep -c "duration: [0-9]\{4,\}" $LOG_DIR/postgresql-$DATE*.log)

if [ $SLOW_QUERIES -gt 100 ]; then
  # Envoyer une alerte
  echo "Alert: $SLOW_QUERIES slow queries detected" | mail -s "pgBadger Alert" dba@example.com
fi

# 4. Archiver les anciens rapports (> 30 jours)
find $REPORT_DIR -name "*.html" -mtime +30 -delete

Comparaison avec d'autres outils

pgBadger vs pg_stat_statements

Critère	pgBadger	pg_stat_statements
Type	Analyse de logs	Extension PostgreSQL
Temps réel	Non (post-mortem)	Oui
Installation	Externe	Intégrée
Impact	Zéro sur PG	Léger (~2%)
Richesse	Très complet	Statistiques seulement
Historique	Illimité (logs)	Limité (mémoire)
Visualisation	HTML interactif	Requêtes SQL

Complémentarité : Utiliser pg_stat_statements pour le temps réel, pgBadger pour l'analyse approfondie.

pgBadger vs Grafana/Prometheus

Critère	pgBadger	Prometheus/Grafana
Setup	Simple (1 binaire)	Complexe (stack complète)
Temps réel	Non	Oui
Requêtes SQL	Oui, détaillées	Non (métriques agrégées)
Historique	Excellent	Bon (rétention configurable)
Alerting	Non	Oui
Coût	Gratuit	Gratuit (open-source)

Complémentarité : Prometheus/Grafana pour le monitoring global, pgBadger pour l'audit SQL détaillé.

pgBadger vs APM (New Relic, Datadog)

Critère	pgBadger	APM
Scope	Base de données	Applicatif + DB
Coût	Gratuit	Payant ($$)
Détail SQL	Excellent	Bon
Traçage distribué	Non	Oui
Corrélation app/DB	Non	Oui

Complémentarité : APM pour une vue end-to-end, pgBadger pour un focus database approfondi.

Ressources et documentation

Documentation officielle

Site web : https://pgbadger.darold.net/
GitHub : https://github.com/darold/pgbadger
Documentation : https://pgbadger.darold.net/documentation.html

Articles et tutoriels

Blog 2ndQuadrant : Séries d'articles sur l'optimisation avec pgBadger
Blog Percona : Best practices PostgreSQL logging
PostgreSQL Wiki : Logging configuration

Communauté

Mailing list : pgsql-general@postgresql.org
Reddit : r/PostgreSQL
Discord : Serveur PostgreSQL francophone et anglophone

Conclusion

Ce qu'il faut retenir

pgBadger est un outil indispensable pour : ✅ Comprendre l'activité de votre base PostgreSQL
✅ Identifier les requêtes à optimiser
✅ Diagnostiquer les problèmes de performance
✅ Auditer la sécurité et les erreurs
✅ Planifier la capacité
✅ Valider les optimisations

Points clés :

📊 Génère des rapports HTML visuels et interactifs
🎯 Gratuit, open-source et performant
🔍 Analyse post-mortem (pas de temps réel)
⚙️ Nécessite une configuration de logs PostgreSQL appropriée
🤝 Complémentaire avec d'autres outils de monitoring

Prochaines étapes

Après avoir compris pgBadger, vous pouvez :

Configurer correctement les logs PostgreSQL
Mettre en place des rapports automatiques quotidiens
Définir des seuils d'alerte adaptés à votre contexte
Combiner avec pg_stat_statements pour une vue complète
Intégrer dans votre workflow DevOps

pgBadger transforme les logs bruts en insights actionnables. C'est un outil simple mais puissant qui doit faire partie de votre boîte à outils PostgreSQL.

Glossaire des termes utilisés

Log : Fichier journal enregistrant les événements d'une application
Parsing : Analyse syntaxique d'un texte structuré
Checkpoint : Point de sauvegarde garantissant la persistance des données
Deadlock : Verrou circulaire bloquant plusieurs transactions
Temp file : Fichier temporaire créé pour des opérations mémoire insuffisantes
work_mem : Mémoire allouée par opération de tri/hash
Bloat : Espace perdu dans les tables/index dû à MVCC
VACUUM : Opération de nettoyage et récupération d'espace
APM : Application Performance Monitoring (surveillance des performances)

⏭️ pg_stat_kcache : Métriques système (CPU, I/O)

FilesExpand file tree

07.1-pgbadger.md

Latest commit

History