¿Cómo uso la replicación lógica para replicar tablas entre mis instancias de base de datos de Amazon RDS para PostgreSQL?

Resolución

Amazon RDS para PostgreSQL admite la replicación lógica con PostgreSQL 10.4 y versiones posteriores. La versión 2.2.0 y posteriores de la edición de Amazon Aurora compatible con PostgreSQL admite la replicación lógica con PostgreSQL 10.6 y versiones posteriores.

Para obtener más información, consulta Replicación lógica para Amazon RDS para PostgreSQL. 

La siguiente resolución replica dos tablas de origen en dos tablas de destino.

Activación de la replicación lógica

Sigue estos pasos:

1. Crea un grupo de parámetros personalizado y define el parámetro rds.logical_replication en 1.
   Nota: Como el parámetro rds.logical_replication es un parámetro estático, debes reiniciar la instancia de base de datos. Tras reiniciar la instancia de base de datos, el parámetro wal_level cambia a lógico.
2. Asocia el grupo de parámetros con la instancia de base de datos.
3. Ejecuta la siguiente consulta para comprobar que wal_level es lógico y que rds.logical_replication está activado:

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

   Resultado esperado:

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

Creación de tablas de origen e inserción de datos

Conéctate a la instancia de base de datos de PostgreSQL y, a continuación, completa los pasos siguientes en la base de datos de origen:

1. Ejecuta los siguientes comandos para crear las tablas de origen:

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

2. Ejecuta los siguientes comandos para agregar datos a las tablas de origen:

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

Creación de una publicación para las tablas de origen

En la base de datos de origen, sigue estos pasos:

1. Ejecuta el siguiente comando para crear una publicación de las dos tablas:

   CREATE PUBLICATION testpub FOR TABLE reptab1, reptab2;

2. Ejecuta la siguiente consulta para comprobar que los detalles de la publicación son correctos:

   SELECT * FROM pg_publication;

   Resultado esperado:

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

3. Ejecuta la siguiente consulta para comprobar que las tablas de origen están en la publicación:

   SELECT * FROM pg_publication;

   Resultado esperado:

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

Conexión a la base de datos de destino y creación de las tablas de destino

En la base de datos de destino, sigue estos pasos:

1. Ejecuta los siguientes comandos para crear las tablas de destino:

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

   Nota: Utiliza los mismos nombres que las tablas de origen.

2. Ejecuta las siguientes consultas para comprobar que los datos no estén en las tablas de destino.
   Tabla uno:

   SELECT count(*) FROM reptab1;

   Resultado esperado:

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

   Tabla dos:

   SELECT count(*) FROM reptab2;

   Resultado esperado:

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

Creación de una suscripción en la base de datos de destino

Antes de crear la suscripción, ejecuta los siguientes comandos para confirmar que no has almacenado una versión en texto sin formato de tu nombre de usuario y contraseña en los registros de la base de datos:

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

Para crear la suscripción en la base de datos de destino, sigue estos pasos:

1. Ejecuta el siguiente comando para crear la suscripción:

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

   Nota: Sustituye source RDS/host endpoint por el punto de enlace de la instancia de base de datos de origen y source_db_name por el nombre de la instancia de base de datos. Sustituye user por tu nombre de usuario y password por tu contraseña.

2. Ejecuta la siguiente consulta para comprobar que la suscripción está activa:

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

   Resultado esperado:

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

3. Ejecuta las siguientes consultas para comprobar que los datos están en las tablas de destino.
   Tabla uno:

   SELECT count(*) FROM reptab1;

   Resultado esperado:

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

   Tabla dos:

   SELECT count(*) FROM reptab2;

   Resultado esperado:

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

Verificación de los detalles de la ranura de replicación en la base de datos de origen

Al crear una suscripción en la base de datos de destino, esta crea una ranura de replicación en la base de datos de origen.

Para verificar los detalles de la ranura de replicación, ejecuta la siguiente consulta en la base de datos de origen:

SELECT * FROM pg_replication_slots;

Resultado esperado:

 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)

Probar la replicación desde las tablas de origen

En la base de datos de origen, sigue estos pasos:

1. Ejecuta los siguientes comandos para agregar filas a las tablas de origen:

   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. Ejecuta las siguientes consultas para comprobar que los datos han cambiado en las tablas de origen.
   Tabla uno:

   SELECT count(*) FROM reptab1;

   Resultado esperado:

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

   Tabla dos:

   SELECT count(*) FROM reptab2;

   Resultado esperado:

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

Verificar que los datos de las tablas de origen se hayan replicado en las tablas de destino

En la base de datos de destino, ejecuta las siguientes consultas para confirmar que los datos de las tablas de origen se han replicado en las tablas de destino.

Tabla uno:

SELECT count(*) FROM reptab1;

Resultado esperado:

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

Tabla dos:

SELECT count(*) FROM reptab2;

Resultado esperado:

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

Borrar las ranuras de replicación y desactivar la replicación lógica

Cuando hayas completado la replicación y ya no la necesites, borra las ranuras y desactiva la replicación lógica. Las ranuras de replicación inactivas hacen que los archivos de registro de escritura anticipada (WAL) se acumulen en la instancia de base de datos de origen. Los archivos WAL pueden llenar el almacenamiento y provocar tiempos de inactividad.

Sigue estos pasos:

1. En la base de datos de destino, ejecuta el siguiente comando para eliminar la suscripción:

   DROP SUBSCRIPTION testsub;

2. En la base de datos de destino, ejecuta la siguiente consulta para comprobar que se ha eliminado la suscripción:

   SELECT * FROM pg_subscription;

   Resultado esperado:

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

   Nota: Al eliminar la suscripción en la base de datos de destino, la base de datos de destino también elimina las ranuras de replicación de la base de datos de origen.

3. En la base de datos de origen, ejecuta la siguiente consulta para comprobar que las ranuras de replicación se han eliminado de la base de datos de origen:

   SELECT * FROM pg_replication_slots;

   Resultado esperado:

   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. En la base de datos de origen, ejecuta el siguiente comando para eliminar la publicación:

   DROP PUBLICATION testpub;

5. En la base de datos de origen, ejecuta las siguientes consultas para comprobar que se ha eliminado la publicación:

   SELECT * FROM pg_publication;  
   SELECT * FROM pg_publication_tables;

   Resultado esperado:

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

6. En el grupo de parámetros personalizados de la instancia de base de datos, define el parámetro rds.logical_replication en 0. 
   Nota: Reinicia la instancia de base de datos para que se apliquen los cambios.

7. Revisa los parámetros max_replication_slots, max_wal_senders, max_logical_replication_workers, max_worker_processes y max_sync_workers_per_subscription en función de tu uso.

8. Ejecuta la siguiente consulta para comprobar si hay ranuras de replicación inactivas y el tamaño de las ranuras:

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

9. (Opcional) Ejecuta el siguiente comando para eliminar las ranuras de replicación:

   SELECT pg_drop_replication_slot('Your_slotname_name')

Información relacionada

Replication (Replicación) en el sitio web de PostgreSQL

Logical replication (Replicación lógica) en el sitio web de PostgreSQL