Comment répliquer des tables dans RDS for PostgreSQL à l'aide de la réplication logique ?

Résolution

Un cas d'utilisation typique de la réplication logique consiste à répliquer un ensemble de tables entre deux instances de base de données Amazon RDS for PostgreSQL. RDS for PostgreSQL prend en charge la réplication logique avec PostgreSQL 10.4 et les versions ultérieures. Amazon Aurora édition compatible avec PostgreSQL version 2.2.0 et les versions ultérieures prennent en charge la réplication logique avec PostgreSQL 10.6 et les versions ultérieures.

Dans la résolution fournie, deux tables sources sont répliquées sur deux tables cibles à l'aide de la réplication logique dans RDS for PostgreSQL. La réplication logique effectue d'abord le chargement initial des données déjà présentes dans les tables sources, puis continue de répliquer les modifications en cours.

Activer la réplication logique

Pour activer la réplication logique dans RDS for PostgreSQL, modifiez un groupe de paramètres personnalisés pour affecter à rds.logical_replication la valeur 1 et attachez le paramètre rds.logical_replication à l'instance de base de données. Mettez à jour le groupe de paramètres pour affecter à rds.logical_replication la valeur 1 si un groupe de paramètres personnalisés est attaché à une instance de base de données. Le paramètre rds.logical_replication est un paramètre statique qui nécessite de redémarrer l'instance de base de données pour être appliqué. Lorsque l'instance de base de données redémarre, la valeur du paramètre wal_level est logical.

Vérifiez les valeurs des paramètres wal_level et rds.logical_replication :

postgres=> SELECT name,setting FROM pg_settings WHERE name IN ('wal_level','rds.logical_replication');
          name           | setting
-------------------------+---------
 rds.logical_replication | on
 wal_level               | logical
(2 rows)

Se connecter à la base de données source dans l'instance de base de données source

Connectez-vous à la base de données source dans l'instance de base de données RDS for PostgreSQL. Créez des tables sources :

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

Insérez les données dans les tables sources :

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

Créer une publication pour les tables sources

Créez une publication pour les tables sources. Utilisez une requête SELECT pour vérifier les détails de la publication créée :

source=> CREATE PUBLICATION testpub FOR TABLE reptab1,reptab2;
CREATE PUBLICATION
source=> SELECT * FROM pg_publication;
  oid   | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot
--------+---------+----------+--------------+-----------+-----------+-----------+-------------+------------
 115069 | testpub |    16395 | f            | t         | t         | t         | t           | f
(1 row)

Vérifiez que les tables sources sont ajoutées à la publication :

source=> SELECT * FROM pg_publication_tables;
 pubname | schemaname | tablename
---------+------------+-----------
 testpub | public     | reptab1
 testpub | public     | reptab2
(2 rows)

Remarque : pour répliquer toutes les tables d'une base de données, exécutez cette commande :

CREATE PUBLICATION testpub FOR ALL TABLES;

Se connecter à la base de données cible et créer des tables cibles

Connectez-vous à la base de données cible dans l'instance de base de données cible. Créez des tables cibles portant les mêmes noms que les tables sources. Assurez-vous qu'aucune donnée n'est présente dans les tables cibles, en exécutant une requête SELECT sur les tables cibles :

target=> CREATE TABLE reptab1 (slno int primary key);
CREATE TABLE
target=> CREATE TABLE reptab2 (name varchar(20));
CREATE TABLE
target=> SELECT count(*) FROM reptab1;
 count
-------
     0
(1 row)
target=> SELECT count(*) FROM reptab2;
 count
-------
     0
(1 row)

Créer et vérifier l'abonnement dans la base de données cible

Créez l'abonnement dans la base de données cible. Utilisez une requête SELECT pour vérifier que l'abonnement est activé :

target=> CREATE SUBSCRIPTION testsub CONNECTION 'host=<source RDS/host endpoint> port=5432 dbname=<source_db_name> user=<user> password=<password>' PUBLICATION testpub;
NOTICE:  Created replication slot "testsub" on publisher
CREATE SUBSCRIPTION
target=> SELECT oid,subname,subenabled,subslotname,subpublications FROM pg_subscription;
  oid  | subname | subenabled | subslotname | subpublications
