Création d’un labo local PostgreSQL avec exemples pratiques

Introduction

Mettre en place un laboratoire local PostgreSQL est l’un des investissements les plus précieux qu’un développeur, un consultant DevOps ou une équipe de startup puisse faire. Cela offre un environnement sûr et isolé où vous pouvez tester des modifications de schéma, expérimenter des paramètres de configuration, simuler des pannes et pratiquer des procédures de sauvegarde sans risquer les données de production. Contrairement aux tests ponctuels sur un serveur partagé, un laboratoire local vous donne un contrôle total sur les versions, les extensions et les limites de ressources, et il peut être détruit et reconstruit à volonté.

Un laboratoire local bien structuré sert également d’environnement d’apprentissage cohérent pour les nouveaux membres de l’équipe. En suivant un processus de configuration documenté, vous garantissez que tout le monde travaille avec la même version et la même configuration de PostgreSQL, réduisant ainsi le problème du « fonctionne sur ma machine ». De plus, avoir un laboratoire local encourage l’expérimentation : vous pouvez essayer des opérations risquées comme la récupération à un point dans le temps, la mise en place de la réplication ou les mises à niveau d’extensions sans crainte.

Ce guide présente une configuration complète et pratique d’un laboratoire local PostgreSQL sur un poste de travail Linux. Nous couvrirons l’inventaire de l’environnement, la configuration sécurisée, la vérification avec des commandes concrètes et des sorties attendues, les modes de défaillance courants et la récupération, ainsi qu’une liste de vérification opérationnelle pour garder votre laboratoire sain. Tous les exemples utilisent PostgreSQL 16 sur Ubuntu 22.04, mais les principes s’appliquent à d’autres plateformes et versions.

Inventaire des versions et de l’environnement

Avant d’installer ou de configurer quoi que ce soit, faites l’inventaire de votre environnement. Cette étape garantit la reproductibilité et aide à diagnostiquer les problèmes ultérieurement. Pour un laboratoire local, vous devez connaître :

• Le système d’exploitation et la version du noyau
• L’espace disque et la mémoire disponibles
• La version de PostgreSQL à installer
• Les emplacements du port et du répertoire de données

Vérifiez votre système d’exploitation et votre noyau avec :

lsb_release -a
uname -r

Sortie attendue (exemple) :

No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 22.04.3 LTS
Release:        22.04
Codename:       jammy
6.2.0-39-generic

Vérifiez l’espace disque et la mémoire disponibles :

df -h /var/lib/postgresql
free -h

Sortie attendue (exemple) :

Filesystem      Size  Used Avail Use% Mounted on
/dev/sda1       200G   50G  150G  25% /
              total        used        free      shared  buff/cache   available
Mem:           15Gi       2.1Gi        12Gi       245Mi       1.2Gi        13Gi
Swap:          2.0Gi          0B       2.0Gi

Assurez-vous que PostgreSQL ne tourne pas déjà depuis un paquet système pour éviter les conflits de ports. Vérifiez avec :

pg_isready

Si PostgreSQL est en cours d’exécution, vous verrez :

/tmp:5432 - accepting connections

Sinon, la commande se termine par un message d’erreur comme :

/tmp:5432 - no response

Pour un laboratoire propre, décidez si vous souhaitez utiliser le paquet système ou une installation depuis les sources. Ce guide utilise le dépôt apt officiel de PostgreSQL pour obtenir la dernière version mineure. Épinglez la version pour garantir la cohérence :

sudo apt-cache policy postgresql-16

Exemple de sortie :

postgresql-16:
  Installed: (none)
  Candidate: 16.4-1.pgdg22.04+1
  Version table:
     16.4-1.pgdg22.04+1 500
        500 http://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 Packages

Enregistrez toutes les versions dans un fichier (par exemple, lab-inventory.txt) pour référence.

Chemin de configuration sécurisé

Une fois votre environnement inventorié, procédez à une configuration sûre et délimitée. L’objectif est d’isoler le laboratoire de toute installation PostgreSQL existante et de faciliter sa remise à zéro sans affecter les autres projets.

Installer PostgreSQL

Ajoutez le dépôt officiel PostgreSQL et installez la version souhaitée. Nous épinglons PostgreSQL 16 pour ce guide.

sudo apt update
sudo apt install -y postgresql-16

L’installation crée un cluster par défaut avec les données dans /var/lib/postgresql/16/main et écoute sur le port 5432. Pour le laboratoire, nous allons créer un cluster séparé pour éviter d’interférer avec tout cluster par défaut existant.

