PostgreSQL et ses journaux de transactions

1

Technologie WAL

L’acronyme WAL est souvent utilisé dans les discussions sur les journaux de transactions. Sa signification est simple : Write Ahead Log, un journal qui est écrit avant. En fait, il s’agit de l’algorithme gérant les journaux de transactions sous PostgreSQL.

Lorsqu’un utilisateur fait une modification dans sa base de données, un ou plusieurs fichiers de données seront modifiés. Ces changements seront tracés dans le journal de transactions en cours d’utilisation au plus tard lors de la validation de la transaction contenant ces changements. Cela peut arriver plus tôt si les changements à ajouter aux journaux de transactions ne tiennent pas dans la mémoire allouée pour cela (voir le paramètre wal_buffers). Ce n’est qu’un peu après que soit bgwriter, soit un processus postgres enregistrera les modifications sur les fichiers de données.

Cela veut donc dire que chaque modification est enregistrée deux fois : une fois dans les journaux de transactions, une fois dans les fichiers de données. On pourrait croire à une perte de temps (et donc de performances), mais, dans les faits, on gagne en rapidité et en fiabilité.

Commençons par la rapidité. Considérons un utilisateur qui met à jour une table avec une requête UPDATE. Son processus serveur va devoir modifier la ligne touchée par l’UPDATE pour indiquer qu’elle doit être considérée comme une ligne morte à partir de cet identifiant de transaction. Il va aussi devoir ajouter la nouvelle ligne vivante, sans compter la mise à jour de la table TOAST (si nécessaire) et des index associés. Il peut y avoir beaucoup d’écritures à des emplacements non contigus sur le disque, donc nécessitant un déplacement de la tête de lecture, donc générateur de lenteur. Or, l’utilisateur a besoin d’avoir rapidement l’information que les données sont enregistrées (le message COMMIT). Du coup, il est préférable dans un premier temps d’écrire séquentiellement dans un fichier toutes les modifications à faire, car une écriture séquentielle est bien plus rapide.

On gagne aussi en stabilité. Si le serveur PostgreSQL s’arrête brutalement pendant l’écriture des journaux de transactions, la transaction en cours d’écriture n’est pas validée. Du coup, il n’y a pas de pertes de données. Supposons maintenant que le crash survienne après l’écriture dans les journaux de transactions, mais avant l’écriture dans les fichiers de données. Au relancement de PostgreSQL, le serveur commence par relire les journaux de transactions et écrit dans les fichiers de données les données présentes dans les journaux de transactions, mais absentes des fichiers de données.

Pour que ce système fonctionne, PostgreSQL doit s’assurer que, quand il enregistre des données dans les journaux de transactions, ces données sont bien sur disque, et pas dans le cache du système d’exploitation. Pour cela, PostgreSQL demande au système de synchroniser les données qui sont dans son cache sur le disque grâce à l’instruction système C fsync. Suivant le système d’exploitation, il est possible de choisir l’appel système chargé de synchroniser les disques par l’intermédiaire du paramètre wal_sync_method. Le paramètre fsync du fichier de configuration postgresql.conf permet de désactiver ce comportement, mais, dans ce cas, la sécurité de vos données n’est pas assurée. Il est néanmoins possible de désactiver ce paramètre dans certains cas, car le gain en performances (par exemple pendant une grosse insertion ou modification) est très important. C’est intéressant dans certains cas très précis où une corruption de l’instance n’est pas problématique (par exemple, lors du chargement d’une sauvegarde sur un nouveau serveur). Dans tous les autres cas (autrement dit, dans la très grande majorité des cas), le paramètre fsync doit être activé.

2

Stockage des journaux de transactions

Au moment de l’opération d’initialisation du cluster via initdb, le répertoire pg_xlog est créé. Il est par défaut dans le répertoire de l’instance PostgreSQL. Ce répertoire va contenir tous les journaux de transactions de cette instance. Le XLOG, partie finale du nom du répertoire, est un autre acronyme souvent utilisé pour parler des journaux de transactions.

Créons une nouvelle instance pour voir ce que contient, tout de suite après l’initialisation de l’instance, le répertoire pg_xlog :

guillaume@laptop:~$ export PGDATA=/home/guillaume/glmf guillaume@laptop:~$ initdb The files belonging to this database system will be owned by user "guillaume". [ ... coupé ... ] Success. You can now start the database server using: postgres -D /home/guillaume/glmf or pg_ctl -D /home/guillaume/glmf -l logfile start guillaume@laptop:~$ ls -lhG $PGDATA/pg_xlog total 17M -rw------- 1 guillaume 16M 2008-07-13 16:19 000000010000000000000000 drwx------ 2 guillaume 4,0K 2008-07-13 16:19 archive_status

Nous discuterons du répertoire archive_status plus tard. Commençons plutôt par le seul fichier actuellement disponible.

000000010000000000000000 est le premier journal de transactions après la création d’une nouvelle instance. Il fait 16 Mo. Son nom est codé : les huit premiers chiffres correspondent au TimeLineID, les suivants résultent d’une séquence. Pour le reste de l’article, je ne mentionnerai que la fin du nom du fichier (1 pour 000000010000000000000001, 2 pour 000000010000000000000002, etc.).

Un journal de transactions fait toujours 16 Mo, quelle que soit la quantité d’informations qu’il contient. Il est préalloué sur le disque pour que PostgreSQL ne soit pas confronté à des problèmes d’espace disque. Il est possible de savoir où aura lieu la prochaine écriture sur ce journal de transactions grâce à la fonction pg_current_xlog_location() ;

guillaume@laptop:~$ pg_ctl start server starting LOG: database system was shut down at 2008-07-13 16:19:29 CEST LOG: autovacuum launcher started LOG: database system is ready to accept connections guillaume@laptop:~$ psql -q postgres postgres=# SELECT pg_current_xlog_location(); pg_current_xlog_location -------------------------- 0/43F9C8 (1 row)

Créons une table :

postgres=# \! ls -lhG /home/guillaume/glmf/pg_xlog total 17M -rw------- 1 guillaume 16M 2008-07-13 16:19 000000010000000000000000 drwx------ 2 guillaume 4,0K 2008-07-13 16:19 archive_status postgres=# CREATE TABLE t1 (id int4); postgres=# SELECT pg_current_xlog_location(); pg_current_xlog_location -------------------------- 0/43FAC8 (1 row) postgres=# \! ls -lhG /home/guillaume/glmf/pg_xlog total 17M -rw------- 1 guillaume 16M 2008-07-13 16:20 000000010000000000000000 drwx------ 2 guillaume 4,0K 2008-07-13 16:19 archive_status

Premier point constaté : créer une table ne génère pas suffisamment d’informations pour demander la création d’un autre journal de transactions. Par contre, le pointeur d’emplacement courant dans le journal des transactions indique qu’il a été déplacé de la position 0/43F9C8 à la position 0/43FAC8, donc des données ont été enregistrées. Maintenant, ajoutons des données dans la table :

postgres=# INSERT INTO t1 VALUES (1); postgres=# INSERT INTO t1 VALUES (2); postgres=# INSERT INTO t1 VALUES (3); postgres=# \! ls -lhG /home/guillaume/glmf/pg_xlog total 17M -rw------- 1 guillaume 16M 2008-07-13 16:21 000000010000000000000000 drwx------ 2 guillaume 4,0K 2008-07-13 16:19 archive_status

Là aussi, l’insertion des trois enregistrements continue sur le même journal de transactions. En fait, il faut ajouter beaucoup plus de données pour qu’un autre journal de transactions soit utilisé.

