Introduction :

Dans le monde des bases de données, garantir une disponibilité continue est une exigence incontournable, surtout pour les systèmes critiques où chaque minute d’arrêt peut entraîner des pertes significatives. Lorsqu’il s’agit de migrer une base de données vers une nouvelle version, ce défi prend une toute autre dimension. Comment mettre à jour votre système sans interrompre les services, tout en préservant l’intégrité des données ?

PostgreSQL offre une solution élégante : la réplication logique. Cet outil permet de transférer des données de manière fluide entre différentes versions de PostgreSQL, tout en maintenant la base de données source opérationnelle. Dans cet article, nous allons explorer étape par étape comment utiliser cette fonctionnalité pour réaliser une montée de version sans temps d’arrêt, du déploiement initial à la bascule finale vers la nouvelle version.

Que vous soyez en train de planifier une migration ou simplement curieux de découvrir les possibilités offertes par PostgreSQL, suivez ce guide pratique qui vous permettra de transformer un défi complexe en une opération maîtrisée et efficace.

Le test :

1. Préparation

Pour tester cette nouvelle méthode, nous aurons besoin de deux instances PostgreSQL. Pour cet article j’ai choisit de démontrer la technique en migrant d’une version 14 à une version 17 de PostgreSQL.

Je commence donc par installer les versions sur deux machines différentes pouvant communiquer entre elles (c’est important) :

Sur les deux machines nous pouvons exécuter les commandes suivantes :

root@ip-192-1-1-246:~# sudo apt update sudo apt upgrade -y

...

root@ip-192-1-1-246:~# sudo apt -y install gnupg2 wget vim

...

root@ip-192-1-1-246:~# sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
root@ip-192-1-1-246:~# curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc|sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

root@ip-192-1-1-246:~# sudo apt -y update
Get:1 file:/etc/apt/mirrors/debian.list Mirrorlist [38 B]
Get:2 file:/etc/apt/mirrors/debian-security.list Mirrorlist [47 B]
Hit:3 https://cdn-aws.deb.debian.org/debian bookworm InRelease
Hit:4 https://cdn-aws.deb.debian.org/debian bookworm-updates InRelease
Hit:5 https://cdn-aws.deb.debian.org/debian bookworm-backports InRelease
Hit:6 https://cdn-aws.deb.debian.org/debian-security bookworm-security InRelease
Get:7 http://apt.postgresql.org/pub/repos/apt bookworm-pgdg InRelease [129 kB]
Get:8 http://apt.postgresql.org/pub/repos/apt bookworm-pgdg/main amd64 Packages [359 kB]
Fetched 489 kB in 1s (348 kB/s)
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
All packages are up to date.

Puis sur notre première machine :

root@ip-192-1-1-246:~# sudo apt install postgresql-14
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
libcommon-sense-perl libgdbm-compat4 libio-pty-perl libipc-run-perl
libjson-perl libjson-xs-perl libllvm16 libperl5.36 libpq5 libsensors-config
libsensors5 libtypes-serialiser-perl libxslt1.1 libz3-4 logrotate perl
perl-modules-5.36 postgresql-client-14 postgresql-client-common
postgresql-common ssl-cert sysstat

...

root@ip-192-1-1-246:~# systemctl status postgresql@14-main.service
● postgresql@14-main.service - PostgreSQL Cluster 14-main
Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled-runtime;>
Active: active (running) since Wed 2024-12-04 09:43:55 UTC; 2min 55s ago
Process: 15248 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect >
Main PID: 15253 (postgres)
Tasks: 7 (limit: 4633)
Memory: 17.3M
CPU: 239ms
CGroup: /system.slice/system-postgresql.slice/postgresql@14-main.service
├─15253 /usr/lib/postgresql/14/bin/postgres -D /var/lib/postgresq>
├─15255 "postgres: 14/main: checkpointer "
├─15256 "postgres: 14/main: background writer "
├─15257 "postgres: 14/main: walwriter "
├─15258 "postgres: 14/main: autovacuum launcher "
├─15259 "postgres: 14/main: stats collector "
└─15260 "postgres: 14/main: logical replication launcher "

