Comment utiliser la réplication logique pour répliquer des tables entre mes instances de base de données Amazon RDS pour PostgreSQL ?

Résolution

Amazon RDS pour PostgreSQL prend en charge la réplication logique avec PostgreSQL 10.4 et versions ultérieures. Amazon Aurora édition compatible avec PostgreSQL version 2.2.0 et ultérieure prend en charge la réplication logique avec PostgreSQL 10.6 et versions ultérieures.

Pour plus d'informations, consultez la section Exécution d'une réplication logique pour Amazon RDS pour PostgreSQL.

La résolution suivante reproduit deux tables sources vers deux tables cibles.

Activer la réplication logique

Procédez comme suit :

1. Créez un groupe de paramètres personnalisés et définissez le paramètre rds.logical_replication sur 1.
   Remarque : Le paramètre rds.logical_replication étant un paramètre statique, vous devez redémarrer l'instance de base de données. Après avoir redémarré l'instance de base de données, le paramètre wal_level devient logique.
2. Associez le groupe de paramètres à votre instance de base de données.
3. Exécutez la requête suivante pour vérifier que wal_level est logique et que rds.logical_replication est activé :

   SELECT name,setting FROM pg_settings WHERE name IN ('wal_level','rds.logical_replication');

   Sortie attendue :

             name           | setting
   -------------------------+---------
    rds.logical_replication | on
    wal_level               | logical
   (2 rows)

Créer des tables sources et insertion de données

Connectez-vous à l'instance de base de données PostgreSQL, puis effectuez les étapes suivantes dans la base de données source :

1. Exécutez les commandes suivantes pour créer les tables sources :

   CREATE TABLE reptab1 (slno int primary key);  
   CREATE TABLE reptab2 (name varchar(20));

2. Exécutez les commandes suivantes pour ajouter des données dans les tables sources :

   INSERT INTO reptab1 VALUES (generate_series(1,1000));  
   INSERT INTO reptab2 SELECT SUBSTR ('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',((random()*(36-1)+1)::integer),1) FROM generate_series(1,50);

Créer une publication pour les tables sources

Dans la base de données source, procédez comme suit :

1. Exécutez la commande suivante pour créer une publication des deux tables :

   CREATE PUBLICATION testpub FOR TABLE reptab1, reptab2;

2. Exécutez la requête suivante pour vérifier que les détails de la publication sont corrects :

   SELECT * FROM pg_publication;

   Sortie attendue :

     oid   | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot  
   --------+---------+----------+--------------+-----------+-----------+-----------+-------------+------------  
    115069 | testpub |    16395 | f            | t         | t         | t         | t           | f  
   (1 row)

3. Exécutez la requête suivante pour vérifier que les tables sources se trouvent dans la publication :

   SELECT * FROM pg_publication;

   Sortie attendue :

    pubname | schemaname | tablename  
   
   ---------+------------+-----------  
   
    testpub | public | reptab1  
   
    testpub | public | reptab2  
   
   (2 rows)

Connectez-vous à la base de données cible et créez les tables cibles

Dans la base de données cible, procédez comme suit :

1. Exécutez les commandes suivantes pour créer les tables cibles :

   CREATE TABLE reptab1 (slno int primary key);  
   CREATE TABLE reptab2 (name varchar(20));

   Remarque : Utilisez les mêmes noms que les tables sources.

2. Exécutez les requêtes suivantes pour vérifier que les données ne figurent pas dans les tables cibles.
   Tableau 1 :

   SELECT count(*) FROM reptab1;

   Sortie attendue :

    count  
   -------  
        0  
   (1 row)

   Tableau 2 :

   SELECT count(*) FROM reptab2;

   Sortie attendue :

    count  
   -------  
        0  
   (1 row)

Créer un abonnement dans la base de données cible

Avant de créer l'abonnement, exécutez les commandes suivantes pour vérifier que vous n'avez pas enregistré de version en texte brut de votre nom d'utilisateur et de votre mot de passe dans les journaux de base de données :

SET log_min_messages to 'PANIC';  
SET log_statement to NONE;

Pour créer l'abonnement dans la base de données cible, procédez comme suit :

1. Exécutez la commande suivante pour créer l’abonnement :

   CREATE SUBSCRIPTION testsub CONNECTION 'host=source RDS/host endpoint port=5432 dbname=source_db_name user=user password=password' PUBLICATION testpub;

   Remarque : Remplacez le point de terminaison RDS/hôte source par le point de terminaison de votre instance de base de données et source_db_name par le nom de votre instance de base de données. Remplacez user par votre nom d'utilisateur et password par votre mot de passe.

2. Exécutez la requête suivante pour vérifier que l'abonnement est actif :

   SELECT oid,subname,subenabled,subslotname,subpublications FROM pg_subscription;

   Sortie attendue :

     oid  | subname | subenabled | subslotname | subpublications  
   -------+---------+------------+-------------+-----------------  
    16434 | testsub | t          | testsub     | {testpub}  
   (1 row)

