À la suite de l’article sur l’intégration de la réplication logique dans Patroni, nous allons expliquer comme PostgreSQL permet, à partir de la version 17, la synchronisation des slots de réplication logique à travers les différents nœuds d’un ensemble de réplication physique.

Pour commencer, voici quelques rappels :

• le flux de réplication logique permet de synchroniser les données de tables présentes dans le fournisseur de données, à partir d’une publication vers une souscription ;
• ce flux utilise, par défaut, un slot de réplication : cet objet, présent côté publication, note la consommation des données de la souscription sous la forme d’un LSN (Log Sequence Number), soit une position dans les journaux de transactions.

En effet, un élément important lors de l’exploitation d’un flux de réplication logique est le slot qui est automatiquement créé côté publication : par défaut, lorsque l’instance PostgreSQL contenant cette publication subit une défaillance et qu’il est nécessaire de remplacer cette instance par un de ses réplicas, apparait alors un gros problème pour notre réplication logique : le slot n’existe tout simplement pas sur cette nouvelle instance primaire, et il est impossible de savoir si les données de notre flux de réplication logique sont correctement synchronisées entre la publication et la souscription. La seule méthode fiable est alors de tout resynchroniser, ce qui est trop couteux dans bien des cas.

Pour corriger ce manquement, l’extension pg_failover_slots est disponible à partir de 2023, mais, dès la version 17 de PostgreSQL, publiée à la fin de l’année 2024, la fonctionnalité de synchronisation des slots est intégrée dans PostgreSQL.

L’idée de cette fonctionnalité est simplement de faire en sorte que le slot créé lors de la création d’une souscription de la réplication logique soit présent et à jour sur tous les réplicas de l’instance primaire où est créée cette souscription.

Dans cet article, pour illustrer ce fonctionnement et détailler les différents éléments de cette mise en place, nous utilisons un ensemble d’instances PostgreSQL : une instance primaire et deux instances secondaires, contenant une publication, puis une instance distincte dans laquelle nous créons la souscription de la réplication logique.

Le diagramme suivant représente les instances et les différents flux entre ces instances :

Les instances sont hébergées dans des systèmes Debian nommés comme suit :

• pg17ph1, pg17ph2 et pg17ph3 : systèmes hébergeant la publication dans un ensemble d’instances en réplication physique ;
• pg17lg1 : système hébergeant la souscription.

La première étape est la mise en place de l’ensemble d’instances PostgreSQL en réplication physique, avec un paramétrage rendant possible la synchronisation des slots. En effet, cette synchronisation utilise le flux de réplication physique, cela implique donc un paramétrage un peu plus exigeant que celui par défaut pour une réplication. Les paramètres sont modifiés dans les trois instances, même si certains d’entre eux ne sont utiles que dans une instance secondaire, et c’est important, car les rôles des trois instances changent au cours du temps, et ce paramétrage est donc utile à un moment sur chacun des nœuds.

Un point critique est la possibilité d’ordonnancer la synchronisation des slots : par défaut, les slots physiques et logiques avancent indépendamment les uns des autres, de telle sorte qu’il est possible que le slot de la réplication logique soit en avance sur les slots de la réplication physique. Dans ce cas, le problème apparaitra lors d’une bascule : si le nœud promu est en retard, alors la réplication logique ne pourra reprendre et sera désynchronisée.

Création d’une structure de données

Pour cet article, nous allons utiliser une structure de données minimale : une base de données, une table, et quelques lignes insérées suffiront à démontrer la faisabilité du sujet de cet article.

La base de données contient aussi la publication de la réplication logique et le slot correspondant. Cette base de données est aussi utilisée pour la connexion des réplications physiques, car c’est par cet intermédiaire que la synchronisation du slot de réplication logique se fait.

CREATE DATABASE lambdadb;

Il est aussi nécessaire de créer des rôles spécifiques à la réplication :

CREATE ROLE replirol  LOGIN REPLICATION;
CREATE ROLE mlreplrol LOGIN REPLICATION;

Les mots de passe ne sont pas créés dans cet exemple, pas plus que les entrées dans le fichier pg_hba.conf, même s’il s’agit d’une étape nécessaire.

La table créée est la suivante :

CREATE TABLE public.lambda
(
    id integer GENERATED BY DEFAULT AS IDENTITY,
    ev timestamptz default now(),
    d text,
    r double precision
);
GRANT SELECT ON TABLE public.lambda TO mlreplrol ;

INSERT INTO public.lambda ( d, r ) VALUES ('a', 1.0), ('b', 1.33) ;

Configuration des instances PostgreSQL

Les trois instances sont configurées de la même manière, car elles peuvent toutes, à leur tour, avoir les rôles primaires ou secondaires, et il est préférable d’avoir le moins de choses à faire lors des changements de rôle.

