Comment PostgreSQL stocke les données : structure de la base et journal des transactions (WAL)

Quand tu bosses avec une base de données, tout paraît super simple : t’ajoutes une ligne, tu mets à jour un enregistrement, tu vires un client. Mais derrière cette simplicité, il y a tout un mécanisme bien pensé et un peu complexe. Où sont vraiment stockées ces données ? Comment PostgreSQL arrive à rien perdre, même si le serveur plante d’un coup ?

Pour piger ça, il faut comprendre deux trucs clés : où sont physiquement les données (tables, index, infos système) et comment marche le mécanisme de protection de ces données — le journal des transactions, alias WAL (Write-Ahead Logging).

Toute la base PostgreSQL est stockée sous forme de fichiers dans un dossier spécial — data_directory. En général, il est ici :

/var/lib/postgresql/17/main

Dans ce dossier, t’as tout le cœur de la base : les tables, les index, la méta, la config. Et aussi le journal WAL — c’est le mécanisme qui prend tous les changements en premier. Avant que les données arrivent dans la table sur le disque, elles sont écrites dans le WAL. C’est comme un brouillon où la base note chaque étape, pour qu’en cas de crash, on puisse tout restaurer jusqu’à la dernière opération.

Grâce à cette approche, PostgreSQL assure fiabilité et robustesse, même dans les pires conditions.

Tables

Chaque table, c’est physiquement un fichier séparé ou un ensemble de fichiers. Ces fichiers sont dans le sous-dossier base/. La structure ressemble à ça :

$PGDATA/base/
├── 16384/
│   ├── 12345   ← table
│   ├── 12346   ← index
│   └── ...

• 16384 — c’est l’identifiant interne de la base (OID).
• 12345 — l’identifiant de la table en question.

Si la table est grosse, PostgreSQL la découpe en segments de 1 Go :

12345
12345.1
12345.2
...

Les fichiers ne contiennent pas des "lignes" comme dans un CSV texte — c’est un format de "pages binaires" de 8 Ko.

WAL : Write-Ahead Logging — c’est pas juste un "log"

Passons maintenant à une des parties les plus importantes et souvent mal comprises de PostgreSQL — WAL, ou Write-Ahead Logging. Malgré le mot log dans le nom, le WAL, c’est pas un simple fichier log texte, comme les logs d’erreurs ou de requêtes. C’est un mécanisme vital de cohérence et de restauration des données, qui bosse au niveau des changements bas niveau du système de fichiers.

Le WAL, c’est pas un rapport d’événements, mais un enregistrement préalable de tous les changements que PostgreSQL va faire sur les données. Cet enregistrement se fait avant la vraie modif des tables sur le disque. C’est pour ça que ça s’appelle write-ahead — "écriture en avance".

Quand tu insères une nouvelle ligne dans une table, PostgreSQL :

1. NE met pas à jour la table sur le disque tout de suite — ce serait lent et risqué.
2. D’abord, il écrit dans le WAL que cette ligne va être ajoutée.
3. Ensuite seulement, quand ça l’arrange (genre en tâche de fond), les données arrivent vraiment dans la table.

C’est comme un chèque à la banque : d’abord tu le signes (WAL), et ensuite la banque met à jour le compte (table). Si jamais y’a un souci — t’as toujours le chèque, tu peux refaire l’opération.

Format et structure du WAL

• Les fichiers WAL sont en format binaire.
• Chaque fichier, c’est un flux d’opérations strictement ordonné, qui décrit les changements internes des pages de données, des structures d’index, des commits, etc.
• Un fichier WAL a une taille fixe — par défaut 16 Mo.

Important : le WAL ne contient pas de "commandes SQL" ou de "lignes de table" comme tu les connais. Il contient des instructions pour le moteur PostgreSQL, pour rejouer les changements, page par page.

Que se passe-t-il en cas de crash ?