Créer un cluster de laboratoire dédié

Utilisez pg_createcluster pour créer un nouveau cluster avec un port spécifique et un répertoire de données sous votre répertoire personnel pour un accès facile et la portabilité.

sudo pg_createcluster 16 lab --port=5433 --datadir=/home/$USER/pg_lab_data --start

Sortie attendue :

Creating new PostgreSQL cluster 16/lab ...
/usr/lib/postgresql/16/bin/initdb -D /home/$USER/pg_lab_data --auth-local peer --auth-host scram-sha-256
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /home/$USER/pg_lab_data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/New_York
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctlcluster 16 lab start

Configurer les paramètres de PostgreSQL

Modifiez les paramètres clés pour un laboratoire afin d’améliorer les performances et la sécurité. Modifiez le fichier postgresql.conf du cluster, généralement situé à /home/$USER/pg_lab_data/postgresql.conf. Utilisez ce qui suit comme base :

nano /home/$USER/pg_lab_data/postgresql.conf

Ajoutez ou modifiez ces lignes :

shared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 4MB
maintenance_work_mem = 64MB
wal_level = replica
max_wal_senders = 5
archive_mode = on
archive_command = 'test ! -f /home/$USER/pg_lab_archive/%f && cp %p /home/$USER/pg_lab_archive/%f'

Ces paramètres activent l’archivage WAL pour pratiquer la sauvegarde et la récupération, et ils ajustent la mémoire pour une machine de laboratoire typique. Créez le répertoire d’archive et définissez la propriété :

sudo mkdir -p /home/$USER/pg_lab_archive
sudo chown postgres:postgres /home/$USER/pg_lab_archive

Redémarrez le cluster pour appliquer les modifications :

sudo pg_ctlcluster 16 lab restart

Vérifiez l’état du cluster :

pg_lsclusters

Sortie attendue :

Ver Cluster Port Status Owner    Data directory             Log file
16  lab     5433 online postgres /home/$USER/pg_lab_data    /var/log/postgresql/postgresql-16-lab.log

Sécuriser l’accès

Pour une utilisation en laboratoire local, vous pouvez définir un mot de passe pour l’utilisateur postgres et configurer les connexions locales pour utiliser l’authentification par mot de passe. Cependant, l’authentification peer sur les sockets Unix locaux est pratique pour les scripts. Nous allons créer un utilisateur de laboratoire dédié avec un mot de passe pour tester les applications.

Basculez vers l’utilisateur système postgres et créez un rôle :

sudo -u postgres psql -p 5433 -c "CREATE ROLE labuser WITH LOGIN PASSWORD 'labpassword';"

Sortie attendue :

CREATE ROLE

Accordez le statut de superutilisateur pour l’expérimentation en laboratoire (facultatif, mais utile) :

sudo -u postgres psql -p 5433 -c "ALTER ROLE labuser WITH SUPERUSER;"

Vous pouvez maintenant vous connecter en tant que labuser via TCP/IP si nécessaire. Assurez-vous que pg_hba.conf autorise l’authentification par mot de passe pour les connexions TCP locales. Le défaut pour le cluster peut autoriser scram-sha-256 pour les connexions hôte. Testez avec :

psql -h localhost -p 5433 -U labuser -d postgres -c "SELECT version();"

Vous serez invité à saisir le mot de passe, puis vous verrez la version.

Vérification et diagnostic

Après la configuration, vérifiez que chaque composant fonctionne et surveillez les métriques clés. Cette section fournit des vérifications concrètes avec des sorties attendues.

Connectivité de base et état du serveur

Utilisez pg_isready en ciblant votre cluster :

pg_isready -h localhost -p 5433

Sortie attendue :

localhost:5433 - accepting connections

Vérifiez la disponibilité du serveur et les connexions actuelles via SQL :

psql -h localhost -p 5433 -U labuser -d postgres -c "SELECT now() - pg_postmaster_start_time() AS uptime, count(*) FROM pg_stat_activity;"

Exemple de sortie :

     uptime      | count
-----------------+-------
 00:15:23.1234   |     1

Vérifier les paramètres de configuration

Vérifiez que vos modifications ont pris effet :

psql -h localhost -p 5433 -U labuser -d postgres -c "SHOW shared_buffers; SHOW archive_mode;"

Sortie attendue :

 shared_buffers
----------------
 256MB
(1 row)

 archive_mode
--------------
 on
(1 row)

Tester l’archivage WAL

