Utilisation de pglogical pour synchroniser les données entre les instances

Toutes les versions Postgre SQL prennent en charge l'pglogicalextension. L'extension pglogical est antérieure à la fonctionnalité de réplication logique fonctionnellement similaire introduite par Postgre SQL dans la version 10. Pour de plus amples informations, veuillez consulter Réalisation d'une réplication logique pour Amazon RDS pour Postgre SQL.

L'pglogicalextension prend en charge la réplication logique entre deux ou plusieurs . RDSpour les SQL instances de base de données Postgre. Il prend également en charge la réplication entre les différentes SQL versions de Postgre et entre les bases de données exécutées sur des instances de SQL base RDS de données Postgre et des clusters de bases de données Aurora PostgreSQL. L'extension pglogical utilise un modèle de publication et d'abonnement pour répliquer les changements apportés aux tables et aux autres objets, tels que les séquences, d'un serveur de publication à un abonné. Elle s'appuie sur un emplacement de réplication pour assurer la synchronisation des changements d'un nœud de serveur de publication à un nœud abonné, défini comme suit.

• Le nœud éditeur est le pour l'SQLinstance de base de données Postgre qui est la source des données à répliquer sur d'autres nœuds. Le nœud de serveur de publication définit les tables à répliquer dans un ensemble de publication.

• Le nœud abonné est le pour l'SQLinstance de base de données Postgre qui reçoit les WAL mises à jour de l'éditeur. L'abonné crée un abonnement pour se connecter à l'éditeur et obtenir les WAL données décodées. Lorsque l'abonné crée l'abonnement, l'emplacement de réplication est créé sur le nœud de serveur de publication.

Vous trouverez ci-après des informations sur la configuration de l'extension pglogical.

Exigences et limites de l'extension pglogique

Toutes les versions actuellement disponibles d' prennent en charge l'pglogicalextension.

Le nœud de serveur de publication et le nœud abonné doivent tous deux être configurés pour la réplication logique.

Les tables que vous souhaitez répliquer d'un éditeur à un abonné doivent porter les mêmes noms et le même schéma. Ces tables doivent également contenir les mêmes colonnes, et les colonnes doivent utiliser les mêmes types de données. Les tables des serveurs de publication et des abonnés doivent avoir les mêmes clés primaires. Nous vous recommandons de n'utiliser PRIMARY KEY que le comme contrainte unique.

Les tables du nœud abonné peuvent avoir des contraintes plus permissives que celles du nœud éditeur en ce qui concerne les CHECK contraintes et NOT NULL les contraintes.

L'pglogicalextension fournit des fonctionnalités telles que la réplication bidirectionnelle qui ne sont pas prises en charge par la fonctionnalité de réplication logique intégrée à Postgre SQL (version 10 et supérieure). Pour plus d'informations, consultez Réplication SQL bidirectionnelle Postgre à l'aide de pglogical.

Configuration de l'extension pglogical

Pour configurer l'pglogicalextension sur votre , vous devez l'ajouter pglogical aux bibliothèques partagées sur le groupe de paramètres de base de données personnalisé pour votre instance de base de données RDS pour Postgre SQL. Vous devez également définir la valeur du paramètre rds.logical_replication sur 1, pour activer le décodage logique. Enfin, vous créez l'extension dans la base de données. Vous pouvez utiliser le plugin AWS Management Console ou le AWS CLI pour ces tâches.

Vous devez disposer d'autorisations en tant que rôle rds_superuser pour effectuer ces tâches.

Les étapes suivantes supposent que votre instance de RDS pour Postgre SQL est associé à un groupe de paramètres de base de données de de base de données personnalisé. Pour plus d'informations sur la création d'un groupe de paramètres de cluster de bases de données, consultez Groupes de paramètres pour Amazon RDS.

Console

Pour configurer l'extension pglogical

1. Connectez-vous au AWS Management Console et ouvrez la RDS console Amazon à l'adresse https://console.aws.amazon.com/rds/.

2. Dans le volet de navigation, choisissez l' de SQL base de données Postgre.

3. Ouvrez l'onglet Configuration de votre . RDSpour une SQL instance de base de données Postgre. Parmi les détails de l'instance, trouvez le lien Groupe de paramètres.

4. Cliquez sur le lien pour ouvrir les paramètres personnalisés associés à votre . RDSpour une SQL instance de base de données Postgre.

5. Dans le champ de recherche Parameters (Paramètres), tapez shared_pre pour trouver le paramètre shared_preload_libraries.

6. Choisissez Edit parameters (Modifier les paramètres) pour accéder aux valeurs des propriétés.

