Votre application collaborative de gestion de tickets de caisse démarre fort, et fort de votre réseau, vous vous construisez une belle clientèle régionale, puis nationale. Bravo ! Avec le temps, votre projet grandit et évolue, puis vous vous retrouvez plus tôt que vous ne le pensiez avec une base de clientèle répartie sur tous les fuseaux horaires et sur tous les continents. Malheureusement, votre application commence à montrer des signes de faiblesse, des lenteurs, un manque de performance, des connexions interrompues, etc. Et ce, alors que vous aviez pris toutes les précautions sur votre infrastructure d’origine : autoscaling, healthchecks et auto-remédiation, read replicas sur votre base de données, code optimisé aux petits oignons…

L’origine du problème

Comme vous le savez probablement, dans le cas où vous devez servir des clients tout autour du globe, il ne suffit pas d’avoir une application optimisée pour servir du contenu depuis un seul endroit.

Dans ce cas de figure, John, situé en Amérique du Nord, doit parcourir un chemin important depuis son routeur jusqu’au serveur situé en Europe de l’Ouest. En plus d'ajouter une nouvelle source d’indisponibilité (la rupture des connexions transatlantiques) (ndlr: on peut s’accorder sur sa faible probabilité d'occurrence), cela rajoute une latence incompressible à l’accès aux données qui peut rendre l’expérience utilisateur très désagréable.

CDN et edge locations à la rescousse !

Dans les cas les plus simples (qui sont aussi les plus fréquents), on peut utiliser un CDN ou un mécanisme de Edge Locations pour servir les ressources statiques depuis des points d’accès dispersés tout autour du globe.

Un CDN, pour Content Delivery Network, est un système composé d’un maillage de serveurs répartis sur l’ensemble de la planète (ou du moins, sur l’ensemble de la zone géographique où vos clients sont situés). Son but est de servir des ressources statiques au plus proche des utilisateurs, avec des fonctionnalités telles que du cache et de la terminaison TLS.

Les Edge Locations sont des points d’entrée d’un réseau répartis sur l’ensemble du globe. Dans le cadre d’AWS, on peut utiliser des services tels que AWS Global Accelerator pour diriger les clients vers ces Edge Locations. Grâce à ces points d’entrée sur le réseau AWS, les requêtes circulent ensuite sur le réseau privé d’AWS (donc en dehors d’Internet) pour rejoindre avec une latence réduite des ressources réseau telles que des Application Load Balancers ou des instances EC2.

Déplacer la donnée au plus près de l’utilisateur

Ces deux méthodes sont généralement efficaces, mais parfois il peut s’avérer nécessaire de partitionner votre application et de rapprocher physiquement l’infrastructure, du load balancer à la base de données. Dans ce cas, l’utilisation des CDN et Edge Locations ne sera plus suffisante et il faut commencer à réfléchir à des architectures permettant la réplication des données de manière globale.

On va donc essayer de rapprocher John de notre application et de sa base de données :

Maintenant, nos deux utilisateurs ont accès à la même application, avec des performances réseau bien plus élevées. Cependant, nous avons vite oublié que notre application de gestion de tickets de caisse est une application collaborative… Ainsi, si John et Jean veulent travailler sur un même ticket, quelles solutions s’offrent à eux ?

• Se connecter au serveur de l’autre pour travailler sur un ticket en particulier ? Cela fonctionnerait, mais on reviendrait à notre problème initial de performances réseau.
• Travailler en pair à travers Teams, avec un partage d’écran ? Pourquoi pas, mais cela supprime le travail asynchrone.
• S’envoyer les changements via une messagerie instantanée ? Dommage, c’était tout le but de notre application que d’éviter cela…

Au-delà de solutions plus ou moins “exotiques”, une bonne approche pour résoudre le problème rencontré par John est la réplication bi-directionnelle ! Elle assure qu’une écriture sur une base de données soit répliquée sur l’autre, et vice-versa. De cette manière, nos deux compères pourront travailler ensemble dans des conditions de performance optimales. Voyons comment implémenter cela dans PostgreSQL.