Dec 04 09:43:53 ip-192-1-1-246 systemd[1]: Starting postgresql@14-main.service>
Dec 04 09:43:55 ip-192-1-1-246 systemd[1]: Started postgresql@14-main.service >

Puis sur la deuxième machine :

admin@ip-192-1-1-89:~$ sudo apt install postgresql-17
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
libcommon-sense-perl libgdbm-compat4 libio-pty-perl libipc-run-perl
libjson-perl libjson-xs-perl libllvm16 libperl5.36 libpq5 libsensors-config
libsensors5 libtypes-serialiser-perl libxslt1.1 libz3-4 logrotate perl
perl-modules-5.36 postgresql-client-17 postgresql-client-common
postgresql-common ssl-cert sysstat

admin@ip-192-1-1-89:~$ systemctl status postgresql@17-main.service
● postgresql@17-main.service - PostgreSQL Cluster 17-main
Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled-runtime; >
Active: active (running) since Wed 2024-12-04 09:52:33 UTC; 2min 13s ago
Process: 15235 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 1>
Main PID: 15240 (postgres)
Tasks: 6 (limit: 4633)
Memory: 20.5M
CPU: 332ms
CGroup: /system.slice/system-postgresql.slice/postgresql@17-main.service
├─15240 /usr/lib/postgresql/17/bin/postgres -D /var/lib/postgresql>
├─15241 "postgres: 17/main: checkpointer "
├─15242 "postgres: 17/main: background writer "
├─15244 "postgres: 17/main: walwriter "
├─15245 "postgres: 17/main: autovacuum launcher "
└─15246 "postgres: 17/main: logical replication launcher "

Dec 04 09:52:31 ip-192-1-1-89 systemd[1]: Starting postgresql@17-main.service ->
Dec 04 09:52:33 ip-192-1-1-89 systemd[1]: Started postgresql@17-main.service ->

Nos deux instances sont maintenant installées. Sur notre première base de données, nous allons créer une base, avec deux tables, et quelques lignes.

postgres@ip-192-1-1-246:/etc/postgresql/14/main$ psql
psql (14.15 (Debian 14.15-1.pgdg120+1))
Type "help" for help.

 

postgres=# CREATE DATABASE mydb;
CREATE DATABASE
postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".
mydb=# CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE,
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE
mydb=# CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INT REFERENCES customers(id),
amount NUMERIC(10,2) NOT NULL,
order_date TIMESTAMP DEFAULT NOW()
);
CREATE TABLE
mydb=# INSERT INTO customers (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com'),
('Charlie', 'charlie@example.com');
INSERT 0 3
mydb=# INSERT INTO orders (customer_id, amount) VALUES
(1, 50.75),
(2, 20.00),
(1, 75.00);
INSERT 0 3

2. Configurer la base de données source

Sur notre première machine, nous allons modifier les paramètres du fichier de configuration de PostgreSQL pour permettre de pouvoir créer la réplication :

root@ip-192-1-1-246:~# su - postgres
postgres@ip-192-1-1-246:~$ cd /etc/postgresql/14/main
postgres@ip-192-1-1-246:/etc/postgresql/14/main$ vi postgresql.conf

Il s’agit de modifier les paramètres suivants :

wal_level = logical
max_replication_slots = 4
max_wal_senders = 4

Nous modifierons ensuite le pg_hba pour rajouter l’autorisation de connexion entre les deux machines :

postgres@ip-192-1-1-246:/etc/postgresql/14/main$ vi pg_hba.conf

Il suffira de rajouter une ligne :

host replication all <destination_ip> scram-sha-256

host replication all <source_ip> scram-sha-256

host all replication <destination_ip> scram-sha-256

host all replication <source-ip> scram-sha-256

Il ne faut pas oublier de redémarrer le serveur PostgreSQL une fois ces modifications effectuées :