Si le serveur PostgreSQL s’éteint d’un coup — genre coupure de courant — tout n’est pas perdu. Au prochain démarrage, la base ne panique pas, elle charge tranquillement la dernière version "stable" des données depuis le disque. Ensuite, elle prend le journal des transactions (WAL), où il reste tous les derniers changements, et elle les "rejoue" proprement — elle applique ce qui n’a pas encore été écrit dans les fichiers principaux. Résultat : la base revient dans un état totalement cohérent, comme si rien ne s’était passé.

Fonctionnalités avancées du WAL

Point-In-Time Recovery (PITR). Garder les fichiers WAL permet de restaurer la base à n’importe quel instant entre deux backups complets.

Réplication en streaming. PostgreSQL peut envoyer les enregistrements WAL à un autre serveur en temps réel. Ça permet d’avoir une réplique chaude — une copie de la base synchronisée avec la principale.

Restauration incrémentale. Avec un backup complet, le WAL permet de restaurer seulement les changements, sans recopier toute la base à chaque fois.

Créer des backups binaires : pg_basebackup

Si tu t’es déjà un peu fait la main avec pg_dump, tu sais qu’il est top pour faire des backups logiques (c’est-à-dire copier la structure de la base et ses données sous forme de requêtes SQL). Mais si tu veux faire un backup physique ? Genre une copie miroir de tous les fichiers de la base ? Là, c’est l’outil pg_basebackup qui vient à la rescousse.

pg_basebackup, c’est une utilité qui permet de faire des copies physiques des données PostgreSQL. C’est super utile pour les grosses bases, où il faut gérer la restauration efficacement. Le gros avantage de pg_basebackup, c’est qu’il est super rapide.

Syntaxe de base de la commande pg_basebackup

Pour utiliser pg_basebackup, faut piger sa commande. Tu la lances dans ton terminal, et ça ressemble à ça :

pg_basebackup -D /backup_directory -F tar -z -P

Voyons ce que ça fait :

• -D /backup_directory — indique le dossier où seront stockés tes fichiers de backup.
• -F tar — format des données. L’option tar crée une archive au format .tar. Tu peux aussi utiliser plain pour avoir la structure de fichiers de la base.
• -z — compresse le backup, ça fait gagner de la place sur le disque. Toujours cool quand le backup prend moins de place !
• -P — affiche la progression en temps réel. C’est rassurant : tu vois que ça avance, et que le serveur n’est pas "planté".

Exemple d’utilisation :

pg_basebackup -D /backups/university_backup -F tar -z -P

Après la commande, dans le dossier /backups/university_backup, tu trouves une copie de la base au format .tar.

Avantages de pg_basebackup

Efficacité : les backups incrémentaux évitent de dupliquer les données inchangées, ça fait gagner du temps et de la place.

Facile à utiliser : l’outil pg_basebackup gère tout tout seul, y compris les fichiers WAL.

Fiabilité : grâce à l’intégration avec la mécanique PostgreSQL, pg_basebackup crée des copies exactes de toute la base, faciles à restaurer.

Exemples d’utilisation

Passons à la pratique ! Voici des exemples concrets pour utiliser pg_basebackup et faire des backups de ta base PostgreSQL. On va voir comment faire un backup de base, comment ajouter la compression, et comment activer l’archivage du journal des transactions (WAL) pour pouvoir restaurer "à un instant T". Ces commandes sont parfaites pour débuter ou pour des scénarios plus avancés.

Créer un backup de base

pg_basebackup -D /backups/full_backup -F tar -z -P

Résultat : un backup complet de la base dans une archive .tar.

Configurer la compression et le format

On va créer un backup avec un niveau de compression élevé :

pg_basebackup -D /backups/full_backup -F tar -z -Z 9 -P

Ici, -Z 9 indique le niveau de compression (max = 9).

Archivage du WAL

Si tu configures l’archivage du WAL, la base peut être restaurée à n’importe quel moment. La commande pour activer le backup du WAL :

pg_basebackup -D /backups/incremental_backup -F tar -z -P --wal-method=archive