Point important : on considère là que le seul goulot d’étranglement de notre application est sa base de données. C’est rarement le cas, je vous l’accorde.

Les différents types de réplication

Avant de rentrer en détail dans la mise en place de la réplication bi-directionnelle, voyons déjà quelles solutions de réplication sont disponibles avec PostgreSQL.

Réplication physique

Pour comprendre le fonctionnement de la réplication physique, il est nécessaire de rappeler un élément clé du fonctionnement de PostgreSQL : le WAL (pour Write-ahead Log). Pour optimiser les écritures sur la base de données, les requêtes ne sont en fait pas écrites en base les unes après les autres, mais par lot. Pour cela, les changements sont d’abord journalisés dans le WAL. Dans un second temps et à intervalle régulier, toutes les modifications sont appliquées en une seule fois. Bien entendu, cette optimisation est parfaitement invisible d’un point de vue utilisateur. Ce mécanisme permet d'obtenir plusieurs bénéfices :

• Réduire la charge sur le disque. En limitant les lectures et écritures sur les fichiers de données qui peuvent être complexes à analyser, on rend le système plus performant
• Limiter le risque de pertes de données. L’opération d’écriture sur le WAL étant plus rapide, on s’assure de garder l’intégrité des données en cas de crash : il suffira simplement de relire le journal au redémarrage pour avoir nos données dans l’état avant de rencontrer l’anomalie.
• Mettre en place de la récupération “point-in-time”. Une fois que le WAL a été appliqué sur les fichiers de données, on peut l’archiver et le réutiliser au moment de restaurer une sauvegarde plus ancienne. Dans ce cas, il suffira de récupérer le morceau d’archive qui nous amène à l’instant qui nous intéresse.

On peut maintenant se plonger sereinement dans la réplication physique. En se basant sur la copie des WAL entre un nœud principal et un nœud secondaire (rien n’interdit d’en avoir plusieurs), il est possible de garantir une synchronisation complète et exacte des différents serveurs de base de données. Pour cela, deux mécanismes sont à notre disposition :

• Réplication des fichiers (ou file-based WAL shipping) : envoyer un fichier de WAL dès qu’il est complet, c’est-à-dire dès qu’il a atteint sa taille limite (16Mo par défaut). Cela limite le trafic entre les nœuds, mais empêche la synchronicité et augmente les possibilités de perte de données en cas de failover.
• Réplication des entrées (ou streaming replication) : envoyer les inscriptions au WAL dès qu’elles sont émises, ce qui permet de réaliser une réplication synchrone au prix d’un trafic réseau plus élevé.

Le choix entre réplication des fichiers et réplication des entrées dépendra de l’usage de la base de données par les applications et devra bien entendu être analysé pour chaque projet. Par exemple, pour un besoin de consistance stricte entre deux nœuds (dans un scénario de read replica avec failover), il faudra choisir la réplication des entrées.

À noter qu’il n’est pas possible de mettre en place une réplication physique entre deux nœuds fonctionnant sur des versions majeures différentes.

Puisque le WAL décrit des changements au niveau du disque, et non au niveau des tuples directement, il n’est pas possible d’avoir une notion d’origine des changements. Ainsi, la réplication physique n’est pas compatible avec notre cas d’usage qui nécessite une réplication bi-directionnelle.

Réplication logique

À la différence de la réplication physique qui se base sur les changements au niveau du disque, la réplication logique se base sur les changements au niveau des tuples. Cela permet de pouvoir appliquer une granularité beaucoup plus fine au niveau des informations transmises : quelles tables, quels tuples, quelles opérations… De plus, il est également possible depuis PostgreSQL 16 de filtrer l’origine des opérations : cela permet de réaliser une réplication bi-directionnelle, et ce sera l'objet de l'exemple présenté plus bas.

Cependant, la réplication logique ne diffuse pas les opérations de DDL (Data Definition Language). Ainsi, il est nécessaire de gérer les schémas des différents nœuds séparément.

