Réplication logique PostgreSQL (publication / abonnement)

Réplication logique au niveau table entre deux instances PostgreSQL : wal_level=logical, contrôle de la replica identity, publication et abonnement, synchronisation initiale, supervision, gestion des conflits, synchronisation manuelle des séquences. Inclut aussi les étapes de bascule finale (cutover) qui limitent l'interruption à quelques secondes pour les migrations entre versions (major upgrade, changement de serveur).

La réplication logique n'est ni une solution HA ni une méthode de sauvegarde : pour une copie exacte de toute l'instance et le failover, utilisez la streaming replication ou Patroni, et pour les sauvegardes pgBackRest. La montée de version majeure sur place sur le même serveur (pg_upgrade) est décrite dans pg-major-upgrade.

Topologie: 2 serveurs : publieur (publisher) + abonné (subscriber) ; le publieur peut être un serveur unique, une streaming replication ou un cluster Patroni

Scénarios

• Réplication continue (reporting, intégration, copie partielle) — Les tables sélectionnées sont répliquées en continu vers une autre instance (serveur de reporting, alimentation d'un entrepôt de données, intégration entre services). L'abonné reste accessible en écriture.
• Migration : nouveau serveur / nouvelle version majeure + bascule finale (cutover) — Passage vers un nouveau serveur ou une nouvelle version majeure avec quelques secondes d'interruption : copie initiale et synchronisation continue, puis courte suspension des écritures et bascule finale (cutover).

Avant de commencer

• PostgreSQL … tourne sur le publieur et la base … existe.
• Le cluster Patroni … est sain et … est actuellement Leader ; hot_standby_feedback = on sur les replicas (valeur par défaut de pg-patroni).
• Le standby … est connecté au primary via un slot de réplication physique (primary_slot_name = …, option slot de pg-streaming). La synchronisation des slots ne fonctionne pas sans ce slot.
• PostgreSQL … a été installé sur l'abonné avec pg-install et tourne ; les versions … des extensions utilisées sur le publieur (PostGIS, etc.) sont installées.
• L'abonné peut joindre …l'adresse HAProxy/VIP du publieur (…:…)…le port … du publieur… (pare-feu / groupe de sécurité).
• Quelle est la valeur actuelle de wal_level sur le publieur ? Si elle n'est pas logical, la modifier exige un redémarrage : planifiez une fenêtre de maintenance.
• Les tables à publier ont une clé primaire ou un index unique adapté. Sinon UPDATE/DELETE échouent sur le publieur : ajoutez une PK aux tables listées ou définissez une replica identity (étape ci-dessous).
• La réplication logique ne transporte pas le DDL (CREATE/ALTER TABLE), ni les valeurs de séquences, ni les large objects. Pendant la réplication, les modifications de schéma seront appliquées manuellement, d'abord sur l'abonné puis sur le publieur ; l'équipe a été informée.
• Le publieur dispose d'une marge disque pour le WAL : si l'abonné s'arrête, le slot accumule du WAL (limite : … Go).

Phases du guide

• Préparation du publieur — Vérifier que le publieur est le …Leader…primary… · Écrire les paramètres de réplication logique · Écrire les paramètres de réplication logique dans Patroni · Redémarrer le publieur · Redémarrer les membres Patroni · Créer l'utilisateur de réplication …
• Replica identity — Lister les tables sans PK · Définir une replica identity pour les tables sans PK
• Publication — Créer la publication
• Préparation de l'abonné — Écrire les paramètres des workers de l'abonné · Redémarrer l'abonné · Écrire le mot de passe du publieur dans .pgpass · Créer la base de données et le schéma · Transférer les rôles et les droits
• Abonnement et synchronisation initiale — Créer l'abonnement · Attendre la fin de la synchronisation initiale · Comparer le nombre de lignes · Test de modification en direct
• Préservation du slot lors du failover — Définir le slot comme permanent dans Patroni · Vérifier que le slot est visible sur les replicas · Primary : définir les slots de standby synchronisés · Standby : activer la synchronisation des slots · Rediriger l'abonnement vers le nouveau primary après un failover · Recréer l'abonnement après un failover
• Supervision — État de l'abonnement (abonné) · Retard du slot (publieur)
• Gestion des conflits — Trouver l'erreur · Corriger la ligne en conflit sur l'abonné · Sauter la transaction en conflit
• Synchronisation des séquences — Transférer les valeurs des séquences du publieur vers l'abonné
• Bascule finale (cutover) — Arrêter les écritures sur le publieur · Vérifier que les dernières modifications ont été transférées · Synchroniser les séquences une dernière fois · Supprimer l'abonnement · Collecter les statistiques · Rediriger les applications vers l'abonné …
• Opérations de maintenance — Ajout d'une nouvelle table · Modification de schéma (ALTER TABLE) · Suppression d'un abonnement (publieur injoignable)