Réplication logique et décodage logique dans Azure Database pour PostgreSQL

Une instance de serveur flexible Azure Database pour PostgreSQL prend en charge les méthodologies d’extraction et de réplication de données logiques suivantes :

Réplication logique
1. Utilisation de la réplication logique native de PostgreSQL pour répliquer des objets de données. La réplication logique permet un contrôle affiné de la réplication des données, notamment la réplication des données au niveau des tables.
2. Utilisation de l’extension pglogical qui fournit une réplication de diffusion en continu logique et des capacités supplémentaires telles que la copie du schéma initial de la base de données, la prise en charge de TRUNCATE, la possibilité de répliquer des instructions DDL, etc.
Décodage logique qui est implémenté en décodant le contenu du journal WAL (write-ahead log).

Comparer la réplication logique et le décodage logique

La réplication logique et le décodage logique ont plusieurs similitudes. Les deux :

vous permettent de répliquer des données à partir de Postgres ;
utilisent le journal WAL (write-ahead log) comme source des modifications ;
utilisent des emplacements de réplication logique pour envoyer des données. Un emplacement représente un flux de modifications.
Utilisent la propriété REPLICA IDENTITY d’une table pour déterminer les modifications qui peuvent être envoyées.
Ne répliquent pas les modifications du DDL.

Les deux technologies présentent les différences suivantes :

Réplication logique :

Vous permet de spécifier une table ou un ensemble de tables à répliquer.

Le décodage logique :

Extrait des modifications dans toutes les tables d’une base de données.

Prérequis pour la réplication logique et le décodage logique

Accédez à la page des paramètres du serveur sur le portail.
Définissez le paramètre du serveur wal_level sur logical.
Si vous souhaitez utiliser une extension pglogical, recherchez les paramètres shared_preload_librarieset azure.extensions, puis sélectionnez pglogical dans la zone de liste déroulante.
Définissez la valeur du paramètre max_worker_processes sur au moins 16. Sinon, vous pouvez rencontrer des problèmes tels que WARNING: out of background worker slots.
Enregistrez les modifications et redémarrez le serveur pour appliquer les modifications.
Vérifiez que votre instance Azure Database pour PostgreSQL – Serveur flexible autorise le trafic réseau provenant de votre ressource de connexion.
Accordez des autorisations de réplication à l’utilisateur administrateur.
```
ALTER ROLE <adminname> WITH REPLICATION;
```
Il peut être judicieux de vérifier que le rôle que vous utilisez dispose de privilèges sur le schéma que vous répliquez. Sinon, vous risquez de recevoir des erreurs telles que Permission denied for schema.

Remarque

Il est toujours recommandé de séparer votre utilisateur de réplication du compte administrateur habituel.

Utiliser la réplication logique et le décodage logique

L’utilisation de la réplication logique native est le moyen le plus simple de répliquer des données à partir de votre instance de serveur flexible Azure Database pour PostgreSQL. Vous pouvez utiliser l’interface SQL ou le protocole de diffusion en continu pour consommer les modifications. Vous pouvez également utiliser l’interface SQL pour consommer des modifications en utilisant le décodage logique.

Réplication logique native

La réplication logique utilise les termes « éditeur » et « abonné ».

L’éditeur est la base de données d’instance Azure Database pour PostgreSQL – Serveur flexible depuis laquelle vous envoyez des données.
L’abonné est la base de données d’instance de serveur flexible Azure Database pour PostgreSQL à laquelle vous envoyez des données.

Voici un exemple de code que vous pouvez utiliser pour tester la réplication logique.

Connectez-vous à la base de données de publication. Créez une table et ajoutez-y des données.

CREATE TABLE basic (id INTEGER NOT NULL PRIMARY KEY, a TEXT);
INSERT INTO basic VALUES (1, 'apple');
INSERT INTO basic VALUES (2, 'banana');

Créez une publication pour la table.

CREATE PUBLICATION pub FOR TABLE basic;

Connectez-vous à la base de données de l’abonné. Créez une table avec le même schéma que sur l’éditeur.
```
CREATE TABLE basic (id INTEGER NOT NULL PRIMARY KEY, a TEXT);
```

Créez un abonnement qui se connecte à la publication que vous avez créée précédemment.

CREATE SUBSCRIPTION sub CONNECTION 'host=<server>.postgres.database.azure.com user=<rep_user> dbname=<dbname> password=<password>' PUBLICATION pub;

Vous pouvez maintenant interroger la table sur l’abonné. Vous voyez qu’elle a reçu des données de l’éditeur.
```
SELECT * FROM basic;
```
Vous pouvez ajouter des lignes à la table de l’éditeur et voir les modifications sur l’abonné.

Si vous n’êtes pas en mesure de voir les données, basculez vers un utilisateur membre du rôle et vérifiez le contenu de la azure_pg_admin table.

Consultez la documentation PostgreSQL pour en savoir plus sur la réplication logique.

Utiliser la réplication logique entre des bases de données sur le même serveur