root@ip-192-1-1-246:~# systemctl stop postgresql@14-main.service
root@ip-192-1-1-246:~# systemctl start postgresql@14-main.service

3. Configurer la base de donnée de destination

Après avoir configuré notre base de donnée depuis laquelle nous allons faire notre migration, il nous faut a présent configurer celle qui va recevoir la nouvelle base de donnée migrée.

Pour cela, nous allons répéter les étapes de configuration de la base de donnée source, en les adaptant sur notre base de donnée de destination : modifier le postgresql.conf, puis le pg_hba.conf, redémarrer ensuite la base de données

postgres@ip-192-1-1-89:~$ cd /etc/postgresql/17/main/
postgres@ip-192-1-1-89:/etc/postgresql/17/main$ vi postgresql.conf

wal_level = logical
max_replication_slots = 4
max_wal_senders = 4

postgres@ip-192-1-1-246:/etc/postgresql/14/main$ vi pg_hba.conf

host replication all <destination_ip> scram-sha-256

host replication all <source_ip> scram-sha-256

host all replication <destination_ip> scram-sha-256

host all replication <source-ip> scram-sha-256

root@ip-192-1-1-246:~# systemctl stop postgresql@14-main.service
root@ip-192-1-1-246:~# systemctl start postgresql@14-main.service

Il ne faudra pas oublier de créer la base de donnée ainsi que toutes les structures de tables et autres objets dans notre base cible pour qu’elle puisse recevoir les données. Pour avoir les scripts de création de la base de données, vous pouvez faire un pg_dump avec l’option

postgres@ip-192-1-1-89:~$ psql
psql (17.2 (Debian 17.2-1.pgdg120+1))
Type "help" for help.

postgres=# CREATE DATABASE mydb;
CREATE DATABASE

N’oubliez pas de donner tout les droits à votre utilisateur de replication pour qu’il puisse lire, écrire… Sur votre base de données repliquée, sur la source, comme sur la destination :

postgres=# GRANT ALL PRIVILEGES ON DATABASE "mydb" to replication;
GRANT

mydb=# GRANT ALL PRIVILEGES ON all tables in schema public to replication;
GRANT

4. Mise en place de la réplication logique

Maintenant que nos deux environnement sont bien en place, nous sommes prêts à mettre en route le processus de réplication logique pour commencer à transférer les données. Les étapes du dessous ont demandé une première intervention hors horaire de prod, notamment pour redémarrer le service postgreSQL, mais le but d’une migration avec réplication logique, c’est de pouvoir ensuite n’avoir rien à toucher jusqu’au moment de basculer les applicatifs d’une ip a une autre.

Sur notre machine source, on créé la publication qui va nous servir à transférer nos tables :

postgres@ip-192-1-1-246:~$ psql
psql (14.15 (Debian 14.15-1.pgdg120+1))
Type "help" for help.

 

postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".
mydb=# CREATE PUBLICATION my_pub FOR ALL TABLES;
CREATE PUBLICATION

On va ensuite créé la souscription sur la base de données cible de notre migration :

mydb=# create subscription my_sub connection 'host=192.1.1.246 port=5432 dbname=mydb user=replication password=replication'publication my_pub;
NOTICE: created replication slot "my_sub" on publisher
CREATE SUBSCRIPTION

Maintenant que la subscription est en place, on peut vérifier qu’elle fonctionne. Pendant ce temps, la vrai production, sur la version 14, peut continuer à fonctionner, elle sera automatiquement repliquée sur la nouvelle version 17.

On peut vérifier ou en est notre replication avec la commande SELECT * FROM pg_stat_subscription;

mydb=# SELECT * FROM pg_stat_subscription;
-[ RECORD 1 ]---------+------------------------------
subid | 16422
subname | my_sub
worker_type | apply
pid | 16076
leader_pid |
relid |
received_lsn | 0/1733988
last_msg_send_time | 2024-12-04 14:23:59.873074+00
last_msg_receipt_time | 2024-12-04 14:23:59.872357+00
latest_end_lsn | 0/1733988
latest_end_time | 2024-12-04 14:23:59.873074+00