Configuration du rôle « primaire »

Le premier paramètre à modifier est nécessaire pour autoriser la réplication logique, avec ou sans synchronisation des slots :

• wal_level = logical

Le second paramètre concerne la synchronisation proprement dite, qui est permise avec le paramètre suivant :

• sync_replication_slots = on

Les slots doivent être créés avec l’appel de la fonction pg_create_physical_replication_slot(), dans chacune des instances PostgreSQL, en utilisant les noms de slots ci-dessus, par exemple :

select pg_create_physical_replication_slot('pg17ph2_slot');
select pg_create_physical_replication_slot('pg17ph3_slot');

Dans une instance primaire, la configuration minimale doit alors être :

wal_level = logical
sync_replication_slots = on

Configuration du rôle « secondaire »

Ensuite, les instances secondaires doivent être en mesure de se connecter à l’instance primaire, en utilisant la base de données créée pour lire l’état du slot de réplication logique et en déclarant le slot de réplication physique à utiliser, par exemple :

primary_conninfo = 'host=pg17ph1 port=5432 user=replirol dbname=lambdadb'
primary_slot_name = 'pg17ph2_slot'

Le nom d’hôte de la connexion de réplication doit être adapté en fonction de l’état du cluster de réplication physique, en utilisant l’hôte primaire.

De plus, il est nécessaire d’indiquer le nom du slot de réplication physique permettant la synchronisation, par exemple :

primary_slot_name = 'pg17ph2_slot'

Enfin, la boucle de retour est nécessaire, pour que l’instance primaire obtienne la confirmation de la réplication de l’état du slot :

hot_standby_feedback = on

En résumé, la configuration des instances pg17ph2 et pg17ph3 est la suivante :

• pg17ph2 :

wal_level = logical
sync_replication_slots = on
primary_conninfo = 'host=pg17ph1 port=5432 user=replirol dbname=lambdadb'
primary_slot_name = 'pg17ph2_slot'
hot_standby_feedback = on

• pg17ph3 :

wal_level = logical
sync_replication_slots = on
primary_conninfo = 'host=pg17ph1 port=5432 user=replirol dbname=lambdadb'
primary_slot_name = 'pg17ph3_slot'
hot_standby_feedback = on

Mise en place de la réplication physique

La création des instances secondaires peut se faire avec la commande pg_basebackup :

pg_basebackup -h pg17ph1 -X stream -U replirol -D 17/main/

Dans cet exemple, l’option -R n’est pas utilisée, car le paramètre primary_conninfo est déjà réglé précédemment, et son utilisation ici surchargerait la valeur dans le fichier postgresql.auto.conf, ce qui amènerait une ambiguïté voire un dysfonctionnement.

Par contre, il est nécessaire de créer le fichier standby.signal pour chacune des instances secondaires :

touch $PGDATA/standby.signal

Création des slots de réplication

Dans la prévision d’une bascule, les slots de réplication physique peuvent être provisionnés sur les deux replicas :

• Dans l’instance pg17ph2 :

select pg_create_physical_replication_slot('pg17ph2_slot');
select pg_create_physical_replication_slot('pg17ph3_slot');

• Dans l’instance pg17ph3 :

select pg_create_physical_replication_slot('pg17ph1_slot');
select pg_create_physical_replication_slot('pg17ph2_slot');

Création de la réplication logique

Enfin, la publication, qui permet la réplication logique, se fait dans l’instance primaire, depuis la base de données lambdadb où est présente la table :

CREATE PUBLICATION lambda_pub WITH (publish = 'insert, update, delete, truncate');

ALTER PUBLICATION lambda_pub ADD TABLE ONLY public.lambda;

Puis, dans l’instance créée pour recevoir les données de la réplication logique, on crée la souscription, dont la chaîne de connexion doit permettre de suivre le slot :

CREATE SUBSCRIPTION lambda_sub_01
    CONNECTION 'host=pg17ph1,pg17ph2,pg17ph3 user=mlreplrol dbname=lambdadb target_session_attrs=primary'
    PUBLICATION lambda_pub
    WITH ( slot_name = 'lambda_sub_01_slot'
           , failover = true) ;

La réplication fonctionne, après la synchronisation initiale, et les données de la table sont visibles dans cette instance, comme le montre la requête suivante :

psql -h pg17lg1 -d lambdadb -c "SELECT * from lambda ORDER BY id ASC"
 id |              ev               | d |   r
----+-------------------------------+---+-------
  1 | 2027-07-06 08:57:23.917987+00 | a |     1
  2 | 2027-07-06 08:57:23.917987+00 | b |  1.33
(2 rows)

Vérification de la synchronisation du slot