Quand vous voulez configurer la réplication logique entre différentes bases de données qui se trouvent sur la même instance Azure Database pour PostgreSQL – Serveur flexible, il est essentiel de suivre des instructions spécifiques pour éviter les restrictions d’implémentation existant actuellement. Pour l’instant, la création d’un abonnement qui se connecte au même cluster de base de données réussit seulement si l’emplacement de réplication n’est pas créé dans la même commande ; sinon, l’appel CREATE SUBSCRIPTION se bloque sur un événement d’attente LibPQWalReceiverReceive. Ceci se produit en raison d’une restriction existante dans le moteur Postgres, qui pourrait être supprimée dans les versions futures.

Pour configurer efficacement la réplication logique entre vos bases de données « source » et « cible » sur le même serveur tout en contournant cette restriction, suivez les étapes décrites ci-dessous :

Tout d’abord, créez une table nommée « basic » avec un schéma identique dans les bases de données source et cible :

-- Run this on both source and target databases
CREATE TABLE basic (id INTEGER NOT NULL PRIMARY KEY, a TEXT);

Ensuite, dans la base de données source, créez une publication pour la table et créez séparément un emplacement de réplication logique en utilisant la fonction pg_create_logical_replication_slot, ce qui permet d’éviter le problème de blocage qui se produit généralement quand l’emplacement est créé dans la même commande que l’abonnement. Vous devez utiliser le plug-in pgoutput :

-- Run this on the source database
CREATE PUBLICATION pub FOR TABLE basic;
SELECT pg_create_logical_replication_slot('myslot', 'pgoutput');

Par la suite, dans votre base de données cible, créez un abonnement à la publication précédemment créée, en vous assurant que create_slot est défini sur false pour empêcher votre instance de base de données Azure flexible pour PostgreSQL de créer un nouvel emplacement, et en spécifiant correctement le nom de l'emplacement créé à l'étape précédente. Avant d’exécuter la commande, remplacez les espaces réservés dans la chaîne de connexion par vos informations d’identification de base de données réelles :

-- Run this on the target database
CREATE SUBSCRIPTION sub
   CONNECTION 'dbname=<source dbname> host=<server>.postgres.database.azure.com port=5432 user=<rep_user> password=<password>'
   PUBLICATION pub
   WITH (create_slot = false, slot_name='myslot');

Après avoir configuré la réplication logique, vous pouvez maintenant la tester en insérant un nouvel enregistrement dans la table « basic » de votre base de données source, puis en vérifiant qu’il est répliqué dans votre base de données cible :

-- Run this on the source database
INSERT INTO basic SELECT 3, 'mango';

-- Run this on the target database
TABLE basic;

Si tout est configuré correctement, vous devez voir le nouvel enregistrement de la base de données source dans votre base de données cible, confirmant ainsi la réussite de la configuration de la réplication logique.

Extension pglogical

Voici un exemple de configuration de pglogical au niveau du serveur de base de données du fournisseur et de l’abonné. Pour plus d’informations, reportez-vous à la documentation de l’extension pglogical. Vérifiez aussi que vous avez effectué les tâches prérequises listées ci-dessus.

Installez l’extension pglogical sur les serveurs de bases de données du fournisseur et de l’abonné.
```
\c myDB
CREATE EXTENSION pglogical;
```
Si l’utilisateur de la réplication n’est pas l’utilisateur d’administration du serveur (qui a créé le serveur), veillez à lui accorder l’appartenance à un rôle azure_pg_admin et à lui affecter les attributs REPLICATION et LOGIN. Pour plus d’informations, consultez la documentation de pglogical.
```
GRANT azure_pg_admin to myUser;
ALTER ROLE myUser REPLICATION LOGIN;
```

Sur le serveur de base de données du fournisseur (source/éditeur), créez le nœud du fournisseur.

select pglogical.create_node( node_name := 'provider1',
dsn := ' host=myProviderServer.postgres.database.azure.com port=5432 dbname=myDB user=myUser password=<password>');

Créez un jeu de réplication.

select pglogical.create_replication_set('myreplicationset');

Ajoutez toutes les tables de la base de données au jeu de réplication.
```
SELECT pglogical.replication_set_add_all_tables('myreplicationset', '{public}'::text[]);
```
Comme méthode alternative, vous pouvez également ajouter des tables d'un schéma spécifique (par exemple, testUser) à un ensemble de réplication par défaut.
```
SELECT pglogical.replication_set_add_all_tables('default', ARRAY['testUser']);
```

Sur le serveur de base de données de l’abonné, créez un nœud d’abonné.

select pglogical.create_node( node_name := 'subscriber1',
dsn := ' host=mySubscriberServer.postgres.database.azure.com port=5432 dbname=myDB user=myUser password=<password>' );

Créez un abonnement pour démarrer le processus de synchronisation et de réplication.

select pglogical.create_subscription (
subscription_name := 'subscription1',
replication_sets := array['myreplicationset'],
provider_dsn := 'host=myProviderServer.postgres.database.azure.com port=5432 dbname=myDB user=myUser password=<password>');