L’avantage particulier de cette méthode par rapport à la réplication physique est qu’elle est compatible entre versions majeures de PostgreSQL. Ainsi, cette fonctionnalité devient essentielle pour réaliser une mise à jour majeure d’une instance sans indisponibilité. Il suffit de démarrer une nouvelle instance avec la nouvelle version majeure, de mettre en place une réplication logique entre la nouvelle instance et l’ancienne, et de faire une bascule DNS sur la nouvelle. Le temps d’indisponibilité de la solution se réduit au temps nécessaire sans écritures pour que le replication lag (la différence de données entre l’instance émettrice et l’instance réceptrice) soit nul.

Synchronicité

Il est possible de rendre synchrone la réplication entre deux instances, dans deux cas :

• Réplication physique des entrées
• Réplication logique

Pour ce faire, le nœud principal doit indiquer dans sa configuration une liste de noms sous la clé synchronous_standby_names. Dans le cas de la réplication physique, c’est le nom indiqué dans la clé primary_conninfo, dans le cas de la réplication logique, c’est la valeur indiquée dans la souscription (ou le nom de la souscription par défaut). Lorsque cette configuration est réalisée, les opérations COMMIT sur le nœud principal attendront d’avoir une confirmation de réception de la part des nœuds secondaires avant de se terminer.

L’implémentation pré-PostgreSQL 16 : pglogical, le char d’assaut

Avant la sortie de PostgreSQL 16, il était d’ores et déjà possible de mettre en place une réplication bi-directionnelle grâce à pglogical, une extension pour PostgreSQL développée par 2nd Quadrant (entreprise britannique rachetée par EnterpriseDB en 2020). Elle se base sur les fonctionnalités natives disponibles depuis PostgreSQL 9.5 et propose des rajouts, tels que le filtrage des origines qui nous intéresse dans ce cas.

Pour configurer la réplication bi-directionnelle avec cette extension, il suffit ici de rajouter un argument forward_origins := '{}' sur l’appel à pglogical.create_subscription (à l’intérieur d’une transaction), afin de ne pas transmettre les modifications ne provenant pas du nœud souscripteur. Cela permet de ne pas rencontrer le problème du “ping-pong” que nous verrons plus bas.

L’extension pglogical est certes parfaitement fonctionnelle, mais elle peut parfois paraître complexe à mettre en place pour des petits besoins. À l'exception des services managés tels que AWS RDS qui la proposent out of the box, il est nécessaire de l’installer séparément (via des gestionnaires de paquets) ou de la compiler spécifiquement pour la plateforme sur laquelle PostgreSQL est déployé, puis de gérer son cycle de vie séparément. Nous allons donc voir comment s’affranchir de l’extension.

L’implémentation post-PostgreSQL 16

Préambule

Les requêtes qui suivent sont répertoriées dans un projet GitHub. Les instructions pour lancer le notebook correspondant sont indiquées dans le README à la racine du dépôt.

Données initiales

Pour illustrer notre propos, nous allons créer une table ticket comportant deux colonnes : un UUID qui sera auto-généré et une valeur numérique représentant le montant total du ticket de caisse.

Nous allons commencer par nous connecter sur notre instance d’Europe de l'Ouest, créer notre table et y insérer quelques tuples.

DROP TABLE IF EXISTS ticket; CREATE TABLE ticket(
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    total NUMERIC(5,2)
)
INSERT INTO ticket(total) VALUES (50.23), (22.3);
SELECT * FROM ticket;

Création de la publication

Notre table est prête et contient déjà quelques données. On va maintenant créer une publication pour que notre instance nord-américaine puisse se synchroniser avec les changements locaux.

CREATE PUBLICATION pub_ticket_western_europe FOR TABLE ticket;

Création de la souscription

Maintenant, nous pouvons nous connecter à l'instance nord-américaine pour mettre en place la souscription. Les opérations de DDL (Data Definition Language) n’étant pas répliquées, on va commencer par créer la table ticket avec la même spécification.

DROP TABLE IF EXISTS ticket; CREATE TABLE ticket(
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    total NUMERIC(5,2)
)

On va pouvoir souscrire à la publication réalisée plus haut sur l'autre instance.

