Vous trouverez en ligne les différentes versions complètes de ce document.

Vous n’avez pas le droit d’utiliser cette création à des fins commerciales.

Si vous modifiez, transformez ou adaptez cette création, vous n’avez le droit de distribuer la création qui en résulte que sous un contrat identique à celui-ci.

Vous devez citer le nom de l’auteur original de la manière indiquée par l’auteur de l’œuvre ou le titulaire des droits qui vous confère cette autorisation (mais pas d’une manière qui suggérerait qu’ils vous soutiennent ou approuvent votre utilisation de l’œuvre). À chaque réutilisation ou distribution de cette création, vous devez faire apparaître clairement au public les conditions contractuelles de sa mise à disposition. La meilleure manière de les indiquer est un lien vers cette page web. Chacune de ces conditions peut être levée si vous obtenez l’autorisation du titulaire des droits sur cette œuvre. Rien dans ce contrat ne diminue ou ne restreint le droit moral de l’auteur ou des auteurs.

Le texte complet de la licence est disponible sur http://creativecommons.org/licenses/by-nc-sa/2.0/fr/legalcode

Cela inclut les diapositives, les manuels eux-mêmes et les travaux pratiques. Cette formation peut également contenir quelques images et schémas dont la redistribution est soumise à des licences différentes qui sont alors précisées.

Sur les versions précédentes susceptibles d’être encore rencontrées en production, seuls quelques points très importants sont évoqués, en plus éventuellement de quelques éléments historiques.

Sauf précision contraire, le système d’exploitation utilisé est Linux.

Nous verrons ici les principes derrière la réplication logique, les différences avec la réplication physique classique, sa mise en place, son administration et sa supervision.

Historiquement sont apparus des outils de réplication logique externes à PostgreSQL, qui peuvent encore rendre des services.

La réplication physique, qui existe dans PostgreSQL depuis la version 9.0, fonctionne par application de bloc d’octets ou de delta de bloc. Elle a beaucoup évolué mais possède quelques limitations difficilement contournables directement pour certains besoins.

La réplication logique apporte des réponses à ces limitations depuis PostgreSQL 10. Seules des solutions tierces apportaient ce type de réplication à PostgreSQL auparavant.

Les cas d’usage typiques de la réplication logique sont la copie partielle de données, et pas d’une instance entière, vers une instance PostgreSQL indépendante et ouverte en écriture.

À proprement parler, la réplication logique entre instances n’est qu’un cas particulier du système de l’infrastrucure de logical decoding. D’autres plugins permettent la réplication de données vers des systèmes totalement différents de PostgreSQL, ou encore le CDC (Change Data Capture).

Pour l’instance origine comme pour l’instance cible, il est préférable d’utiliser une version récente de PostgreSQL (14 au moins, la dernière majeure de préférence) pour profiter des nombreuses améliorations et optimisations. Nous parlerons parfois des versions antérieures, même non supportées, si elles peuvent avoir une importance dans des migrations de version majeure.

Principe & limites de la réplication physique :

La réplication physique est une réplication au niveau bloc. Le serveur primaire envoie au secondaire les octets à ajouter/remplacer dans des fichiers. Le serveur secondaire n’a aucune information sur les objets logiques (tables, index, vues matérialisées, bases de données). Il n’y a donc pas de granularité possible, c’est forcément l’instance complète qui est répliquée. Il est impossible de ne répliquer que certaines bases ou que certaines tables. Un serveur secondaire est en lecture seule, il est donc impossible de créer des index spécifiques ou même des tables de travail, y compris temporaires.

De plus, un serveur secondaire ne peut se connecter qu’à un serveur primaire de même version majeure. On ne peut donc pas se servir de la réplication physique pour mettre à jour la version majeure du serveur. Enfin, il n’est pas possible de faire de la réplication entre des serveurs d’architectures matérielles ou logicielles différentes (32/64 bits, little/big endian, version de bibliothèque C, Windows vers Linux, etc.).

Réplication logique :

La réplication logique propose une solution à tous ces problèmes.

La réplication logique est une réplication du contenu des tables. Plus précisément, elle réplique les résultats des ordres SQL exécutés sur la table publiée et l’applique sur la table cible. Les lignes insérées, modifiées et/supprimées sur le serveur d’origine sont répliquées sur la destination. La table cible peut être modifiée (index notamment), et son contenu différer de la table source.

Elle se paramètre donc table par table, et même opération par opération.

Elle est asymétrique dans le sens où il existe une seule origine des écritures pour une table. Il est possible de réaliser des réplications croisées où un ensemble de tables est répliqué du serveur 1 vers le serveur 2 et un autre ensemble de tables est répliqué du serveur 2 vers le serveur 1, ce qui est une forme limitée de multimaître. En version 16, la possibilité de filtrer l’origine d’une table permet d’éviter les boucles et d’alimenter la même table depuis deux instances différentes. Cette fonctionnalité est encore jeune et peut mener à des problèmes d’intégrité des données (voir notamment cet article de Brian Pace).

Comme la réplication physique, la réplication logique fonctionne par défaut, mais peut fonctionner en mode synchrone, si l’on accepte l’impact sur les performances en écriture.

La réplication logique peut avoir pour origine un serveur secondaire (lui-même en réplication physique) si l’origine et la destination sont sous PostgreSQL 16 ou supérieur.

La réplication logique permet de répliquer entre deux serveurs PostgreSQL de versions différentes, et ainsi de procéder à des migrations majeures.

Noter que le décodage logique permet d’écrire assez facilement des plugins de sortie alternatifs sans outil supplémentaire sur le serveur pour du Change Data Capture, ou pour récupérer les flux de données vers d’autres environnements n’ayant rien à voir avec PostgreSQL (voir plus loin).

Ce schéma suppose deux instances PostgreSQL, toutes les deux ouvertes en écriture.

Dans le cadre de la réplication logique, on ne réplique pas une instance vers une autre. On publie les modifications effectuées sur le contenu d’une table à partir d’un serveur. Ce serveur est le serveur origine, ou publieur (publisher). Sur ce serveur, on crée des « publications ».

Une publication enregistre un jeu de modifications que d’autres serveurs pourront récupérer en s’abonnant (subscription).

De ceci, il découle que :

• le serveur origine est le serveur où les écritures sur une table sont enregistrées pour publication vers d’autres serveurs ;
• les serveurs intéressés par ces enregistrements sont les serveurs destinations ;
• un serveur origine doit proposer une publication des modifications ;
• les serveurs destinations intéressés doivent s’abonner à une publication.

Dans un cluster de réplication logique, un serveur peut avoir un rôle de serveur origine ou de serveur destination. Il peut aussi avoir les deux rôles. Dans ce cas, il sera origine pour certaines tables et destinations pour d’autres. La réplication croisée sur une même table n’est possible que depuis la version 16.

NB : dans le texte qui suit, peuvent être utilisés indifféremment les termes publieur/éditeur/origine d’une part, abonné/souscripteur/destination d’autre part, ou encore abonnement/souscription, éventuellement en anglais.

La réplication logique utilise le même canal d’informations que la réplication physique : les enregistrements des journaux de transactions. Le transfert se fait par une connexion en streaming (ce n’est pas possible en log shipping). Pour que les journaux disposent de suffisamment d’informations, le paramètre wal_level doit être configuré avec la valeur logical.

Une fois cette configuration effectuée et PostgreSQL redémarré sur le serveur origine, le serveur destination peut se connecter au serveur origine dans le cadre de la réplication. Lorsque cette connexion est faite, un processus wal sender apparaît sur le serveur origine. Ce processus est en communication avec un processus logical replication worker sur le serveur destination.

Comme la réplication physique, la réplication logique peut être configurée en asynchrone comme en synchrone, suivant le même paramétrage (synchronous_commit, synchronous_standby_names). Rappelons que la réplication synchrone a un impact important sur les performances en écritures.

Chaque abonné maintient un slot de réplication sur l’instance de l’éditeur. Par défaut, il est créé et supprimé automatiquement avec la souscription. L’utilisation de slots est obligatoire (contrairement à la réplication physique). La copie initiale des données crée également des slots de réplication temporaires.

Contrairement à ce qui se passe en réplication physique, l’intégralité des journaux n’est pas transmise. Le walsender procède à un « décodage logique » des journaux, que la documentation définit ainsi : Le décodage logique correspond au processus d’extraction de tous les changements persistants sur des tables d’une base de données dans un format cohérent et simple à comprendre, qui peut être interprété sans une connaissance détaillée de l’état interne de la base de données.

Dans PostgreSQL, le décodage logique est implémenté en décodant le contenu des journaux de transaction (WAL), qui décrivent les changements au niveau stockage, dans un format spécifique tel qu’un flux de lignes ou des ordres SQL.

Le décodage logique permet de n’envoyer aux abonnés que les informations qui concernent la table qu’ils ont demandée. Cela permet aussi de n’envoyer que les transactions validées lors du COMMIT (du moins dans les cas simples). Cette conversion est hélas un peu gourmande en processeur.

En face, si l’abonné est une instance PostgreSQL, le rôle du logical replication worker est de retranscrire les modifications reçues dans les tables concernées.

La réplication logique propose une granularité à la table près, voire même un niveau en dessous. Une publication se crée en indiquant la table pour laquelle on souhaite publier les modifications. On peut en indiquer plusieurs. On peut en ajouter en modifiant la publication. Cependant, une nouvelle table ne sera pas ajoutée automatiquement à la publication, sauf dans deux cas précis : la publication a été créée en demandant la publication de toutes les tables (clause FOR ALL TABLES) ou, à partir de la version 15, la publication a été créée en demandant la publication de toutes les tables d’un schéma (clause FOR TABLES IN SCHEMA).

Il est possible d’ajouter une table partitionnée à une publication. Cette fonctionnalité permet de laisser à PostgreSQL le soin d’ajouter et maintenir à jour la liste des partitions dans la publication. Il est également possible de faire en sorte que les modifications soient publiées avec le nom de la partition finale ou celui de la table partitionnée. Cela permet plus de flexibilité en autorisant de répliquer des données entre des tables avec des structures hétérogènes (partitionnées ou non). Dans le cas d’une réplication depuis une table partitionnée vers une autre table partitionnée, l’agencement des partitions peut être différent.

À partir de la version 15, la granularité est encore plus basse : il est possible de ne répliquer que certaines colonnes et que certaines lignes.

La granularité peut aussi se voir au niveau des opérations de modification réalisées. On peut très bien ne publier que les opérations d’insertion, de modification ou de suppression. Par défaut, tout est publié.

La réplication logique entre deux instances PostgreSQL permet plusieurs choses impensables en réplication physique. Les cas d’utilisation sont en fait très différents.

On peut rajouter ou supprimer des index sur la table répliquée.

Il est possible de modifier des valeurs dans la table répliquée, sur le serveur abonné. Ces modifications sont susceptibles d’être écrasées par des modifications de la table source sur les mêmes lignes. Il est aussi possible de perdre la synchronisation entre les tables, notamment si on modifie la clé primaire sur l’abonné.

Les triggers ne se déclenchent par défaut que sur la base d’origine. On peut activer ainsi un trigger sur la table répliquée :

ALTER TABLE matable ENABLE REPLICA TRIGGER nom_trigger ;

Tout cela est parfois très pratique, mais peut poser de sérieux problème de cohérence de données entre les deux instances si l’on ne fait pas attention.

La vue pg_stat_subscription_stats (à partir de PostgreSQL 15) permet de suivre les erreurs de réplication sur la destination :

\d pg_stat_subscription_stats
                 View "pg_catalog.pg_stat_subscription_stats"
      Column       |           Type           | Collation | Nullable | …
-------------------+--------------------------+-----------+----------+--
 subid             | oid                      |           |          |
 subname           | name                     |           |          |
 apply_error_count | bigint                   |           |          |
 sync_error_count  | bigint                   |           |          |
 stats_reset       | timestamp with time zone |           |          |

Il faudra tout de même aller chercher le détail dans les traces. La réinitialisation se fait avec la fonction pg_stat_reset_subscription_stats(), avec l’OID de la souscription, ou NULL pour tout réinitialiser.

En cas de blocage, à cause d’une colonne absente, d’un doublon, d’une autre contrainte sur la cible ou pour une autre raison, il faut corriger sur la destination, puis laisser le stock de données bloquées s’insérer avant de pouvoir faire autre chose. L’alternative est de désactiver ou reconstruire la réplication, ce qui peut être long et poser des problèmes de réconciliation de données.

Il existe quelques cas surprenants. Par exemple, une colonne remplie grâce à une valeur DEFAULT sur l’origine sera répliquée à l’identique sur la destination ; mais une colonne calculée (clause GENERATED avec expression) sera calculée indépendamment sur l’origine et sur la destination, éventuellement différemment.

Il est possible de créer une publication sur une table elle-même répliquée. La sécurité pour éviter des boucles n’a été ajoutée qu’avec PostgreSQL 16.

La réplication logique n’a pas que des atouts, elle a aussi ses propres limitations.

DDL et DML :

La première, et plus importante, est qu’elle ne réplique que les changements de données des tables (commandes DML), et pas de la définition des objets de la base de données (commandes DDL). Une exception a été faite à partir de la version 11 pour répliquer les ordres TRUNCATE car, même s’il s’agit d’un ordre DDL d’après le standard, cet ordre modifie les données d’une table.

L’ajout (ou la suppression) d’une colonne ne sera pas répliqué, causant de ce fait un problème de réplication quand l’utilisateur y ajoutera des données. La mise à jour sera bloquée jusqu’à ce que les tables abonnées soient aussi mises à jour. Pour éviter le blocage, il est préférable de commencer une opération d’ajout de colonne sur l’abonné, et une opération de suppression de colonne sur le publieur.