En fonction des écritures, la réplication avance, c’est-à-dire que le LSN, qui désigne la position courante dans les journaux de transactions, est incrémenté. Cette donnée est visible dans la vue pg_replication_slots, et pour vérifier que le slot de réplication logique est correctement synchronisé, il est possible de consulter cette vue sur les trois instances concernées :

for host in `seq 1 3`;
do
psql -X -h "pg17ph${host}" -U postgres <<EOF
select 'pg17ph${host}' as host_name, slot_name, active
        , restart_lsn, confirmed_flush_lsn, failover, synced
    from pg_replication_slots
    where slot_name='lambda_sub_01_slot'
EOF
done

 host_name |     slot_name      | active | restart_lsn | confirmed_flush_lsn | failover | synced
-----------+--------------------+--------+-------------+---------------------+----------+--------
 pg17ph1   | lambda_sub_01_slot | t      | 0/3FD9A58   | 0/3FD9A90           | t        | f

 host_name |     slot_name      | active | restart_lsn | confirmed_flush_lsn | failover | synced
-----------+--------------------+--------+-------------+---------------------+----------+--------
 pg17ph2   | lambda_sub_01_slot | f      | 0/3FD9A58   | 0/3FD9A90           | t        | t

 host_name |     slot_name      | active | restart_lsn | confirmed_flush_lsn | failover | synced
-----------+--------------------+--------+-------------+---------------------+----------+--------
 pg17ph3   | lambda_sub_01_slot | f      | 0/3FD9A58   | 0/3FD9A90           | t        | t

On peut voir que les booléens failover sont tous vrais, et que sur les instances secondaires, les booléens synced sont vrais aussi.

Les positions LSN sont les mêmes partout : le slot est correctement synchronisé.

Bascule des instances physiques

Vient le moment fatidique : la perte de l’instance primaire, dans notre cas, pg17ph1 !

Dans ce cas, il faut promouvoir une des instances secondaires, par exemple :

psql -h pg17ph2 -d lambdadb -c "SELECT pg_promote()"

Puis reconfigurer la réplication physique de la troisième instance pour qu’elle reprenne, et avec elle la synchronisation du slot de réplication logique.

Une fois ces modifications apportées, la réplication logique doit alors suivre automatiquement, comme on peut le voir dans le log suivant, extrait de l’instance pg17lg1 :

2027-07-06 09:11:18.326 UTC [15559] LOG:  data stream from publisher has ended
2027-07-06 09:11:18.326 UTC [15559] ERROR:  could not send end-of-streaming message to primary: SSL connection has been closed unexpectedly
        no COPY in progress
2027-07-06 09:11:18.336 UTC [16954] LOG:  logical replication apply worker for subscription "lambda_sub_01" has started
2027-07-06 09:11:18.337 UTC [123] LOG:  background worker "logical replication apply worker" (PID 15559) exited with exit code 1
2027-07-06 09:11:18.383 UTC [16954] ERROR:  could not connect to the publisher: connection to server at "pg17ph1" (fc42:5009:ba4b:5ab0:1266:6aff:fef7:97), port 5432 failed: FATAL:  the database system is shutting down
        connection to server at "pg17ph2" (fc42:5009:ba4b:5ab0:1266:6aff:fec6:6133), port 5432 failed: server is in hot standby mode
        connection to server at "pg17ph3" (fc42:5009:ba4b:5ab0:1266:6aff:fe75:3d43), port 5432 failed: server is in hot standby mode
2027-07-06 09:11:18.385 UTC [123] LOG:  background worker "logical replication apply worker" (PID 16954) exited with exit code 1
2027-07-06 09:11:23.338 UTC [16956] LOG:  logical replication apply worker for subscription "lambda_sub_01" has started

Il est toutefois nécessaire de contrôler le fonctionnement :

• les modifications des données parviennent-elles jusqu’à la table présente dans pg17lg1 ?
• le slot est-il correctement synchronisé entre les deux instances de réplication physiques restantes ?

Si oui, alors l’objectif est atteint, la synchronisation fonctionne et est utilisable !

Conclusion

Cette synchronisation apparait nécessaire pour que la réplication logique survive à un incident sur l’instance physique, ce qui ne manquera pas d’arriver. Mais ce besoin impose un paramétrage particulier des instances PostgreSQL, l’utilisation de slot de réplication physique et de la boucle de feedback, ce qui n’est pas gratuit.

Dans tous les cas, la réplication logique répond à des besoins d’architecture auxquels ne répond pas la réplication physique, et est parfois nécessaire. Il convient de mettre en place les sécurités qui permettent une continuité de service, et la synchronisation du slot de réplication logique en est une.

Malgré cela, cet ensemble de configuration reste imparfait : lorsque la réplication logique est en avance sur la réplication physique, alors les slots synchronisés seront eux aussi en retard sur la publication, ce qui nécessite une réinitialisation de celle-ci.

Mais il existe une solution que nous verrons dans un prochain article.