Réplication logique PostgreSQL : un guide testé sur deux clusters

La réplication logique PostgreSQL copie les données de tables choisies depuis un serveur de publication vers un abonné, puis transmet les changements suivants. Une publication définit les tables et les types de modifications ; un abonnement s’y connecte et applique les données.

Ce guide suit un exemple unidirectionnel testé avec deux clusters PostgreSQL 18.6 distincts sous Windows. Nous avons vérifié la copie initiale, les changements de lignes, une incompatibilité de schéma et sa correction, puis la suppression des slots de réplication. Les 17 contrôles ont réussi. Les données sont fictives ; aucune base de production n’a été utilisée.

Préparer deux clusters temporaires

Utilisez deux clusters distincts, avec publisher_demo sur le serveur de publication et subscriber_demo sur l’abonné. Notre test limitait les deux serveurs à 127.0.0.1, sur des ports différents, avec une authentification par mot de passe SCRAM. Exécutez chaque bloc SQL dans la base indiquée, avec l’autocommit de psql activé sauf lorsqu’un bloc ouvre explicitement une transaction.

Les conditions de configuration comprennent wal_level=logical sur le serveur de publication et une capacité suffisante pour les slots, les processus d’envoi du WAL et les workers de l’abonné. Prévoyez la synchronisation initiale en plus de l’abonnement principal. Redémarrez lorsqu’un réglage l’exige. Notre laboratoire disposait de huit slots et huit processus d’envoi côté publication, ainsi que de quatre workers de réplication logique et dix processus workers au total côté abonné. Ce ne sont pas des recommandations de dimensionnement pour la production.

Le laboratoire utilise son administrateur pour créer les objets et un compte distinct, sans privilège superutilisateur, pour la connexion de réplication. Avec une base hébergée, vérifiez les réglages et privilèges autorisés par le service. Notre guide d’hébergement PostgreSQL, en anglais explique le partage des responsabilités entre service géré et base administrée par vos soins.

Créer les tables correspondantes des deux côtés

Exécutez ce bloc une fois dans chaque base. La table de l’abonné doit être vide ; arrêtez-vous si le schéma du laboratoire existe déjà :

CREATE SCHEMA replication_lab;
CREATE TABLE replication_lab.items (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    quantity integer NOT NULL
);

La clé primaire identifiera les lignes à modifier ou supprimer. La documentation des abonnements explique la correspondance des tables par leur nom qualifié par le schéma. Nous utilisons volontairement des définitions identiques. La création de l’abonnement ne crée pas ce schéma à votre place.

Alimenter la source et définir la publication

Exécutez ce bloc uniquement dans publisher_demo. Il crée deux lignes avant le démarrage de la réplication, donne au rôle de connexion l’accès à cette table et publie les insertions, mises à jour et suppressions :

INSERT INTO replication_lab.items (name, quantity)
VALUES ('alpha', 10), ('beta', 20);

CREATE ROLE replica_reader WITH LOGIN REPLICATION;
GRANT CONNECT ON DATABASE publisher_demo TO replica_reader;
GRANT USAGE ON SCHEMA replication_lab TO replica_reader;
GRANT SELECT ON replication_lab.items TO replica_reader;

CREATE PUBLICATION items_pub FOR TABLE replication_lab.items
WITH (publish = 'insert, update, delete');

La publication exclut explicitement la troncature. La liste des opérations contrôle les changements ultérieurs, pas l’exécution de la copie initiale. Une publication exige aussi une identité de réplication pour les mises à jour et suppressions publiées. Notre clé primaire la fournit. Un test négatif distinct a confirmé qu’une mise à jour d’une table publiée sans cette identité échoue sur le serveur de publication. REPLICA IDENTITY FULL est une solution de repli dont les coûts diffèrent, pas un réglage à généraliser.

Avant la connexion, définissez un mot de passe pour replica_reader, par exemple avec \password replica_reader dans psql, et autorisez sa connexion à publisher_demo dans la configuration d’authentification de la source. Les exigences de sécurité décrivent LOGIN, REPLICATION et les permissions nécessaires à la copie initiale. Le nom d’une publication ne constitue pas, à lui seul, une frontière de contrôle d’accès.

Créer l’abonnement sur l’abonné

Préparez une variable psql nommée publisher_conn contenant la chaîne de connexion libpq de la source : hôte, port, base, utilisateur et chemin absolu vers passfile. Le fichier de mots de passe doit se trouver sur l’hôte de l’abonné et être lisible par le compte système de PostgreSQL. Respectez son format et ses permissions ; n’insérez pas de mot de passe réutilisable dans un exemple SQL partagé. Notre script a fourni un fichier temporaire, supprimé après le test.

Exécutez ce bloc dans subscriber_demo, hors transaction explicite. :'publisher_conn' est une substitution de variable psql, pas une syntaxe SQL du serveur :

CREATE SUBSCRIPTION items_sub
CONNECTION :'publisher_conn'
PUBLICATION items_pub
WITH (copy_data = true, slot_name = 'logical_lab_slot');

Avec ces réglages, CREATE SUBSCRIPTION crée le slot sur la source et lance les workers. Avec copy_data=true, les lignes existantes sont copiées avant le rattrapage des changements suivants. La fin de la commande ne signifie pas que la copie est terminée.

Vérifier la copie initiale, puis modifier des lignes

Sur l’abonné, examinez les données et l’état de synchronisation de la table :

SELECT id, name, quantity
FROM replication_lab.items ORDER BY id;

