Le « multimaître » semble être le graal de la réplication logique. Pour y arriver, il faut entre autre ajouter à PostgreSQL un mécanisme de résolution des conflits. Les fonctionnalités décrites dans cet article et ajoutées dans la version 18 s’attellent à implémenter la détection de conflit de réplication logique. Leur résolution sera intégrée dans une future version. Les mises à jour issues de la réplication logique peuvent échouer si des conflits sont détectés : ce genre d’événement bloque la réplication. Certaines opérations, comme la mise à jour ou la suppression d'une donnée qui n'est pas présente, ne génèrent pas d'erreur : l'opération est simplement ignorée. Pourtant ce genre d'évènement est aussi un conflit de réplication. La version 18 catégorise les conflits en sept groupes, les détecte, collecte des statistiques à leur sujet dans la vue `pg_stat_subscription_stats` et émet des messages les concernant dans les traces de l'instance. Voici la liste de ces conflits : * `update_missing` : la ligne mise à jour est manquante ; * `delete_missing` : la ligne supprimée est manquante ; * `insert_exists` : la ligne insérée viole une contrainte unique non déferrable ; * `update_exists` : la ligne mise à jour viole une contrainte unique non déferrable ; * `multiple_unique_conflicts` : la ligne insérée ou mise à jour viole plusieurs contraintes uniques non déferrables ; * `update_origin_ differ` : la ligne mise à jour a été précédemment modifiée par une autre origine ; * `delete_origin_differ` : la ligne supprimée a été modifiée précédemment par une autre origine. Les deux derniers types de conflits nécessitent `track_commit_timestamp` pour être détectés. Attention, il n'est pas activé par défaut. Les trois précédents nécessitent l'activation de ce paramètre pour compléter les messages avec des infos sur l'origine du commit et sur son timestamp. La suite de l'article présente ces conflits plus en détail : * `update_missing` : la ligne mise à jour est manquante. Mise en place du test : ```sql -- Serveur 1 CREATE ROLE repli WITH LOGIN REPLICATION PASSWORD 'repli'; CREATE TABLE conflits (i int PRIMARY KEY, t text); CREATE PUBLICATION srv1 FOR TABLE conflits; GRANT SELECT ON conflits TO repli; INSERT INTO conflits(i,t) VALUES (1, 'first'); ``` ```sql -- Serveur 2 CREATE TABLE conflits (i int PRIMARY KEY, t text); CREATE SUBSCRIPTION from_srv1 CONNECTION 'host=/var/run/postgresql port=5435 user=repli dbname=benoit application_name=srv2' PUBLICATION srv1; ``` La vue de supervision ne contient pour le moment pas d'erreur : ```sql -- Serveur 2 TABLE pg_stat_subscription_stats \gx ``` ```test -[ RECORD 1 ]-------------------+---------- subid | 112863 subname | from_srv1 apply_error_count | 0 sync_error_count | 0 confl_insert_exists | 0 confl_update_origin_differs | 0 confl_update_exists | 0 confl_update_missing | 0 confl_delete_origin_differs | 0 confl_delete_missing | 0 confl_multiple_unique_conflicts | 0 stats_reset | ø ``` Si la ligne 1 est supprimée sur le **serveur 2** avant d'être mise à jour sur le **serveur 1** : ```sql -- Serveur 2 DELETE FROM conflits WHERE i = 1; ``` ```sql -- Serveur 1 UPDATE conflits SET t = 'updated' WHERE i = 1; ``` Nous pouvons observer qu'un conflit est tracé dans la vue sur le **serveur 2** : ```text -[ RECORD 1 ]-------------------+---------- subid | 112863 subname | from_srv1 apply_error_count | 0 sync_error_count | 0 confl_insert_exists | 0 confl_update_origin_differs | 0 confl_update_exists | 0 confl_update_missing | 1 confl_delete_origin_differs | 0 confl_delete_missing | 0 confl_multiple_unique_conflicts | 0 stats_reset | ø ``` L'update a été ignoré, et un message est écrit dans les traces du **serveur 2** nous informant du conflit : ```text [1223274] LOG: conflict detected on relation "public.conflits": conflict=update_missing [1223274] DETAIL: Could not find the row to be updated. Remote row (1, updated); replica identity (i)=(1). [1223274] CONTEXT: processing remote data for replication origin "pg_112863" during message type "UPDATE" for replication target relation "public.conflits" in transaction 49504, finished at 0/224E8158 ``` Le message permet d'identifier le type de conflit et la table (ligne 1 et 2), la ligne sur la source (ligne 3). La dernière ligne permet d'identifier l'origine de réplication et donc la souscription (puisque l'origine est nommée `pg_`). * `delete_missing` : la ligne supprimée est manquante. Si nous supprimons maintenant la ligne 1 de l'exemple précédent sur le **serveur 1** : ```sql -- Serveur 1 DELETE FROM conflits WHERE i = 1; ``` Nous pouvons observer que le conflit est tracé dans la vue sur le **serveur 2** : ```text -[ RECORD 1 ]-------------------+---------- subid | 112863 subname | from_srv1 apply_error_count | 0 sync_error_count | 0 confl_insert_exists | 0 confl_update_origin_differs | 0 confl_update_exists | 0 confl_update_missing | 1 confl_delete_origin_differs | 0 confl_delete_missing | 1 confl_multiple_unique_conflicts | 0 stats_reset | ø ``` Le delete a été ignoré et un message est écrit dans les traces du **serveur 2** nous informant du conflit : ```text [1223274] LOG: conflict detected on relation "public.conflits": conflict=delete_missing [1223274] DETAIL: Could not find the row to be deleted. Replica identity (i)=(1). [1223274] CONTEXT: processing remote data for replication origin "pg_112863" during message type "DELETE" for replication target relation "public.conflits" in transaction 49505, finished at 0/224E8200 ``` * `insert_exists` : la ligne insérée viole une contrainte d'unicité non déferrable. Insérons maintenant une ligne sur le **serveur 2** puis sur le **serveur 1** : ```sql --- Serveur 2 INSERT INTO conflits(i,t) VALUES (1, 'conflit'); ``` ```sql --- Serveur 1 INSERT INTO conflits(i,t) VALUES (1, 'first'); ``` Dans la vue `pg_stat_subscription_stats`, nous voyons que les colonnes `apply_error_count` et `confl_insert_exists` s'incrémentent au fil du temps. C'est normal, car contrairement aux deux premiers exemples l'`INSERT` ne peut être ignoré. PostgreSQL retente donc de l'appliquer indéfiniment à moins que l'option `disable_on_error` de la souscription ne soit configurée à `true`, auquel cas la souscription est désactivée. Dans les traces de l'instance du **serveur 2**, le message d'erreur suivant nous informe du conflit : ```text [1224136] ERROR: conflict detected on relation "public.conflits": conflict=insert_exists [1224136] DETAIL: Key already exists in unique index "conflits_pkey", modified locally in transaction 80750 at 2025-10-09 11:59:33.845548+02. Key (i)=(1); existing local row (1, conflit); remote row (1, first). [1224136] CONTEXT: processing remote data for replication origin "pg_112863" during message type "INSERT" for replication target relation "public.conflits" in transaction 49506, finished at 0/224E82F0 ``` Le message permet la encore d'identifier le type de conflit et la table en ligne 1. Le nom de la contrainte violée est ensuite mentionné avec le contenu des lignes sur la source et la cible. Lorsque `track_commit_timestamp` est désactivé (c'est le défaut), la mention ` at 2025-10-09 11:59:33.845548+02` en ligne 2 n'est pas incluse dans le message. Les messages suivant montrent que le _background worker_ responsable d'appliquer les modifications redémarre pour échouer ensuite à nouveau, ce qui confirme ce que l'on observait dans la vue : ```text [1024037] LOG: background worker "logical replication apply worker" (PID 1224136) exited with exit code 1 [1224153] LOG: logical replication apply worker for subscription "from_srv1" has started ``` Il faut supprimer la ligne sur le **serveur 2** pour que la réplication reprenne. ```sql -- Serveur 2 DELETE FROM conflits WHERE i = 1; ``` * `update_exists` : la ligne mise à jour viole une contrainte d'unicité non déferrable. Pour observer ce conflit, les requêtes suivantes doivent être exécutées : ```sql -- Serveur 2 INSERT INTO conflits(i,t) VALUES (2, 'conflit'); ``` ```sql -- Serveur 1 UPDATE conflits SET i = 2 WHERE i = 1; ``` Cette fois-ci, ce sont les colonnes `apply_error_count` et `confl_update_exists` qui sont incrémentées. Là encore, le nombre d'erreurs augmente indéfiniment avec la configuration actuelle. Cette fois-ci dans les traces de **serveur 2**, on voit l'erreur suivante : ```text [1235951] ERROR: conflict detected on relation "public.conflits": conflict=update_exists [1235951] DETAIL: Key already exists in unique index "conflits_pkey", modified locally in transaction 80867 at 2025-10-09 14:40:36.769303+02. Key (i)=(2); existing local row (2, conflit); remote row (2, first); replica identity (i)=(1). [1235951] CONTEXT: processing remote data for replication origin "pg_112863" during message type "UPDATE" for replication target relation "public.conflits" in transaction 49508, finished at 0/224E86C8 ``` Pour ce message aussi, l’activation du paramètre `track_commit_timestamp` permet de tracer le timestamp de commit de la transaction en ligne 2. Là encore, le _background worker_ s'arrête et un nouveau est démarré. Le cycle continue tant que le problème persiste : ```text [1024037] LOG: background worker "logical replication apply worker" (PID 1235951) exited with exit code 1 [1235954] LOG: logical replication apply worker for subscription "from_srv1" has started ``` Il faut supprimer la ligne sur le **serveur 2** pour que la réplication reprenne. ```sql -- Serveur 2 DELETE FROM conflits WHERE i = 2; ``` * `multiple_unique_conflicts` : la ligne insérée ou mise à jour viole plusieurs contraintes uniques non déferrables. Avant l'ajout de ce conflit, PostgreSQL s'arrêtait au premier conflit. Si une opération entrait en conflit avec plusieurs contraintes, il fallait donc s'y reprendre plusieurs fois avant de corriger complètement le problème. L'expérience utilisateur était donc mauvaise. Pour démontrer le conflit, nous allons reproduire le cas précédent avec des contraintes supplémentaires sur la table : ```sql -- Serveur 1 et 2 ALTER TABLE conflits ADD UNIQUE (t); ``` ```sql -- Serveur 2 INSERT INTO conflits(i,t) VALUES (1, 'second'); ``` ```sql -- Serveur 1 UPDATE conflits SET i = 1, t = 'second' WHERE i = 2; ``` Cette fois, le message est différent dans les traces de l'instance : il liste l'ensemble des contraintes violées. ```text [1319115] ERROR: conflict detected on relation "public.conflits": conflict=multiple_unique_conflicts [1319115] DETAIL: Key already exists in unique index "conflits_pkey", modified locally in transaction 83328 at 2025-10-10 16:39:28.91569+02. Key (i)=(1); existing local row (1, second); remote row (1, second); replica identity (i)=(2). Key already exists in unique index "conflits_t_key", modified locally in transaction 83328 at 2025-10-10 16:39:28.91569+02. Key (t)=(second); existing local row (1, second); remote row (1, second); replica identity (i)=(2). [1319115] CONTEXT: processing remote data for replication origin "pg_112863" during message type "UPDATE" for replication target relation "public.conflits" in transaction 49625, finished at 0/2E316CA0 ``` Les lignes 2 et 4 contiennent des _timestamps_ qui ne sont visibles que si `track_commit_timestamp` est activé. Là encore, le _background worker_ s'arrête et un nouveau est démarré. Le cycle continue tant que le problème persiste : ```text [1024037] LOG: background worker "logical replication apply worker" (PID 1319115) exited with exit code 1 [1319131] LOG: logical replication apply worker for subscription "from_srv1" has started ``` Les compteurs `confl_multiple_unique_conflicts` et `apply_error_count` de la vue de statistique sont incrémentés. Afin de poursuivre la démonstration, vidons la table sur le **serveur 1** et le **serveur 2** et retirons la contrainte d'unicité sur la colonne `t` : ```sql -- Serveur 1 et 2 TRUNCATE conflits; ALTER TABLE conflits DROP CONSTRAINT conflits_t_key; ``` Avant de de voir les deux derniers types de conflits, nous allons devoir compléter l'architecture et mettre en place des réplications bidirectionnelles. Nous allons commencer par compléter la configuration des **serveurs 1** et **2** en créant l'utilisateur de réplication et la publication sur le **serveur2** : ```sql -- Serveur 2 CREATE ROLE repli WITH LOGIN REPLICATION PASSWORD 'repli'; CREATE PUBLICATION srv2 FOR TABLE conflits; GRANT SELECT ON conflits TO repli; ``` Nous allons maintenant compléter en créant la souscription sur le **serveur1** qui consomme les modifications depuis la publication du **serveur 2**. Profitons de la mise en place de cette réplication bi-directionnelle pour illuster l'importance des origines de réplication et du paramètre de souscription `origin`. Il est justement apparu dans PostgreSQL 16 pour faciliter des réplications logiques croisées. Par défaut, il vaut `any` : ```sql -- Serveur 1 CREATE SUBSCRIPTION from_srv2 CONNECTION 'host=/var/run/postgresql port=5436 user=repli dbname=benoit application_name=srv1' PUBLICATION srv2; ``` Testons une insertion : ```sql -- Serveur 1 INSERT INTO conflits VALUES (1, 'first'); ``` Le message d'erreur suivant est visible dans les traces du **serveur 1**, il s'agit d'un message que nous avons déjà rencontré précédemment : ```text [1252871] ERROR: conflict detected on relation "public.conflits": conflict=insert_exists [1252871] DETAIL: Key already exists in unique index "conflits_pkey", modified locally in transaction 49569 at 2025-10-09 17:45:34.455884+02. Key (i)=(1); existing local row (1, first); remote row (1, first). [1252871] CONTEXT: processing remote data for replication origin "pg_123045" during message type "INSERT" for replication target relation "public.conflits" in transaction 82761, finished at 0/8633678 [1023946] LOG: background worker "logical replication apply worker" (PID 1252871) exited with exit code 1 ``` À ce stade, la réplication est bloquée. La ligne `CONTEXT` nous informe que l'insert provient de l'origine `pg_123045`. La requête suivante exécutée sur le **serveur 1** montre qu'il s'agit de origine de réplication rattaché à la souscription nommée `from_srv2`. ```sql SELECT ro.*, sub.oid, sub.subname FROM pg_replication_origin AS ro, pg_subscription AS sub; ``` ```text roident | roname | oid | subname ---------+-----------+--------+----------- 1 | pg_123045 | 123045 | from_srv2 (1 row) ``` L'insertion est donc exécutée sur le **serveur 1** et répliquée sur le **serveur 2**. Elle est ensuite redécodée sur le **serveur 2** et renvoyée au **serveur 1** qui ne peut appliquer la modification car la ligne est déjà présente, ce qui provoque une violation de contrainte d'unicité. Pour corriger le problème, il faut modifier la souscription pour qu'elle ne récupère que les modifications qui n'ont pas d'origine de réplication (en d'autres termes, celles qui sont issues des requêtes DML et des `TRUNCATE` du serveur qui fait le décodage logique) : ```sql -- Serveur 1 ALTER SUBSCRIPTION from_srv2 SET (origin = none); ``` ```sql -- Serveur 2 ALTER SUBSCRIPTION from_srv1 SET (origin = none); ``` Cela débloque la réplication et règle le problème. Vidons la table sur le **serveur 1** et le **serveur 2** : ```sql -- Serveur 1 et 2 TRUNCATE conflits; ``` Ajoutons maintenant une troisieme instance dont les modifications sont consommées par avec le **serveur 2** ! ```sql -- Serveur 3 CREATE ROLE repli WITH LOGIN REPLICATION PASSWORD 'repli'; CREATE TABLE conflits (i int PRIMARY KEY, t text); CREATE PUBLICATION srv3 FOR TABLE conflits; GRANT SELECT ON conflits TO repli; ``` ```sql -- Serveur 2 CREATE SUBSCRIPTION from_srv3 CONNECTION 'host=/var/run/postgresql port=5437 user=repli dbname=benoit application_name=srv2' PUBLICATION srv3 WITH (origin=none); ``` Nous pouvons maintenant nous intéresser aux deux derniers types de conflits : * `update_origin_ differ` : la ligne mise à jour a été précédemment modifiée par une autre origine. ```sql -- Serveur 3 INSERT INTO conflits(i,t) VALUES (1, 'from srv3'); ``` L'ordre est répliqué vers le **serveur 2** mais pas le **serveur 3** à cause du paramètre `origin = none` des souscriptions. ```sql -- Serveur 2 DELETE FROM conflits; ``` ```sql -- Serveur 1 INSERT INTO conflits(i,t) VALUES (1, 'from srv1'); ``` La ligne est bien répliquée sur le **serveur 2** : ```sql -- Server 2 TABLE conflits ; ``` ```text i | t ---+----------- 1 | from srv1 (1 row) ``` ```sql -- Serveur 3 UPDATE conflits SET t = 'from srv3 !!' WHERE i = 1; ``` À nouveau, la ligne est bien répliquée sur le **serveur 2** : ```sql -- Server 2 TABLE conflits ; ``` ```text i | t ---+-------------- 1 | from srv3 !! (1 row) ``` Dans la vue `pg_stat_subscription_stats`, la colonne `confl_update_origin_differs` a été incrémentée pour la souscription `from_srv3`. Dans les traces apparaît également le message suivant : ```text [1301971] LOG: conflict detected on relation "public.conflits": conflict=update_origin_differs [1301971] DETAIL: Updating the row that was modified by a different origin "pg_112863" in transaction 83317 at 2025-10-10 14:13:58.64601+02. Existing local row (1, from srv1); remote row (1, from srv3 !!); replica identity (i)=(1). [1301971] CONTEXT: processing remote data for replication origin "pg_112871" during message type "UPDATE" for replication target relation "public.conflits" in transaction 792, finished at 0/1C86ED8 ``` Le message nous donne une information supplémentaire : le nom de l'origine de réplication responsable de la précédente modification de la ligne (`pg_112863`). Ce type de conflit est complètement ignoré si `track_commit_timestamp` est désactivé. La mise à jour ne provoque pas d'erreur lors de son exécution, mais provoque une incohérence entre les données du **serveur 1** et du **serveur 2**. Pour le moment PostgreSQL ne propose pas de solution pour réagir à ce conflit. Il s'agit d'un second volet de la fonctionnalité qui sera ajouté dans une version future. * `delete_origin_differ` : la ligne supprimée a été modifiée précédemment par une autre origine. ```sql -- Serveur 1 DELETE FROM conflits WHERE i = 1; ``` La ligne est bien supprimée sur le **serveur 2**. Dans la vue `pg_stat_subscription_stats` la colonne `confl_delete_origin_differs` a été incrémentée pour la souscription `from_srv1`. Dans les traces apparaît également le message suivant : ```text [1253168] LOG: conflict detected on relation "public.conflits": conflict=delete_origin_differs [1253168] DETAIL: Deleting the row that was modified by a different origin "pg_112871" in transaction 83318 at 2025-10-10 14:14:12.814603+02. Existing local row (1, from srv3 !!); replica identity (i)=(1). [1253168] CONTEXT: processing remote data for replication origin "pg_112863" during message type "DELETE" for replication target relation "public.conflits" in transaction 49620, finished at 0/2E2F34A0 ``` Comme l'autre conflit lié aux origines de réplication, ce type de conflit est complètement ignoré si `track_commit_timestamp` est désactivé.