Retrouvez cet article dans : Linux Magazine 105
1. Configuration du moyen de stockage
Après avoir installé un serveur PostgreSQL, il est important, entre autres configurations, de bien réfléchir à la façon dont on va stocker les journaux applicatifs. Il existe trois grands choix :- Soit l’administrateur décide que PostgreSQL se charge lui-même de la gestion des fichiers constituant les journaux.
- Soit l’administrateur préfère déléguer cette tâche à un autre outil, plus spécialisé, comme syslog (ce qui est plus dans la philosophie Unix, et qui sera certainement privilégié au cas où cet administrateur gère plusieurs serveurs, physiques et/ou logiciels, car toutes les traces pourront être regroupées et gérées au sein d’un même logiciel).
- Soit l’administrateur veut gérer lui-même les fichiers.
log_destination = ‘stderr’ logging_collector = on log_directory = ‘pg_log’ log_filename = ‘postgresql-%Y-%m-%d.log’ log_rotation_age = 1d log_rotation_size = 100MBSi, suite à une rotation, le nouveau nom du fichier correspond à un fichier déjà existant, PostgreSQL pourra gérer la situation de deux façons : soit en écrasant le fichier existant, soit en ajoutant les messages à la suite du fichier existant. Cela se configure avec le paramètre
log_destination = ‘syslog’ logging_collector = off syslog_facility = ‘local0’ syslog_ident = ‘postgres’Dans ce cas, la rotation des journaux applicatifs ne dépend plus des paramètres PostgreSQL, mais d’un outil externe comme Logrotate. Note Au cas où vous seriez sous Windows, la valeur syslog n’est pas disponible. Par contre, vous pouvez envoyer les traces au journal des événements de Windows en initialisant log_destination à eventlog. Le dernier cas, où l’administrateur veut gérer cette partie lui-même, la configuration demande de configurer
2. Configuration de la quantité d’informations à tracer
Quand les développeurs choisissent de publier une information via les journaux applicatifs, ils indiquent le message, mais aussi son niveau d’importance. Un administrateur peut choisir de ne stocker les messages que s’ils sont d’un certain niveau. Pour cela, il faut utiliser le paramètreDEBUG5,DEBUG4,DEBUG3,DEBUG2,DEBUG1, messages de débogage utiles principalement aux développeurs (mais aussi aux utilisateurs qui veulent en apprendre plus sur le fonctionnement du système... par exempleDEBUG2est le niveau permettant, entre autres, de connaître les tables qui seront traitées par unVACUUMet/ou unANALYZEsuite à l’activation du démonautovacuum) ;INFO, informations mineures ;NOTICE;WARNING, messages d’avertissements (donc sans conséquence dans l’immédiat) ;ERROR, principalement des requêtes en erreur ;LOG, informations importantes ;FATAL, le processus ne peut plus continuer (généralement lorsqu’une connexion échoue, par exemple un mot de passe erroné) ;PANIC, le système n’est plus disponible, car une erreur empêche son utilisation sans intervention de l’administrateur.
3. Informations spécifiques à tracer
En dehors des messages d’utilisation standard, il est possible de demander des informations supplémentaires pour surveiller l’activité de certains processus comme
L’un des principaux apports de la version 8.3 pour la journalisation concerne le suivi d’activités spécifiques et principalement liées aux performances : les checkpoints, les verrous, les fichiers temporaires.
4. Ajouter un préfixe aux traces
Chaque message envoyé ne contient que le message : pas d’horodatage, pas d’informations sur la session concernée. syslog ajoute automatiquement son propre préfixe contenant date, heure, PID, nom d’hôte, etc. Si vous utilisez la sortie stderr, il vous est conseillé de modifier le paramètre log_line_prefix pour au moins ajouter la date et l’heure, informations capitales pour trier les messages. Cela étant dit, le préfixe étant complètement personnalisable, un ensemble de caractères joker permettent à l’administrateur d’indiquer bien plus d’informations comme l’utilisateur connecté, la base de connexion, l’adresse IP et le port du client, etc. Attention, si vous utilisez la sortie csvlog, cette dernière est déjà strictement définie. Personnaliser log_line_prefix aura plus d’inconvénients que d’avantages.
Astuce N’oubliez pas d’ajouter un espace à la fin du préfixe pour bien séparer préfixe et message, PostgreSQL ne le fait pas pour vous.
5. Langue des traces
PostgreSQL est capable de s’exprimer en français. Les traces peuvent donc être enregistrées en français. C’est intéressant quand on débute, car il est plus facile d’appréhender ce SGBD avec sa langue native. Malheureusement, cela complique aussi les choses pour plusieurs raisons. La première est qu’une recherche sur Google a plus de chances de rapporter des informations si le message recherché est en anglais. La seconde, à peu près du même ordre, est que vous devrez donner le message en anglais si vous cherchez de l’aide sur une des listes de discussion du projet PostgreSQL. Les utilisateurs avancés et les développeurs ne feront pas l’effort de traduire les messages laissés en français. Enfin, la dernière raison, la plus gênante, les anciennes traductions laissaient à désirer : parfois erronées, parfois vagues. La version 8.3.0 est la première à disposer de traductions entièrement revues. Les prochaines versions mineures des branches 8.2 à 7.4 auront aussi ces nouvelles traductions.
PostgreSQL utilise le paramètre lc_messages pour savoir dans quelle langue les messages du serveur doivent être traduits. Pour basculer vers la langue originale, l’anglais, il suffit de configurer cette variable à ‘C’.
Voici un exemple de configuration en direct :
tests=# SHOW lc_messages;
lc_messages
-------------
fr_FR.UTF-8
(1 ligne)
tests=# ALTER;
ERREUR: erreur de syntaxe sur ou près de " ; "
LIGNE 1 : ALTER;
^
tests=# SET lc_messages TO ‘C’;
SET
tests=# ALTER;
ERROR: syntax error at or near ";"
LIGNE 1 : ALTER;
^
6. La grosse nouveauté de la version 8.3 : la journalisation au format CSV
PostgreSQL nous permet de sélectionner l’outil qui va gérer les fichiers de la journalisation. Il nous permet aussi de préciser le niveau des informations désirées, ainsi que d’avoir des informations spécifiques sur certains événements. Le dernier point restant est une utilisation simple des journaux, permettant un tri et un filtre rapide. Une solution est apportée avec la version 8.3. Un format commun pour l’échange d’informations sous forme de tableau est le CSV. Ce format sépare chaque colonne par une virgule. L’importation dans un tableur, quel qu’il soit, en est simplifiée. PostgreSQL propose donc ce format pour les journaux applicatifs. Pour l’activer, il suffit d’initialiserguillaume@laptop:/opt/postgresql-8.3/data/pg_log$ ll total 44 -rw------- 1 postgres postgres 0 2008-02-27 08:49 postgresql-2008-02-27.log -rw------- 1 postgres postgres 13209 2008-02-27 09:02 postgresql-2008-02-27.csv -rw------- 1 postgres postgres 0 2008-02-28 00:00 postgresql-2008-02-28.log -rw------- 1 postgres postgres 9499 2008-02-28 00:00 postgresql-2008-02-28.csvDans cet exemple, deux journaux sont créés tous les jours, un avec une extension
utilisateur=guillaume base=postgres hôte=[local]",,,,,,,, 2008-02-27 10:12:45.175 CET,,,6731,"",47c5298d.1a4b,1,"/opt/postgresql-8.3/bin/postgres",2008-02-27 10:12:45 CET,,0,LOG,00000,"connexion reçue : hôte=[local]",,,,,,,, 2008-02-27 10:12:45.176 CET,"guillaume","tests",6731,"[local]",47c5298d.1a4b,2,"authentication",2008-02-27 10:12:45 CET,,0,LOG,00000,"connexion autorisée : utilisateur=guillaume, base de données=tests",,,,,,,, 2008-02-27 10:12:52.599 CET,"guillaume","tests",6731,"[local]",47c5298d.1a4b,3,"idle",2008-02-27 10:12:45 CET,1/4,0,LOG,00000,"instruction : show shared_buffers;",,,,,,,, 2008-02-27 10:13:04.401 CET,"guillaume","tests",6731,"[local]",47c5298d.1a4b,4,"idle",2008-02-27 10:12:45 CET,1/5,0,LOG,00000,"instruction : show lc_messages;",,,,,,,, 2008-02-27 10:13:15.511 CET,"guillaume","tests",6731,"[local]",47c5298d.1a4b,5,"idle",2008-02-27 10:12:45 CET,1/6,0,LOG,00000,"instruction : set lc_messages to ‘C’;",,,,,,,, 2008-02-27 10:13:16.764 CET,"guillaume","tests",6731,"[local]",47c5298d.1a4b,6,"idle",2008-02-27 10:12:45 CET,1/7,0,LOG,00000,"statement: show lc_messages;",,,,,,,, 2008-02-27 10:13:26.845 CET,"guillaume","tests",6731,"[local]",47c5298d.1a4b,7,"idle",2008-02-27 10:12:45 CET,,0,LOG,00000,"disconnection: session time: 0:00:41.669 user=guillaume database=tests host=[local]",,,,,,,,Le constat est simple : le tableau contient déjà de nombreuses colonnes, et est peu lisible ainsi, encore moins que le journal habituel. Il est donc nécessaire de passer par un outil pour mieux le déchiffrer. Le détail des colonnes est disponible dans le tableau 1.
6.1 Intégration du journal dans un tableur
Le format CSV a été conçu notamment pour permettre des échanges entre tableurs. Prenons l’exemple de Calc, le tableur d’OpenOffice.org. Voici les étapes pour récupérer le contenu du journal dans Calc :
- démarrer Calc ;
- aller dans Fichier/Ouvrir ;
- sélectionner le journal ;
- le dialogue qui s’ouvre doit avoir par défaut les bons paramètres (jeu de caractères Unicode, lire depuis la ligne 1, et la virgule comme champ séparateur... voir la figure 1) ;
- cliquer sur OK.
Figure 1 : Options d’import du fichier CSV
Figure 2 : Copie d’écran de Calc après ouverture d’un journal au format CSV
Et voilà ! Vous devriez aboutir au résultat de la figure 2. Il n’y a pas d’en-tête sur le tableau, mais le contenu des colonnes est décrit dans le tableau 1. Vous pouvez ensuite utiliser les fonctionnalités du tableur pour trier, filtrer, construire un rapport, embellir le journal.
6.2 Intégration du journal dans une table d’une base PostgreSQL
Utiliser Calc est un moyen simple. Le gros inconvénient est qu’il faut charger en permanence les nouveaux fichiers et qu’il est plus difficile d’automatiser certaines recherches. Un administrateur de bases de données préfère souvent garder l’information dans une base SQL qu’il pourra accéder de partout. Le tout est de pouvoir intégrer facilement les données du fichier au format CSV dans une table. Sous PostgreSQL, c’est possible. Il existe même une instruction SQL pour cela, la commande COPY.
Cette commande existe depuis bien longtemps, mais ce n’est qu’à partir de la version 8.0 que le mode CSV a été ajouté. Elle permet principalement d’importer (COPY FROM) et d’exporter (COPY TO) des données dans un format CSV ou s’en rapprochant. Cette instruction est principalement utilisée dans les sauvegardes au format SQL, car elle permet un enregistrement très rapide des données dans une table.
Il ne nous reste plus qu’à avoir la structure de la table qui va accueillir les données. Le manuel PostgreSQL donne directement l’instruction SQL de création. La voici pour mémoire :
CREATE TABLE postgres_log ( log_time timestamp(3) with time zone, user_name text, database_name text, process_id integer, connection_from text, session_id text, session_line_num bigint, command_tag text, session_start_time timestamp with time zone, virtual_transaction_id text, transaction_id bigint, error_severity text, sql_state_code text, message text, detail text, hint text, internal_query text, internal_query_pos integer, context text, query text, query_pos integer, location text, PRIMARY KEY (session_id, session_line_num) );
Une fois la table créée, il ne nous reste plus qu’à importer les données avec la commande :
COPY postgres_log FROM ‘/chemin/complet/vers/le/journalapplicatif.csv’ WITH csv;
Il est donc très facile d’automatiser cette commande via un script cron. L’important est de savoir quels ont été les journaux déjà importés, pour pouvoir importer les autres. Il est possible d’utiliser une autre table, qui contiendra une ligne par journal importé.
CREATE TABLE imported_logs ( log_filename text, imported_time timestamp with time zone DEFAULT now(), PRIMARY KEY (log_filename) );Le script va tout d’abord récupérer la liste des journaux qui se trouvent dans cette table et rechercher tous ceux qui ne correspondent pas à cette liste. Lorsque le script en trouve un, il l’importe immédiatement. Voici un exemple du script bash en question :
#!/bin/sh
# Personnalisation possible
PGDATA=${PGDATA:-/chemin/complet/vers/le/repertoire/des/donnees}
# Fin de la personnalisation possible du script
cd $PGDATA/pg_log
requete_journal=”SELECT count(*) FROM imported_logs WHERE log_filename=”
for fichier in *.csv
do
existe=$(psql -Atc “$requete_journal ‘$fichier’” logs)
if test $existe -eq “0”
then
psql logs <<_EOF_SQL_
BEGIN;
COPY postgres_log FROM ‘$PGDATA/pg_log/$fichier’ WITH csv;
INSERT INTO imported_logs (log_filename) VALUES (‘$fichier’);
COMMIT;
_EOF_SQL_
fi
done
En ajoutant ce script dans un cron quotidien, les journaux sont automatiquement importés dans la table.
Note
Ce script, bien que fonctionnel, n’est pas écrit dans les règles de l’art. Il serait déjà préférable de l’écrire dans un langage plus évolué comme Perl ou Python. Il serait bien vu aussi de vérifier les erreurs éventuelles. Sans compter qu’il est loin d’être performant. En effet, il est préférable de récupérer la liste des journaux importés en une seule requête et de comparer cette liste au fichier en cours de traitement, plutôt que d’exécuter une requête pour vérifier la présence de chaque fichier. Cela étant dit, ce n’est pas le but de cet article, l’auteur laisse donc l’amélioration de ce script en exercice à ses lecteurs.
6.3 Utilisation de la table
Une fois que les données sont dans la table, leur gestion est très simple. Il suffit d’utiliser les ordres SQL habituels. Par exemple, si un administrateur veut récupérer tous les messages d’erreur, il peut utiliser cette requête :SELECT log_time, error_severity, message FROM postgres_log WHERE error_severity = ‘ERROR’;ce qui lui donnera un résultat ressemblant à ceci :
log_time | error_severity | message ----------------------------+----------------+----------------------------- 2008-03-12 09:16:47.521+01 | ERROR | syntax error at or near ";" (1 ligne)S’il souhaite récupérer toutes les traces des trois dernières heures, il lui suffit d’exécuter cette requête :
SELECT log_time, message FROM postgres_log WHERE log_time > now() - ‘3 hours’::interval;et voici un résultat possible :
log_time | message ----------------------------+--------------------------------------- 2008-03-12 09:16:12.872+01 | connexion reçue : hôte=[local] 2008-03-12 09:16:12.872+01 | connexion autorisée : utilisateur=guillaume, base de données=tests 2008-03-12 09:16:18.292+01 | instruction : show lc_messages; [...]Les tris sont aussi possibles, la plus fréquente étant par date :
SELECT log_time, message FROM postgres_log WHERE log_time>now() - ‘3 hours’::interval ORDER BY log_time;La clause de regroupement permet de savoir, par exemple, le nombre d’erreurs par jour, mais aussi le nombre de messages par jour et par sévérité :
logs=# SELECT log_time::date, count(*) FROM postgres_log logs-# WHERE error_severity=’ERROR’ GROUP BY 1; log_time | count ------------+------- 2008-03-12 | 1 (1 ligne) logs=# SELECT log_time::date, error_severity, count(*) FROM postgres_log logs-# GROUP BY 1, 2 ORDER BY 1, 2; log_time | error_severity | count ------------+----------------+------- 2008-02-27 | FATAL | 2 2008-02-27 | INFO | 7 2008-02-27 | LOG | 102 2008-02-28 | ATTENTION | 1 2008-02-28 | FATAL | 1 2008-02-28 | LOG | 1203 2008-02-29 | FATAL | 1 2008-02-29 | LOG | 64 2008-03-12 | ERREUR | 58 2008-03-12 | INFO | 2 2008-03-12 | LOG | 446Il est aussi possible d’utiliser les vues pour conserver des rapports intéressants, voire les combiner. Bref, les possibilités sont nombreuses.
Conclusion
Cette vue complète des possibilités de journalisation applicative de PostgreSQL montre ses grandes capacités dans ce domaine, ainsi qu’une possibilité importante de configuration. C’est le genre de capacités qui plaira à tout administrateur de bases de données.Retrouvez cet article dans : Linux Magazine 105
Laissez une réponse
Vous devez avoir ouvert une session pour écrire un commentaire.