CREATE SUBSCRIPTION sub_ticket_western_europe
    CONNECTION 'host=pg_western_europe port=5432 user=postgres'
    PUBLICATION pub_ticket_western_europe;

Lorsqu'on crée une souscription sur un nœud, le processus commence par prendre un instantané de la table sur le nœud publicateur. Pour vérifier que cela fonctionne correctement, regardons le contenu de notre table :

SELECT * FROM ticket;

Nous avons bien les données insérées plus haut dans la base européenne. Maintenant, que se passe-t-il si on essaie d'insérer un nouveau tuple ?

INSERT INTO ticket (total) VALUES (15.32); SELECT * FROM ticket;

Nous avons bien notre nouveau ticket. Par contre, il est logique qu'il ne soit pas répliqué de l'autre côté de l'Atlantique puisque nous n'avons pas encore réalisé la route "retour". C'est ce que nous allons faire à présent.

Création d'une publication dans le sens retour

Commençons par créer une publication sur notre instance nord-américaine :

CREATE PUBLICATION pub_ticket_na FOR TABLE ticket;

Ensuite, on va se connecter en Europe de l’Ouest et créer une souscription, puis on va regarder le contenu de notre table pour vérifier que le ticket créé en Amérique remonte bien.

CREATE SUBSCRIPTION sub_ticket_na
    CONNECTION 'host=pg_na port=5432 user=postgres'
    PUBLICATION pub_ticket_na;
SELECT * FROM ticket;