Une table nouvellement créée ne sera pas non plus automatiquement répliquée.

Les tables partitionnées, sur la source ou l’origine, ne sont bien gérées qu’à partir de PostgreSQL 13.

Il n’y a pas de réplication des valeurs des séquences. Les valeurs des séquences sur les serveurs de destination seront donc obsolètes.

Identitification des lignes:

Et en cas de désynchronisation, la présence de clés primaires facilitera beaucoup la détection des lignes manquantes.

Sinon, il faut définir une clause ̀REPLICA IDENTITY sur la table origine. Utiliser un index unique peut convenir :

ALTER TABLE nomtable REPLICA IDENTITY USING INDEX nomtable_col_idx ;

Si vraiment on n’a pas le choix, on peut définir que l’ensemble des champs de la ligne servira à la correspondance :

ALTER TABLE nomtable REPLICA IDENTITY FULL ;

Faute d’index, les mises à jour effectuent des Seq Scan sur la table de destination, ce qui généralement catastrophique pour les performances. Avec PostgreSQL 16 ou supérieur, un simple index B-tree ou hash sur la table destination peut permettre d’éviter ces Seq Scan.

Large Objects :

Les Large Objects (pour le stockage de gros binaires) étant stockés dans une table système, ils ne sont pas pris en compte par la réplication logique. Il est préférable de passer par le type bytea pour les données binaires.

Ressources :

La réplication logique a un coût en CPU (sur les deux instances concernées) relativement important : attention aux petites configurations. Il y a également un coût en RAM et disque (voir plus bas).

Latence :

La réplication n’est par défaut déclenchée que lors du COMMIT sur le primaire, nous verrons que cela peut être optimisé à partir de PostgreSQL 14.

Bascule :

La situation peut devenir compliquée lors d’une restauration ou bascule d’un des serveurs impliqués, surtout avant PostgreSQL 17 (voir plus bas).

Migration majeure :

Il est délicat de faire une mise à jour majeure quand de la réplication logique est en place (sur le publieur comme l’abonné).

• pg_dump/pg_restore perd complètement les origines de réplication, et il faudra resynchroniser manuellement ;
• pg_upgrade sait migrer un serveur sans casser la réplication logique, mais seulement si le serveur à migrer est déjà en version 17 au moins (voir la documentation pour le publieur ou l’abonné).

Nous allons voir les étapes de configuration dans le cas simple d’une publication origine alimentant un abonnement destination.

En réplication logique comme physique, c’est toujours le serveur destination qui se connecte au serveur origine. Pour la réplication physique, on utilise plutôt les termes de serveur primaire et de serveur secondaire mais c’est toujours du secondaire vers le primaire, ou de l’abonné vers l’éditeur en réplication logique.

Tout comme pour la réplication physique, il est nécessaire de disposer d’un utilisateur PostgreSQL capable de se connecter au serveur origine et capable d’initier une connexion de réplication. Voici la commande pour créer ce rôle :

CREATE ROLE logrepli LOGIN REPLICATION;

Cet utilisateur doit pouvoir lire le contenu des tables répliquées. Il lui faut donc le droit SELECT sur ces objets, souvent simplement ceci :

GRANT SELECT ON ALL TABLES IN SCHEMA public TO logrepli;

Enfin, la connexion du serveur destination doit être possible sur le serveur origine. Il est donc nécessaire d’avoir une ligne du style :

host base_publication   logrepli XXX.XXX.XXX.XXX/XX scram-sha-256

en remplaçant XXX.XXX.XXX.XXX/XX par l’adresse CIDR du serveur destination. La méthode d’authentification peut aussi être changée suivant la politique interne. Suivant la méthode d’authentification, il sera nécessaire ou pas de configurer un mot de passe pour cet utilisateur. Ne pas oublier de recharger la configuration.

Niveau de journalisation :

Les journaux de transactions doivent disposer de suffisamment d’informations pour que le wal sender puisse envoyer les bonnes informations au logical replication worker. Le fichier postgresql.conf doit donc contenir :

wal_level = logical

Le défaut est replica, et il faudra donc sans doute redémarrer l’instance origine.

Paramétrage mémoire :

La réplication logique, contrairement à la réplication physique, n’est déclenchée que lors d’un COMMIT (voir cet article). Par défaut, il n’y a pas d’envoi des données tant que la transaction est en cours, ce qui d’ailleurs peut ajouter beaucoup de délai de réplication pour les transactions longues.

Le paramètre logical_decoding_work_mem contrôle la quantité de mémoire allouée à un processus walsender pour conserver les modifications en mémoire. Au-delà, elles sont stockées sur le disque, ou parfois envoyées tout de suite au client (voir plus bas).

logical_decoding_work_mem vaut par défaut 64 Mo. Il peut être réduit pour baisser l’utilisation de la mémoire des walsender, ou augmenté pour réduire les écritures sur le disque.

Sur le serveur destination, il n’y a pas de configuration à réaliser dans les fichiers postgresql.conf et pg_hba.conf.

Ensuite, il faut récupérer la définition des objets répliqués pour les créer sur le serveur de destination. Un moyen simple est d’utiliser pg_dump et d’envoyer le résultat directement à psql pour restaurer immédiatement les objets. Cela se fait ainsi :

pg_dump -h origine --schema-only base | psql base

Il est aussi possible de sauvegarder la définition d’une seule table en ajoutant l’option -t suivi du nom de la table pour avoir son script, ou d’utiliser la clause --filter de pg_dump (à partir de PostgreSQL 17). On peut aussi récupérer le SQL généré, le modifier, et l’appliquer manuellement.

Il est conseillé de déclarer l’objet sur la destination avec la même définition que sur l’origine, mais ce n’est pas obligatoire, tant que les mises à jour arrivent à se faire. Les index peuvent différer, des types être plus laxistes, des colonnes supplémentaires, générées ou pas, être ajoutées.

Une fois que les tables sont définies des deux côtés (origine et destination), il faut créer une publication sur le serveur origine. Cette publication indiquera à PostgreSQL les tables répliquées et les opérations concernées.

La clause FOR ALL TABLES permet de répliquer toutes les tables de la base, sans avoir à les nommer spécifiquement. De plus, toute nouvelle table sera répliquée automatiquement dès sa création.

À partir de la version 15, la clause FOR TABLES IN SCHEMA permet de répliquer toutes les tables du schéma indiqué sans avoir à nommer les tables spécifiquement. De plus, toute nouvelle table de ce schéma sera répliquée automatiquement dès sa création. (Il faudra tout de même rafraîchir l’abonnement sur le destinataire).

Si on ne souhaite répliquer qu’un sous-ensemble, il faut spécifier toutes les tables à répliquer en utilisant la clause FOR TABLE et en séparant les noms des tables par des virgules.

Depuis la version 15, il est possible de ne répliquer que certaines colonnes d’une table, par exemple ainsi :

CREATE PUBLICATION pub1
  FOR TABLE t1 (c1, c3);

Toujours depuis cette version, il est possible de ne répliquer que les lignes validant une certaine expression. Par exemple :

CREATE PUBLICATION pub_brest
  FOR TABLE employes WHERE (ville='Brest');

Par défaut, une table est répliquée intégralement, donc toutes les colonnes et toutes les lignes.

La clause FOR TABLES n’est pas obligatoire, la publication peut être vide au départ.

Cette publication est concernée par défaut par toutes les opérations d’écriture (INSERT, UPDATE, DELETE, TRUNCATE). Cependant, il est possible de préciser les opérations si on ne les souhaite pas toutes. Pour cela, il faut utiliser le paramètre de publication publish en utilisant les valeurs insert, update, delete et/ou truncate et en les séparant par des virgules si on en indique plusieurs.

Lorsque l’on publie les modifications sur une table partitionnée, PostgreSQL utilise par défaut le nom de la partition finale. Il est possible de lui demander d’utiliser le nom de la table partitionnée grâce à l’option publish_via_partition_root = true. Cela permet de répliquer d’une table partitionnée vers une table normale ou une table partitionnée avec un agencement de partitions différent.

Une fois la publication créée, le serveur destination doit s’y abonner. Il doit pour cela indiquer sur quel serveur se connecter et à quelle publication souscrire.

Chaîne de connexion :

Le serveur s’indique avec la chaîne infos_connexion, dont la syntaxe est la syntaxe habituelle des chaînes de connexion avec host, port, user, password, dbname, etc.

Droits :

Pour créer ou modifier la souscription, il faut être superutilisateur, ou posséder le rôle pg_create_subscription (à partir de PostgreSQL 16). Dans ce dernier cas, il faut aussi le droit CREATE sur la base.

Il y a une subtilité pour le mot de passe : il doit être présent dans la chaîne de connexion (password = motdepasse), ce qui impose une méthode de connexion avec mot de passe (par exemple scram-sha-256 mais pas peer ou ldap). Le superutilisateur n’a pas cette contrainte.

Une alternative est de créer la souscription en tant que superutilisateur en désactivant le mot de passe avant de changer le propriétaire :

ALTER SUBSCRIPTION abonnement SET (password_required = false) ;
ALTER SUBSCRIPTION abonnement OWNER TO erpadmin ;

Sans cela, PostgreSQL n’empêchera pas de transférer la propriété d’une souscription à un non-superutilisateur ; mais l’obligation du mot de passe risque de poser divers problèmes lors des modifications de la souscription.

Bien sûr, les accès aux tables répliquées ne nécessitent aucun droit sur les souscriptions même.

Autres paramètres :

Le champ nom_publication doit être remplacé par le nom de la publication créée précédemment sur le serveur origine.

Les paramètres de souscription sont détaillés ci-dessous.

Les options de souscription sont assez nombreuses et permettent de créer une souscription pour des cas particuliers. (Pour les détails, voir la documentation officielle.)

Par exemple, si le serveur destination possède déjà les données du serveur origine, il faut placer le paramètre copy_data à la valeur false dans la clause WITH de CREATE SUBSCRIPTION.

enabled = false permet de mettre en place la souscription sans la démarrer.

streaming :

Ce paramètre est très important pour les performances.

Par défaut, le walsender de l’origine attend le COMMIT, et aussi d’avoir décodé toute la transaction, avant de l’envoyer aux abonnés. De grosses transactions peuvent alors entraîner de la consommation mémoire (jusque logical_decoding_work_mem), puis l’apparition d’énormes fichiers temporaires dans le répertoire pg_replslot du serveur d’origine.

Depuis la version 14, le client peut demander l’envoi des données au fil de l’eau, sans attendre le COMMIT, dès que logical_decoding_work_mem est atteint. Le serveur destination stocke alors lui-même les données dans un fichier temporaire, et ne les rejoue qu’à réception du COMMIT.

Cette fonctionnalité doit s’activer explicitement avec le paramètre streaming au niveau de la souscription depuis le client :

CREATE SUBSCRIPTION sub_stream
  CONNECTION 'connection string'
  PUBLICATION pub WITH (streaming = on);

ou :

ALTER SUBSCRIPTION sub_stream SET (streaming = on);

Depuis PostgreSQL 16, on peut même paralléliser :

ALTER SUBSCRIPTION sub_stream SET (streaming = parallel);

Dans ce cas plusieurs workers apparaissent pour appliquer les modifications en parallèle. S’il n’est pas possible de créer les workers car il y a déjà trop de background workers, on revient en pratique au fonctionnement de streaming = on.)

binary

Activer le mode binaire est potentiellement plus rapide mais dépend beaucoup des types employés, qui ne peuvent pas tous être convertis.

Pour rendre la mise en place plus concrète, voici trois exemples de mise en place de la réplication logique. On commence par une réplication complète d’une base, qui permettrait notamment de faire une montée de version. On continue avec une réplication partielle, ne prenant en compte que 2 des 3 tables de la base. Et on finit par une réplication croisée sur la table partitionnée.

Voici le schéma de la base d’exemple, b1 :

CREATE TABLE t1 (id_t1 serial, label_t1 text);
CREATE TABLE t2 (id_t2 serial, label_t2 text);

CREATE TABLE t3 (id_t3 serial, label_t3 text, clepartition_t3 integer)
  PARTITION BY LIST (clepartition_t3);

CREATE TABLE t3_1 PARTITION OF t3 FOR VALUES IN (1);
CREATE TABLE t3_2 PARTITION OF t3 FOR VALUES IN (2);
CREATE TABLE t3_3 PARTITION OF t3 FOR VALUES IN (3);

INSERT INTO t1 SELECT i, 't1, ligne '||i FROM generate_series(1, 100) i;
INSERT INTO t2 SELECT i, 't2, ligne '||i FROM generate_series(1, 1000) i;
INSERT INTO t3 SELECT i, 't3, ligne '||i, 1 FROM generate_series(  1, 100) i;
INSERT INTO t3 SELECT i, 't3, ligne '||i, 2 FROM generate_series(101, 300) i;
INSERT INTO t3 SELECT i, 't3, ligne '||i, 3 FROM generate_series(301, 600) i;

ALTER TABLE t1 ADD PRIMARY KEY(id_t1);
ALTER TABLE t2 ADD PRIMARY KEY(id_t2);
ALTER TABLE t3 ADD PRIMARY KEY(id_t3, clepartition_t3);

Pour ce premier exemple, nous allons détailler les quatre étapes nécessaires.

La configuration du serveur d’origine commence par la création du rôle de réplication. On lui donne ensuite les droits sur toutes les tables. Ici, la commande ne s’occupe que des tables du schéma public, étant donné que nous n’avons que ce schéma. Dans le cas où la base dispose d’autres schémas, il serait nécessaire d’ajouter les ordres SQL pour ces schémas.