Vous pouvez ensuite vérifier l’état de l’abonnement.

SELECT subscription_name, status FROM pglogical.show_subscription_status();

Attention

Pglogical ne prend actuellement pas en charge une réplication DDL automatique. Le schéma initial peut être copié manuellement en utilisant pg_dump --schema-only. Les instructions DDL peuvent être exécutées simultanément sur le fournisseur et sur l’abonné en utilisant la fonction pglogical.replicate_ddl_command. Tenez compte des autres limitations de l’extension listées ici.

Décodage logique

Le décodage logique peut être utilisé via le protocole de diffusion en continu ou une interface SQL.

Protocole de diffusion en continu

Il est souvent préférable de consommer les modifications en utilisant le protocole de diffusion en continu. Vous pouvez créer votre propre connecteur ou contrôle serveur consommateur, ou utiliser un service tiers comme Debezium.

Consultez la documentation de wal2json pour obtenir un exemple utilisant le protocole de diffusion en continu avec pg_recvlogical.

Interface SQL

Dans l’exemple ci-dessous, nous utilisons l’interface SQL avec le plug-in wal2json.

Créez un emplacement.

SELECT * FROM pg_create_logical_replication_slot('test_slot', 'wal2json');

Émettez des commandes SQL. Par exemple :

CREATE TABLE a_table (
   id varchar(40) NOT NULL,
   item varchar(40),
   PRIMARY KEY (id)
);

INSERT INTO a_table (id, item) VALUES ('id1', 'item1');
DELETE FROM a_table WHERE id='id1';

Consommez les modifications.

SELECT data FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'pretty-print', '1');

Le résultat se présente ainsi :

{
      "change": [
      ]
}
{
      "change": [
               {
                        "kind": "insert",
                        "schema": "public",
                        "table": "a_table",
                        "columnnames": ["id", "item"],
                        "columntypes": ["character varying(40)", "character varying(40)"],
                        "columnvalues": ["id1", "item1"]
               }
      ]
}
{
      "change": [
               {
                        "kind": "delete",
                        "schema": "public",
                        "table": "a_table",
                        "oldkeys": {
                              "keynames": ["id"],
                              "keytypes": ["character varying(40)"],
                              "keyvalues": ["id1"]
                        }
               }
      ]
}

Supprimez l’emplacement une fois que vous avez fini de l’utiliser.
```
SELECT pg_drop_replication_slot('test_slot');
```

Consultez la documentation de PostgreSQL pour en savoir plus sur le décodage logique.

Écran

Vous devez surveiller le décodage logique. Tout emplacement de réplication non utilisé doit être supprimé. Les emplacements sont bloqués dans les journaux WAL Postgres et les catalogues système appropriés jusqu’à ce que les modifications aient été lues. Si votre abonné ou votre consommateur est en échec ou s’il est mal configuré, les journaux non consommés s’accumulent et remplissent votre stockage. En outre, les journaux non consommés augmentent le risque de saturation des ID de transaction. Les deux situations peuvent entraîner l’indisponibilité du serveur. Par conséquent, les emplacements de réplication logique doivent être consommés en continu. Si un emplacement de réplication logique n’est plus utilisé, supprimez-le immédiatement.

La colonne « active » dans la vue pg_replication_slots indique s’il existe un consommateur connecté à un emplacement.

SELECT * FROM pg_replication_slots;

Définissez des alertes sur les ID de transaction maximum utilisés et les métriques De stockage utilisées pour vous avertir lorsque les valeurs augmentent au-delà des seuils normaux.

Limites

Les limitations de la réplication logique s’appliquent comme indiqué ici.
Emplacements et basculement haute disponibilité : quand vous utilisez des serveurs haute disponibilité avec Azure Database pour PostgreSQL, notez que les emplacements de réplication logique ne sont pas conservés lors des événements de basculement. Pour maintenir les emplacements de réplication logique et garantir la cohérence des données après un basculement, il est recommandé d’utiliser l’extension Emplacements de basculement PG (PG Failover Slots). Pour plus d’informations sur l’activation de cette extension, reportez-vous à la documentation.

Important

Vous devez supprimer l’emplacement de réplication logique dans le serveur principal si l’abonné correspondant n’existe plus. Sinon, les fichiers WAL s’accumulent dans le serveur principal, remplissant le stockage. Le serveur principal est automatiquement basculé en mode lecture seule lorsque l’utilisation du stockage atteint 95 % ou lorsque la capacité disponible est inférieure à 5 Gio. Supposons que le seuil de stockage dépasse une certaine limite et que l’emplacement de réplication logique n’est pas utilisé (en raison d’un abonné non disponible), dans ce cas, l’instance de serveur flexible Azure Database pour PostgreSQL supprime automatiquement cet emplacement de réplication logique inutilisé. Cette action libère les fichiers WAL accumulés et évite que votre serveur devienne indisponible en raison d’une situation de saturation du stockage.

Rétroaction

Cette page vous a-t-elle été utile ?

Last updated on 2025-10-02