7. Ajoutez pglogical à la liste dans le champ Values (Valeurs). Utilisez une virgule pour séparer les éléments de la liste de valeurs.

   

8. Recherchez le paramètre rds.logical_replication et définissez-le sur 1, pour activer la réplication logique.

9. Redémarrez l' pour l'SQLinstance de base de données Postgre afin que vos modifications prennent effet.

10. Lorsque l'instance est disponible, vous pouvez utiliser psql (oupgAdmin) pour vous connecter à l'. RDSpour une SQL instance de base de données Postgre.

    psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
    

11. Pour vérifier que pglogical est initialisé, exécutez la commande suivante.

    SHOW shared_preload_libraries;
    shared_preload_libraries 
    --------------------------
    rdsutils,pglogical
    (1 row) 

12. Vérifiez le paramètre qui active le décodage logique, comme suit.

    SHOW wal_level;
    wal_level
    -----------
     logical
    (1 row)

13. Créez l'extension, comme suit.

    CREATE EXTENSION pglogical;
    EXTENSION CREATED

14. Sélectionnez Enregistrer les modifications.

15. Ouvrez la RDS console Amazon à l'adresse https://console.aws.amazon.com/rds/.

16. Choisissez l'instance d' dans la liste des bases de données pour la sélectionner, puis choisissez Redémarrer dans le menu Actions.

AWS CLI

Pour configurer l'extension pglogical

Pour configurer pglogical à l'aide du AWS CLI, vous appelez l'modify-db-parameter-groupopération pour modifier certains paramètres de votre groupe de paramètres personnalisé, comme indiqué dans la procédure suivante.

1. Utilisez ce qui suit AWS CLI commande à ajouter pglogical au shared_preload_libraries paramètre.

   aws rds modify-db-parameter-group \
      --db-parameter-group-name custom-param-group-name \
      --parameters "ParameterName=shared_preload_libraries,ParameterValue=pglogical,ApplyMethod=pending-reboot" \
      --region aws-region

2. Utilisez ce qui suit AWS CLI commande à définir rds.logical_replication 1 pour activer la fonctionnalité de décodage logique pour l'. RDSpour une SQL instance de base de données Postgre.

   aws rds modify-db-parameter-group \
      --db-parameter-group-name custom-param-group-name \
      --parameters "ParameterName=rds.logical_replication,ParameterValue=1,ApplyMethod=pending-reboot" \
      --region aws-region

3. Utilisez ce qui suit AWS CLI commande pour redémarrer l' pour SQL instance de base de données Postgre afin que la bibliothèque pglogical soit initialisée.

   aws rds reboot-db-instance \
       --db-instance-identifier your-instance \
       --region aws-region

4. Lorsque l'instance est disponible, utilisez-la psql pour vous connecter à l'. RDSpour une SQL instance de base de données Postgre.

   psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
   

5. Créez l'extension, comme suit.

   CREATE EXTENSION pglogical;
   EXTENSION CREATED

6. Redémarrez l' pour SQL instance de base de données Postgre en procédant comme suit AWS CLI commande.

   aws rds reboot-db-instance \
       --db-instance-identifier your-instance \
       --region aws-region

Configuration de la réplication logique pour le pour une instance de base de données Postgre SQL

La procédure suivante explique comment démarrer une réplication logique entre deux . RDSpour les SQL instances de base de données Postgre. Les étapes supposent que la source (serveur de publication) et la cible (abonné) ont toutes deux l'extension pglogical configurée comme indiqué dans le document Configuration de l'extension pglogical.

Pour créer le nœud de serveur de publication et définir les tables à répliquer

Ces étapes supposent que votre pour que l'SQLinstance de base de données Postgre possède une ou plusieurs tables que vous souhaitez répliquer sur un autre nœud. Vous devez recréer la structure de la table du serveur de publication sur l'abonné, donc d'abord, récupérer la structure de la table si nécessaire. Vous pouvez le faire en utilisant la métacommande psq1 \d tablename et en créant ensuite la même table sur l'instance de l'abonné. La procédure suivante crée un exemple de table sur le serveur de publication (source) à des fins de démonstration.

1. Utilisez psql pour vous connecter à l'instance qui possède la table que vous voulez utiliser comme source pour les abonnés.

   psql --host=source-instance.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
   

   Si vous ne disposez pas d'une table existante que vous souhaitez répliquer, vous pouvez créer un exemple de table comme suit.

   1. Créez un exemple de tableau à l'aide de l'SQLinstruction suivante.

      CREATE TABLE docs_lab_table (a int PRIMARY KEY);

   2. Remplissez le tableau avec les données générées à l'aide de l'SQLinstruction suivante.

      INSERT INTO docs_lab_table VALUES (generate_series(1,5000));
      INSERT 0 5000

   3. Vérifiez que les données existent dans le tableau à l'aide de l'SQLinstruction suivante.

      SELECT count(*) FROM docs_lab_table;