Déclenchez un basculement manuel de WAL et vérifiez que le fichier d’archive apparaît dans le répertoire d’archive :

psql -h localhost -p 5433 -U labuser -d postgres -c "SELECT pg_switch_wal();"
ls -l /home/$USER/pg_lab_archive/

La sortie attendue de ls montre un nouveau fichier avec un nom hexadécimal de 24 caractères, par exemple :

-rw------- 1 postgres postgres 16777216 Sep 28 14:30 000000010000000000000001

Créer une base de données et une table de test

Créez une base de données et une table d’exemple pour pratiquer les opérations :

createdb -h localhost -p 5433 -U labuser testdb
psql -h localhost -p 5433 -U labuser -d testdb -c "CREATE TABLE test (id serial PRIMARY KEY, data text); INSERT INTO test (data) VALUES ('hello'), ('world');"
psql -h localhost -p 5433 -U labuser -d testdb -c "SELECT * FROM test;"

Sortie attendue de la dernière commande :

 id | data
----+-------
  1 | hello
  2 | world
(2 rows)

Vérifier les journaux pour les erreurs

Inspectez le journal PostgreSQL pour toute erreur ou avertissement :

sudo tail -n 20 /var/log/postgresql/postgresql-16-lab.log

Recherchez des entrées comme LOG: database system is ready to accept connections et aucune ligne ERROR.

Modes de défaillance et récupération

Un laboratoire local sert à pratiquer la récupération après défaillance. Nous couvrons ici les pannes simulées, comment les détecter et comment récupérer sans perte de données.

Panne simulée

Arrêtez brusquement le cluster en utilisant pg_ctlcluster avec le mode immédiat :

sudo pg_ctlcluster 16 lab stop -- -m immediate

Cela simule un crash, laissant la base de données dans un état potentiellement incohérent. Vérifiez l’état du cluster :

pg_lsclusters

La sortie attendue montre Status à « down » :

Ver Cluster Port Status Owner    Data directory             Log file
16  lab     5433 down   postgres /home/$USER/pg_lab_data    /var/log/postgresql/postgresql-16-lab.log

Démarrez le cluster normalement :

sudo pg_ctlcluster 16 lab start

PostgreSQL effectue automatiquement une récupération après crash et rejoue le WAL. Vérifiez les journaux pour les messages de récupération :

sudo grep "database system was interrupted" /var/log/postgresql/postgresql-16-lab.log
sudo grep "redo starts" /var/log/postgresql/postgresql-16-lab.log
sudo grep "database system is ready" /var/log/postgresql/postgresql-16-lab.log

Exemples de lignes de journal :

LOG:  database system was interrupted; last known up at 2024-09-28 14:30:00 EDT
LOG:  redo starts at 0/2000028
LOG:  database system is ready to accept connections

Pratique de la récupération à un point dans le temps (PITR)

Utilisez votre archive WAL pour récupérer jusqu’à un point avant qu’une table ne soit supprimée. C’est une compétence essentielle.

1. Enregistrez l’heure actuelle et créez un marqueur :

psql -h localhost -p 5433 -U labuser -d testdb -c "CREATE TABLE important (id int); INSERT INTO important VALUES (1);"
psql -h localhost -p 5433 -U labuser -d testdb -c "SELECT now();"

Supposons que l’heure soit 2024-09-28 14:45:00.

1. Simulez une perte de données :

psql -h localhost -p 5433 -U labuser -d testdb -c "DROP TABLE important;"

1. Arrêtez le cluster et restaurez à partir de la sauvegarde. Pour ce laboratoire, prenez d’abord une sauvegarde de base. Si vous ne l’avez pas encore fait, vous pouvez utiliser pg_basebackup pour en créer une maintenant. Mais pour la pratique, supposez que vous avez une sauvegarde de base dans /home/$USER/pg_lab_backup prise plus tôt. Sinon, créez-en une maintenant :

mkdir -p /home/$USER/pg_lab_backup
sudo -u postgres pg_basebackup -h localhost -p 5433 -D /home/$USER/pg_lab_backup/base -Ft -z -P

Ensuite, arrêtez le cluster :

sudo pg_ctlcluster 16 lab stop

1. Restaurez la sauvegarde de base et configurez la récupération. Copiez la sauvegarde de base dans un nouveau répertoire de données, créez un fichier recovery.signal et définissez recovery_target_time dans postgresql.auto.conf :