Les fichiers postgresql.conf et pg_hba.conf sont modifiés pour y ajouter la configuration nécessaire.

Comme dit précédemment, les fichiers postgresql.conf et pg_hba.conf sont modifiés pour y ajouter la configuration nécessaire. Le serveur PostgreSQL du serveur d’origine est alors redémarré pour qu’il prenne en compte cette nouvelle configuration.

La méthode d’authentification trust ne devrait jamais être utilisée en production, elle n’est là que pour la démonstration. La méthode recommandée est l’utilisation d’un mot de passe fort pour logrepli, stocké dans le fichier .pgpass de postgres sur le serveur destinataire (comme en réplication physique d’ailleurs).

Pour cet exemple, nous ne devrions configurer que le serveur s2 mais tant qu’à y être, autant le faire pour les quatre serveurs destinations.

La configuration consiste en la création de l’utilisateur de réplication (utile seulement pour s4). Puis, nous utilisons pg_dump pour récupérer la définition de tous les objets grâce à l’option -s (ou --schema-only). Ces ordres SQL sont passés à psql pour qu’il les intègre dans la base b1 du serveur s2.

On utilise la clause ALL TABLES pour une réplication complète d’une base.

Maintenant que le serveur s1 est capable de publier les informations de réplication, le serveur intéressé doit s’y abonner. Lors de la création de la souscription, il doit préciser comment se connecter au serveur origine et le nom de la publication.

La création de la souscription ajoute immédiatement un slot de réplication sur le serveur origine.

Par défaut, les données initiales de la table t1 sont immédiatement envoyées du serveur s1 vers le serveur s2.

Toute opération d’écriture sur la table t1 du serveur s1 doit être répliquée sur le serveur s2.

Sur le serveur s1 :

INSERT INTO t1 VALUES (101, 't1, ligne 101');
UPDATE t1 SET label_t1=upper(label_t1) WHERE id_t1=10;
DELETE FROM t1 WHERE id_t1=11;
SELECT * FROM t1 WHERE id_t1 IN (101, 10, 11);

 id_t1 |   label_t1
-------+---------------
   101 | t1, ligne 101
    10 | T1, LIGNE 10
(2 rows)

Sur le serveur s2 :

SELECT count(*) FROM t1;

 count
-------
   100

SELECT * FROM t1 WHERE id_t1 IN (101, 10, 11);

 id_t1 |   label_t1
-------+---------------
   101 | t1, ligne 101
    10 | T1, LIGNE 10
(2 rows)

La mise en place d’une réplication partielle est identique à la mise en place d’une réplication complète à une exception près : la publication doit mentionner la liste des tables à répliquer. Chaque nom de table est séparé par une virgule.

Mise en place :

Cela donne donc dans notre exemple :

CREATE PUBLICATION publi_partielle
  FOR TABLE t1,t2;

Il ne reste plus qu’à souscrire à cette publication à partir du serveur s3 avec la requête indiquée.

Vérification :

Sur s3, nous n’avons que les données des deux tables répliquées :

SELECT count(*) FROM t1;

 count
-------
   100

SELECT count(*) FROM t2;

 count
-------
  1000

SELECT count(*) FROM t3;

 count
-------
     0

À noter que nous avons déjà les données précédemment modifiées :

SELECT * FROM t1 WHERE id_t1 IN (101, 10, 11);

 id_t1 |   label_t1
-------+---------------
   101 | t1, ligne 101
    10 | T1, LIGNE 10

Maintenant, ajoutons une ligne dans chaque table de s1 :

INSERT INTO t1 VALUES (102, 't1, ligne 102');
INSERT INTO t2 VALUES (1001, 't2, ligne 1002');
INSERT INTO t3 VALUES (-1, 't3, cle 1, ligne -1', 1);

Et vérifions qu’elles apparaissent bien sur s3 pour t1 et t2, mais pas pour t3 :

SELECT * FROM t1 WHERE id_t1=102;

 id_t1 |   label_t1
-------+---------------
   102 | t1, ligne 102

SELECT * FROM t2 WHERE id_t2=1001;

 id_t2 |    label_t2
-------+----------------
  1001 | t2, ligne 1002

SELECT * FROM t3 WHERE id_t3 < 0;

 id_t3 | label_t3 | clepartition_t3
-------+----------+-----------------
(0 rows)

La réplication logique ne permet pas pour l’instant de faire du multi-maîtres pour une même table. Cependant, il est tout à fait possible de croiser les réplications, c’est-à-dire de répliquer un ensemble de tables de serveur s1 (origine) vers s4 (destination), de répliquer un autre ensemble en sens inverse, du serveur s4 vers s1.

Pour rendre cela encore plus intéressant, nous allons utiliser la table t3 et ses partitions. Le but est de pouvoir écrire dans la partition t3_1 sur s1 et dans la partition t3_2 sur s4, simulant ainsi une table où il sera possible d’écrire sur les deux serveurs à condition de respecter la clé de partitionnement.

Pour le mettre en place, nous allons travailler en deux temps :

• nous allons commencer par mettre en réplication t3_1 ;
• et nous finirons en mettant en réplication t3_2.

Rien de bien nouveau ici, il s’agit d’une réplication partielle. On commence par créer la publication sur le serveur s1 et on souscrit à cette publication sur le serveur s4.

Cependant, le serveur s4 n’est plus seulement un serveur destination, il devient aussi un serveur origine. Il est donc nécessaire de le configurer pour ce nouveau rôle. Cela passe par une configuration similaire et symétrique à celle vue pour s1 :

• Fichier postgresql.conf :

wal_level = logical

(Si ce n’était pas déjà fait, il faudra redémarrer l’instance PostgreSQL sur s4).

• Fichier pg_hba.conf :

host all logrepli 192.168.10.0/24 trust

(Ne pas oublier de recharger la configuration.)

Là-aussi, rien de bien nouveau. On crée la publication sur le serveur s4 et on souscrit à cette publication sur le serveur s1.

Sur s1 :

SELECT * FROM t3 WHERE id_t3 > 999;

 id_t3 | label_t3 | clepartition_t3
-------+----------+-----------------
(0 rows)

INSERT INTO t3 VALUES (1001, 't3, ligne 1001', 1);
SELECT * FROM t3 WHERE id_t3>999;

 id_t3 |    label_t3    | clepartition_t3
-------+----------------+-----------------
  1001 | t3, ligne 1001 |               1

Sur s4 :

SELECT * FROM t3 WHERE id_t3 > 999;

 id_t3 |    label_t3    | clepartition_t3
-------+----------------+-----------------
  1001 | t3, ligne 1001 |               1

INSERT INTO t3 VALUES (1002, 't3, ligne 1002', 2);
SELECT * FROM t3 WHERE id_t3 > 999;

 id_t3 |    label_t3    | clepartition_t3
-------+----------------+-----------------
  1001 | t3, ligne 1001 |               1
  1002 | t3, ligne 1002 |               2

Sur s1 :

SELECT * FROM t3 WHERE id_t3>999;

 id_t3 |    label_t3    | clepartition_t3
-------+----------------+-----------------
  1001 | t3, ligne 1001 |               1
  1002 | t3, ligne 1002 |               2
(2 rows)

Principe :

Cet outil en ligne de commande apparu avec PostgreSQL 17 facilite la création d’une grosse instance en réplication logique. Le principe est de partir d’un serveur secondaire, donc en réplication physique, pour le transformer en instance indépendante et en réplication logique depuis son ancien primaire.

Un secondaire physique est facile et rapide à créer, par exemple avec pg_basebackup, ou en restaurant une sauvegarde physique. Une fois la connexion en streaming en place, la copie des données du primaire est complète jusqu’à un point connu dans le flux de journaux. La rapidité de l’outil permet aussi d’éviter d’avoir à réappliquer la masse des modifications survenues pendant la première synchronisation logique des données. Celle-ci pourrait durer des heures, voire des jours et pendant ce temps les slots de réplication logique sur le serveur source sont bloqués, et peuvent accumuler de gros volumes de données.

Opérations :

Concrètement, une fois que le secondaire physique en place et a rattrapé son retard sur le primaire, il faut l’arrêter, et lancer pg_createsubscriber. Celui-ci a besoin de la connexion au primaire et du chemin de postgresql.conf (s’il n’est pas dans le PGDATA de la cible), et éventuellement de la liste des bases à répliquer logiquement (par défaut, toutes), ou des noms des slots, abonnements et souscriptions. Après quelques vérifications, l’outil crée une réplication logique par base, donc à chaque fois une publication sur le primaire et une souscription sur le secondaire. Il désactive la réplication physique et change l’identifiant système de l’instance : on obtient donc bien deux instances indépendantes. Le point où la réplication physique s’est arrêtée dans le flux de journaux devient le point de départ de chaque réplication logique, grâce à la fonction pg_replication_origin_advance().

Bien sûr, tous les droits et paramétrages nécessaires aux réplications physique et logique doivent avoir été mis en place. Notamment, les nombres de slots et workers sur la source et la cible doivent être montés au moins au nombre de bases de données concernées, sans oublier les workers nécessaires à l’activité habituelle de la source. Il faut éviter de créer des ordres DDL (non répliqués) pendant l’opération.

Après conversion, l’instance est totalement répliquée logiquement, et maintient la synchronisation. Si on ne veut pas tout répliquer, il est facile et rapide de supprimer les tables en trop.

Pour le détail des vérifications, paramétrages et du fonctionnement, voir pg_createsubscriber dans la documentation officielle.

Cas d’utilisation :

Pour une petite volumétrie, ou pour répliquer une petite partie d’une grande instance, ou encore si l’instance cible existe déjà, la technique classique est plus recommandée.

Une limitation majeure de pg_createsubscriber est que les instances source et la cible, et l’outil lui-même, doivent avoir la même version majeure, et être au moins en version 17 de PostgreSQL.

L’outil pourrait servir pour des migrations majeures de PostgreSQL 17 vers des versions supérieures (voir plus bas).

Dans cette partie, nous allons tout d’abord voir les changements de la réplication logique au niveau du système d’exploitation, et tout particulièrement au niveau des processus et des fichiers.

Ensuite, nous regarderons quelques procédures importantes d’administration et de maintenance.

Tout comme il existe un processus wal sender communiquant avec un processus wal receiver dans le cadre de la réplication physique, il y a aussi deux processus discutant ensemble dans le cadre de la réplication logique.

Le logical replication launcher est toujours exécuté. Ce processus a pour but de demander le lancement d’un logical replication apply worker lors de la création d’une souscription. Ce worker se connecte au serveur origine et applique toutes les modifications dont s1 lui fait part. Si la connexion se passe bien, un processus wal sender est ajouté sur le serveur origine pour communiquer avec le worker sur le serveur destination.

Sur notre serveur s2, destinataire pour la publication complète du serveur s1, nous avons les processus suivant :

postmaster -D /opt/postgresql/datas/s2
postgres: checkpointer process
postgres: writer process
postgres: wal writer process
postgres: autovacuum launcher process
postgres: bgworker: logical replication launcher
postgres: bgworker: logical replication apply worker for subscription 16445

Le serveur s1 est origine de trois publications (d’où les 3 wal sender) et destinataire d’une souscription (d’où le seul logical replication apply worker). Il a donc les processus suivants :

postmaster -D /opt/postgresql/datas/s1
postgres: checkpointer process
postgres: writer process
postgres: wal writer process
postgres: autovacuum launcher process
postgres: bgworker: logical replication launcher
postgres: bgworker: logical replication apply worker for subscription 16573
postgres: wal sender process logrepli [local] idle
postgres: wal sender process logrepli [local] idle
postgres: wal sender process logrepli [local] idle

À part les deux premiers, ces paramètres ont la même utilité que pour une réplication physique. Les valeurs par défaut sont généralement suffisantes, mais doivent parfois être augmentées.

Exception : max_slot_wal_keep_size doit être mis en place à une valeur élevée pour qu’un slot de réplication très en retard ou oublié ne sature le répertoire pg_wal du serveur origine.

max_logical_replication_workers spécifie le nombre maximal de workers de réplication logique (leader, parallel, de synchronisation). Ils sont pris dans la réserve définie par max_worker_processes.

Si les valeurs sont trop basses, les réplications seront bloquées. Il faudra augmenter ces valeurs et redémarrer le serveur.

La réplication logique maintient des données dans deux répertoires : pg_replslot et pg_logical.

pg_replslot contient un répertoire par slot de réplication physique ou logique. On y trouvera aussi des slots temporaires lors de l’initialisation de la réplication logique.

pg_replslot contient aussi le résultat du décodage logique des modifications pour chaque transaction en cours (fichiers .spill), lorsque logical_decoding_work_mem n’est pas suffisant et que l’option streaming n’est pas activée. Ce répertoire peut donc atteindre une taille importante si le serveur exécute beaucoup de transactions longues avec du volume en écriture, ou si l’abonné met du temps à répliquer les données. Il est donc important de surveiller la place prise par ce répertoire.

pg_logical contient des métadonnées et une volumétrie beaucoup plus faible.

À cela s’ajoutent les journaux de transaction conservés dans pg_wal/ en fonction de l’avancement des slots de réplication.

Sur s2, nous allons créer un utilisateur applicatif en lui donnant tous les droits sur les tables répliquées, entre autres :

CREATE ROLE u1 LOGIN;
GRANT ALL ON ALL TABLES IN SCHEMA public TO u1;