2. Identifiez ce pour SQL instance de base de données Postgre en tant que nœud d'éditeur, comme suit.

   SELECT pglogical.create_node(
       node_name := 'docs_lab_provider',
       dsn := 'host=source-instance.aws-region.rds.amazonaws.com port=5432 dbname=labdb');
    create_node
   -------------
      3410995529
   (1 row)

3. Ajoutez la table que vous souhaitez répliquer à l'ensemble de réplication par défaut. Pour plus d'informations sur les ensembles de réplication, consultez Replication sets (Ensembles de réplication) dans la documentation pglogical.

   SELECT pglogical.replication_set_add_table('default', 'docs_lab_table', 'true', NULL, NULL);
    replication_set_add_table
     ---------------------------
     t
     (1 row)

La configuration du nœud de serveur de publication est terminée. Vous pouvez maintenant configurer le nœud abonné pour recevoir les mises à jour du serveur de publication.

Pour configurer le nœud abonné et créer un abonnement pour recevoir des mises à jour

Ces étapes supposent que le pour l'SQLinstance de base de données Postgre a été configuré avec l'pglogicalextension. Pour de plus amples informations, veuillez consulter Configuration de l'extension pglogical.

1. Utilisez psql pour vous connecter à l'instance qui doit recevoir les mises à jour du serveur de publication.

   psql --host=target-instance.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
   

2. Sur le pour l'SQLinstance de base de données Postgre, créez la même table que celle qui existe sur l'éditeur. Pour cet exemple, la table est docs_lab_table. Vous pouvez créer la table comme suit.

   CREATE TABLE docs_lab_table (a int PRIMARY KEY);

3. Vérifiez que cette table est vide.

   SELECT count(*) FROM docs_lab_table;
    count
   -------
     0
   (1 row)

4. Identifiez ce pour l'SQLinstance de base de données Postgre en tant que nœud d'abonné, comme suit.

   SELECT pglogical.create_node(
       node_name := 'docs_lab_target',
       dsn := 'host=target-instance.aws-region.rds.amazonaws.com port=5432 sslmode=require dbname=labdb user=postgres password=********');
    create_node
   -------------
      2182738256
   (1 row)

5. Créez l'abonnement.

   SELECT pglogical.create_subscription(
      subscription_name := 'docs_lab_subscription',
      provider_dsn := 'host=source-instance.aws-region.rds.amazonaws.com port=5432 sslmode=require dbname=labdb user=postgres password=*******',
      replication_sets := ARRAY['default'],
      synchronize_data := true,
      forward_origins := '{}' );  
    create_subscription
   ---------------------
   1038357190
   (1 row)

   Lorsque vous terminez cette étape, les données de la table du serveur de publication sont créées dans la table de l'abonné. Vous pouvez vérifier que cela s'est produit à l'aide de la SQL requête suivante.

   SELECT count(*) FROM docs_lab_table;
    count
   -------
     5000
   (1 row)

À partir de ce moment, les modifications apportées à la table sur le serveur de publication sont répliquées sur la table sur l'abonné.

Rétablissement de la réplication logique après une mise à niveau majeure

Avant de pouvoir effectuer une mise à niveau de version majeure d'un pour une SQL instance de base de données Postgre configurée en tant que nœud d'éditeur pour la réplication logique, vous devez supprimer tous les emplacements de réplication, même ceux qui ne sont pas actifs. Nous vous recommandons de détourner temporairement les transactions de base de données du nœud éditeur, de supprimer les emplacements de réplication, de de mettre à niveau l'SQLinstance de base de données RDS pour Postgre, puis de rétablir et de redémarrer la réplication.

Les emplacements de réplication sont hébergés uniquement sur le nœud de serveur de publication. Dans un scénario de réplication logique, le nœud d'SQLabonné RDS destiné à Postgre ne comporte aucun emplacement à supprimer, mais il ne peut pas être mis à niveau vers une version majeure tant qu'il est désigné comme nœud d'abonné avec un abonnement auprès de l'éditeur. Avant de mettre à niveau le nœud RDS réservé aux SQL abonnés Postgre, supprimez l'abonnement et le nœud. Pour plus d'informations, consultezGestion des emplacements de réplication logiques pour SQL.

Détermination de la perturbation de la réplication logique