sudo cp -R /home/$USER/pg_lab_backup/base /home/$USER/pg_lab_recovered
sudo chown -R postgres:postgres /home/$USER/pg_lab_recovered
sudo -u postgres touch /home/$USER/pg_lab_recovered/recovery.signal
echo "recovery_target_time = '2024-09-28 14:44:59'" | sudo tee -a /home/$USER/pg_lab_recovered/postgresql.auto.conf > /dev/null
echo "restore_command = 'cp /home/$USER/pg_lab_archive/%f %p'" | sudo tee -a /home/$USER/pg_lab_recovered/postgresql.auto.conf > /dev/null

1. Démarrez le cluster récupéré sur un port différent (par exemple, 5434) pour éviter les conflits, puis vérifiez les données :

sudo -u postgres /usr/lib/postgresql/16/bin/pg_ctl -D /home/$USER/pg_lab_recovered -o "-p 5434" start

Une fois démarré, connectez-vous et vérifiez que la table important existe :

psql -h localhost -p 5434 -U labuser -d testdb -c "SELECT * FROM important;"

Sortie attendue :

 id
----
  1
(1 row)

Cet exercice de récupération démontre la valeur de l’archivage WAL et donne confiance dans les procédures de reprise après sinistre.

Erreur de configuration et retour en arrière

Supposons que vous définissiez un paramètre trop élevé et que PostgreSQL ne démarre pas. Par exemple, définissez shared_buffers = 4GB sur une machine avec seulement 2 Go de mémoire. Modifiez la configuration, puis tentez le démarrage :

sudo nano /home/$USER/pg_lab_data/postgresql.conf   # change shared_buffers to 4GB
sudo pg_ctlcluster 16 lab start

Cela peut échouer avec une erreur dans le journal :

FATAL:  could not create shared memory segment: Cannot allocate memory
DETAIL:  Failed system call was shmget(key=5433001, size=4294967296, 03600).

Corrigez en revenant à la valeur précédente et redémarrez :

sudo sed -i 's/shared_buffers = 4GB/shared_buffers = 256MB/' /home/$USER/pg_lab_data/postgresql.conf
sudo pg_ctlcluster 16 lab start

Vérifiez qu’il démarre et que le paramètre est correct.

Pièges courants et comment les éviter

Même les utilisateurs expérimentés peuvent rencontrer des problèmes lors de la gestion d’un laboratoire local PostgreSQL. Voici quelques erreurs fréquentes et comment les corriger.

Oublier de définir correctement la commande d’archive

Si la commande d’archive échoue silencieusement, les fichiers WAL s’accumulent dans pg_wal et finissent par remplir le disque. La cause la plus courante est un répertoire d’archive manquant ou des permissions incorrectes. Vérifiez toujours que le répertoire d’archive existe et est accessible en écriture par l’utilisateur postgres :

sudo -u postgres test -w /home/$USER/pg_lab_archive && echo "writable" || echo "not writable"

Si le répertoire n’est pas accessible en écriture, corrigez la propriété et les permissions, puis redémarrez le cluster.

Confondre les ports entre les clusters

Lorsque vous exécutez plusieurs clusters, il est facile de se connecter au mauvais. Spécifiez toujours le port explicitement dans chaque commande. Utilisez pg_lsclusters pour confirmer le port de chaque cluster et envisagez d’ajouter des alias à votre profil shell :

alias psql-lab='psql -h localhost -p 5433 -U labuser'

Ne pas prendre de sauvegardes de base avant de pratiquer la PITR

La récupération à un point dans le temps nécessite une sauvegarde de base comme point de départ. Si vous sautez cette étape et n’avez que des archives WAL, vous ne pouvez pas restaurer. Entraînez-vous à prendre une sauvegarde de base avec pg_basebackup avant toute opération destructive. Planifiez une sauvegarde de base hebdomadaire même dans un laboratoire pour prendre l’habitude.

Négliger l’emplacement des fichiers journaux

Lorsque les choses tournent mal, le premier endroit à regarder est le journal du cluster. Par défaut, les journaux se trouvent dans /var/log/postgresql/. Si vous ne trouvez pas de journal, vérifiez log_directory dans postgresql.conf. Mettez en place une commande tail simple pour surveiller les erreurs pendant les expériences :

sudo tail -f /var/log/postgresql/postgresql-16-lab.log

Modifier les paramètres sans redémarrer correctement

Certains paramètres nécessitent un redémarrage, d’autres un rechargement, et certains peuvent être modifiés à la volée. Si une nouvelle valeur ne prend pas effet, vérifiez pg_settings :