-------+---------+------------+-------------+-----------------
 16434 | testsub | t          | testsub     | {testpub}
(1 row)

Important : pour éviter de stocker une version en texte brut de votre nom d'utilisateur et de votre mot de passe dans les journaux de la base de données, avant de créer l'abonnement, exécutez les commandes suivantes :

target=> SET log_min_messages to 'PANIC';
SET
target=> SET log_statement to NONE;
SET

Lorsque l'abonnement est créé, il charge toutes les données présentes dans les tables sources dans les tables cibles. Exécutez une requête SELECT sur les tables cibles pour vérifier que les données initiales sont chargées :

target=> SELECT count(*) FROM reptab1;
 count
-------
  1000
(1 row)
target=> SELECT count(*) FROM reptab2;
 count
-------
    50
(1 row)

Vérifier l'emplacement de réplication dans la base de données

La création d'un abonnement dans la base de données cible crée un emplacement de réplication dans la base de données source. Vérifiez les détails de l'emplacement de réplication en exécutant la requête SELECT suivante sur la base de données source :

source=> SELECT * FROM pg_replication_slots;
 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

Vérifiez que les modifications de données dans les tables sources sont répliquées dans les tables cibles en insérant des lignes dans les tables sources :

source=> INSERT INTO reptab1 VALUES(generate_series(1001,2000));
INSERT 0 1000
source=> INSERT INTO reptab2 SELECT SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',((random()*(36-1)+1)::integer),1) FROM generate_series(1,50);
INSERT 0 50
source=> SELECT count(*) FROM reptab1;
 count
-------
  2000
(1 row)
source=> SELECT count(*) FROM reptab2; count
-------
   100
(1 row)

Tester la réplication en vérifiant le nombre de lignes dans les tables cibles

Vérifiez le nombre de lignes des tables cibles pour vous assurer que les nouvelles insertions sont répliquées dans les tables cibles :

target=> SELECT count(*) FROM reptab1;
 count
-------
  2000
(1 row)
target=> SELECT count(*) FROM reptab2;
 count
-------
   100
(1 row)

Nettoyer et désactiver la réplication logique

Nettoyez et désactivez la réplication logique lorsque la réplication est terminée et n'est plus requise. Les emplacements de réplication inactifs entraînent l'accumulation de fichiers WAL sur les instances de base de données sources. Les fichiers WAL peuvent remplir l'espace de stockage et provoquer des pannes.

Supprimez l'abonnement sur la base de données cible :

target=> DROP SUBSCRIPTION testsub;
NOTICE:  Dropped replication slot "testsub" on publisher
DROP SUBSCRIPTION
target=> SELECT * FROM pg_subscription;
oid | subdbid | subname | subowner | subenabled | subconninfo | subslotname | subsynccommit | subpublications
----+---------+---------+----------+------------+-------------+-------------+---------------+-----------------
(0 rows)

Remarque : la suppression de l'abonnement supprime également l'emplacement de réplication créé par l'abonnement.

Vérifiez que l'emplacement de réplication est supprimé de la base de données source en exécutant l'instruction de requête SELECT suivante sur la source :

source=> SELECT * FROM pg_replication_slots;
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)

Supprimez la publication. Vérifiez que la publication a bien été supprimée :

source=> DROP PUBLICATION testpub;
DROP PUBLICATION
source=> SELECT * FROM pg_publication;
 oid | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot
-----+---------+----------+--------------+-----------+-----------+-----------+-------------+------------
(0 rows)
source=> SELECT * FROM pg_publication_tables;
 pubname | schemaname | tablename
---------+------------+-----------
(0 rows)

Affectez au paramètre rds.logical_replication la valeur 0 dans le groupe de paramètres personnalisés attaché à l'instance de base de données. Redémarrez l'instance de base de données, si nécessaire, si elle n'utilise pas la réplication logique.

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.

Remarque : les commandes suivantes permettent de vérifier qu'il existe des emplacements de réplication inactifs, de déterminer les tailles respectives des emplacements, puis de supprimer les emplacements, si nécessaire.

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

SELECT pg_drop_replication_slot('Your_slotname_name');

---

Informations connexes

Documentation PostgreSQL relative à la réplication