postgres=# INSERT INTO t1 SELECT x FROM generate_series(1, 300000) AS x; postgres=# \! ls -lhG /home/guillaume/glmf/pg_xlog total 33M -rw------- 1 guillaume 16M 2008-07-13 16:22 000000010000000000000000 -rw------- 1 guillaume 16M 2008-07-13 16:22 000000010000000000000001 drwx------ 2 guillaume 4,0K 2008-07-13 16:19 archive_status postgres=# INSERT INTO t1 SELECT x FROM generate_series(1, 300000) AS x; postgres=# \! ls -lhG /home/guillaume/glmf/pg_xlog total 49M -rw------- 1 guillaume 16M 2008-07-13 16:22 000000010000000000000000 -rw------- 1 guillaume 16M 2008-07-13 16:22 000000010000000000000001 -rw------- 1 guillaume 16M 2008-07-13 16:22 000000010000000000000002 drwx------ 2 guillaume 4,0K 2008-07-13 16:19 archive_status

En continuant cette opération de nombreuses fois, PostgreSQL se met à créer les journaux de transactions dont il a besoin. Nous allons exécuter plusieurs fois la grosse insertion pour arriver à un seuil critique :

postgres=# INSERT INTO t1 SELECT x FROM generate_series(1, 300000) AS x; postgres=# INSERT INTO t1 SELECT x FROM generate_series(1, 300000) AS x; postgres=# INSERT INTO t1 SELECT x FROM generate_series(1, 300000) AS x; postgres=# INSERT INTO t1 SELECT x FROM generate_series(1, 300000) AS x;

LOG: checkpoints are occurring too frequently (16 seconds apart) HINT: Consider increasing the configuration parameter "checkpoint_segments". postgres=# \! ls -lhG /home/guillaume/glmf/pg_xlog total 113M -rw------- 1 guillaume 16M 2008-07-13 16:22 000000010000000000000000 -rw------- 1 guillaume 16M 2008-07-13 16:22 000000010000000000000001 -rw------- 1 guillaume 16M 2008-07-13 16:23 000000010000000000000002 -rw------- 1 guillaume 16M 2008-07-13 16:23 000000010000000000000003 -rw------- 1 guillaume 16M 2008-07-13 16:23 000000010000000000000004 -rw------- 1 guillaume 16M 2008-07-13 16:23 000000010000000000000005 -rw------- 1 guillaume 16M 2008-07-13 16:23 000000010000000000000006 drwx------ 2 guillaume 4,0K 2008-07-13 16:19 archive_status

Nous avons donc là les journaux de transactions, du 0 au 6. Une dernière insertion :

postgres=# \! ls -lhG /home/guillaume/glmf/pg_xlog total 129M -rw------- 1 guillaume 16M 2008-07-13 16:23 000000010000000000000003 -rw------- 1 guillaume 16M 2008-07-13 16:23 000000010000000000000004 -rw------- 1 guillaume 16M 2008-07-13 16:23 000000010000000000000005 -rw------- 1 guillaume 16M 2008-07-13 16:23 000000010000000000000006 -rw------- 1 guillaume 16M 2008-07-13 16:23 000000010000000000000007 -rw------- 1 guillaume 16M 2008-07-13 16:22 000000010000000000000008 -rw------- 1 guillaume 16M 2008-07-13 16:22 000000010000000000000009 -rw------- 1 guillaume 16M 2008-07-13 16:23 00000001000000000000000A drwx------ 2 guillaume 4,0K 2008-07-13 16:19 archive_status

Que constatons-nous ? PostgreSQL semble s’être débarrassé des fichiers 0, 1 et 2 et a créé les fichiers 7 à A. Or, nous avions vu que cette insertion générait un seul journal de transactions. En fait, il s’est passé ceci : du fait du grand nombre de journaux créés (et du coup au dépassement de la valeur du paramètre checkpoint_segments), PostgreSQL a exécuté un CHECKPOINT automatique. Ce CHECKPOINT a enregistré un certain nombre de pages disque modifiées en mémoire dans les fichiers de données. Du coup, certains des journaux de transactions n’étaient plus nécessaires. Plutôt que d’être supprimés, ils ont été renommés pour être de nouveau utilisables. En fait, PostgreSQL ne travaille pas sur le fichier A actuellement. Pour s’en assurer, utilisons la fonction interne pg_xlogfile_name() :

postgres=# SELECT pg_xlogfile_name(pg_current_xlog_location()); pg_xlogfile_name -------------------------- 000000010000000000000007 (1 row)

Nous en sommes au numéro 7. En fait, nous aurions pu le deviner avec la dernière exécution de la commande ls. Le fichier 7 est le dernier à avoir été modifié (2008-07-13 16:23).

Nous avons passé rapidement le message niveau LOG qui disait checkpoints are occurring too frequently (16 seconds apart). En fait, quand beaucoup de journaux de transactions sont générés, il est possible de faire en sorte que PostgreSQL écrive un message d’avertissement dans les traces. Ce moyen, c’est le paramètre checkpoint_warning. Si PostgreSQL exécute des CHECKPOINT automatiques dans un délai inférieur à la valeur de ce paramètre (en secondes), un message est enregistré dans les traces.

L’autre moyen d’avoir un CHECKPOINT automatique est de laisser passer un certain temps, exactement plus de checkpoint_timeout secondes. Après un tel temps d’attente, voici ce qu’il nous reste comme journaux de transactions :

postgres=# \! ls -lhG /home/guillaume/glmf/pg_xlog total 129M -rw------- 1 guillaume 16M 2008-07-13 16:23 000000010000000000000006 -rw------- 1 guillaume 16M 2008-07-13 16:30 000000010000000000000007 -rw------- 1 guillaume 16M 2008-07-13 16:22 000000010000000000000008 -rw------- 1 guillaume 16M 2008-07-13 16:22 000000010000000000000009 -rw------- 1 guillaume 16M 2008-07-13 16:23 00000001000000000000000A -rw------- 1 guillaume 16M 2008-07-13 16:23 00000001000000000000000B -rw------- 1 guillaume 16M 2008-07-13 16:23 00000001000000000000000C -rw------- 1 guillaume 16M 2008-07-13 16:23 00000001000000000000000D drwx------ 2 guillaume 4,0K 2008-07-13 16:19 archive_status

Suivant l’activité de la base de données, un CHECKPOINT permettra de s’assurer d’avoir des journaux de transactions prêts à être utilisés. PostgreSQL utilise dans le cadre normal (2+checkpoint_completion_target) * checkpoint_segments + 1 journal de transactions. Dans le pire des cas, vous pourrez en avoir plus. Si vous avez trois fois plus de journaux de transactions que la valeur indiquée dans checkpoint_segments, PostgreSQL les supprimera au lieu de les renommer, ceci pour éviter d’utiliser trop d’espace disque.

Faisons un CHECKPOINT manuel :

postgres=# CHECKPOINT; postgres=# \! ls -lhG /home/guillaume/glmf/pg_xlog total 129M -rw------- 1 guillaume 16M 2008-07-13 16:32 000000010000000000000007 -rw------- 1 guillaume 16M 2008-07-13 16:22 000000010000000000000008 -rw------- 1 guillaume 16M 2008-07-13 16:22 000000010000000000000009 -rw------- 1 guillaume 16M 2008-07-13 16:23 00000001000000000000000A -rw------- 1 guillaume 16M 2008-07-13 16:23 00000001000000000000000B -rw------- 1 guillaume 16M 2008-07-13 16:23 00000001000000000000000C -rw------- 1 guillaume 16M 2008-07-13 16:23 00000001000000000000000D -rw------- 1 guillaume 16M 2008-07-13 16:23 00000001000000000000000E drwx------ 2 guillaume 4,0K 2008-07-13 16:19 archive_status

On se retrouve pratiquement dans le même cas qu’au tout début. Nous travaillons sur le premier journal (le 7), mais nous avons aussi les sept autres journaux prêts à être utilisés.

Pour résumer, que nous indiquent toutes ces manipulations ?

1. PostgreSQL écrit fréquemment dans les journaux de transactions.

2. Il crée les journaux quand il en a besoin s’ils n’existent pas déjà.

3. Dans le meilleur (et le plus fréquent) des cas, il réutilise les anciens journaux pour éviter d’avoir à créer des fichiers vides de 16 Mo.

4. Chaque journal a un nom propre (codé en hexadécimal) dont l’identifiant est incrémenté de 1 pour chaque journal.