SELECT r.srrelid::regclass AS table_name, r.srsubstate
FROM pg_subscription_rel AS r
JOIN pg_subscription AS s ON s.oid = r.srsubid
WHERE s.subname = 'items_sub';

Notre première vérification complète a montré les lignes 1, alpha, 10 et 2, beta, 20, avec l’état r. Dans pg_subscription_rel, r signifie que la table est prête pour la réplication normale. Cet état ne garantit pas durablement un délai nul pour chaque transaction suivante.

Exécutez maintenant cette transaction sur la source :

BEGIN;
INSERT INTO replication_lab.items (name, quantity) VALUES ('gamma', 30);
UPDATE replication_lab.items SET quantity = 11 WHERE id = 1;
DELETE FROM replication_lab.items WHERE id = 2;
COMMIT;

Répétez la requête de données sur l’abonné jusqu’à l’apparition des changements validés attendus. Avec la sortie non alignée de psql, nous avons obtenu :

1|alpha|11
3|gamma|30

La ligne gamma est arrivée, la quantité d’alpha est passée à 11 et beta a disparu. Le test a attendu ces valeurs précises au lieu de supposer qu’une pause de durée fixe suffirait. Il vérifie le résultat de ce petit jeu de données, sans établir un débit de réplication ni garantir une latence.

Gérer séparément l’état des séquences

Sur l’abonné, examinez la séquence associée à la colonne d’identité :

SELECT last_value, is_called
FROM replication_lab.items_id_seq;

Elle indiquait encore last_value=1 et is_called=false, tandis que la source indiquait 3 et true. Les identifiants de lignes ont été copiés, mais la séquence de l’abonné n’a pas avancé. Ne commencez pas à y générer des identifiants locaux en supposant qu’ils suivront le maximum répliqué.

Les restrictions documentées concernent notamment l’état des séquences et les modifications de schéma. Tout transfert prévu des écritures exige des contrôles distincts des séquences, du schéma, de l’application et de la cohérence. Ce guide n’établit pas une procédure de basculement.

Observer une incompatibilité de schéma et la corriger

Le bloc suivant crée volontairement une incompatibilité dans le laboratoire temporaire. Exécutez-le sur la source :

ALTER TABLE replication_lab.items ADD COLUMN note text;
INSERT INTO replication_lab.items (name, quantity, note)
VALUES ('delta', 40, 'review');

L’abonné n’a pas reçu la nouvelle colonne. Son journal a signalé l’absence d’une colonne répliquée nommée note, son compteur d’erreurs d’application a augmenté et la ligne 4 n’est pas apparue. Corrigez le schéma de l’abonné :

ALTER TABLE replication_lab.items ADD COLUMN note text;

La ligne en attente est alors arrivée avec name=delta, quantity=40 et note=review. Nous n’avons ni recréé l’abonnement, ni ignoré une transaction, ni supprimé des données. Pour un ajout planifié, préparer d’abord un schéma compatible sur l’abonné peut éviter cette interruption. Les migrations plus complexes nécessitent leur propre ordre d’exécution testé.

Surveiller le worker et le slot de la source

Sur l’abonné, ces requêtes montrent l’activité des workers et les compteurs d’erreurs cumulés :

SELECT subname, pid, received_lsn,
       last_msg_receipt_time, latest_end_lsn
FROM pg_stat_subscription
WHERE subname = 'items_sub';

SELECT subname, apply_error_count, sync_error_count
FROM pg_stat_subscription_stats
WHERE subname = 'items_sub';

Le guide de supervision décrit les workers d’abonnement. La synchronisation initiale peut mobiliser des workers supplémentaires. Un compteur conserve des événements passés ; il ne suffit pas à établir l’état actuel de la réplication. Comparez les observations dans le temps, consultez le journal et vérifiez les données attendues.

Sur la source, examinez le slot nommé :

SELECT slot_name, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots
WHERE slot_name = 'logical_lab_slot';

pg_replication_slots expose la position confirmée par le consommateur et la plus ancienne position WAL dont le slot peut encore avoir besoin. Une connexion active ne prouve pas, à elle seule, que le consommateur suit le rythme. Surveillez aussi le WAL conservé et l’espace disque : un consommateur arrêté ou en erreur peut entraîner une rétention de WAL sur la source.

Supprimer l’abonnement avant d’arrêter le laboratoire

Gardez la source accessible et exécutez ce bloc sur l’abonné, hors transaction explicite :

DROP SUBSCRIPTION items_sub;

DROP SUBSCRIPTION se reconnecte normalement à la source pour supprimer le slot associé. Notre test a confirmé la disparition de l’abonnement et de tous les slots du laboratoire, tandis que les trois lignes copiées restaient dans la table de l’abonné. Si la source est inaccessible, il faut vérifier et traiter explicitement les slots restants ; une suppression incomplète n’est pas un nettoyage terminé.

Après vérification, supprimez items_pub sur la source, retirez les tables et le rôle temporaires, puis arrêtez les deux serveurs. Notre script a terminé ce nettoyage et supprimé ses fichiers de mots de passe temporaires. Il a conservé les répertoires de données arrêtés et les comptes rendus de test expurgés pour inspection.

La réplication a aussi transmis la suppression de notre exemple. Elle ne fournit donc pas un point de récupération historique indépendant. Maintenez les tests de sauvegarde et de restauration, en anglais, comme une tâche distincte. Les 17 contrôles vérifient une réplication locale et unidirectionnelle ; ils ne couvrent pas le réseau distant, TLS, les écritures concurrentes, une migration entre versions, la charge de production ou le basculement.