3. Exécutez les requêtes suivantes pour vérifier que les données se trouvent dans les tables cibles.
   Tableau 1 :

   SELECT count(*) FROM reptab1;

   Sortie attendue :

    count  
   -------  
     1000  
   (1 row)

   Tableau 2 :

   SELECT count(*) FROM reptab2;

   Sortie attendue :

    count  
   -------  
       50  
   (1 row)

Vérifiez les détails du slot de réplication dans la base de données source

Lorsque vous créez un abonnement dans la base de données cible, celle-ci crée un slot de réplication dans la base de données source.

Pour vérifier les détails du slot de réplication, exécutez la requête suivante sur la base de données source :

SELECT * FROM pg_replication_slots;

Sortie attendue :

 slot_name |  plugin  | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size  
 ----------+----------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------+---------------  
 testsub   | pgoutput | logical   | 115048 | source   | f         | t      |        846 |      |         6945 | 58/B4000568 | 58/B40005A0         | reserved   |  
(1 row)

Tester la réplication à partir des tables sources

Dans la base de données source, procédez comme suit :

1. Exécutez les commandes suivantes pour ajouter des lignes dans les tables sources :

   INSERT INTO reptab1 VALUES(generate_series(1001,2000));  
   INSERT INTO reptab2 SELECT SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',((random()*(36-1)+1)::integer),1) FROM generate_series(1,50);

2. Exécutez les requêtes suivantes pour vérifier que les données ont changé dans les tables sources.
   Tableau 1 :

   SELECT count(*) FROM reptab1;

   Sortie attendue :

    count  
   -------  
      2000  
   (1 row)

   Tableau 2 :

   SELECT count(*) FROM reptab2;

   Sortie attendue :

    count  
   -------  
      100  
   (1 row)

Vérifier que les données des tables sources sont répliquées dans les tables cibles

Dans la base de données cible, exécutez les requêtes suivantes pour confirmer que les données des tables sources sont répliquées dans les tables cibles.

Tableau 1 :

SELECT count(*) FROM reptab1;

Sortie attendue :

count  
-------  
   2000  
(1 row)

Tableau 2 :

SELECT count(*) FROM reptab2;

Sortie attendue :

 count  
-------  
   100  
(1 row)

Vider les emplacements de réplication et désactiver la réplication logique

Une fois que vous avez terminé la réplication et que vous n'en avez plus besoin, effacez les slots et désactivez la réplication logique. Les slots de réplication inactifs entraînent l'accumulation de fichiers de journalisation en écriture anticipée (WAL) sur l'instance de base de données source. Les fichiers WAL peuvent remplir l'espace de stockage et provoquer une durée d’indisponibilité.

Procédez comme suit :

1. Sur la base de données cible, exécutez la commande suivante pour supprimer l'abonnement :

   DROP SUBSCRIPTION testsub;

2. Sur la base de données cible, exécutez la requête suivante pour vérifier que l'abonnement est supprimé :

   SELECT * FROM pg_subscription;

   Sortie attendue :

   oid | subdbid | subname | subowner | subenabled | subconninfo | subslotname | subsynccommit | subpublications  
   ----+---------+---------+----------+------------+-------------+-------------+---------------+-----------------  
   (0 rows)

   Remarque : Lorsque vous supprimez l'abonnement sur la base de données cible, celle-ci supprime également les slots de réplication de la base de données source.

3. Sur la base de données source, exécutez la requête suivante pour vérifier que les slots de réplication sont supprimés de la base de données source :

   SELECT * FROM pg_replication_slots;

   Sortie attendue :

   slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size  
   ----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------+--------------  
   (0 rows)

4. Sur la base de données source, exécutez la commande suivante pour supprimer la publication :

   DROP PUBLICATION testpub;

5. Sur la base de données source, exécutez les requêtes suivantes pour vérifier que la publication est supprimée :

   SELECT * FROM pg_publication;  
   SELECT * FROM pg_publication_tables;

   Sortie attendue :

    oid | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot  
   -----+---------+----------+--------------+-----------+-----------+-----------+-------------+------------  
   (0 rows)  
   pubname | schemaname | tablename  
   ---------+------------+-----------  
   (0 rows)

6. Dans le groupe de paramètres personnalisés de votre instance de base de données, définissez le paramètre rds.logical_replication sur 0. 
   Remarque : Redémarrez l'instance de base de données pour que les modifications soient appliquées.

7. Vérifiez les paramètres max_replication_slots, max_wal_senders, max_logical_replication_workers, max_worker_processes et max_sync_workers_per_subscription en fonction de votre utilisation.

8. Exécutez la requête suivante pour vérifier s'il existe des slots de réplication inactifs et connaître leur taille :

   SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn)) AS replicationSlotLag, active FROM pg_replication_slots;

9. (Facultatif) Exécutez la commande suivante pour supprimer les slots de réplication :

   SELECT pg_drop_replication_slot('Your_slotname_name')

Informations connexes

Réplication sur le site Web de PostgreSQL

Réplication logique sur le site Web de PostgreSQL