Vous pouvez déterminer que le processus de réplication a été interrompu en interrogeant le nœud de serveur de publication ou le nœud abonné, comme suit.

Pour vérifier le nœud de serveur de publication

• Utilisez psql pour vous connecter au nœud de serveur de publication, puis interrogez la fonction pg_replication_slots. Notez la valeur dans la colonne active. Normalement, cela renvoie la valeur t (true), ce qui montre que la réplication est active. Si la requête renvoie la valeur f (false), cela indique que la réplication vers l'abonné a cessé.

  SELECT slot_name,plugin,slot_type,active FROM pg_replication_slots;
                      slot_name              |      plugin      | slot_type | active
  -------------------------------------------+------------------+-----------+--------
   pgl_labdb_docs_labcb4fa94_docs_lab3de412c | pglogical_output | logical   | f
  (1 row)

Pour vérifier le nœud abonné

Sur le nœud abonné, vous pouvez vérifier l'état de la réplication de trois manières différentes.

• Parcourez les SQL journaux Postgre sur le nœud abonné pour trouver les messages d'échec. Le journal identifie l'échec avec des messages qui incluent le code de sortie 1, comme indiqué ci-dessous.

  2022-07-06 16:17:03 UTC::@:[7361]:LOG: background worker "pglogical apply 16404:2880255011" (PID 14610) exited with exit code 1
  2022-07-06 16:19:44 UTC::@:[7361]:LOG: background worker "pglogical apply 16404:2880255011" (PID 21783) exited with exit code 1

• Interrogez la fonction pg_replication_origin. Connectez-vous à la base de données sur le nœud abonné en utilisant psql et interrogez la fonction pg_replication_origin, comme suit.

  SELECT * FROM pg_replication_origin;
   roident | roname
  ---------+--------
  (0 rows)

  L'ensemble de résultats vide signifie que la réplication a été perturbée. Normalement, vous obtenez un résultat qui ressemble au suivant.

     roident |                       roname
    ---------+----------------------------------------------------
           1 | pgl_labdb_docs_labcb4fa94_docs_lab3de412c
    (1 row)

• Interrogez la fonction pglogical.show_subscription_status comme indiqué dans l'exemple suivant.

  SELECT subscription_name,status,slot_name FROM pglogical.show_subscription_status();
       subscription_name | status |              slot_name
  ---====----------------+--------+-------------------------------------
   docs_lab_subscription | down   | pgl_labdb_docs_labcb4fa94_docs_lab3de412c
  (1 row)

  Cette sortie montre que la réplication a été perturbée. Son statut est down. Normalement, la sortie indique le statut replicating.

Si votre processus de réplication logique a été perturbé, vous pouvez rétablir la réplication en suivant les étapes suivantes.

Pour rétablir la réplication logique entre les nœuds de serveur de publication et abonné.

Pour rétablir la réplication, vous devez d'abord déconnecter l'abonné du nœud de serveur de publication, puis rétablir l'abonnement, comme indiqué dans les étapes suivantes.

1. Connectez-vous au nœud abonné à l'aide de psql, comme suit.

   psql --host=222222222222.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb

2. Désactivez l'abonnement en utilisant la fonction pglogical.alter_subscription_disable.

   SELECT pglogical.alter_subscription_disable('docs_lab_subscription',true);
    alter_subscription_disable
   ----------------------------
    t
   (1 row)

3. Obtenez l'identifiant du nœud de serveur de publication en interrogeant pg_replication_origin, comme suit.

   SELECT * FROM pg_replication_origin;
    roident |               roname
   ---------+-------------------------------------
          1 | pgl_labdb_docs_labcb4fa94_docs_lab3de412c
   (1 row)

4. Utilisez la réponse de l'étape précédente avec la commande pg_replication_origin_create pour attribuer l'identifiant qui peut être utilisé par l'abonnement lorsqu'il est rétabli.

   SELECT pg_replication_origin_create('pgl_labdb_docs_labcb4fa94_docs_lab3de412c');
     pg_replication_origin_create
   ------------------------------
                               1
   (1 row)

5. Activez l'abonnement en transmettant son nom avec un statut true, comme indiqué dans l'exemple suivant.

   SELECT pglogical.alter_subscription_enable('docs_lab_subscription',true);
     alter_subscription_enable
   ---------------------------
    t
   (1 row)

Vérifiez le statut du nœud. Son statut doit être replicating, tel qu'indiqué dans cet exemple.

SELECT subscription_name,status,slot_name
  FROM pglogical.show_subscription_status();
             subscription_name |   status    |              slot_name
