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

Azure Database pour PostgreSQL – Serveur flexible prend en charge les méthodologies d’extraction et de réplication logiques des données 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 vous donne un contrôle précis sur la réplication des données, y compris la réplication des données au niveau de la table.
2. Utilisation de l’extension pglogical qui fournit une réplication de streaming logique et d’autres fonctionnalités, 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 DDL, etc.
Décodage logique implémenté en décodant le contenu du journal en écriture avant (WAL).

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 portail.
Affectez au paramètre wal_levella valeur logical.
Si vous souhaitez utiliser l’extension pglogical, recherchez les paramètres shared_preload_libraries et 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 serveur flexible Azure Database pour PostgreSQL autorise le trafic réseau à partir de votre ressource de connexion.
Accordez des autorisations de réplication à l’utilisateur administrateur.
```
ALTER ROLE <adminname> WITH REPLICATION;
```
Vérifiez 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.

Note

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 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 à l’aide du décodage logique.

Réplication logique native

La réplication logique utilise les termes de l’éditeur et de l’abonné.

L’éditeur est la base de données de serveur flexible Azure Database pour PostgreSQL qui envoie des données.
L’abonné est la base de données de serveur flexible Azure Database pour PostgreSQL qui reçoit 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’il reçoit 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 ne voyez pas les données, basculez vers un utilisateur membre du rôle et vérifiez le contenu de la azure_pg_admin table.

Pour en savoir plus sur la réplication logique, consultez la documentation PostgreSQL.

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

Pour configurer la réplication logique entre différentes bases de données sur le même serveur flexible Azure Database pour PostgreSQL, suivez des instructions spécifiques pour éviter les restrictions d’implémentation. Actuellement, vous pouvez uniquement créer un abonnement qui se connecte au même cluster de base de données si l’emplacement de réplication n’est pas créé dans la même commande. Sinon, l’appel CREATE SUBSCRIPTION reste bloqué sur un événement d’attente LibPQWalReceiverReceive. Ce comportement est dû à une restriction existante au sein du moteur Postgres, qui peut être supprimée dans les versions ultérieures.

Pour configurer la réplication logique entre vos bases de données « source » et « cible » sur le même serveur tout en évitant cette restriction, procédez comme suit :

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 slot de réplication logique à l’aide de la fonction pg_create_logical_replication_slot. Cette approche permet d’éviter le problème de blocage qui survient généralement lorsque l’emplacement est créé dans la même commande que l’abonnement. Utilisez le pgoutput plug-in :

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

Ensuite, dans votre base de données cible, créez un abonnement à la publication précédemment créée. Définissez create_slot sur false pour empêcher votre serveur flexible Azure Database pour PostgreSQL de créer un nouveau slot, et spécifiez le nom du slot que vous avez 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, testez-la en insérant un nouvel enregistrement dans la basic table de votre base de données source, puis en vérifiant qu’elle est répliquée 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 correctement configuré, vous voyez le nouvel enregistrement de la base de données source dans votre base de données cible, confirmant 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, consultez la documentation sur l’extension pglogical. Veillez également à effectuer les tâches préalables répertoriées précédemment.

Installez l’extension pglogical dans la base de données sur le fournisseur et les serveurs de base de données de l’abonné.
```
\c myDB
CREATE EXTENSION pglogical;
```
Si l’utilisateur de réplication n’est pas l’utilisateur d’administration du serveur (l’utilisateur qui a créé le serveur), accordez à l’utilisateur l’appartenance au azure_pg_admin rôle et affectez les attributs REPLICATION et LOGIN à l’utilisateur. 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>');

Vérifiez l’état de l’abonnement.

SELECT subscription_name, status FROM pglogical.show_subscription_status();

Caution

Pglogical ne prend actuellement pas en charge la réplication DDL automatique. Vous pouvez copier manuellement le schéma initial à l’aide pg_dump --schema-onlyde . Vous pouvez exécuter des instructions DDL sur le fournisseur et l’abonné simultanément à l’aide de la pglogical.replicate_ddl_command fonction. Tenez compte des autres limitations de l’extension listées ici.

Décodage logique

Vous pouvez utiliser le décodage logique via le protocole de streaming ou l’interface SQL.

Protocole de diffusion en continu

Consommer des modifications avec le protocole de streaming est souvent préférable. Vous pouvez créer votre propre consommateur ou connecteur, ou utiliser un service tiers tel que Debezium.

Pour obtenir un exemple qui utilise le protocole de diffusion en continu avec pg_recvlogical, consultez la documentation wal2json : exemple utilisant le protocole de diffusion en continu avec pg_recvlogical.

Interface SQL

Dans l’exemple suivant, utilisez 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"]
                        }
               }
      ]
}

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

Pour en savoir plus sur le décodage logique, consultez la documentation PostgreSQL : décodage logique.

Monitor

Vous devez surveiller le décodage logique. Supprimez tout emplacement de réplication inutilisé. Les slots conservent les journaux WAL de PostgreSQL ainsi que les catalogues système associés jusqu’à ce que les modifications soient 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, vous devez consommer en continu les slots de réplication logique. 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 si un consommateur est 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.
Slots et basculement HA - Dans PostgreSQL 16 et les versions antérieures, lors de l'utilisation de serveurs avec haute disponibilité (HA) activée dans Azure Database pour PostgreSQL, les slots 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, utilisez l’extension PG Failover Slots et configurez des paramètres associés tels que hot_standby_feedback = on. Pour plus d’informations sur l’activation de cette extension, consultez la documentation.

Prise en charge du basculement pour les emplacements de réplication logique

Pour PostgreSQL 17 et versions ultérieures, la synchronisation d’emplacements est prise en charge en mode natif. Si vous activez les configurations PostgreSQL correctes (sync_replication_slots, ), hot_standby_feedbackles emplacements de réplication logique sont conservés automatiquement après le basculement et aucune extension n’est requise.

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 bascule automatiquement en mode lecture seule lorsque l’utilisation du stockage atteint 95 % ou lorsque la capacité disponible est inférieure à 5 Gio. Si 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), Azure Database pour PostgreSQL serveur flexible 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.

Commentaires

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

Last updated on 2026-07-10