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

  • Article

S’APPLIQUE À : Azure Database pour PostgreSQL – Serveur flexible

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

  1. 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 de la table.
    2. Utilisation de l’extension pglogical qui fournit une réplication logique en streaming 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 DDL, etc.

  2. Décodage logique implémenté par le décodage du 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 de 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

  1. Accédez à la page des paramètres du serveur sur le portail.

  2. Définissez le paramètre du serveur wal_level sur logical.

  3. 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.

  4. Changez la valeur de paramètre max_worker_processes et remplacez la par au moins 16. Sinon, vous pouvez rencontrer des problèmes tels que WARNING: out of background worker slots.

  5. Enregistrez les modifications et redémarrez le serveur pour appliquer les modifications.

  6. Vérifiez que votre instance de serveur flexible Azure Database pour PostgreSQL autorise le trafic réseau à partir de votre ressource de connexion.

  7. Accordez les autorisations de réplication de l’utilisateur administrateur.

    ALTER ROLE <adminname> WITH REPLICATION;

  8. Vous voudrez peut-être vous assurer que le rôle que vous utilisez dispose de privilèges sur le schéma que vous répliquez. Sinon, vous risquez d’exécuter 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 Azure Database pour PostgreSQL serveur flexible. 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 « éditeur » et « abonné ».

  • L’éditeur est la base de données de serveur flexible Azure Database pour PostgreSQL à partir de laquelle vous envoyez des données.
  • L’abonné est la base de données de serveur flexible Azure Database pour PostgreSQL à laquelle vous envoyez des données.

Voici quelques exemples de code que vous pouvez utiliser pour tester la réplication logique.

  1. Connectez-vous à la base de données de publication. Créez une table et ajoutez 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');

  2. Créez une publication pour la table.

    CREATE PUBLICATION pub FOR TABLE basic;

  3. 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);

  4. Créez un abonnement qui se connecte à la publication que vous avez créée plut tôt.

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

  5. Vous pouvez maintenant interroger la table sur l’abonné. Vous voyez qu’il a reçu des données de l’éditeur.

    SELECT * FROM basic;

    Vous pouvez ajouter des lignes à la table de l’éditeur et afficher les modifications sur l’abonné.

    Si vous ne pouvez pas voir les données, activez le privilège de connexion pour azure_pg_admin et vérifiez le contenu du tableau.

    ALTER ROLE azure_pg_admin login;

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

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

Lorsque vous souhaitez configurer la réplication logique entre différentes bases de données résidant sur la même instance de serveur flexible Azure Database pour PostgreSQL, il est essentiel de suivre des instructions spécifiques pour éviter les restrictions d’implémentation actuellement présentes. À partir de maintenant, la création d’un abonnement qui se connecte au même cluster de base de données réussit uniquement 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 . Cela se produit en raison d’une restriction existante dans le moteur Postgres, qui pourrait être supprimée dans les versions ultérieures.

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 à l’aide de la fonction pg_create_logical_replication_slot, ce qui permet d’éviter le problème de blocage qui se produit généralement lorsque 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 qu’il create_slot est défini pour false empêcher Azure Database pour PostgreSQL serveur flexible de créer un emplacement et de spécifier 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 l’installation 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. Assurez-vous également que vous avez effectué les tâches préalables répertoriées ci-dessus.

  1. Installez l’extension pglogical sur les serveurs de bases de données du fournisseur et de l’abonné.

    \c myDB
CREATE EXTENSION pglogical;

  2. Si l’utilisateur de 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 attribuer des attributs REPLICATION et LOGIN. Pour plus d’informations, consultez la documentation de pglogical.

    GRANT azure_pg_admin to myUser;
ALTER ROLE myUser REPLICATION LOGIN;

  3. 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=myPassword');

  4. Créez un jeu de réplication.

    select pglogical.create_replication_set('myreplicationset');

  5. 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']);

  6. 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=myPasword' );

  7. 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=myPassword');

  8. 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 à l’aide de pg_dump --schema-only. Les instructions DDL peuvent être exécutées simultanément sur le fournisseur et l’abonné à l’aide de 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 à l’aide du 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 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.

  1. Créez un emplacement.

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

  2. É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';

  3. 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"]
                        }
               }
      ]
}

  4. Déposez l'emplacement une fois que vous avez fini de l'utiliser.

    SELECT pg_drop_replication_slot('test_slot');

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

Monitor

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 consommateur tombe en panne ou s'il est mal configuré, les journaux non consommés s'accumulent et remplissent votre espace de stockage. En outre, les journaux non consommés augmentent le risque d’un bouclage 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 Stockage Métriques de serveur flexibles utilisées Azure Database pour PostgreSQL pour vous avertir lorsque les valeurs augmentent les seuils normaux passés.

Limites

  • Les limites de la réplication logique s’appliquent comme indiqué ici.

  • Emplacements et basculement haute disponibilité : lorsque vous utilisez des serveurs à haute disponibilité avec Azure Database pour PostgreSQL serveur flexible, n’oubliez pas que les emplacements de réplication logique ne sont pas conservés pendant les é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. Pour plus d’informations sur l’activation de cette extension, reportez-vous à la documentation.

Important

Vous devez abandonner le slot de réplication logique dans le serveur primaire si l’abonné correspondant n’existe plus. Dans le cas contraire, les fichiers WAL s’accumulent dans le serveur principal, remplissant le stockage. Supposons que le seuil de stockage dépasse un certain seuil 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 ne devienne indisponible en raison d’une situation de saturation du stockage.

Contenu connexe