Maintenant, nous nous connectons avec cet utilisateur et vérifions s’il peut écrire dans la table répliquée :

\c b1 u1
INSERT INTO t1 VALUES (103, 't1 sur s2, ligne 103');

C’est bien le cas, contrairement à ce que l’on aurait pu croire instinctivement. Le seul moyen d’empêcher ce comportement par défaut est de lui supprimer les droits d’écriture :

\c b1 postgres
REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM u1;
\c b1 u1
INSERT INTO t1 VALUES (104);

ERROR:  permission denied for relation t1

L’utilisateur u1 ne peut plus écrire dans les tables répliquées.

Sans cette interdiction, on peut arriver à des problèmes très gênants. Par exemple, nous avons inséré dans la table t1 de s2 la valeur 103 :

SELECT * FROM t1 WHERE id_t1=103;

 id_t1 |       label_t1
-------+----------------------
   103 | t1 sur s2, ligne 103

Cette ligne n’apparaît pas sur s1 :

SELECT * FROM t1 WHERE id_t1=103;

 id_t1 | label_t1
-------+----------
(0 rows)

De ce fait, on peut l’insérer sur la table t1 de s1 :

INSERT INTO t1 VALUES (103, 't1 sur s1, ligne 103');

Et maintenant, on se trouve avec deux serveurs désynchronisés :

• sur s1 :

SELECT * FROM t1 WHERE id_t1=103;

 id_t1 |       label_t1
-------+----------------------
   103 | t1 sur s1, ligne 103
(1 row)

• sur s2 :

SELECT * FROM t1 WHERE id_t1=103;

 id_t1 |       label_t1
-------+----------------------
   103 | t1 sur s2, ligne 103
(1 row)

Notez que le contenu de la colonne label_t1 n’est pas identique sur les deux serveurs.

Ce n’est pas le seul problème : cette valeur insérée sur s1 va devoir être répliquée. Le processus de réplication logique n’arrive alors plus à appliquer les données sur s2, avec ces messages dans les traces :

LOG:  logical replication apply worker for subscription "subscr_complete" has started
ERROR:  duplicate key value violates unique constraint "t1_pkey"
DETAIL:  Key (id_t1)=(103) already exists.
LOG:  worker process: logical replication worker for subscription 16445 (PID 31113) exited with exit code 1

Il faut corriger manuellement la situation, par exemple en supprimant la ligne de t1 sur le serveur s2 :

DELETE FROM t1 WHERE id_t1=103;

SELECT * FROM t1 WHERE id_t1=103;

 id_t1 | label_t1
-------+----------
(0 rows)

Au bout d’un certain temps, le worker est relancé, et la nouvelle ligne est finalement disponible :

SELECT * FROM t1 WHERE id_t1=103;

 id_t1 |       label_t1
-------+----------------------
   103 | t1 sur s1, ligne 103
(1 row)

Dans des cas plus complexes et avec plus de données, la réconciliation des données peut devenir très complexe et chronophage.

Seules les opérations DML sont répliquées pour les tables ciblées par une publication. Toutes les opérations DDL sont ignorées, que ce soit l’ajout, la modification ou la suppression d’un objet, y compris si cet objet fait partie d’une publication.

Il est donc conseillé que toute modification de schéma soit effectuée sur toutes les instances d’un cluster de réplication. Ce n’est cependant pas requis. Il est tout à fait possible d’ajouter un index sur un serveur sans vouloir l’ajouter sur d’autres. C’est d’ailleurs une des raisons de passer à la réplication logique.

Par contre, dans le cas du changement de définition d’une table répliquée (ajout ou suppression d’une colonne, par exemple), il est nettement préférable de réaliser cette opération sur tous les serveurs intégrés dans cette réplication.

La création d’une table est une opération DDL. Elle est donc ignorée dans le contexte de la réplication logique. Si l’on veut la répliquer, il faut d’abord créer la table manuellement dans la base destinataire. Puis, plusieurs cas se présentent :

• si la publication a été définie FOR ALL TABLES, la nouvelle table sera prise en compte immédiatement ;
• si la publication a été définie FOR ALL TABLES IN SCHEMA, et que la nouvelle table est dans le bon schéma, elle sera aussi prise en compte ;
• si la publication liste les tables une à une, il va falloir l’ajouter manuellement à la publication ainsi :

ALTER PUBLICATION … ADD TABLE … ;

Dans les deux cas, sur les serveurs destinataires, il va falloir rafraîchir les souscriptions :

ALTER SUBSCRIPTION … REFRESH PUBLICATION ;

Si l’on a oublié de créer la table sur le destinataire, cela provoquera une erreur :

ERROR:  relation "public.t4" does not exist

Si la publication contient des tables partitionnées, la même commande doit être exécutée lorsque l’on ajoute ou retire des partitions à une de ces tables partitionnées.

Il est possible d’ajouter une table à une publication définie sur un schéma différent avec FOR ALL TABLES IN SCHEMA.

Exemple :

Sur le serveur origine s1, on crée la table t4, on lui donne les bons droits, et on insère des données :

CREATE TABLE t4 (id_t4 integer, PRIMARY KEY (id_t4));
GRANT SELECT ON TABLE t4 TO logrepli;
INSERT INTO t4 VALUES (1);
-- optionnel pour les publications table à table
ALTER PUBLICATION publi_partielle ADD TABLE t4 ;

Sur le serveur s2, on regarde le contenu de la table t4 :

SELECT * FROM t4;

ERROR:  relation "t4" does not exist
LINE 1: SELECT * FROM t4;
                      ^

La table n’existe pas. En effet, la réplication logique ne s’occupe que des modifications de contenu des tables, pas des changements de définition. Il est donc nécessaire de créer la table sur le serveur destination, ici s2 :

CREATE TABLE t4 (id_t4 integer, primary key (id_t4));
SELECT * FROM t4;

 id_t4
-------
(0 rows)

Elle ne contient toujours rien. Ceci est dû au fait que la souscription n’a pas connaissance de la réplication de cette nouvelle table. Il faut donc rafraîchir les informations de souscription :

ALTER SUBSCRIPTION subscr_complete REFRESH PUBLICATION;

SELECT * FROM t4;

 id_t4
-------
     1

Un publieur qui a une table avec plus de colonnes qu’un abonné posera problème à la première insertion de ligne ou modification de la colonne, et ce message apparaîtra dans les traces :

ERROR:  logical replication target relation "public.t4" is missing replicated column: "c9"

Le contraire n’est pas vrai : un abonné peut avoir une table ayant plus de colonnes que la même table sur le publieur. C’est un des intérêt de la réplication logique. Les colonnes n’ont pas non plus besoin d’être dans le même ordre sur les deux instances.

Il est donc conseillé d’ajouter la nouvelle colonne sur l’abonné en premier lieu, puis de faire la même opération sur le publieur.

Si jamais vous faites l’opération dans le sens inverse et qu’une ligne est insérée avant avoir terminé l’opération, la réplication sera en erreur jusqu’à ce que l’opération soit terminée.

Comme indiqué ci-dessus, une table peut avoir plus de colonnes sur l’abonné mais pas sur le publieur. De ce fait, pour supprimer une colonne, il convient de commencer par la supprimer sur le publieur, puis de la supprimer sur l’abonné.

Si jamais vous faites l’opération dans le sens inverse et qu’une ligne est insérée avant la fin de l’opération, la réplication sera en erreur jusqu’à ce que l’opération soit terminée.

L’ajout d’une contrainte identique sur les deux machines doit se faire d’abord sur le primaire. Sans cela, il y a une fenêtre pour que de nouvelles données violant cette contrainte soient insérées dans l’origine et bloquent la réplication.

Ajoutons que les contraintes ne sont pas obligatoirement les mêmes sur l’origine et la destination. Pour faciliter l’administration, c’est tout de même conseillé. Rappelons qu’une clé primaire ou unique est nécessaire pour repérer plus efficacement les lignes.

En cas de différence, il vaut donc mieux que les contraintes les plus strictes soient posées sur le publieur. Une ligne insérée sans problème sur l’origine et violant une contrainte sur la destination bloquera la réplication.

Voici un exemple complet de correction d’une erreur de réplication.

Commençons par mettre en place une réplication logique entre deux serveurs s1 (port 5432) et s2 (port 5433). Cette réplication prend en compte la seule table de la base tests1.

Dans la base tests1 sur le publieur (s1) :

CREATE TABLE t1(c1 integer, c2 integer);
ALTER TABLE t1 ADD PRIMARY KEY(c1);
CREATE PUBLICATION pub1 FOR ALL TABLES;

Dans la base tests1 sur l’abonné (s2) :

CREATE TABLE t1(c1 integer, c2 integer);
ALTER TABLE t1 ADD PRIMARY KEY(c1);
CREATE SUBSCRIPTION sub1 CONNECTION 'port=5432 dbname=tests1' PUBLICATION pub1;

À partir de maintenant, toute écriture sur s1 sera lisible aussi sur s2 :

tests1=# \c tests1 - - 5432
You are now connected to database "tests1" as user "postgres" via socket in "/tmp" at port "5432".
tests1=# INSERT INTO t1 VALUES (1,1), (2,2);
INSERT 0 2
tests1=# TABLE t1;
 c1 | c2
----+----
  1 |  1
  2 |  2
(2 rows)

tests1=# \c tests1 - - 5433
You are now connected to database "tests1" as user "postgres" via socket in "/tmp" at port "5433".
tests1=# TABLE t1;
 c1 | c2
----+----
  1 |  1
  2 |  2
(2 rows)

Ajoutons maintenant une contrainte sur l’abonné :

tests1=# \c tests1 - - 5433
You are now connected to database "tests1" as user "postgres".
tests1=# ALTER TABLE t1 ADD CHECK (c2<10);
ALTER TABLE

Tout ajout se passera bien, sur s1 et s2, tant que la contrainte est respectée :

tests1=# \c tests1 - - 5432
You are now connected to database "tests1" as user "postgres" via socket in "/tmp" at port "5432".
tests1=# INSERT INTO t1 VALUES (3,3), (4,4);
INSERT 0 2
tests1=# TABLE t1;
 c1 | c2
----+----
  1 |  1
  2 |  2
  3 |  3
  4 |  4
(4 rows)

tests1=# \c tests1 - - 5433
You are now connected to database "tests1" as user "postgres" via socket in "/tmp" at port "5433".
tests1=# TABLE t1;
 c1 | c2
----+----
  1 |  1
  2 |  2
  3 |  3
  4 |  4
(4 rows)

Par contre, si la contrainte n’est pas respectée, l’ajout se fera uniquement sur s1 (qui n’a pas la contrainte) :

tests1=# \c tests1 - - 5432
You are now connected to database "tests1" as user "postgres" via socket in "/tmp" at port "5432".
tests1=# INSERT INTO t1 VALUES (11,11);
INSERT 0 1
tests1=# TABLE t1;
 c1 | c2
----+----
  1 |  1
  2 |  2
  3 |  3
  4 |  4
 11 | 11
(5 rows)

tests1=# \c tests1 - - 5433
You are now connected to database "tests1" as user "postgres" via socket in "/tmp" at port "5433".
tests1=# TABLE t1;
 c1 | c2
----+----
  1 |  1
  2 |  2
  3 |  3
  4 |  4
(4 rows)

Les traces du serveur s2 nous expliquent pourquoi :

LOG:  logical replication apply worker for subscription "sub1" has started
ERROR:  new row for relation "t1" violates check constraint "t1_c2_check"
DETAIL:  Failing row contains (11, 11).
CONTEXT:  processing remote data for replication origin "pg_16390" during message type "INSERT" for replication target relation "public.t1" in transaction 748, finished at 0/1C442C8
LOG:  background worker "logical replication worker" (PID 194674) exited with exit code 1

Ce message sera répété tant que l’erreur ne sera pas corrigée.

Par exemple, ces cinq lignes sont bien insérées dans la table t1 du serveur s1 mais pas dans celle du serveur s2 :

postgres=# \c tests1 - - 5432
You are now connected to database "tests1" as user "postgres".
tests1=# INSERT INTO t1 (c1) SELECT generate_series(5, 9);
INSERT 0 5
tests1=# TABLE t1;
 c1 | c2
----+----
  1 |  1
  2 |  2
  3 |  3
  4 |  4
 11 | 11
  5 |
  6 |
  7 |
  8 |
  9 |
(10 rows)

tests1=# \c tests1 - - 5433
You are now connected to database "tests1" as user "postgres" via socket in "/tmp" at port "5433".
tests1=# TABLE t1;
 c1 | c2
----+----
  1 |  1
  2 |  2
  3 |  3
  4 |  4
(4 rows)

Supprimer la contrainte résoudra facilement et rapidement le problème… si la contrainte n’avait pas lieu d’être. Si, au contraire, cette contrainte est nécessaire, et si nous avons seulement oublié de l’ajouter sur le serveur s1, il faut pouvoir supprimer la ligne 11 sur s1, ajouter la contrainte sur s1 et reprendre la réplication sur s2.

Voyons comment faire cela. La suppression de la ligne 11 est simple. Profitons-en en plus pour récupérer l’identifiant de transaction qui a créé la ligne :

tests1=# \c tests1 - - 5432
You are now connected to database "tests1" as user "postgres" via socket in "/tmp" at port "5432".
tests1=# DELETE FROM t1 WHERE c1=11 RETURNING xmin;
 xmin
------
  748
(1 row)

DELETE 1
tests1=# TABLE t1;
 c1 | c2
----+----
  1 |  1
  2 |  2
  3 |  3
  4 |  4
  5 |
  6 |
  7 |
  8 |
  9 |
