Avec l'expansion des activités, la performance et la fiabilité des bases de données deviennent cruciales. Des problèmes comme les requêtes lentes, les conflits de verrouillage et les goulots d'étranglement de ressources peuvent survenir.

En paramétrant la journalisation de PostgreSQL, les administrateurs peuvent superviser efficacement l'activité de la base, identifier les points faibles et optimiser le système. Les logs aident non seulement au dépannage, mais fournissent aussi des détails sur les opérations, permettant aux développeurs et aux équipes d'exploitation d'analyser le comportement des utilisateurs et les performances. Une configuration judicieuse des logs est donc essentielle pour la santé et l'optimisation de PostgreSQL.

Configuration de la journalisation PostgreSQL

Voici les étapes concrètes et des exemples pour paramétrer les logs PostgreSQL et les utiliser afin de surveiller l'activité et d'analyser les performances.

1. Modification du fichier de configuration PostgreSQL

Les paramètres de journalisation se définissent principalement dans le fichier postgresql.conf.

1. Localiser le fichier :
   Sur Debian/Ubuntu, il se trouve souvent dans /etc/postgresql/<version>/main/postgresql.conf. Sur CentOS/RHEL, dans /var/lib/pgsql/<version>/data/postgresql.conf.

2. Éditer le fichier :
   Ouvrez le fichier avec un éditeur de texte et ajustez les paramètres suivants : ```

   Répertoire de destination des logs

   log_directory = 'pg_log'

   Format du nom des fichiers de log

   log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

   Niveau de journalisation minimal

   log_min_messages = info # debug5, debug4, ..., panic

   Seuil pour les requêtes lentes (en millisecondes)

   log_min_duration_statement = 1000 # enregistre toute requête > 1 seconde

   Activer la journalisation des attentes de verrou

   log_lock_waits = on

   Journaliser les connexions et déconnexions

   log_connections = on log_disconnections = on

3. Redémarrer le service :
   Appliquez les changements en redémarrant PostgreSQL : ``` sudo systemctl restart postgresql

2. Surveillance de l'activité via les logs

Une fois la configuration active, PostgreSQL enregistre les informations pertinentes. Voici comment exploiter ces logs.

Exemple 1 : Consulter les requêtes lentes
Avec log_min_duration_statement = 1000, toutes les requêtes dépassant 1 seconde sont loguées. Pour les visualiser :

cat /var/lib/pgsql/<version>/data/pg_log/postgresql-*.log | grep 'duration'

Explication : Cette commande filtre les lignes contenant "duration", affichant ainsi les requêtes longues.

Exemple 2 : Analyser les conflits de verrouillage
Si log_lock_waits est activé, PostgreSQL note chaque attente de verrou. Pour les identifier :

cat /var/lib/pgsql/<version>/data/pg_log/postgresql-*.log | grep 'lock'

Explication : Les lignes contenant "lock" signalent des situations de blocage potentielles.

3. Analyse avancée avec pgBadger

pgBadger est un outil qui génère des rapports HTML détaillés à partir des logs PostgreSQL, facilitant l'enalyse.

Installation :

sudo apt-get install pgbadger   # Debian/Ubuntu
sudo yum install pgbadger       # CentOS/RHEL

Génération d'un rapport :

pgbadger /var/lib/pgsql/<version>/data/pg_log/postgresql-*.log -o report.html

Ouvrez ensuite report.html pour consulter :

• Nombre d'exécutions et temps moyen par requête
• Statistiques sur les attentes de verrou
• Données de connexion/déconnexion
• Erreurs et avertissements

Exemple pratique : Identification d'un goulot d'étranglement

Imaginons que le rapport pgBadger montre une requête particulièrement longue :

SELECT * FROM orders WHERE customer_id = 12345;

Actions correctives :

• Vérifier si un index existe sur la colonne customer_id pour accélérer la recherche.
• Utiliser EXPLAIN ANALYZE pour examiner le plan d'exécution : ``` EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

Explication : EXPLAIN ANALYZE fournit le plan d'exécution réel et les temps, aidant à identifier les opérations coûteuses.