5. Test de replication, bascule, et nettoyage

Une fois que la synchronisation de votre replication logique est terminée, ce qui peut prendre un certain temps si vous avez beaucoup de données, vous pouvez constater de vous même sur les lignes que vous ajoutez, modifiez ou supprimez sur votre instance source sont repliquées sur l’instance de destination.

Par exemple, ajoutons un nouveau customer sur notre base source :

postgres@ip-192-1-1-246:~$ psql
psql (14.15 (Debian 14.15-1.pgdg120+1))
Type "help" for help.

 

postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".
mydb=# INSERT INTO customers (name, email) VALUES ('Diana', 'diana@example.com');
INSERT 0 1

Si nous allons requêter sur notre instance de destination :

mydb=# select * from customers where name='Diana';
id | name | email | created_at
----+-------+-------------------+----------------------------
4 | Diana | diana@example.com | 2024-12-04 14:31:05.708031
(1 row)

Quand vous vous êtes bien assuré que tout fonctionne, vous pouvez alors rediriger les drivers odbc de vos applications vers le nouveau serveur et non plus l’ancien.

Une fois que cela est fait, vous pouvez alors supprimer le lien de replication, puisque l’ancienne instance ne sera plus alimentée, et même supprimer l’ancienne version si vous n’en avez plus l’utilité.

Sur la destination, notre nouveau serveur de prod :

DROP SUBSCRIPTION my_sub;

Sur la source, ancien serveur qui va être supprimé :

DROP PUBLICATION my_pub;

Conclusion

La réplication logique se distingue comme l’une des meilleures solutions pour minimiser le temps d’arrêt lors d’une migration de version PostgreSQL. En permettant une synchronisation continue des données entre deux instances, elle garantit une transition en douceur sans jamais interrompre les services en cours. Cela en fait un choix idéal pour les environnements critiques où la disponibilité est primordiale.

Avantages :

Zéro downtime : la source reste opérationnelle pendant toute la migration.
Flexibilité : possibilité de migrer vers une infrastructure différente (nouveau matériel, cloud, etc.).
Granularité : la réplication logique peut se limiter à certaines tables si nécessaire.

Inconvénients :

Complexité initiale : la configuration et les tests nécessitent une bonne maîtrise des paramètres de PostgreSQL.
Impact sur les performances : la charge de réplication peut légèrement affecter les performances de la base source, surtout avec un grand volume de données.
Non pris en charge pour certains types de données : les types spécifiques ou les extensions non standards ne sont pas toujours compatibles avec la réplication logique.

Si la réplication logique est souvent la méthode privilégiée pour des mises à jour critiques, elle n’est pas la seule option. Des alternatives comme les outils de sauvegarde et restauration ou la réplication physique peuvent répondre à d’autres besoins spécifiques, notamment pour des bases de données très volumineuses ou des scénarios nécessitant une réplication complète du système.

Dans tous les cas, le choix de la méthode dépendra de votre contexte, de vos contraintes techniques et de vos objectifs métier. Prenez le temps d’évaluer les différentes options pour garantir une migration réussie et sans surprise.

Continuez votre lecture sur le blog :

• Réplication logique avec PostgreSQL (Capdata team) [PostgreSQL]
• Migration PostgreSQL via SLONY-I ou comment réduire le temps de coupure (Capdata team) [PostgreSQL]
• Migrer d’un cluster Galera MariaDB 10.3 vers MariaDB 10.5 avec la réplication logique (David Baffaleuf) [ContainerMySQLNon classé]
• Comparatif des gestionnaires de VIP dans un cluster Patroni : épisode 1 (KEEPALIVED) (David Baffaleuf) [ContainerPostgreSQL]
• Pyrseas et Postgresql : Comparer facilement des schémas de base de données (Sarah FAVEERE) [PostgreSQL]