(9 rows)

tests1=# \c tests1 - - 5433
You are now connected to database "tests1" as user "postgres" via socket in "/tmp" at port "5433".
tests1=# TABLE t1;
 c1 | c2
----+----
  1 |  1
  2 |  2
  3 |  3
  4 |  4
(4 rows)

La ligne 11 est bien supprimée du serveur s1 mais ça n’a pas débloqué pour autant la situation sur le serveur s2. C’est normal. L’information d’ajout de la ligne est dans les journaux disponibles sur le serveur s2. Supprimer la ligne sur s1 ne supprime pas l’enregistrement de l’insertion préalable de cette ligne des journaux de transactions.

En attendant, ajoutons la contrainte sur s1 pour ne plus avoir de « mauvaises » données insérées puis répliquées :

tests1=# \c tests1 - - 5432
You are now connected to database "tests1" as user "postgres".
tests1=# ALTER TABLE t1 ADD CHECK (c2<10);
ALTER TABLE

Rétablissons maintenant la réplication sur le serveur s2. Nous ne voulons pas appliquer l’enregistrement qui insère la ligne 11 sur le serveur s2. (Sinon il serait possible de lever temporairement la contrainte, et laisser la ligne 11 s’insérer ; puis le DELETE ci-dessus s’appliquerait, et on pourrait remettre la contrainte en place sur s2.)

Pour cela, il faut pouvoir avancer le pointeur du prochain enregistrement à rejouer pour notre slot de réplication. Il existe une fonction dédiée : pg_replication_slot_advance(). Cette fonction prend en premier argument le nom du slot de réplication à modifier, et en deuxième argument la nouvelle position dans les journaux de transactions. Il va donc falloir trouver l’emplacement suivant dans la transaction qui a insérée cette ligne 11.

Nous savons à quel emplacement le slot est bloqué grâce à la vue pg_replication_slots et son champ confirmed_flush_lsn, qui indique le dernier enregistrement reçu, mais pas forcément appliqué :

tests1=# \c tests1 - - 5432
tests1=# SELECT confirmed_flush_lsn FROM pg_replication_slots WHERE slot_name='sub1';
 confirmed_flush_lsn
---------------------
 0/1C44248
(1 row)

Maintenant, il faut décoder les enregistrements après cet emplacement là. Auparavant, il fallait utiliser l’outil pg_walinspect. Voici ce que nous donne la fonction pg_get_wal_records_info() de cette extension pour les enregistrements allant de 0/1C40ED0 à la dernière position :

tests1=# \c tests1 - - 5432
tests1=# CREATE EXTENSION pg_walinspect;
CREATE EXTENSION
tests1=# SELECT start_lsn, xid, resource_manager, record_type, block_ref
  FROM pg_get_wal_records_info('0/1C44248', pg_current_wal_lsn()) \gx

-[ RECORD 1 ]----+-------------------------------------------------
start_lsn        | 0/1C44248
xid              | 748
resource_manager | Heap
record_type      | INSERT
block_ref        | blkref #0: rel 1663/16384/16385 fork main blk 0
-[ RECORD 2 ]----+-------------------------------------------------
start_lsn        | 0/1C44288
xid              | 748
resource_manager | Btree
record_type      | INSERT_LEAF
block_ref        | blkref #0: rel 1663/16384/16388 fork main blk 1
-[ RECORD 3 ]----+-------------------------------------------------
start_lsn        | 0/1C442C8
xid              | 748
resource_manager | Transaction
record_type      | COMMIT
block_ref        |
-[ RECORD 4 ]----+-------------------------------------------------
start_lsn        | 0/1C442F8
xid              | 0
resource_manager | Standby
record_type      | RUNNING_XACTS
block_ref        |
-[ RECORD 5 ]----+-------------------------------------------------
start_lsn        | 0/1C44330
xid              | 749
resource_manager | Heap
record_type      | INSERT
block_ref        | blkref #0: rel 1663/16384/16385 fork main blk 0
-[ RECORD 6 ]----+-------------------------------------------------
start_lsn        | 0/1C44370
xid              | 749
resource_manager | Btree
record_type      | INSERT_LEAF
block_ref        | blkref #0: rel 1663/16384/16388 fork main blk 1
-[ RECORD 7 ]----+-------------------------------------------------
start_lsn        | 0/1C443B0
xid              | 749
resource_manager | Heap
record_type      | INSERT
block_ref        | blkref #0: rel 1663/16384/16385 fork main blk 0
-[ RECORD 8 ]----+-------------------------------------------------
start_lsn        | 0/1C443F0
xid              | 749
resource_manager | Btree
record_type      | INSERT_LEAF
block_ref        | blkref #0: rel 1663/16384/16388 fork main blk 1
-[ RECORD 9 ]----+-------------------------------------------------
start_lsn        | 0/1C44430
xid              | 749
resource_manager | Heap
record_type      | INSERT
block_ref        | blkref #0: rel 1663/16384/16385 fork main blk 0
-[ RECORD 10 ]---+-------------------------------------------------
start_lsn        | 0/1C44470
xid              | 749
resource_manager | Btree
record_type      | INSERT_LEAF
block_ref        | blkref #0: rel 1663/16384/16388 fork main blk 1
-[ RECORD 11 ]---+-------------------------------------------------
start_lsn        | 0/1C444B0
xid              | 749
resource_manager | Heap
record_type      | INSERT
block_ref        | blkref #0: rel 1663/16384/16385 fork main blk 0
-[ RECORD 12 ]---+-------------------------------------------------
start_lsn        | 0/1C444F0
xid              | 749
resource_manager | Btree
record_type      | INSERT_LEAF
block_ref        | blkref #0: rel 1663/16384/16388 fork main blk 1
-[ RECORD 13 ]---+-------------------------------------------------
start_lsn        | 0/1C44530
xid              | 749
resource_manager | Heap
record_type      | INSERT
block_ref        | blkref #0: rel 1663/16384/16385 fork main blk 0
[...]

Les trois premiers enregistrements concernent la transaction 748 (colonne xid). C’est bien cette transaction qui a ajouté la ligne 11, comme nous l’indiquait le résultat de la requête DELETE ainsi que le message dans les traces de PostgreSQL indiqué plus haut.