Misère, notre ligne rajoutée plus haut ne remonte pas. En analysant les logs de notre instance européenne, on voit ce genre de messages (peut varier car l'UUID est autogénéré) :

pg-western-europe-1 |2025-02-05 11:04:03.441 UTC [84] LOG:  logical replication table synchronization worker for subscription "sub_ticket_na", table "ticket" has started
pg-western-europe-1 |2025-02-05 11:04:03.465 UTC [84] ERROR:  duplicate key value violates unique constraint "ticket_pkey"
pg-western-europe-1 |2025-02-05 11:04:03.465 UTC [84] DETAIL:  Key (id)=(c7eb8a92-35ea-4be5-9c51-1691f645381a) already exists.
pg-western-europe-1 |2025-02-05 11:04:03.465 UTC [84] CONTEXT:  COPY ticket, line 1

On arrive à un des problèmes inhérents à la réplication bi-directionnelle : les conflits. À la création de la souscription, on essaie de récupérer les données déjà présentes sur le publicateur. Or, celui-ci contient des données récupérées par la création de la souscription dans le sens “aller”, donc la copie génère une erreur liée à l'unicité des clés primaires. On va donc utiliser l'option copy_data pour désactiver la copie initiale, ce qui pourrait nécessiter en conditions réelles de faire une synchronisation de base par un autre biais (Lambdas, Spring Batch, …).

DROP SUBSCRIPTION sub_ticket_na
CREATE SUBSCRIPTION sub_ticket_na
    CONNECTION 'host=pg_na port=5432 user=postgres'
    PUBLICATION pub_ticket_na
    WITH (copy_data=false, ORIGIN=none);

Ensuite, on va se rendre sur la base nord-américaine pour insérer une ligne…

INSERT INTO ticket(total) VALUES(14.34);

Et retourner en Europe pour s’assurer qu’elle apparaisse bien.

SELECT * FROM ticket;

Superbe, maintenant on obtient bien dans la base Européenne les lignes insérées dans la base Américaine. Par contre, on rencontre à présent un petit souci dans celle-ci :

pg-na-1 |2025-02-05 12:44:34.052 UTC [91] LOG:  logical replication apply worker for subscription "sub_ticket_western_europe" has started
pg-na-1 |2025-02-05 12:44:34.060 UTC [91] ERROR:  duplicate key value violates unique constraint "ticket_pkey"
pg-na-1 |2025-02-05 12:44:34.060 UTC [91] DETAIL:  Key (id)=(15d85d16-aba1-481b-b5d4-4b8303a8fc1b) already exists.
pg-na-1 |2025-02-05 12:44:34.060 UTC [91] CONTEXT:  processing remote data for replication origin "pg_16394" during message type "INSERT" for replication target relation "public.ticket" in transaction 762, finished at 0/155A788
pg-na-1 |2025-02-05 12:44:34.062 UTC [1] LOG:  background worker "logical replication apply worker" (PID 91) exited with exit code 1

La ligne répliquée sur la base européenne revient sur la base américaine, car nous avions déjà un canal de réplication d'ouvert. Dans la réplication logique, à partir du moment où une opération est en erreur, cela devient bloquant et les opérations suivantes ne sont plus répliquées.

C'est là que PostgreSQL 16.x introduit une amélioration, avec l'ajout de l'option ORIGIN dans les souscriptions. Cette option permet de ne recevoir que les événements dont l’origine est le nœud publicateur : ainsi, il n’est plus possible de recevoir des événements que l’instance souscripteur avait émis (et transmis) à d’autres nœuds. On va donc supprimer la souscription existante pour en recréer une avec la bonne configuration.

DROP SUBSCRIPTION sub_ticket_western_europe;
CREATE SUBSCRIPTION sub_ticket_western_europe
    CONNECTION 'host=pg_western_europe port=5432 user=postgres'
    PUBLICATION pub_ticket_western_europe
    WITH (copy_data=false, ORIGIN=none);

Maintenant, on va essayer d'insérer une ligne côté NA et s'assurer que nous n'avons pas le problème de retour en réalisant la même opération en Europe, puis faire un SELECT en Amérique pour s’assurer qu’elle remonte bien (et donc que le processus de réplication n’est pas bloqué).

INSERT INTO ticket(total) VALUES(17.28); -- en amérique
SELECT * FROM ticket; -- en europe

INSERT INTO ticket(total) VALUES(20.31); -- en europe
SELECT * FROM ticket; -- en amérique

La ligne avec total=17.28 créée en Amérique du Nord a bien été répliquée en Europe, et on a même pu valider que le processus n'était pas bloqué en créant une autre ligne (total=20.31) en Europe, qui s'est bien retrouvée de l'autre côté. Plus de problèmes de ping-pong !

Notes additionnelles : surveiller et débugger la réplication

Dans le cas où la réplication serait bloquée par une ligne en erreur (clé primaire déjà existante par exemple), il est possible d'exécuter certaines commandes pour débloquer la situation.

SELECT * FROM pg_stat_subscription_stats;
SELECT oid, subname, subenabled, subdisableonerr FROM pg_subscription;
SELECT pg_replication_origin_advance('pg_00000', '0/1588FF9'::pg_lsn); -- dans ce cas, l'adresse hexa doit être celle remontée dans les logs, plus 1. Cela permet de passer à l'instruction suivante

Conclusion

Nous avons pu explorer les possibilités offertes nativement depuis PostgreSQL 16.0 pour mettre en œuvre la réplication logique bidirectionnelle entre deux instances. Bien que cette solution rende plus facile la distribution d’une application, il faut tout de même prendre en compte les limitations qu’elle pose :

• Pas de réplication des DDL (Data Definition), ce qui nécessite une grande rigueur sur l’application simultanées des modifications de schéma entre les instances. À noter que les schémas ne doivent pas être parfaitement synchronisés à chaque instant pour que la réplication fonctionne.
• Dans la réplication logique, les séquences ne sont pas répliquées. Dans un cas de lecture seule, cela ne pose pas de problème puisque la séquence de l’instance receveuse ne sera pas sollicitée. Dans notre cas, il faut faire en sorte que les séquences de chaque instance ne rentrent pas en conflit, ou alors utiliser des UUIDs pour supprimer le problème d’unicité.
• La résolution de conflits automatique n’est pas implémentée nativement, il faudra passer par pglogical (et la configuration pglogical.conflict_resolution) si le besoin se présente.

La réplication logique reste cependant une alternative solide à la réplication physique et cette nouveauté permet de l’utiliser dans des cas plus complexes qu’auparavant.