psql -h localhost -p 5433 -U labuser -d postgres -c "SELECT name, setting, pending_restart FROM pg_settings WHERE name = 'shared_buffers';"

Si pending_restart est t, vous devez redémarrer le cluster. Utilisez pg_ctlcluster restart pour appliquer toutes les modifications en attente.

Liste de vérification opérationnelle

Maintenez un laboratoire sain avec ces étapes reproductibles. Exécutez cette liste avant et après chaque session de laboratoire. Attribuez un responsable unique pour la maintenance du laboratoire, par exemple Priya Shah, responsable de l’ingénierie, qui est chargée de veiller à ce que la liste soit respectée et examine l’état du laboratoire chaque mois.

Avant de commencer le travail

• [ ] Vérifier l’espace disque : df -h /home/$USER/pg_lab_data
• [ ] S’assurer que le cluster est en cours d’exécution : pg_lsclusters
• [ ] Vérifier la connectivité : pg_isready -h localhost -p 5433
• [ ] Examiner les entrées de journal récentes : sudo tail /var/log/postgresql/postgresql-16-lab.log
• [ ] Confirmer que le répertoire d’archive contient des fichiers WAL récents : ls -lt /home/$USER/pg_lab_archive | head
• [ ] Enregistrer l’heure de début pour référence ultérieure : date

Pendant le travail

• [ ] Pour les opérations destructives, prendre une sauvegarde logique : pg_dump -h localhost -p 5433 -U labuser testdb > testdb_before.sql
• [ ] Utiliser des transactions pour les modifications en plusieurs étapes : BEGIN; ...; ROLLBACK; ou COMMIT;
• [ ] Surveiller les requêtes longues : SELECT pid, now() - pg_stat_activity.query_start AS duration, query FROM pg_stat_activity WHERE state <> 'idle' ORDER BY duration DESC;
• [ ] Si les performances se dégradent, vérifier pg_stat_activity et pg_stat_database.

Après le travail

• [ ] Capturer l’état final : lister les bases de données, les tables, les tailles : \l+ et \dt+.
• [ ] Arrêter le cluster proprement : sudo pg_ctlcluster 16 lab stop
• [ ] Vérifier l’arrêt : pg_lsclusters
• [ ] Archiver tous les scripts ou notes importants.
• [ ] Facultativement, réinitialiser le laboratoire à un état propre en recréant le cluster (voir ci-dessous).

Script de réinitialisation rapide

Pour réinitialiser le laboratoire à un état propre, supprimez et recréez le cluster. Enregistrez ceci sous reset_lab.sh :

#!/bin/bash
set -e
sudo pg_ctlcluster 16 lab stop || true
sudo pg_dropcluster 16 lab --stop
sudo pg_createcluster 16 lab --port=5433 --datadir=/home/$USER/pg_lab_data --start
sudo -u postgres psql -p 5433 -c "CREATE ROLE labuser WITH LOGIN PASSWORD 'labpassword' SUPERUSER;"

Rendez-le exécutable et exécutez-le en cas de besoin : chmod +x reset_lab.sh && ./reset_lab.sh.

Conclusion

Un laboratoire local PostgreSQL est un outil essentiel pour toute équipe qui valorise l’expérimentation sûre et le développement des compétences. En suivant ce guide, vous avez mis en place un cluster PostgreSQL 16 dédié avec épinglage de version, paramètres ajustés, archivage WAL et accès sécurisé. Vous avez appris à vérifier chaque composant avec des commandes concrètes et des sorties attendues, et vous avez pratiqué la récupération après défaillance, y compris la récupération après crash et la récupération à un point dans le temps. La liste de vérification opérationnelle fournit une discipline continue pour garder votre laboratoire fiable.

Prochaines étapes pour étendre votre laboratoire :

• Tester les configurations de réplication en ajoutant un serveur de secours.
• Pratiquer la réplication logique et le partitionnement de tables.
• Expérimenter avec des extensions comme pg_stat_statements pour l’analyse des requêtes.
• Automatiser l’approvisionnement du laboratoire avec des scripts shell ou des outils de gestion de configuration.
• Documenter vos procédures de laboratoire pour l’intégration de l’équipe.

N’oubliez pas que la clé d’un apprentissage efficace est la répétabilité. Réinitialisez souvent votre laboratoire, essayez de nouvelles choses et cassez-le sans crainte. Votre laboratoire local PostgreSQL est le bac à sable où vous construisez une véritable confiance pour la production.