Le premier enregistrement indique une insertion (record_type à INSERT sur la table référencée 1663/16384/16389 (colonne block_ref). Le premier numéro est l’OID du tablespace, le deuxième numéro est l’OID de la base de données et le dernier numéro est le refilenode de la table. Il se trouve que la table t1 a comme relfilenode 16389 :

tests1=# \c tests1 - - 5432
tests1=# SELECT relfilenode FROM pg_class WHERE relname='t1';
 relfilenode
-------------
       16389
(1 row)

Le deuxième enregistrement indique une écriture dans un index B-tree. Il s’agit de l’index lié à la clé primaire sur la table t1.

Enfin, le troisième enregistrement concerne la validation de la transaction. La transaction 748 s’arrête à l’emplacement 0/1C442F8. Nous devons donc avancer le slot de réplication sub1 à cet emplacement :

tests1=# \c tests1 - - 5432
tests1=# SELECT pg_replication_slot_advance('sub1', '0/1C442F8');
 pg_replication_slot_advance
-----------------------------
 (sub1,0/1C442F8)
(1 row)

tests1=# \c tests1 - - 5433
You are now connected to database "tests1" as user "postgres" via socket in "/tmp" at port "5433".
tests1=# TABLE t1;
 c1 | c2
----+----
  1 |  1
  2 |  2
  3 |  3
  4 |  4
  5 |
  6 |
  7 |
  8 |
  9 |
(9 rows)

Nous pouvons voir que la réplication a repris immédiatement et que les deux tables contiennent les mêmes données. Et on peut de nouveau ajouter des données :

tests1=# \c tests1 - - 5432
You are now connected to database "tests1" as user "postgres" via socket in "/tmp" at port "5432".
tests1=# INSERT INTO t1 VALUES (-1), (-2);
INSERT 0 2
tests1=# TABLE t1;
 c1 | c2
----+----
  1 |  1
  2 |  2
  3 |  3
  4 |  4
  5 |
  6 |
  7 |
  8 |
  9 |
 -1 |
 -2 |
(11 rows)

tests1=# \c tests1 - - 5433
You are now connected to database "tests1" as user "postgres" via socket in "/tmp" at port "5433".
tests1=# TABLE t1;
 c1 | c2
----+----
  1 |  1
  2 |  2
  3 |  3
  4 |  4
  5 |
  6 |
  7 |
  8 |
  9 |
 -1 |
 -2 |
(11 rows)

Dans la réplication physique, les opérations de maintenance ne sont réalisables que sur le serveur primaire, qui va envoyer le résultat de ces opérations aux serveurs secondaires.

Ce n’est pas le cas dans la réplication logique. Il faut bien voir les serveurs d’une réplication logique comme étant des serveurs indépendants les uns des autres.

Donc il faut configurer leur maintenance, avec les opérations VACUUM, ANALYZE, REINDEX, comme pour n’importe quel serveur PostgreSQL.

Les sauvegardes logiques incluent les publications et souscriptions. Deux options (--no-publications et --no-subscriptions) permettent de les exclure.

Par contre, les slots de réplication liées aux publications, et leur position dans le flux de transaction, ne sont pas sauvegardés. Cela peut poser problème pour une restauration sans perte.

Après une restauration, il faudra soigneusement vérifier dans les traces que les réplications logiques ont repris leur fonctionnement, et qu’il n’y a pas de perte dans les données transmises.

Restauration d’une publication

Voici l’ordre SQL exécuté pour la restauration d’une publication complète :

CREATE PUBLICATION publi_complete FOR ALL TABLES
  WITH (publish = 'insert, update, delete');

Et ceux correspondant à la restauration d’une publication partielle :

CREATE PUBLICATION publi_partielle
  WITH (publish = 'insert, update, delete');
ALTER PUBLICATION publi_partielle ADD TABLE ONLY t1;

Si le slot manque, le recréer sur l’instance d’origine est possible :

SELECT pg_create_logical_replication_slot ('abonnement','pgoutput') ;

Ces opérations sont obligatoirement manuelles. De toute façon, il faudra se poser la question de la resynchronisation des données. Généralement, l’origine aura été restaurée dans un état antérieur à celui déjà répliqué : les données répliquées à présent absentes de l’origine sont-elles toutes à conserver ? Comment gérer les clés primaires qui vont souvent entrer en conflit ?

Restauration d’une souscription

Pour une souscription, l’ordre SQL dans la sauvegarde est :

CREATE SUBSCRIPTION subscr_t3_2
  CONNECTION 'port=5444 user=logrepli dbname=b1'
  PUBLICATION publi_t3_2
  WITH (connect = false, slot_name = 'subscr_t3_2');

Contrairement à l’ordre exécuté manuellement à la création, celui-ci précise le nom du slot de réplication (au cas où il aurait été personnalisé) et désactive la connexion immédiate. Cette désactivation a pour effet de désactiver la souscription, de ne pas créer le slot de réplication et d’empêcher la copie initiale des données (dont nous n’avons pas besoin étant donné que nous les avons dans la sauvegarde, au moins en partie).

Une fois la sauvegarde restaurée et les vérifications nécessaires effectuées, il est possible d’activer la souscription et de la rafraîchir :

ALTER SUBSCRIPTION subscr_complete ENABLE ;
ALTER SUBSCRIPTION subscr_complete REFRESH PUBLICATION ;

Ces opérations sont obligatoirement manuelles.

ALTER SUBSCRIPTION nom_abonnement REFRESH PUBLICATION
WITH (copy_data = false) ;

Pendant ces opérations, il est fortement conseillé d’interdire les écritures dans les tables répliquées pour avoir une vision claire de ce qui a été répliqué et ne l’a pas été. Les slots doivent souvent être reconstruits, et il faut éviter que les tables soient modifiées entre-temps.

Restauration de l’instance d’origine :

Cela dépend de la méthode de sauvegarde/restauration utilisée, mais la restauration du serveur origine ne conserve généralement pas les slots de réplication (qui sont périmés de toute façon).

Il faudra recréer les slots, peut-être recréer les souscriptions, et pendant ce temps des trous dans les données répliquées peuvent apparaître, qu’il faudra vérifier ou corriger manuellement.

Bascule de l’instance d’origine :

Ici, l’instance d’origine est arrêtée et un de ses secondaires est promu comme nouveau serveur principal. Les slots de réplication étant propres à une instance, il ne seront pas disponibles immédiatement sur la nouvelle origine. Il faudra aussi reparamétrer la connexion des abonnements.

Il y a donc à nouveau un risque sérieux de perdre au moins quelques données répliquées.

Restauration de l’instance de destination :

Un slot de réplication sur l’origine garantit seulement que les journaux seront toujours disponibles pendant une indisponibilité du souscripteur. Ils ne permettent pas de revenir sur des données déjà répliquées.

En redémarrant, les abonnements vont tenter de se raccrocher au slot de réplication de l’origine, ce qui fonctionnera, mais ils ne recevront que des données jamais répliquées. Là encore des « trous » dans les données répliquées peuvent apparaître si l’instance destination n’a pas été restaurée dans un état suffisamment récent !

Bascule de l’instance destination :

C’est le cas le plus favorable. Si la bascule s’est faite proprement sans perte entre l’ancienne destination et la nouvelle, il ne devrait pas y avoir de perte de données répliquées. Cela devra tout de même faire partie des contrôles.

Rappelons que les slots de réplication sont propres à une instance, qu’elle soit secondaire ou primaire. Le slot de réplication logique et le walsender associé sont à créer sur le serveur secondaire (origine), qui procédera au décodage logique, stockera les journaux au besoin, etc. et enverra les informations à l’instance destinataire.

Comme le secondaire est en lecture seule, il faudra continuer à créer et détruire les publications sur le primaire. Le primaire ne verra aucun slot de réplication et ne connaîtra rien de l’instance abonnée destinatrice.

Latence :

Évidemment, la réplication logique est tributaire des délais (voire pause) dans le rejeu des journaux sur le secondaire, et la latence peut en souffrir.

En cas de complète inactivité du primaire, la fonction suivante, exécutée sur le primaire, permet d’envoyer immédiatement dans les journaux le nécessaire pour une synchronisation des réplications logiques :

SELECT pg_log_standby_snapshot() ;

Promotion :

Si le serveur secondaire origine est promu et devient un primaire, la réplication logique qui y est attachée fonctionne toujours.

Il est possible de monter à plusieurs dizaines, voire une centaine, le nombre de réplications logiques depuis un même serveur origine.

Chaque publication nécessite donc un walsender et un slot par abonné sur la source. Sur la cible apparaît un logical replication apply worker pour chaque abonnement. D’autres processus peuvent aussi apparaître pendant la synchronisation (table synchronization worker) ou en cas d’application en parallèle des transactions (parallel apply worker).

On évitera donc de multiplier les réplications inutiles (par exemple en répliquant un schéma entier plutôt que chaque table séparément).

Il faudra parfois monter max_wal_senders et max_replication_slots sur le publieur, mais il n’y a pas besoin de monter max_connections. Sur la destination, il faudra vérifier max_replication_slots, max_logical_replication_workers, voire max_worker_processes. max_sync_workers_per_subscription ou max_parallel_apply_workers_per_subscription (initialisation et parallélisation) peuvent consommer encore d’autres workers. Prévoir donc de la marge.

Et il faut être conscient que chaque worker doit décoder le flux de journaux communs, ne serait-ce que pour chercher ce qui l’intéresse. Il y a donc un coût en CPU et en RAM, voire en disque lors de transactions longues. Dans ce dernier cas, il faudra arbitrer entre l’impact sur la RAM et la création de fichiers temporaires sur disque avec le paramètre logical_decoding_work_mem, et penser à activer l’option streaming = on.

S’il n’y a pas d’abonnement actif sur les tables répliquées, la consommation de ressources sera faible. Par contre, la présence de nombreux serveurs abonnés augmente le risque que certains slots bloquent le recyclage des journaux. Il vaut mieux définir un seuil maximal avec le paramètremax_slot_wal_keep_size, au prix de la désynchronisation de la réplication logique.

Depuis PostgreSQL 16, l’utilisation d’un serveur secondaire dédié est une option intéressante pour ce cas d’usage.

Voir cette discussion sur plgsql-general en janvier 2024.

Dans la base origine :

Le catalogue système pg_publication contient la liste des publications, avec leur méta-données :

TABLE pg_publication ;

     pubname     | pubowner | puballtables | pubinsert | pubupdate | pubdelete
-----------------+----------+--------------+-----------+-----------+-----------
 publi_complete  |       10 | t            | t         | t         | t
 publi_partielle |       10 | f            | t         | t         | t
 publi_t3_1      |       10 | f            | t         | t         | t

Le catalogue système pg_publication_tables contient une ligne par table par publication :

TABLE pg_publication_tables ;

     pubname     | schemaname | tablename
-----------------+------------+-----------
 publi_complete  | public     | t1
 publi_complete  | public     | t3_1
 publi_complete  | public     | t3_2
 publi_complete  | public     | t2
 publi_complete  | public     | t3_3
 publi_complete  | public     | t4
 publi_partielle | public     | t1
 publi_partielle | public     | t2
 publi_t3_1      | public     | t3_1

On peut en déduire deux versions abrégées :

• la liste des tables par publication :

SELECT pubname, array_agg(tablename ORDER BY tablename) AS tables_list
FROM pg_publication_tables
GROUP BY pubname  ORDER BY pubname ;

     pubname     |         tables_list
-----------------+------------------------------
 publi_complete  | {t1,t2,t3_1,t3_2,t3_3,t4,t5}
 publi_partielle | {t1,t2}
 publi_t3_1      | {t3_1}

• la liste des publications par table :

SELECT tablename, array_agg(pubname ORDER BY pubname) AS publications_list
FROM pg_publication_tables
GROUP BY tablename
ORDER BY tablename ;

 tablename |        publicationss_list
-----------+----------------------------------
 t1        | {publi_complete,publi_partielle}
 t2        | {publi_complete,publi_partielle}
 t3_1      | {publi_complete,publi_t3_1}
 t3_2      | {publi_complete}
 t3_3      | {publi_complete}
 t4        | {publi_complete}
 t5        | {publi_complete}

Dans la base destinataire :

Enfin, il y a aussi un catalogue système contenant la liste des souscriptions :

\x
Expanded display is on.
SELECT * FROM pg_subscription;
-[ RECORD 1 ]---+----------------------------------
subdbid         | 16443
subname         | subscr_t3_2
subowner        | 10
subenabled      | t
subconninfo     | port=5444 user=logrepli dbname=b1
subslotname     | subscr_t3_2
subsynccommit   | off
subpublications | {publi_t3_2}

Statut de la réplication :

Comme pour la réplication physique, le retard de réplication est visible ou calculable en utilisant les informations de la vue pg_stat_replication sur le serveur origine :

SELECT * FROM pg_stat_replication ;

-[ RECORD 1 ]----+------------------------------
pid              | 18200
usesysid         | 16442
usename          | logrepli
application_name | subscr_t3_1
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 2017-12-20 10:31:01.13489+01
backend_xmin     |
state            | streaming
sent_lsn         | 0/182D3C8
write_lsn        | 0/182D3C8
flush_lsn        | 0/182D3C8
replay_lsn       | 0/182D3C8
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
-[ RECORD 2 ]----+------------------------------
pid              | 26606
usesysid         | 16442
usename          | logrepli
application_name | subscr_partielle
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 2017-12-20 10:02:28.196654+01
backend_xmin     |
state            | streaming
sent_lsn         | 0/182D3C8
write_lsn        | 0/182D3C8
flush_lsn        | 0/182D3C8
replay_lsn       | 0/182D3C8
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
-[ RECORD 3 ]----+------------------------------
pid              | 15127
usesysid         | 16442
usename          | logrepli
application_name | subscr_complete
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 2017-12-20 11:44:04.267249+01
backend_xmin     |
state            | streaming
sent_lsn         | 0/182D3C8
write_lsn        | 0/182D3C8
flush_lsn        | 0/182D3C8
replay_lsn       | 0/182D3C8
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async

La vue pg_replication_slots est complémentaire de pg_stat_replication car elle contient des statuts :

SELECT * FROM pg_replication_slots ;

-[ RECORD 1 ]-------+--------------------------
slot_name           | abonnement_16001
plugin              | pgoutput
slot_type           | logical
datoid              | 16388
database            | editeur
temporary           | f
active              | t
active_pid          | 1711279
xmin                |
catalog_xmin        | 2388
restart_lsn         | 1/FF7E0670
confirmed_flush_lsn | 1/FF7E06A8
wal_status          | reserved
safe_wal_size       |
two_phase           | f
conflicting         | f

Le dernier champ (apparu en version 16) indique une invalidation à cause d’un conflit de réplication.

Depuis la version 14, une autre vue, pg_stat_replication_slots (description complète dans la documentation, permet de suivre les volumétries (octets, nombre de transactions) écrites sur disque (spilled) ou envoyées en streaming :

SELECT * FROM pg_stat_replication_slots \gx

-[ RECORD 1 ]+-----------
slot_name    | abonnement
spill_txns   | 3
spill_count  | 7
spill_bytes  | 412435584
stream_txns  | 0
stream_count | 0
stream_bytes | 0
total_txns   | 30467
total_bytes  | 161694536
stats_reset  |

Souscriptions :

L’état des souscriptions est disponible sur les serveurs destination à partir de la vue pg_stat_subscription :

SELECT * FROM pg_stat_subscription ;

-[ RECORD 1 ]---------+------------------------------
subid                 | 16573
subname               | subscr_t3_2
pid                   | 18893
relid                 |
received_lsn          | 0/168A748
last_msg_send_time    | 2017-12-20 10:36:13.315798+01
last_msg_receipt_time | 2017-12-20 10:36:13.315849+01
latest_end_lsn        | 0/168A748
latest_end_time       | 2017-12-20 10:36:13.315798+01

Conflits de réplication :

Depuis PostgreSQL 16, un secondaire peut être origine d’une réplication logique. Comme dans une réplication physique classique, il est possible d’avoir des conflits de réplication (le primaire envoie des modifications sur des lignes que le secondaire aurait voulu garder pour ses abonnés).

Dans une réplication physique classique, le conflit entraîne juste l’arrêt de requêtes sur le secondaire. Mais si le secondaire est origine d’une réplication logique, celle-ci peut décrocher. Le problème apparaît surtout lors d’une modification dans le schéma de données. Le message suivant apparaît dans les traces de la destination si un slot de réplication a été invalidé suite à ce conflit :

LOG:  logical replication apply worker for subscription "abonnement_decompte" has started
ERROR:  could not start WAL streaming: ERROR:  can no longer get changes from replication slot "decompte_abonnement_16001"
DETAIL:  This slot has been invalidated because it was conflicting with recovery.

L’option disable_on_error sur la souscription permet d’éviter qu’elle ne tente de se reconnecter en boucle. Le plus propre est de sécuriser la réplication entre primaire et secondaire en passant hot_standby_feedback à on sur le secondaire (ce qui doit toujours se sécuriser sur le primaire en mettant un seuil dans max_slot_wal_keep_size).

Il est possible de surveiller le retard de réplication via l’état des slots de réplication, comme le propose l’outil check_pgactivity (disponible sur github ou les paquets des dépôts). Ici, il n’y a pas de retard sur la réplication, pour les trois slots :

$ ./check_pgactivity -s replication_slots -p 5441 -F human

Service        : POSTGRES_REPLICATION_SLOTS
Returns        : 0 (OK)
Message        : Replication slots OK
Perfdata       : subscr_complete_wal=0File
Perfdata       : subscr_complete_spilled=0File
Perfdata       : subscr_t3_1_wal=0File
Perfdata       : subscr_t3_1_spilled=0File
Perfdata       : subscr_partielle_wal=0File
Perfdata       : subscr_partielle_spilled=0File

Faisons quelques insertions après l’arrêt de s3 (qui correspond à la souscription pour la réplication partielle) :

INSERT INTO t1 SELECT generate_series(1000000, 2000000);

L’outil détecte bien que le slot subscr_partielle a un retard conséquent (8 journaux de transactions) et affiche le nombre de fichiers de débordement créés :

$ ./check_pgactivity -s replication_slots -p 5441 -F human

Service        : POSTGRES_REPLICATION_SLOTS
Returns        : 0 (OK)
Message        : Replication slots OK
Perfdata       : subscr_t3_1_wal=8File
Perfdata       : subscr_t3_1_spilled=0File
Perfdata       : subscr_partielle_wal=8File
Perfdata       : subscr_partielle_spilled=9File
Perfdata       : subscr_complete_wal=8File
Perfdata       : subscr_complete_spilled=9File

Il est aussi possible d’utiliser l’action same_schema avec la sonde Nagios check_postgres (disponible dans les paquets du PDGD ou dans son dépôt) pour détecter des différences de schémas entre deux serveurs (l’origine et une destination).

La réplication logique rend possible une migration entre deux instances de version majeure différente avec une indisponibilité très courte. La base à migrer doit être en version 10 ou supérieure. La technique existe depuis longtemps, d’abord avec des outils de réplication par trigger comme Slony ou Bucardo (qui sont abandonnés).

Le principe est de répliquer une base à l’identique vers la nouvelle instance pendant que la production tourne, donc sans contrainte de temps majeure. Une fois les deux bases synchronisées, la bascule est très rapide : il suffit d’attendre que les toutes dernières données soient répliquées, et de connecter les applications au nouveau serveur. La réplication peut même être inversée pour garder l’ancienne production synchrone, permettant de rebasculer dessus en cas de problème, sans perdre les données modifiées depuis la bascule.

Les étapes sont :

• copie des structures et des objets globaux concernés ;
• mise en place d’une publication sur la source et d’un abonnement sur la cible ;
• suivi de la réplication (lag entre les serveurs) ;
• arrêt des connexions applicatives ;
• attente de la fin de la réplication logique ;
• isolation de la base source des connexions applicatives ;
• synchronisation manuelle des valeurs des séquences (non répliquées) ;
• suppression de la publication et de l’abonnement ;
• éventuellement création d’un abonnement et d’une publication en sens inverse ;
• ouverture de la base cible aux applications.

Les restrictions liées à la réplication logique subsistent :

• les modifications de schéma effectuées pendant la synchronisation ne sont pas répliquées (cela est problématique si l’application elle-même effectue du DDL sur des tables non temporaires) ;
• les TRUNCATE depuis une base v10 ne sont pas répliqués ;
• les Large objects et les séquences ne sont pas répliqués ;
• il est fortement conseillé que toutes les tables aient des clés primaires ;
• la réplication fonctionnant uniquement pour les tables « de base », les vues matérialisées sont à reconstruire sur la cible.

Cette méthode reste donc plus complexe et fastidieuse qu’une migration par pg_dump/pg_restore ou pg_upgrade.

Migration majeure logique avec pg_createsubscriber :

pg_createsubscriber pourrait servir dans le futur pour des migrations majeures depuis PostgreSQL 17 (au moins) vers des versions 18 ou plus, avec une interruption de service minimale. pg_createsubscriber transforme un secondaire physique (de version identique à son primaire, facile et rapide à créer) en un réplica logique. C’est une instance indépendante qui peut être mise à jour avec pg_upgrade, tout en restant en réplication logique.

En effet, pg_upgrade migre les slots de réplication logique (là encore si l’instance d’origine est au moins en version 17, qu’elle soit publieur ou abonnée, les versions précédentes de pg_upgrade obligent à resynchroniser les slots.)

On s’épargne ainsi la complexe, délicate et longue synchronisation logique entre les deux serveurs.

S’il faut réindexer (à cause d’un changement d’OS ou de glibc…), ce peut être fait sur le réplica, sans pression temporelle excessive, avant la bascule vers le nouveau serveur.

Les Large Objects sont copiés par la réplication physique, ce qui peut être utile, mais la réplication logique ne les maintient pas !

Il est parfaitement possible de créer un module de réplication logique qui correspond exactement à vos besoins.

Un exemple basique mais fonctionnel, en C, est commenté en détail dans un billet du blog Dalibo en 2024. Il se contente de tracer des changements, mais c’est un bon point de départ pour autre chose.

Un autre exemple en C est fourni avec le code de PostgreSQL (documentation).

decoder_raw, de Michael Paquier (un des committers de PostgreSQL) est un outil pour regénérer les requêtes, au code assez simple aussi.

Le Wiki de PostgreSQL contient des liens vers plusieurs décodeurs logiques, dont le code peut servir de modèle. Beaucoup ne sont plus maintenus. Les deux décodeurs suivants (en C toujours) le sont, et ils figurent même dans les paquets du PGDG :

• wal2json permet de s’abonner à une table et de restituer ses modifications sous forme d’objets JSON, par transaction ou par tuple ;
• postgres-decoderbufs est utilisé pour l’outil de CDC (Change Data Capture) de Debezium.

Rappelons que la réplication logique native ne réplique pas les ordres DDL. Elle se base uniquement au niveau des données (donc les ordres DML, et TRUNCATE). Les valeurs des séquences et les Larges Objects ne sont pas répliqués.

Avant la version 13, il n’était pas possible d’ajouter une table partitionnée à une publication pour qu’elle et ses partitions soient répliquées. Il fallait ajouter chaque partition individuellement. Cette limitation a été supprimée en version 13. Toujours avant la version 13, il n’était pas possible d’envoyer des données vers une table partitionnée.

Pour les versions inférieures à 14, la réplication logique n’est déclenchée que lors d’un COMMIT, avec un délai de réplication pour les transactions longues. Pensez à streaming=on.

Certaines applications supporteront cette limite. Dans d’autres, il sera plus ou moins facile de reprendre la réplication à zéro. Parfois, une réconciliation manuelle sera nécessaire (la présence de clés primaires peut grandement aider). Dans certains cas, ce problème peut devenir bloquant ou réclamer des développements.

La réplication logique de PostgreSQL apparue en version 10 continue de s’améliorer avec les versions. Elle complète la réplication physique sans la remplacer.

Les cas d’utilisation sont nombreux, mais la supervision est délicate et il faut prévoir les sauvegardes/restaurations et bascules.

La version en ligne des solutions de ces TP est disponible sur https://dali.bo/w5_solutions.

En préalable, nettoyer les instances précédemment créés sur le serveur.

Ensuite, afin de réaliser l’ensemble des TP, créer 4 nouvelles instances PostgreSQL « instance[1-4] », en leur attribuant des ports différents :

# systemctl stop instance1
# systemctl stop instance2
# systemctl stop instance3
# systemctl stop instance4

# rm -rf /var/lib/pgsql/16/instance1
# rm -rf /var/lib/pgsql/16/instance2
# rm -rf /var/lib/pgsql/16/instance3
# rm -rf /var/lib/pgsql/16/instance4

# export PGSETUP_INITDB_OPTIONS='--data-checksums'
# /usr/pgsql-16/bin/postgresql-16-setup initdb instance1
# /usr/pgsql-16/bin/postgresql-16-setup initdb instance2
# /usr/pgsql-16/bin/postgresql-16-setup initdb instance3
# /usr/pgsql-16/bin/postgresql-16-setup initdb instance4

# sed -i "s/#port = 5432/port = 5433/" /var/lib/pgsql/16/instance2/postgresql.conf
# sed -i "s/#port = 5432/port = 5434/" /var/lib/pgsql/16/instance3/postgresql.conf
# sed -i "s/#port = 5432/port = 5435/" /var/lib/pgsql/16/instance4/postgresql.conf

# systemctl start instance1
# systemctl start instance2
# systemctl start instance3
# systemctl start instance4

$ ps -o pid,cmd fx

  PID CMD
 7077 /usr/pgsql-16/bin/postmaster -D /var/lib/pgsql/16/instance4/
 7079  \_ postgres: logger
 7081  \_ postgres: checkpointer
 7082  \_ postgres: background writer
 7083  \_ postgres: walwriter
 7084  \_ postgres: autovacuum launcher
 7085  \_ postgres: logical replication launcher
 7056 /usr/pgsql-16/bin/postmaster -D /var/lib/pgsql/16/instance3/
 7058  \_ postgres: logger
 7060  \_ postgres: checkpointer
 7061  \_ postgres: background writer
 7062  \_ postgres: walwriter
 7063  \_ postgres: autovacuum launcher
 7064  \_ postgres: logical replication launcher
 7035 /usr/pgsql-16/bin/postmaster -D /var/lib/pgsql/16/instance2/
 7037  \_ postgres: logger
 7039  \_ postgres: checkpointer
 7040  \_ postgres: background writer
 7041  \_ postgres: walwriter
 7042  \_ postgres: autovacuum launcher
 7043  \_ postgres: logical replication launcher
 7015 /usr/pgsql-16/bin/postmaster -D /var/lib/pgsql/16/instance1/
 7016  \_ postgres: logger
 7018  \_ postgres: checkpointer
 7019  \_ postgres: background writer
 7020  \_ postgres: walwriter
 7021  \_ postgres: autovacuum launcher
 7022  \_ postgres: logical replication launcher

Le schéma de la base b1 de l’instance origine (instance1) est le suivant. Noter que la table t3 est partitionnée.

CREATE TABLE t1 (id_t1 serial, label_t1 text);
CREATE TABLE t2 (id_t2 serial, label_t2 text);

CREATE TABLE t3 (id_t3 serial, label_t3 text, clepartition_t3 integer)
  PARTITION BY LIST (clepartition_t3);
CREATE TABLE t3_1 PARTITION OF t3 FOR VALUES IN (1);
CREATE TABLE t3_2 PARTITION OF t3 FOR VALUES IN (2);
CREATE TABLE t3_3 PARTITION OF t3 FOR VALUES IN (3);
CREATE TABLE t3_4 PARTITION OF t3 FOR VALUES IN (4);

INSERT INTO t1(label_t1) SELECT 't1, ligne '||i FROM generate_series(1, 100) i;

INSERT INTO t2(label_t2) SELECT 't2, ligne '||i FROM generate_series(1, 1000) i;

INSERT INTO t3(label_t3, clepartition_t3) SELECT 't3, ligne '||i, 1 FROM generate_series(  1, 100) i;
INSERT INTO t3(label_t3, clepartition_t3) SELECT 't3, ligne '||i, 2 FROM generate_series(101, 300) i;
INSERT INTO t3(label_t3, clepartition_t3) SELECT 't3, ligne '||i, 3 FROM generate_series(301, 600) i;

ALTER TABLE t1 ADD PRIMARY KEY(id_t1);
ALTER TABLE t2 ADD PRIMARY KEY(id_t2);
ALTER TABLE t3 ADD PRIMARY KEY(id_t3, clepartition_t3);

Pour répliquer toute la base b1 sur le serveur instance2 :

Sur instance1, créer l’utilisateur de réplication logrepli et lui donner les droits de lecture sur les tables de la base b1.

Sur instance1, modifier la configuration du paramètre wal_level dans le fichier postgresql.conf.

Sur instance1, modifier la configuration des connexions dans le fichier pg_hba.conf.

Redémarrer instance1.

Sur instance2, créer la base b1.

Sur instance2, ajouter dans la base b1 les tables répliquées (sans contenu).

Sur instance1, créer la publication pour toutes les tables.

Sur instance2, créer la souscription.

Vérifier sur instance1, dans la vue pg_stat_replication l’état de la réplication logique.

Sur instance2, consulter pg_stat_subscription.

Vérifier que les tables ont le même contenu que sur instance1 et que les modifications sont également répliquées.

On veut répliquer uniquement les tables t1 et t2 de la base b1 sur le serveur instance3.

Sur instance1, créer la publication pour t1 et t2.

Sur instance3, créer la base b1, les tables à répliquer, puis souscrire à la nouvelle publication de instance1.

Vérifier sur instance1, dans la vue pg_stat_replication l’état de la réplication logique.

Sur instance3, consulter pg_stat_subcription.

Pour répliquer la partition t3_1 du serveur instance1 vers le serveur instance4, puis répliquer la partition t3_2 du serveur instance4 vers le serveur instance2 :

Sur instance1, créer la publication pour la partition t3_1.

Sur instance4, créer l’utilisateur de réplication.

Sur instance4, souscrire à cette nouvelle publication de instance1. Pour créer la table t3_1, il faut aussi créer la table mère t3.

Sur instance4, adapter la valeur du paramètre wal_level dans postgresql.conf.

Sur instance4, adapter les autorisations dans pg_hba.conf pour permettre une réplication depuis instance4.

Redémarrer instance4.

Sur instance4, créer la publication pour t3_4. Il faudra importer la partition t3_4 et donner les droits de lecture à logrepli.

Sur instance1, souscrire à cette nouvelle publication de instance4.

Insérer des données dans t3_4 sur instance4 et vérifier que la réplication se fait de instance4 à instance1.

Voici un exemple de réplication entre des tables qui n’ont pas le même schéma de partitionnement :

• Sur instance1, créer une base bench_part.

• Sur instance2, créer une base bench_part.

• Sur instance1, utiliser pgbench pour créer la table pgbench_account
• avec un partitionnement de type hash et cinq partitions.

• Sur instance2, utiliser pgbench pour créer la table pgbench_account
• avec un partitionnement de type range et trois partitions,
• mais sans insérer de données.

• Sur instance1, autoriser l’utilisateur de réplication à accéder aux tables.
• Créer une publication pour toutes les tables de la base.

• Sur instance2, créer la souscription associée. Que constatez-vous ?

• Sur instance1, supprimer la publication et la recréer avec l’option publish_via_partition_root.

• Sur instance2, recréer la souscription.

• Sur instance1 et instance2, compter les lignes dans chaque partition de pgbench_accounts. Qu’observez-vous ?

Pré-requis

# systemctl stop instance1
# systemctl stop instance2
# systemctl stop instance3
# systemctl stop instance4

# rm -rf /var/lib/pgsql/16/instance1
# rm -rf /var/lib/pgsql/16/instance2
# rm -rf /var/lib/pgsql/16/instance3
# rm -rf /var/lib/pgsql/16/instance4

# export PGSETUP_INITDB_OPTIONS='--data-checksums'
# /usr/pgsql-16/bin/postgresql-16-setup initdb instance1
# /usr/pgsql-16/bin/postgresql-16-setup initdb instance2
# /usr/pgsql-16/bin/postgresql-16-setup initdb instance3
# /usr/pgsql-16/bin/postgresql-16-setup initdb instance4

# sed -i "s/#port = 5432/port = 5433/" /var/lib/pgsql/16/instance2/postgresql.conf
# sed -i "s/#port = 5432/port = 5434/" /var/lib/pgsql/16/instance3/postgresql.conf
# sed -i "s/#port = 5432/port = 5435/" /var/lib/pgsql/16/instance4/postgresql.conf

# systemctl start instance1
# systemctl start instance2
# systemctl start instance3
# systemctl start instance4

$ ps -o pid,cmd fx

  PID CMD
 7077 /usr/pgsql-16/bin/postmaster -D /var/lib/pgsql/16/instance4/
 7079  \_ postgres: logger
 7081  \_ postgres: checkpointer
 7082  \_ postgres: background writer
 7083  \_ postgres: walwriter
 7084  \_ postgres: autovacuum launcher
 7085  \_ postgres: logical replication launcher
 7056 /usr/pgsql-16/bin/postmaster -D /var/lib/pgsql/16/instance3/
 7058  \_ postgres: logger
 7060  \_ postgres: checkpointer
 7061  \_ postgres: background writer
 7062  \_ postgres: walwriter
 7063  \_ postgres: autovacuum launcher
 7064  \_ postgres: logical replication launcher
 7035 /usr/pgsql-16/bin/postmaster -D /var/lib/pgsql/16/instance2/
 7037  \_ postgres: logger
 7039  \_ postgres: checkpointer
 7040  \_ postgres: background writer
 7041  \_ postgres: walwriter
 7042  \_ postgres: autovacuum launcher
 7043  \_ postgres: logical replication launcher
 7015 /usr/pgsql-16/bin/postmaster -D /var/lib/pgsql/16/instance1/
 7016  \_ postgres: logger
 7018  \_ postgres: checkpointer
 7019  \_ postgres: background writer
 7020  \_ postgres: walwriter
 7021  \_ postgres: autovacuum launcher
 7022  \_ postgres: logical replication launcher

CREATE TABLE t1 (id_t1 serial, label_t1 text);
CREATE TABLE t2 (id_t2 serial, label_t2 text);

CREATE TABLE t3 (id_t3 serial, label_t3 text, clepartition_t3 integer)
  PARTITION BY LIST (clepartition_t3);
CREATE TABLE t3_1 PARTITION OF t3 FOR VALUES IN (1);
CREATE TABLE t3_2 PARTITION OF t3 FOR VALUES IN (2);
CREATE TABLE t3_3 PARTITION OF t3 FOR VALUES IN (3);
CREATE TABLE t3_4 PARTITION OF t3 FOR VALUES IN (4);

INSERT INTO t1(label_t1) SELECT 't1, ligne '||i FROM generate_series(1, 100) i;

INSERT INTO t2(label_t2) SELECT 't2, ligne '||i FROM generate_series(1, 1000) i;

INSERT INTO t3(label_t3, clepartition_t3) SELECT 't3, ligne '||i, 1 FROM generate_series(  1, 100) i;
INSERT INTO t3(label_t3, clepartition_t3) SELECT 't3, ligne '||i, 2 FROM generate_series(101, 300) i;
INSERT INTO t3(label_t3, clepartition_t3) SELECT 't3, ligne '||i, 3 FROM generate_series(301, 600) i;

ALTER TABLE t1 ADD PRIMARY KEY(id_t1);
ALTER TABLE t2 ADD PRIMARY KEY(id_t2);
ALTER TABLE t3 ADD PRIMARY KEY(id_t3, clepartition_t3);

Réplication complète d’une base

Sur instance1, créer l’utilisateur de réplication logrepli et lui donner les droits de lecture sur les tables de la base b1.

CREATE ROLE logrepli LOGIN REPLICATION;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO logrepli;

Sur instance1, modifier la configuration du paramètre wal_level dans le fichier postgresql.conf.

wal_level = logical

Sur instance1, modifier la configuration des connexions dans le fichier pg_hba.conf.

host b1 logrepli 127.0.0.1/24 trust

Redémarrer instance1.

Sur instance2, créer la base b1.

$ createdb -p 5433 b1

Sur instance2, ajouter dans la base b1 les tables répliquées (sans contenu).

$ pg_dump -p 5432 -s b1 | psql -p 5433 b1

Sur instance1, créer la publication pour toutes les tables.

CREATE PUBLICATION publi_complete FOR ALL TABLES;

Sur instance2, créer la souscription.

CREATE SUBSCRIPTION subscr_complete
  CONNECTION 'host=127.0.0.1 port=5432 user=logrepli dbname=b1'
  PUBLICATION publi_complete;

Vérifier sur instance1, dans la vue pg_stat_replication l’état de la réplication logique. Sur instance2, consulter pg_stat_subscription.

Sur instance1 :

b1=# SELECT * FROM pg_stat_replication \gx

-[ RECORD 1 ]----+------------------------------
pid              | 7326
usesysid         | 16451
usename          | logrepli
application_name | subscr_complete
client_addr      | 127.0.0.1
client_hostname  |
client_port      | 48094
backend_start    | ...
backend_xmin     |
state            | streaming
sent_lsn         | 0/195BF78
write_lsn        | 0/195BF78
flush_lsn        | 0/195BF78
replay_lsn       | 0/195BF78
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | ...

Sur instance2 :

b1=# select * from pg_stat_subscription \gx

-[ RECORD 1 ]---------+------------------------------
subid                 | 16521
subname               | subscr_complete
pid                   | 7325
relid                 |
received_lsn          | 0/195BF78
last_msg_send_time    | ...
last_msg_receipt_time | ...
latest_end_lsn        | 0/195BF78
latest_end_time       | ...

Vérifier que les tables ont le même contenu que sur instance1 et que les modifications sont également répliquées.

Toute opération d’écriture sur la base b1 du serveur instance1 est répliquée sur instance2.

Sur instance1 :

b1=# INSERT INTO t1 VALUES (101, 't1, ligne 101');

INSERT 0 1

b1=# UPDATE t1 SET label_t1=upper(label_t1) WHERE id_t1=10;

UPDATE 1

b1=# DELETE FROM t1 WHERE id_t1=11;

DELETE 1

b1=# SELECT * FROM t1 WHERE id_t1 IN (101, 10, 11);

 id_t1 |   label_t1
-------+---------------
   101 | t1, ligne 101
    10 | T1, LIGNE 10
(2 rows)

Sur instance2 :

b1=# SELECT count(*) FROM t1;

 count
-------
   100

b1=# SELECT * FROM t1 WHERE id_t1 IN (101, 10, 11);

 id_t1 |   label_t1
-------+---------------
   101 | t1, ligne 101
    10 | T1, LIGNE 10

Réplication partielle d’une base

On veut répliquer uniquement les tables t1 et t2 de la base b1 sur instance3.

Sur instance1, créer la publication pour t1 et t2.

CREATE PUBLICATION publi_partielle
  FOR TABLE t1,t2;

Sur instance3, créer la base b1, les tables à répliquer, puis souscrire à la nouvelle publication de instance1.

$ psql -p 5434 -c "CREATE ROLE logrepli LOGIN REPLICATION;"
$ createdb -p 5434 b1
$ pg_dump -p 5432 -s -t t1 -t t2 b1 | psql -p 5434 b1

CREATE SUBSCRIPTION subscr_partielle
  CONNECTION 'host=127.0.0.1 port=5432 user=logrepli dbname=b1'
  PUBLICATION publi_partielle;

Vérifier sur instance1, dans la vue pg_stat_replication l’état de la réplication logique.

Sur instance1 :

b1=# SELECT * FROM pg_stat_replication \gx
-[ RECORD 1 ]----+------------------------------
pid              | 7326
usesysid         | 16451
usename          | logrepli
application_name | subscr_complete
client_addr      | 127.0.0.1
client_hostname  |
client_port      | 48094
backend_start    | ...
backend_xmin     |
state            | streaming
sent_lsn         | 0/1965548
write_lsn        | 0/1965548
flush_lsn        | 0/1965548
replay_lsn       | 0/1965548
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | ...
-[ RECORD 2 ]----+------------------------------
pid              | 7511
usesysid         | 16451
usename          | logrepli
application_name | subscr_partielle
client_addr      | 127.0.0.1
client_hostname  |
client_port      | 48124
backend_start    | ...
backend_xmin     |
state            | streaming
sent_lsn         | 0/1965548
write_lsn        | 0/1965548
flush_lsn        | 0/1965548
replay_lsn       | 0/1965548
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | ...

Sur instance3, consulter pg_stat_subcription.

Sur instance3 :

b1=# SELECT * FROM pg_stat_subscription \gx
-[ RECORD 1 ]---------+------------------------------
subid                 | 16431
subname               | subscr_partielle
pid                   | 7510
relid                 |
received_lsn          | 0/1965630
last_msg_send_time    | ...
last_msg_receipt_time | ...
latest_end_lsn        | 0/1965630
latest_end_time       | ...

Réplication croisée

Sur instance1, créer la publication pour la partition t3_1.

CREATE PUBLICATION publi_t3_1
  FOR TABLE t3_1;

Sur instance4, créer l’utilisateur de réplication.

$ psql -p 5435 -c "CREATE ROLE logrepli LOGIN REPLICATION;"

Sur instance4, souscrire à cette nouvelle publication de instance1. Pour créer la table t3_1, il faut aussi créer la table mère t3.

$ createdb -p 5435 b1
$ pg_dump  -p 5432 -s -t t3 -t t3_1 b1 | psql -p 5435 b1

CREATE SUBSCRIPTION subscr_t3_1
  CONNECTION 'host=127.0.0.1 port=5432 user=logrepli dbname=b1'
  PUBLICATION publi_t3_1;

Sur instance4, adapter la valeur du paramètre wal_level dans postgresql.conf.

wal_level = logical

Sur instance4, adapter les autorisations dans pg_hba.conf pour permettre une réplication depuis instance4.

host all logrepli 127.0.0.1/24 trust

Redémarrer instance4.

Sur instance4, créer la publication pour t3_4. Il faudra importer la partition t3_4 et donner les droits de lecture à logrepli.

$ pg_dump -p 5432 -s -t t3_4 b1 | psql -p 5435 b1

GRANT SELECT ON t3_4 TO logrepli;

CREATE PUBLICATION publi_t3_4
  FOR TABLE t3_4;

Sur instance1, souscrire à cette nouvelle publication de instance4.

CREATE SUBSCRIPTION subscr_t3_4
  CONNECTION 'host=127.0.0.1 port=5435 user=logrepli dbname=b1'
  PUBLICATION publi_t3_4;

Insérer des données dans t3_4 sur instance4 et vérifier que la réplication se fait de instance4 à instance1.

Sur instance1 :

b1=# SELECT * FROM t3 WHERE id_t3 > 999;

 id_t3 | label_t3 | clepartition_t3
-------+----------+-----------------
(0 rows)

b1=# INSERT INTO t3 VALUES (1001, 't3, ligne 1001', 1);

INSERT 0 1

b1=# SELECT * FROM t3 WHERE id_t3>999;

 id_t3 |    label_t3    | clepartition_t3
-------+----------------+-----------------
  1001 | t3, ligne 1001 |               1

Sur instance4 :

b1=# SELECT * FROM t3 WHERE id_t3 > 999;

 id_t3 |    label_t3    | clepartition_t3
-------+----------------+-----------------
  1001 | t3, ligne 1001 |               1

b1=# INSERT INTO t3 VALUES (1002, 't3, ligne 1002', 4);

INSERT 0 1

b1=# SELECT * FROM t3 WHERE id_t3 > 999;

 id_t3 |    label_t3    | clepartition_t3
-------+----------------+-----------------
  1001 | t3, ligne 1001 |               1
  1002 | t3, ligne 1002 |               4

Sur instance1 :

b1=# SELECT * FROM t3 WHERE id_t3>999;

 id_t3 |    label_t3    | clepartition_t3
-------+----------------+-----------------
  1001 | t3, ligne 1001 |               1
  1002 | t3, ligne 1002 |               4

Réplication et partitionnement

• Sur instance1, créer une base bench_part.

createdb --port 5432 bench_part

• Sur instance2, créer une base bench_part.

createdb --port 5433 bench_part

• Sur instance1, utiliser pgbench pour créer la table pgbench_account
• avec un partitionnement de type hash et cinq partitions.

pgbench --initialize \
        --partition-method=hash \
        --partitions=5 \
        --port=5432 bench_part

• Sur instance2, utiliser pgbench pour créer la table pgbench_account
• avec un partitionnement de type range et trois partitions,
• mais sans insérer de données.

pgbench --initialize \
        --init-steps=dtp \
        --partition-method=range \
        --partitions=3 \
        --port=5433 bench_part

• Sur instance1, autoriser l’utilisateur de réplication à accéder aux tables.
• Créer une publication pour toutes les tables de la base.

GRANT SELECT ON ALL TABLES IN SCHEMA public TO logrepli ;
CREATE PUBLICATION pub_bench FOR ALL TABLES;

• Sur instance2, créer la souscription associée. Que constatez-vous ?

CREATE SUBSCRIPTION sub_bench
  CONNECTION 'host=127.0.0.1 port=5432 user=logrepli dbname=bench_part'
  PUBLICATION pub_bench;

La commande échoue avec le message suivant :

ERROR:  relation "public.pgbench_accounts_5" does not exist

L’erreur fait référence à une partition qui n’existe pas sur la souscription. C’est normal puisque le schéma de partitionnement est différent. Un autre cas de figure peut se présenter : la partition existe, mais les lignes ne correspondent pas aux contraintes de partitionnement. Dans ce cas la souscription sera créée, mais des erreurs seront présentes dans les traces de PostgreSQL.

• Sur instance1, supprimer la publication et la recréer avec l’option publish_via_partition_root.

L’option de publication publish_via_partition_root permet de répondre à ce problème en publiant les modifications avec le nom de la partition mère.

DROP PUBLICATION pub_bench;
CREATE PUBLICATION pub_bench
  FOR ALL TABLES
  WITH ( publish_via_partition_root = true );

• Sur instance2, recréer la souscription.

CREATE SUBSCRIPTION sub_bench
  CONNECTION 'host=127.0.0.1 port=5432 user=logrepli dbname=bench_part'
  PUBLICATION pub_bench;

• Sur instance1 et instance2, compter les lignes dans chaque partition de pgbench_accounts. Qu’observez-vous ?

Sur instance1, la répartition des lignes dans pgbench_account est la suivante :

bench_part=# SELECT tableoid::regclass, count(*)
bench_part-# FROM pgbench_accounts
bench_part-# GROUP BY ROLLUP (1) ORDER BY 1;

      tableoid      | count
--------------------+-------
 pgbench_accounts_1 | 19851
 pgbench_accounts_2 | 20223
 pgbench_accounts_3 | 19969
 pgbench_accounts_4 | 19952
 pgbench_accounts_5 | 20005
 ¤                  | 100000
(6 rows)

Sur instance2, la répartition des lignes est la suivante :

bench_part=# SELECT tableoid::regclass, count(*)
bench_part-# FROM pgbench_accounts
bench_part-# GROUP BY ROLLUP (1) ORDER BY 1;

      tableoid      | count
--------------------+-------
 pgbench_accounts_1 | 33334
 pgbench_accounts_2 | 33334
 pgbench_accounts_3 | 33332
 ¤                  | 100000
(4 rows)

On constate que toutes les lignes sont répliquées et qu’elles sont ventilées différemment sur les deux serveurs.

Ce paramétrage peut également être utilisé pour répliquer depuis une table partitionnée vers une table classique.