-------------------------------+-------------+-------------------------------------
 docs_lab_subscription         | replicating | pgl_labdb_docs_lab98f517b_docs_lab3de412c
(1 row)

Vérifiez le statut de l'emplacement de réplication de l'abonné sur le nœud de serveur de publication. La colonne active de l'emplacement doit retourner t (true), indiquant que la réplication a été rétablie.

SELECT slot_name,plugin,slot_type,active
  FROM pg_replication_slots;
                    slot_name              |      plugin      | slot_type | active
-------------------------------------------+------------------+-----------+--------
 pgl_labdb_docs_lab98f517b_docs_lab3de412c | pglogical_output | logical   | t
(1 row)

Gestion des emplacements de réplication logiques pour SQL

Avant de pouvoir effectuer une mise à niveau de version majeure sur une pour une SQL instance de base de données Postgre qui sert de nœud d'éditeur dans un scénario de réplication logique, vous devez supprimer les emplacements de réplication de l'instance. Le processus de pré-vérification des mises à niveau de versions majeures vous informe que la mise à niveau ne peut pas avoir lieu tant que les emplacements ne sont pas supprimés.

Pour supprimer des emplacements de votre RDS SQL instance de base de données Postgre, supprimez d'abord l'abonnement, puis supprimez l'emplacement.

Pour identifier les emplacements de réplication qui ont été créés à l'aide de l'extension pglogical, connectez-vous à chaque base de données et obtenez le nom des nœuds. Lorsque vous interrogez le nœud abonné, vous obtenez à la fois le nœud de serveur de publication et le nœud abonné dans la sortie, comme le montre cet exemple.

SELECT * FROM pglogical.node;
node_id   |     node_name
------------+-------------------
 2182738256 | docs_lab_target
 3410995529 | docs_lab_provider
(2 rows)

Vous pouvez obtenir les détails de l'abonnement avec la requête suivante.

SELECT sub_name,sub_slot_name,sub_target
  FROM pglogical.subscription;
 sub_name |         sub_slot_name          | sub_target
----------+--------------------------------+------------
  docs_lab_subscription     | pgl_labdb_docs_labcb4fa94_docs_lab3de412c | 2182738256
(1 row)

Vous pouvez maintenant supprimer l'abonnement, comme suit.

SELECT pglogical.drop_subscription(subscription_name := 'docs_lab_subscription');
 drop_subscription
-------------------
                 1
(1 row)

Après avoir supprimé l'abonnement, vous pouvez supprimer le nœud.

SELECT pglogical.drop_node(node_name := 'docs-lab-subscriber');
 drop_node
-----------
 t
(1 row)

Vous pouvez vérifier que le nœud n'existe plus, comme suit.

SELECT * FROM pglogical.node;
 node_id | node_name
---------+-----------
(0 rows)

Référence des paramètres de l'extension pglogical

Dans le tableau, vous pouvez trouver les paramètres associés à l'extension pglogical. Les paramètres tels que pglogical.conflict_log_level et pglogical.conflict_resolution sont utilisés pour gérer les conflits de mise à jour. Des conflits peuvent survenir lorsque des modifications sont apportées localement aux mêmes tables qui sont abonnées aux modifications du serveur de publication. Des conflits peuvent également se produire au cours de divers scénarios, tels que la réplication bidirectionnelle ou lorsque plusieurs abonnés se répliquent à partir du même serveur de publication. Pour plus d'informations, consultez Réplication SQL bidirectionnelle Postgre à l'aide de pglogical.

Paramètre	Description
pglogical.batch_inserts	Insertions de lots si possible Non défini par défaut. Remplacez par « 1 » pour activer, par « 0 » pour désactiver.
pglogical.conflict_log_level	Définit le niveau de journalisation à utiliser pour la journalisation des conflits résolus. Les valeurs de chaîne prises en charge sont debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal, panic.
pglogical.conflict_resolution	Définit la méthode à utiliser pour résoudre les conflits lorsque ceux-ci sont résolubles. Les valeurs de chaîne prises en charge sont error, apply_remote, keep_local, last_update_wins, first_update_wins.
pglogical.extra_connection_options	Options de connexion à ajouter à toutes les connexions de nœuds de pairs.
pglogical.synchronous_commit	valeur de validation synchrone spécifique pglogical
pglogical.use_spi	Utilisez SPI (interface de programmation du serveur) plutôt que le bas niveau API pour appliquer les modifications. Définissez sur « 1 » pour activer, sur « 0 » pour désactiver. Pour plus d'informations à ce sujetSPI, consultez la section Interface de programmation du serveur dans la SQL documentation Postgre.