5. Il cherche à conserver un nombre constant de journaux de transactions. Ce nombre dépend de la configuration.

3

Contenu d’un journal de transactions

Maintenant que nous savons où ils sont stockés et à quoi ils servent, intéressons-nous un peu à leur contenu. Pour cela, nous allons utiliser un outil appelé Xlogviewer. Cet outil fait partie du projet XlogViewer sur pgFoundry (http://pgfoundry.org/projects/xlogviewer/). L’installation n’est pas très simple, notamment parce qu’il faut appliquer un correctif aux sources pour que l’outil soit compatible avec PostgreSQL 8.3, donc l’installation ne sera pas expliquée ici.

Cet outil s’utilise par le biais de deux procédures stockées qu’il faut ajouter à la base utilisée :

guillaume@laptop$ psql -f /opt/postgresql-8.3/share/contrib/xlogviewer.sql postgres CREATE TYPE CREATE FUNCTION CREATE FUNCTION

La procédure stockée que nous allons utiliser demande comme argument le nom du journal de transactions à décoder. La procédure décode à chaque fois tout le journal. Seules certaines colonnes renvoyées par cette procédure sont intéressantes pour nous :

- xid est l’identifiant de la transaction (il y aura donc plusieurs lignes avec le même identifiant pour représenter toutes les modifications effectuées par une transaction).

- rmname correspond au type d’enregistrement.

- total_len est le nombre d’octets enregistrés dans le journal de transactions.

- infoname tente de décoder l’opération effectuée.

Pour ne pas être gêné par les anciennes transactions, nous allons commencer par changer de journal de transactions :

postgres=# SELECT pg_switch_xlog() ;

Maintenant, créons une base de données :

postgres=# CREATE DATABASE db1; CREATE DATABASE

Et regardons le contenu du journal de transactions :

postgres=# SELECT * postgres-# FROM xlogviewer(‘/home/guillaume/glmf/pg_xlog/’|| postgres-# pg_xlogfile_name(pg_current_xlog_location())); rmid | xid | rmname | info | len | total_len | infoname ------+-----+--------+------+-----+-----------+------------- 10 | 390 | HEAP | 9 | 18 | 710 | insert 11 | 390 | BTREE | 9 | 18 | 410 | insert_leaf 11 | 390 | BTREE | 9 | 18 | 170 | insert_leaf 4 | 390 | DBASE | 0 | 16 | 44 | 0 | 0 | XLOG | 10 | 36 | 64 | checkpoint 1 | 390 | XACT | 0 | 16 | 44 | commit (6 rows)

La première ligne nous indique une insertion (insert) dans une table (HEAP). Nous sommes tout à fait en droit de supposer qu’il s’agit de l’insertion dans le catalogue système pg_database.

Ensuite, nous avons deux lignes concernant des index Btree. Elles indiquent une insertion (insert_leaf) dans deux index. pg_database dispose de deux index. Il s’agit donc de la modification des index suite à l’insertion dans la table.

DBASE indique la création de la base.

Une création de base exécute automatiquement un CHECKPOINT, d’où l’enregistrement checkpoint. Remarquez que ce dernier n’est pas lié à une transaction (colonne xid à 0).

Enfin, nous avons le COMMIT indiquant la validation de la transaction.

Maintenant, créons une table et regardons les nouveaux enregistrements contenus dans le journal :

postgres=# CREATE TABLE t1 (id int4, contenu text); CREATE TABLE postgres=# SELECT * postgres-# FROM xlogviewer(‘/home/guillaume/glmf/pg_xlog/’|| postgres-# pg_xlogfile_name(pg_current_xlog_location())); rmid | xid | rmname | info | len | total_len | infoname ------+-----+--------+------+-----+-----------+------------- [... suppression des infos sur les anciennes transactions ...] 2 | 0 | SMGR | 10 | 12 | 40 | create rel 10 | 391 | HEAP | 9 | 18 | 6802 | insert 11 | 391 | BTREE | 9 | 18 | 4458 | insert_leaf 11 | 391 | BTREE | 9 | 18 | 7386 | insert_leaf [... coupé ...] 10 | 391 | HEAP | 0 | 49 | 77 | insert 11 | 391 | BTREE | 0 | 38 | 66 | insert_leaf 11 | 391 | BTREE | 0 | 38 | 66 | insert_leaf 10 | 391 | HEAP | 70 | 146 | 174 | inplace 10 | 391 | HEAP | 70 | 146 | 174 | inplace 10 | 391 | HEAP | 40 | 168 | 196 | 10 | 391 | HEAP | 0 | 49 | 77 | insert 11 | 391 | BTREE | 0 | 38 | 66 | insert_leaf 11 | 391 | BTREE | 0 | 38 | 66 | insert_leaf 1 | 391 | XACT | 0 | 16 | 44 | commit (113 rows)

Ajouter une table réalise un grand nombre de modifications dans les tables et index système. En fait, il y en a 105 qui sont suivies de la validation de la transaction.

Insérons une ligne dans cette table :

postgres=# INSERT INTO t1 VALUES (1, ‘un’); INSERT 0 1 postgres=# SELECT * postgres-# FROM xlogviewer(‘/home/guillaume/glmf/pg_xlog/’|| postgres-# pg_xlogfile_name(pg_current_xlog_location())); [... suppression des infos sur les anciennes transactions ...] 10 | 392 | HEAP | 80 | 31 | 59 | insert 1 | 392 | XACT | 0 | 16 | 44 | commit (116 rows)

Deux enregistrements pour cette insertion : l’insertion elle-même et la validation de la transaction. L’ajout d’une nouvelle ligne contenant un entier et une chaîne de deux caractères a demandé l’ajout de 59 octets dans le journal de transactions.

Ajoutons un index et insérons une donnée :

postgres=# CREATE INDEX i1 ON t1 (id); CREATE INDEX postgres=# INSERT INTO t1 VALUES (2, ‘deux’);

INSERT 0 1 postgres=# SELECT * postgres-# FROM xlogviewer(‘/home/guillaume/glmf/pg_xlog/’|| postgres-# pg_xlogfile_name(pg_current_xlog_location())); rmid | xid | rmname | info | len | total_len | infoname ------+-----+--------+------+-----+-----------+------------- [... suppression des infos sur les anciennes transactions ...] 10 | 395 | HEAP | 0 | 33 | 61 | insert 11 | 395 | BTREE | 9 | 18 | 138 | insert_leaf 1 | 395 | XACT | 0 | 16 | 44 | commit (138 rows)

Cette fois, il y a trois enregistrements : l’insertion de la donnée dans la table (insert), l’insertion dans l’index (insert_leaf) et la validation de la transaction.

Exécutons maintenant une opération CHECKPOINT :

postgres=# CHECKPOINT; CHECKPOINT postgres=# SELECT * postgres-# FROM xlogviewer(‘/home/guillaume/glmf/pg_xlog/’|| postgres-# pg_xlogfile_name(pg_current_xlog_location())); rmid | xid | rmname | info | len | total_len | infoname ------+-----+--------+------+-----+-----------+------------- [... suppression des infos sur les anciennes transactions ...] 0 | 0 | XLOG | 10 | 36 | 64 | checkpoint (139 rows)

Un seul enregistrement est ajouté dans le journal des transactions pour indiquer qu’un CHECKPOINT a été réalisé.

Tous ces exemples ont pour but de montrer que chaque modification de schéma et que chaque modification de données est tracée dans les journaux de transactions.

Le journal de transactions ne contient pas la requête en clair, mais seulement les octets modifiés de chaque bloc de chaque fichier. Le fait d’avoir les octets modifiés permet justement de restaurer une sauvegarde. Pour plus de sécurité, PostgreSQL sauvegarde chaque bloc complet dans le journal de transactions avant d’indiquer les octets à modifier de ce bloc. Cette sauvegarde n’a lieu qu’à la première modification d’un bloc après un checkpoint. Du coup, moins vous avez de checkpoints, moins les journaux grossiront et plus vous gagnerez en rapidité. Il est même possible de désactiver l’enregistrement des pages complètes en positionnant à off le paramètre full_page_writes.

L’utilisation de XlogViewer n’est pas simple, notamment du fait qu’il n’est plus maintenu. Dans le cas où la compilation de PostgreSQL est possible, il est intéressant pour mieux comprendre le système des journaux de transactions de demander au serveur de tracer son utilisation des journaux. C’est même beaucoup plus parlant qu’XlogViewer.

Pour cela, après avoir lancé l’étape configure, mais avant la compilation de PostgreSQL, vous devez activer la fonctionnalité WAL_DEBUG. Voici le moyen le plus simple (à exécuter dans le répertoire des sources de PostgreSQL) :

guillaume@laptop$ echo "#define WAL_DEBUG" >> src/include/pg_config.h

Ceci fait, vous pouvez lancer la compilation avec make. Une fois le serveur compilé et installé, la configuration doit disposer de la ligne suivante :

wal_debug = true

Recommençons une partie de l’exemple ci-dessus. Créons une base de données :

guillaume@laptop:~$ psql -q postgres postgres=# SET client_min_messages TO log; postgres=# CREATE DATABASE db5; LOG: INSERT @ 0/104EA44: prev 0/104EA04; xid 417; bkpb1: Heap - insert: rel 1664/0/1262; tid 0/8 LOG: INSERT @ 0/104EF00: prev 0/104EA44; xid 417; bkpb1: Btree - insert: rel 1664/0/2671; tid 1/5 LOG: INSERT @ 0/104F1CC: prev 0/104EF00; xid 417; bkpb1: Btree - insert: rel 1664/0/2672; tid 1/8 LOG: INSERT @ 0/104F2B8: prev 0/104F1CC; xid 417: Database - create db: copy dir 1/1663 to 16414/1663 LOG: INSERT @ 0/104F324: prev 0/104F2E4; xid 417: Transaction - commit: 2008-07-22 23:15:12.806781+02 LOG: xlog flush request 0/104F350; write 0/104EA04; flush 0/104EA04

Les informations sont assez simples à décoder. Contrairement à XlogViewer, les traces nous indiquent l’objet concerné grâce aux nombres situés après rel. Le premier nombre correspond à l’OID du tablespace, le deuxième à l’OID de la base (0 dans le cas d’un catalogue partagé) et le dernier au relfilenode de l’objet.

Nous avions dit tout à l’heure que l’insertion se faisait dans la table pg_database et dans ses index. Vérifions-ça :

postgres=# SELECT reltablespace, relfilenode, relname, relkind postgres-# FROM pg_class WHERE relname LIKE ‘pg_database%’; reltablespace | relfilenode | relname | relkind ---------------+-------------+---------------------------+--------- 1664 | 2671 | pg_database_datname_index | i 1664 | 2672 | pg_database_oid_index | i 1664 | 1262 | pg_database | r (3 rows)

Le premier enregistrement dans le journal de transactions indique rel 1664/0/1262, ce qui signifie tablespace 1664, base 0 (donc objet partagé entre toutes les bases) et table 1262. D’après le résultat de la requête ci-dessus, cela concerne bien la table pg_database. De la même façon, nous voyons que le second enregistrement concerne l’index pg_stat_database_datname_index et le troisième l’index pg_database_oid_index. Le quatrième enregistrement indique l’ordre de création d’une base et le cinquième précise l’heure de validation.

Maintenant, insérons une donnée dans notre table t1 :

postgres=# INSERT INTO t1 VALUES (10, ‘dix’); LOG: INSERT @ 0/104F390: prev 0/104F350; xid 418; bkpb1: Heap - insert: rel 1663/11511/16390; tid 0/6 LOG: INSERT @ 0/104F4D0: prev 0/104F390; xid 418; bkpb1: Btree - insert: rel 1663/11511/16396; tid 1/5 LOG: INSERT @ 0/104F58C: prev 0/104F4D0; xid 418: Transaction - commit: 2008-07-22 23:21:09.92561+02 LOG: xlog flush request 0/104F5B8; write 0/104F350; flush 0/104F350

Nous vérifions ainsi que le premier enregistrement concerne l’insertion dans la table (le relfilenode 16390 pointe bien vers la table t1), le second s’occupe des modifications de l’index et le dernier concerne la validation.

Ce qu’il faut tirer de tout ceci se résume en quelques points :

- Les journaux de transactions enregistrent toutes les modifications des fichiers de données avant que ceux-ci ne soient modifiés.

- D’autres événements, comme les CHECKPOINT, sont aussi enregistrés dans les journaux.

- Les journaux concernent toutes les bases de données.

4

Fonctionnalités avancées

4.1

Gestion des performances

La première façon d’améliorer les performances, c’est de faire en sorte de diminuer les écritures. Le meilleur moyen, c’est de configurer wal_buffers pour que cette partie de mémoire soit capable de conserver une transaction complète par processus exécuté en parallèle. En effet, une transaction va faire un certain nombre de modifications au niveau des fichiers correspondant aux tables et/ou index de la base. Tant que la transaction n’est pas terminée, l’écrire sur disque n’a aucun intérêt. Dans le cas d’un crash du serveur, ce qui est en mémoire sera perdu, mais comme la transaction n’est pas terminée, qu’elle soit ou non sur disque, les modifications ne devront pas être prises en compte. Donc, autant commencer par ne pas les écrire sur disque tant qu’un COMMIT n’a pas eu lieu. Le vrai problème n’est pas de comprendre l’intérêt de ce paramètre, mais plutôt de savoir le configurer. Certains pensent qu’un mégaoctet est une bonne valeur quand le serveur comprend plusieurs processeurs (car plusieurs processus peuvent s’exécuter réellement en parallèle, ce qui augmente le nombre de transactions en parallèle, et, de ce fait, la mémoire utilisée pour conserver en cache leurs modifications). D’autres réfléchissent plutôt en termes de taille d’un journal de transactions. 8 Mo semble une valeur assez populaire, car ça permet de faire tenir la moitié d’un journal de transactions en mémoire. Quelle que soit la valeur que vous choisirez, souvenez-vous que l’écriture du journal se fait toujours au plus tard au moment du COMMIT, que la mémoire soit pleine ou non. Donc, n’hésitez pas à augmenter ce paramètre, car 64 Ko est une valeur vraiment basse.

L’autre façon d’améliorer les performances revient à éviter de déplacer la tête de lecture du disque. Pour cela, il est préférable de mettre les journaux de transactions sur leur propre disque et le reste des données sur un autre (voire sur plusieurs autres). La mise en place est assez simple, mais demande l’arrêt du serveur le temps du déplacement des journaux :

guillaume@laptop$ pg_ctl stop [... messages coupés ...] guillaume@laptop$ mv $PGDATA/pg_xlog /opt/nouveau/disque guillaume@laptop$ ln -s /opt/nouveau/disque/pg_xlog guillaume@laptop$ pg_ctl start [... messages coupés ...]

L’outil Initdb de la version 8.3 dispose d’une nouvelle option, -X, permettant de placer immédiatement les journaux de transactions à un autre emplacement. Elle fait exactement la même chose : création du répertoire à l’emplacement indiqué, puis création d’un lien symbolique.

Installer un système RAID1 est généralement une bonne idée pour les journaux de transactions. Par contre, le RAID5 est déconseillé, car il est très lent pour les écritures et que les journaux de transactions ne sont, en temps normal, qu’écrits.

4.2

Reprise après arrêt brutal

Le gros intérêt des journaux de transactions tient au fait que PostgreSQL peut les rejouer. Par rejouer, on veut dire que le serveur est capable de les lire et de récupérer les éléments des journaux de transactions, un par un, pour les stocker dans les fichiers de données et ainsi obtenir des fichiers de données cohérents.

Si une transaction s’est bien terminée, que les modifications qu’elle a réalisées ont bien été enregistrées dans les journaux de transactions, mais que les fichiers de données n’ont pas eu le temps d’être mis à jour, la première étape du prochain redémarrage du serveur PostgreSQL consistera à relire les journaux de transactions pour enregistrer les données dans les fichiers de données. Pendant cette période de restauration, les connexions ne seront pas possible. Une fois la restauration terminée, les utilisateurs pourront de nouveau lancer des requêtes.

Voici un exemple des traces associées à la reprise après un crash :

LOG: database system was interrupted at 2008-07-15 15:26:03 CEST LOG: checkpoint record is at 9/E43B3B00 LOG: redo record is at 9/E43B3B00; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 109920594; next OID: 9355796 LOG: next MultiXactId: 34; next MultiXactOffset: 67 LOG: database system was not properly shut down; automatic recovery in progress LOG: record with zero length at 9/E43B3B44 LOG: redo is not required LOG: database system is ready

PostgreSQL se rend compte qu’il a été arrêté brutalement (database system was interrupted). L’enregistrement à recommencer commence à l’emplacement 9/E43B3B00, et PostgreSQL lance la restauration (automatic recovery in progress). Il n’y a qu’un enregistrement de longueur nul après l’emplacement cité. Du coup, la restauration n’est pas nécessaire et le système est prêt.

4.3

PITR

Comme il est possible de restaurer les fichiers de données à partir des journaux de transactions, certains développeurs se sont dit qu’il serait possible de restaurer un serveur à partir d’une sauvegarde des fichiers à un instant t et des journaux de transactions créés à partir de cet instant. La difficulté rencontrée à l’époque était de récupérer le journal de transactions une fois que ce dernier est terminé, mais avant qu’il ne soit renommé pour être réutilisé. Pour cela, un nouveau paramètre a vu le jour : archive_command.

Ce paramètre doit indiquer la commande à exécuter pour archiver un journal de transactions. Cette commande peut être cp, scp, rsync ou toute autre commande, voire un script ou un programme développé par l’administrateur. Il y a une seule obligation : la commande doit renvoyer 0 en cas de succès de l’archivage. Tant que le serveur ne récupère pas ce code de statut 0, il conserve le journal de transactions et ré-exécute la commande d’archivage.

Le cycle de vie d’un journal de transactions devient donc :

- tant que le journal n’est pas plein ou que le délai archive_timeout n’est pas dépassé ou que pg_switch_xlog() n’est pas exécuté :

- utilisation du journal pour y stocker les données et métadonnées ;

- si archive_command est renseigné et tant que le journal n’est pas archivé :

- archivage du journal par la commande archive_command ;

- renommage du journal pour ré-utilisation (ou suppression si le nombre de journaux actuellement présents est trop important).

4.3.1

La sauvegarde

Voici comment mettre en place l’archivage des journaux de transactions. Dans cet exemple, l’archivage se fait sur le même disque, mais, dans la vraie vie, il faut qu’il soit au minimum sur un disque séparé, voire sur un autre serveur.

Il faut commencer par créer le répertoire d’archivage :

guillaume@laptop:~$ mkdir /tmp/pg_xlog_archives

Ensuite, il faut modifier la configuration de PostgreSQL. Plutôt que de modifier le fichier principal, on va lui ajouter un simple include vers le fichier de configuration local nommé archivage.conf :

guillaume@laptop:~$ cat >> $PGDATA/postgresql.conf <<fin_config > include ‘archivage.conf’ > fin_config guillaume@laptop:~$ cat > $PGDATA/archivage.conf <<fin_config > archive_mode = on > archive_command = ‘cp %p /tmp/pg_xlog_archives/%f’ > fin_config

La modification de archive_command se satisfait très bien d’un simple rechargement de la configuration. Par contre, la modification de archive_mode nécessite le redémarrage. Nous allons donc redémarrer le serveur :

guillaume@laptop:~$ pg_ctl restart LOG: shutting down LOG: database system is shut down done server stopped server starting LOG: database system was shut down at 2008-07-12 17:40:30 CEST LOG: autovacuum launcher started LOG: database system is ready to accept connections

Connectons-nous au serveur et essayons de générer des journaux de transactions :

guillaume@laptop:~$ psql -q postgres postgres=# \! ls -lhG /tmp/pg_xlog_archives total 0

Le nouveau répertoire ne contient aucun fichier.

postgres=# SELECT pg_xlogfile_name(pg_current_xlog_location()); pg_xlogfile_name -------------------------- 000000010000000000000007 (1 row)

Nous en sommes au journal de transactions 7. Ce fichier sera archivé une fois qu’il sera rempli. Lançons un ensemble d’INSERT :

postgres=# INSERT INTO t1 SELECT x FROM generate_series(1, 300000) AS x; postgres=# SELECT pg_xlogfile_name(pg_current_xlog_location()); pg_xlogfile_name -------------------------- 000000010000000000000008 (1 row)

Nous sommes bien passés au nouveau journal de transactions...

postgres=# \! ls -lhG /tmp/pg_xlog_archives total 17M -rw------- 1 guillaume 16M 2008-07-13 16:33 000000010000000000000007 ... et le journal 7 a bien été archivé.

L’archivage étant configuré, nous pouvons passer à la sauvegarde des fichiers. Cela se passe en trois étapes :

1. prévenir PostgreSQL du lancement de la sauvegarde fichiers ;

2. sauvegarder les fichiers ;

3. et prévenir PostgreSQL de la fin de la sauvegarde.

La fonction pg_start_backup() est utilisée pour la première étape. Cette fonction commence par un CHECKPOINT pour s’assurer que toutes les données modifiées en mémoire sont bien enregistrées sur disque, puis crée un fichier backup_label donnant quelques informations sur le moment de la sauvegarde. Enfin, elle renvoie l’emplacement actuel dans le journal de transactions :

postgres=# SELECT pg_start_backup(‘sauve_20080713_1634’); pg_start_backup ----------------- 0/84C5654 (1 row) postgres=# \q guillaume@laptop:~$ cat $PGDATA/backup_label START WAL LOCATION: 0/84C5654 (file 000000010000000000000008) CHECKPOINT LOCATION: 0/84C65C4 START TIME: 2008-07-13 16:36:55 CEST LABEL: sauve_20080713_1634

START WAL LOCATION indique le journal de transactions courant au début de la sauvegarde ainsi que la position au sein de ce fichier. CHECKPOINT LOCATION indique la position du dernier CHECKPOINT (les deux positions doivent être identiques vu que le CHECKPOINT est exécuté par la fonction pg_start_backup()). START_TIME indique le jour et l’heure de la sauvegarde. Enfin LABEL correspond à la valeur de l’argument lors de l’appel de la fonction pg_start_backup().

Il ne nous reste plus qu’à faire la sauvegarde au niveau fichiers. N’importe quel outil habituel ira bien : tar, rsync, cp, etc. Peu importe la durée de la sauvegarde, les utilisateurs peuvent toujours se connecter, faire les modifications qu’ils souhaitent... la base de données peut continuer à vivre normalement, sans aucune restriction, pendant la sauvegarde des fichiers.

guillaume@laptop:~$ tar cjf glmf.tar.bz2 glmf

Pendant qu’il fait la sauvegarde, créons une base de données et peuplons-la :

guillaume@laptop:~$ createdb db1 guillaume@laptop:~$ psql -q db1 db1=# CREATE TABLE t2 (id2 int4); db1=# INSERT INTO t2 SELECT x FROM generate_series(1,1000) AS x; db1=# \q

Aucun message d’erreur de la base de données. Les utilisateurs peuvent donc vraiment modifier le schéma comme les données de l’instance. Par contre, tar pourrait se plaindre que des fichiers sont modifiés pendant leur sauvegarde.

Peu importe pour nous, car toutes les modifications réalisées en ce moment sont enregistrées dans les journaux de transactions qui sont archivés. C’est d’ailleurs pour cela qu’il faut commencer par mettre en place l’archivage des journaux avant de réaliser la sauvegarde des fichiers.

Une fois la sauvegarde terminée, nous prévenons PostgreSQL de la fin de la sauvegarde en exécutant la fonction pg_stop_backup().

guillaume@laptop:~$ psql -q postgres postgres=# SELECT pg_stop_backup(); pg_stop_backup ---------------- 0/84E7980 (1 row)

Cette dernière renvoie l’emplacement actuel dans le journal de transactions. Créons une nouvelle table et insérons-y des données :

postgres=# CREATE TABLE t3 (id int4); postgres=# INSERT INTO t3 SELECT x FROM generate_series(1, 1000000) AS x; postgres=# SELECT now(); now ------------------------------- 2008-07-13 16:39:34.954131+02 (1 row) [... un peu plus tard ...] postgres=# SELECT now(); now ------------------------------- 2008-07-13 16:40:09.445774+02 (1 row) postgres=# INSERT INTO t3 SELECT x FROM generate_series(4000000,5000000) AS x; postgres=# SELECT now(); now ------------------------------- 2008-07-13 16:40:33.375076+02 (1 row)

Pour nous assurer que les dernières modifications sont bien archivées, nous allons nous-mêmes demander un changement de journal de transactions :

postgres=# SELECT pg_switch_xlog(); pg_switch_xlog ---------------- 0/FB20BF0 (1 row) postgres=# \q

4.3.2

Une restauration complète

Elle se passe en trois grosses étapes :

- restauration de la sauvegarde des fichiers ;

- configuration de la restauration des journaux de transactions ;

- lancement du serveur.

Nous allons stopper le serveur, déplacer l’ancien répertoire de l’instance pour pouvoir déballer le contenu de l’archive tar :

guillaume@laptop:~$ pg_ctl stop waiting for server to shut down....LOG: received smart shutdown request LOG: autovacuum launcher shutting down LOG: shutting down LOG: database system is shut down done server stopped guillaume@laptop:~$ mv glmf glmf.old guillaume@laptop:~$ tar xjf glmf.tar.bz2

La restauration se configure dans le fichier recovery.conf. Un seul paramètre nous intéresse actuellement, celui qui va indiquer la commande permettant de récupérer le journal de transactions :

guillaume@laptop:~$ cat >> $PGDATA/recovery.conf <<fin_config > restore_command = ‘cp /tmp/pg_xlog_archives/%f %p’ > fin_config

Il nous faut aussi supprimer l’archivage (ou archiver ailleurs que dans l’ancien répertoire) pour éviter que PostgreSQL n’archive de nouveau les journaux qu’il restaure.

guillaume@laptop:~$ > $PGDATA/archivage.conf

Il ne nous reste plus qu’à démarrer le serveur PostgreSQL :

guillaume@laptop:~$ pg_ctl start pg_ctl: another server might be running; trying to start server anyway LOG: database system was interrupted; last known up at 2008-07-13 16:38:17 CEST LOG: starting archive recovery LOG: restore_command = ‘cp /tmp/pg_xlog_archives/%f %p’ cp: cannot stat `/tmp/pg_xlog_archives/00000001.history’: No such file or directory LOG: restored log file "000000010000000000000008.004C5654.backup" from archive LOG: restored log file "000000010000000000000008" from archive LOG: automatic recovery in progress server starting LOG: redo starts at 0/84C5654 LOG: restored log file "000000010000000000000009" from archive LOG: restored log file "00000001000000000000000A" from archive LOG: restored log file "00000001000000000000000B" from archive LOG: restored log file "00000001000000000000000C" from archive LOG: restored log file "00000001000000000000000D" from archive LOG: restored log file "00000001000000000000000E" from archive LOG: restored log file "00000001000000000000000F" from archive

cp: cannot stat `/tmp/pg_xlog_archives/000000010000000000000010’: No such file or directory LOG: could not open file "pg_xlog/000000010000000000000010" (log file 0, segment 16): No such file or directory LOG: redo done at 0/FB20BD4 LOG: last completed transaction was at log time 2008-07-13 16:40:30.632653+02 LOG: restored log file "00000001000000000000000F" from archive cp: cannot stat `/tmp/pg_xlog_archives/00000002.history’: No such file or directory LOG: selected new timeline ID: 2 cp: cannot stat `/tmp/pg_xlog_archives/00000001.history’: No such file or directory LOG: archive recovery complete LOG: autovacuum launcher started LOG: database system is ready to accept connections guillaume@laptop:~$

Quelques explications sur les traces... database system was interrupted indique que la base a été arrêtée de façon anormale. C’est tout à fait normale, la sauvegarde s’est faite alors que le serveur était toujours en cours d’exécution. Ce message n’est donc pas du tout alarmant dans ce cadre.

starting archive recovery indique que le serveur entre dans son mode de restauration. Il indique d’ailleurs la commande de copie des journaux de transactions sur la ligne suivante. Il tente de récupérer un fichier historique sans y parvenir, ce qui n’est pas alarmant en soi. Enfin, il se met à restaurer les journaux de transactions (message du type restored log file "000000010000000000000009").

Une fois la restauration terminée, PostgreSQL va basculer sur une nouvelle TimeLineID (selected new timeline ID: 2). Il indique la fin de la restauration avec le message archive recovery complete et le système est prêt à accepter des connexions des utilisateurs (database system is ready to accept connections).

Dans ce cas, connectons-nous et regardons l’état de la base de données :

guillaume@laptop:~$ psql -q postgres postgres=# \d List of relations Schema | Name | Type | Owner --------+------+-------+----------- public | t1 | table | guillaume public | t3 | table | guillaume (2 rows)

Nous avons nos deux tables... donc nous avons bien la table créée après la sauvegarde de base.

postgres=# SELECT count(*) FROM t1; count --------- 2400003 (1 row) postgres=# SELECT count(*) FROM t3; count --------- 2000000 (1 row)

Et nous avons aussi les données. Concernant la base de données créées pendant la sauvegarde des fichiers :

postgres=# \l List of databases Name | Owner | Encoding -----------+-----------+---------- db1 | guillaume | UTF8 postgres | guillaume | UTF8 template0 | guillaume | UTF8 template1 | guillaume | UTF8 (4 rows) Elle est bien présente. postgres=# \c db1 db1=# \d List of relations Schema | Name | Type | Owner --------+------+-------+----------- public | t2 | table | guillaume (1 row) La table t2 est aussi là. db1=# SELECT count(*) FROM t2; count ------- 1000 (1 row)

Les 1000 lignes insérées sont aussi disponibles.

Nous sommes passés un peu rapidement sur le message concernant la TimeLineID. Nous sommes passés en TimeLineID 2 et cela se reflète dans les noms des nouveaux journaux de transactions :

guillaume@laptop:~$ ls -lhG $PGDATA/pg_xlog total 145M -rw------- 1 guillaume 16M 2008-07-13 16:36 000000010000000000000008 -rw------- 1 guillaume 16M 2008-07-13 16:22 000000010000000000000009 -rw------- 1 guillaume 16M 2008-07-13 16:23 00000001000000000000000A -rw------- 1 guillaume 16M 2008-07-13 16:23 00000001000000000000000B -rw------- 1 guillaume 16M 2008-07-13 16:23 00000001000000000000000C -rw------- 1 guillaume 16M 2008-07-13 16:23 00000001000000000000000D -rw------- 1 guillaume 16M 2008-07-13 16:23 00000001000000000000000E -rw------- 1 guillaume 16M 2008-07-13 16:43 00000002000000000000000F -rw------- 1 guillaume 16M 2008-07-13 16:43 000000020000000000000010 -rw------- 1 guillaume 74 2008-07-13 16:43 00000002.history drwx------ 2 guillaume 4,0K 2008-07-13 16:43 archive_status

De même, nous avons maintenant un fichier historique pour cette TimeLineID.

4.3.3

Restauration partielle

Et si nous avions voulu restaurer jusqu’à un certain moment ? Il est tout à fait possible d’indiquer jusqu’à quand vous voulez restaurer grâce à deux paramètres :

- recovery_target_time permet de préciser une heure d’arrêt de la restauration.

- recovery_target_xid permet d’indiquer la dernière transaction à restaurer en la désignant par son identifiant. Cette méthode est pratiquement inutilisable actuellement, car il n’existe pas de moyens pour lier une transaction à son identifiant en dehors de tracer toutes les requêtes avec l’identifiant de transaction associé (caractère joker %x pour le paramètre log_line_prefix).

Si nous reprenons notre sauvegarde de base et que nous cherchions à la restaurer juste entre les deux insertions dans t3. La deuxième insertion s’étant faite après 16h40, nous devrions pouvoir restaurer juste avant.

Le début est identique à la restauration précédente :

guillaume@laptop:~$ pg_ctl stop waiting for server to shut down...LOG: received smart shutdown request .LOG: autovacuum launcher shutting down LOG: shutting down LOG: database system is shut down ... done server stopped guillaume@laptop:~$ rm -r glmf guillaume@laptop:~$ tar xjf glmf.tar.bz2 guillaume@laptop:~$ cat >> $PGDATA/recovery.conf <<fin_config > restore_command = ‘cp /tmp/pg_xlog_archives/%f %p’ > recovery_target_time = ‘2008-07-13 16:40:00’ > fin_config guillaume@laptop:~$ > /home/guillaume/glmf/archivage.conf

Remarquez l’ajout du paramètre recovery_target_time précisant la date et l’heure de fin de la restauration.

Enfin, nous lançons PostgreSQL. Les messages du serveur vont légèrement changer.

guillaume@laptop:~$ pg_ctl start pg_ctl: another server might be running; trying to start server anyway LOG: database system was interrupted; last known up at 2008-07-13 16:38:17 CEST LOG: starting archive recovery LOG: restore_command = ‘cp /tmp/pg_xlog_archives/%f %p’ LOG: recovery_target_time = ‘2008-07-13 16:40:00+02’ cp: cannot stat `/tmp/pg_xlog_archives/00000001.history’: No such file or directory LOG: restored log file "000000010000000000000008.004C5654.backup" from archive LOG: restored log file "000000010000000000000008" from archive LOG: automatic recovery in progress server starting guillaume@laptop:~$ LOG: redo starts at 0/84C5654 LOG: restored log file "000000010000000000000009" from archive LOG: restored log file "00000001000000000000000A" from archive LOG: restored log file "00000001000000000000000B" from archive LOG: restored log file "00000001000000000000000C" from archive LOG: recovery stopping before commit of transaction 408, time 2008-07-13 16:40:01.887323+02 LOG: redo done at 0/C59D288 LOG: last completed transaction was at log time 2008-07-13 16:40:01.887323+02 cp: cannot stat `/tmp/pg_xlog_archives/00000002.history’: No such file or directory LOG: selected new timeline ID: 2 cp: cannot stat `/tmp/pg_xlog_archives/00000001.history’: No such file or directory LOG: archive recovery complete LOG: autovacuum launcher started LOG: database system is ready to accept connections guillaume@laptop:~$ psql -q postgres postgres=# SELECT count(*) FROM t3; count --------- 1000000 (1 row) postgres=#

La ligne recovery_target_time = ‘2008-07-13 16:40:00+02’ indique qu’il a bien compris que la restauration ne sera pas complète, car elle devra se terminer dans l’état cohérent à 16h40.

D’ailleurs, nous voyons que la restauration s’achève au journal de transactions C (contrairement à auparavant où il avait été jusqu’au F). recovery stopping before commit of transaction 408, time 2008-07-13 16:40:01.887323+02 indique la fin de la restauration, juste avant la validation de la transaction 408 qui a eu lieu à 16h40 et une seconde. Trop tard pour celle-ci.

4.4

LogShipping

Restaurer une sauvegarde des fichiers avec les journaux de transactions associés peut prendre beaucoup de temps. C’est pour cela que la version 8.2 a ajouté une fonctionnalité supplémentaire appelée " Log Shipping ". L’idée est d’avoir un serveur en attente des journaux de transactions archivés pour pouvoir les restaurer dès réception.

L’installation du serveur principal se fait comme au chapitre précédent. Sur le serveur secondaire, nous devons restaurer la sauvegarde de base, ajouter le fichier recovery.conf et lancer PostgreSQL. Cependant, la commande de restauration, celle pointée par le paramètre restore_command, doit tout d’abord attendre l’arrivée du journal de transactions avant de pouvoir le fournir à PostgreSQL.

Nous ne pouvons donc plus utiliser des commandes simples comme cp ou scp. Il est nécessaire d’écrire un script... ou de récupérer un outil tout fait, comme pg_standby disponible en tant que module contrib sur la version 8.3.

pg_standby est un outil développé par Simon Riggs. Son utilisation est très simple. Il demande en argument l’emplacement des journaux archivés, le nom du prochain journal à restaurer et l’endroit où le copier. Certaines options permettent d’aller plus loin :

- l’option -d permet d’avoir des traces supplémentaires ;

- l’option -l permet de créer un lien plutôt que de copier le fichier ;

- l’option -s permet de modifier l’attente entre deux vérifications (par défaut, cinq secondes) ;

- l’option -t permet de spécifier un fichier qui annulera le mode restauration ;

- etc.

Mettons donc en place le Log Shipping. Nous supposons que le serveur maître est déjà configuré pour archiver les journaux de transactions. Nous allons simplement modifier la configuration pour que les journaux soient copiés sur un serveur nommé esclave.

guillaume@laptop:~$ cat > $PGDATA/archivage.conf <<fin_config > archive_mode = on > archive_command = ‘scp %p esclave:/tmp/pg_xlog_archives/%f’ > fin_config

Note
Pour permettre la connexion à l’esclave avec SSH sans indiquer son mot de passe, il est nécessaire de paramétrer l’utilisation de clés et de générer ces clés, mais ce travail est en dehors du périmètre de cet article.

Comme nous n’avons modifié que le paramètre archive_command, nous pouvons simplement recharger la configuration sans redémarrer le serveur :

guillaume@laptop:~$ pg_ctl reload

Ensuite, nous devons mettre en place l’esclave. Commençons par restaurer la sauvegarde des fichiers :

guillaume@esclave:~$ tar xjf glmf.tar.bz2 guillaume@esclave:~$ mv glmf glmf.esclave guillaume@esclave:~$ export PGDATA=/home/guillaume/glmf.esclave

La restauration se configure dans le fichier recovery.conf. Un seul paramètre nous intéresse actuellement, celui qui va indiquer la commande permettant de récupérer le journal de transactions :

guillaume@esclave:~$ cat >> $PGDATA/recovery.conf <<fin_config > restore_command = ‘pg_standby -d /tmp/pg_xlog_archives %f %p >> /home/guillaume/glmf.esclave/pg_standby.log 2>&1’ > fin_config

Il nous faut aussi supprimer l’archivage (ou archiver ailleurs que dans l’ancien répertoire) pour éviter que PostgreSQL n’archive de nouveau les journaux qu’il restaure.

guillaume@esclave:~$ > $PGDATA/archivage.conf

Il ne nous reste plus qu’à démarrer le serveur PostgreSQL :

guillaume@esclave:~$ pg_ctl start server starting LOG: database system was interrupted; last known up at 2008-07-14 10:53:26 CEST LOG: starting archive recovery LOG: restore_command = ‘pg_standby -d /tmp/pg_xlog_archives %f %p >>/home/guillaume/glmf.esclave/recovery.log 2>&1’ cp: cannot stat `/tmp/pg_xlog_archives/00000001.history’: No such file or directory cp: cannot stat `/tmp/pg_xlog_archives/00000001.history’: No such file or directory cp: cannot stat `/tmp/pg_xlog_archives/00000001.history’: No such file or directory LOG: restored log file "000000010000000000000002.00470154.backup" from archive LOG: restored log file "000000010000000000000002" from archive LOG: automatic recovery in progress LOG: restored log file "000000010000000000000003" from archive LOG: restored log file "000000010000000000000004" from archive LOG: restored log file "000000010000000000000005" from archive

Comme la fois précédente, le serveur comprend qu’il est en mode de restauration. Il récupère les journaux 2, 3, 4 et 5.

Maintenant, créons une nouvelle table et générons des données (sur le serveur principal) :

postgres=# CREATE TABLE t2 (id int4); postgres=# INSERT INTO t2 SELECT x FROM generate_series(1, 300000) AS x; postgres=# SELECT pg_switch_xlog(); pg_switch_xlog ---------------- 0/704EDB0 (1 row)

La fonction pg_switch_xlog() nous assure que le serveur principal change de journal de transactions courant et, du coup, que les données vont être envoyées maintenant au serveur secondaire.

Que constatons-nous sur l’esclave ?

guillaume@esclave:~$ tail -f glmf.esclave/pg_standby.log WAL file not present yet. WAL file not present yet. WAL file not present yet. WAL file not present yet. running restore : OK Trigger file : <not set> Waiting for WAL file : 000000010000000000000006 WAL file path : /tmp/pg_xlog_archives/000000010000000000000006 Restoring to... : pg_xlog/RECOVERYXLOG Sleep interval : 5 seconds Max wait interval : 0 forever Command for restore : cp "/tmp/pg_xlog_archives/000000010000000000000006" "pg_xlog/RECOVERYXLOG" Keep archive history : No cleanup required WAL file not present yet. WAL file not present yet.

Quelques explications sur les traces de pg_standby. WAL file not present est le message habituel indiquant qu’il cherche le prochain journal de transactions, mais qu’il ne le trouve pas. Une fois le journal découvert, il lance la restauration (message running restore : OK).

Il affiche quelques autres informations, comme le nom du journal attendu, le chemin complet vers ce fichier, l’endroit où il va le restaurer, la commande complète qu’il va utiliser. Puis, il se retrouve de nouveau en attente.

Arrêtons l’esclave pour voir son état...

guillaume@esclave:~$ ps -ef | grep [p]g_standby 1000 8385 8272 0 11:00 ? 00:00:00 sh -c pg_standby -d /tmp/pg_xlog_archives 000000010000000000000008 pg_xlog/RECOVERYXLOG >>/home/guillaume/glmf.esclave/recovery.log 2>&1 1000 8386 8385 0 11:00 ? 00:00:00 pg_standby -d /tmp/pg_xlog_archives 000000010000000000000008 pg_xlog/RECOVERYXLOG guillaume@esclave:~$ kill -INT 8386 LOG: could not open file "pg_xlog/000000010000000000000008" (log file 0, segment 8): No such file or directory LOG: redo done at 0/704ED94 LOG: last completed transaction was at log time 2008-07-14 11:00:04.022844+02 LOG: restored log file "000000010000000000000007" from archive LOG: selected new timeline ID: 2 LOG: archive recovery complete LOG: autovacuum launcher started LOG: database system is ready to accept connections guillaume@esclave:~$ psql -q postgres postgres=# \d List of relations Schema | Name | Type | Owner --------+------+-------+----------- public | t1 | table | guillaume public | t2 | table | guillaume (2 rows) postgres=# \q

La nouvelle table est présente sur l’esclave. De même pour les données.

Note
Il existe d’autres outils pour gérer le Log Shipping, Walmgr étant l’un des plus connus. Il fait partie de l’ensemble d’outils libres développé par Skype. Pour plus d’informations, consultez https://developer.skype.com/SkypeGarage/DbProjects/SkyTools/WalMgr.

5

Nouveautés en 8.3

En dehors du module contrib pg_standby, la version 8.3 propose d’autres améliorations concernant les journaux de transactions.

La première est l’ajout d’un processus dédié à l’écriture des informations dans le journal des transactions courant. À l’instar du background writer, le wal writer process décharge les processus Postgres de l’écriture sur les journaux de transactions. Ce travail réalisé, le processus s’endort pendant wal_writer_delay milli-secondes (200 par défaut).

L’autre très importante nouveauté de la 8.3 concerne la performance des journaux de transactions. Nous avons vu précédemment que désactiver fsync avait un gros intérêt pour gagner en performances si on négligeait la sécurité des données. La 8.3 dispose d’un mode permettant de gagner en rapidité tout en conservant un bon niveau de sécurité. Il s’agit du asynchronous commit ou, autrement dit, de l’enregistrement asynchrone. En activant ce paramètre, PostgreSQL repousse (très légèrement, d’au plus 100 ms) le moment de l’enregistrement pour pouvoir enregistrer plusieurs transactions à la fois, ce qui fait gagner nettement en performances, principalement lors d’insertions ou de modifications massives (par exemple lors du chargement d’une sauvegarde). L’autre gros intérêt de ce mode est qu’il peut s’activer par session.

De plus, des efforts ont été réalisés pour limiter les écritures dans les journaux de transactions. Par exemple, les modifications effectuées par l’opération CLUSTER ne passent plus par les journaux de transactions. De même, un COPY suivant dans la même transaction la création de la table n’est pas tracé dans les journaux de transactions. Pour ces deux cas, les écritures des journaux de transactions sont évitées si et seulement si l’archivage est désactivé.

restore_command permet l’utilisation d’un nouveau caractère joker, %r. Ce dernier indique le premier journal de transactions réellement utile, ce qui permet aux outils comme pg_standby de supprimer les journaux de transactions antérieurs.

6

Le futur... vers la 8.4 et au-delÃ

PGCon 2008 a permis aux développeurs importants du projet de se rencontrer et de se mettre d’accord sur la mise en place d’une réplication encore plus fine. Cette fois, ce ne sont plus les journaux de transactions qui sont envoyés, mais l’ensemble des modifications d’une transaction. Donc, la réplication proposée sera réalisée transaction par transaction, ce qui permettra d’avoir une vraie réplication synchrone. Si les développements avancent bien, cette fonctionnalité fera partie de la version 8.4. Il s’agit tout simplement d’un LogShipping au niveau des transactions et non plus des groupes de transactions que sont les journaux.

Une autre fonctionnalité très intéressante serait d’avoir des esclaves disponibles en lecture seule. Malheureusement, les développeurs tablent plutôt sur la version 8.5 pour cette fonctionnalité.

7

Rappel sur la configuration

Il y a quelques paramètres de configuration à connaître. Nous en avons déjà fait le tour, ce n’est qu’un résumé avec la valeur par défaut pour chacun.

Nom du paramètre	Par défaut	Commentaires
Configuration globale
fsync	on	active la synchronisation sur disque
synchronous_commit	on	active la synchronisation sur disque au moment du COMMIT
wal_sync_method	fsync	méthode de synchronisation
full_page_writes	on	écriture préalable de la page complète
wal_buffers	64 Ko	tampon disque des journaux de transactions
wal_writer_delay	200 ms	délai entre deux réveils du processus wal writer
commit_delay	0	délai en microsecondes observé après le COMMIT d’une transaction et son écriture sur disque
commit_siblings	5	nombre minimum de transactions en parallèle pour activer le délai commit_delay
Checkpoints
checkpoint_segments	3	nombre de segments de 16 Mo
checkpoint_timeout	5 min	délai entre deux CHECKPOINT automatiques
checkpoint_completion_target	0.5
checkpoint_warning	30 s	Enregistrement d’un message dans les traces si deux CHECKPOINT sont générés l’un à la suite de l’autre dans un délai inférieur à ce paramètre
Archivage
archive_mode	off	active l’archivage
archive_command	‘’	commande à utiliser pour l’archivage
archive_timeout	0	Délai avant passage automatique à un nouveau journal des transactions

8

Conclusion

Les journaux de transactions sont un moyen d’assurer la sécurité de vos données. Cela a un léger coût en termes de performances sauf si vous avez la prudence de les placer sur un disque séparé.

Ils vous permettent de plus une sauvegarde incrémentale d’une mise en place simplissime.

Retrouvez cet article dans : Linux Magazine 108