Uso de pglogical para sincronizar datos entre instancias - Amazon Aurora
Requisitos y limitacionesConfiguración de la extensión pglogicalConfiguración de la replicación lógicaRestablecimiento de la replicación lógica después de una actualizaciónAdministración de ranuras de replicación lógicaReferencia de los parámetros de la extensión pglogical

Uso de pglogical para sincronizar datos entre instancias

Todas las versiones de Aurora PostgreSQL disponibles actualmente admiten la extensión pglogical. La extensión pglogical es anterior a la función de replicación lógica funcionalmente similar que se introdujo en la versión 10 de PostgreSQL. Para obtener más información, consulte Uso de la replicación lógica de PostgreSQL con Aurora.

La extensión pglogical admite la replicación lógica entre dos o más clústeres de base de datos de Aurora PostgreSQL. También admite la replicación entre diferentes versiones de PostgreSQL y entre bases de datos que se ejecutan en instancias de base de datos de RDS para PostgreSQL y clústeres de bases de datos de Aurora PostgreSQL. La extensión pglogical utiliza un modelo de publicación y suscripción para replicar los cambios en las tablas y otros objetos, como secuencias, de un publicador a un suscriptor. Se basa en una ranura de replicación para garantizar que los cambios se sincronicen de un nodo publicador a un nodo suscriptor, que se define de la siguiente manera.

  • El nodo publicador es el clúster de base de datos de Aurora PostgreSQL, que es la fuente de datos que se van a replicar en otros nodos. El nodo publicador define las tablas que se van a replicar en un conjunto de publicaciones.

  • El nodo suscriptor es el clúster de base de datos de Aurora PostgreSQL que recibe las actualizaciones WAL del publicador. El suscriptor crea una suscripción para conectarse al publicador y obtener los datos WAL decodificados. Cuando el suscriptor crea la suscripción, se crea la ranura de replicación en el nodo del publicador.

A continuación, encontrará información sobre cómo configurar la extensión pglogical.

Requisitos y limitaciones de la extensión pglogical

Todas las versiones disponibles actualmente de Aurora PostgreSQL admiten la extensión pglogical.

Tanto el nodo publicador como el nodo suscriptor deben estar configurados para la replicación lógica.

Las tablas que desee replicar del suscriptor en el publicador deben tener los mismos nombres y el mismo esquema. Estas tablas también deben contener las mismas columnas y las columnas deben utilizar los mismos tipos de datos. Tanto las tablas de los publicadores como las de suscriptores deben tener las mismas claves principales. Se recomienda utilizar únicamente la PRIMARY KEY como restricción única.

Las tablas del nodo suscriptor pueden tener restricciones más permisivas que las del nodo publicador para las restricciones CHECK y NOT NULL.

La extensión pglogical proporciona funciones como la replicación bidireccional que no son compatibles con la función de replicación lógica integrada en PostgreSQL (versión 10 y posteriores). Para obtener más información, consulte PostgreSQL bi-directional replication using pglogical (Replicación bidireccional de PostgreSQL mediante pglogical).

Configuración de la extensión pglogical

Para configurar la extensión pglogical en el clúster de base de datos de Aurora PostgreSQL, añada pglogical a las bibliotecas compartidas en el grupo de parámetros de clúster de bases de datos personalizado para su clúster de bases de datos de Aurora PostgreSQL. También debe establecer el valor del parámetro rds.logical_replication en 1 para activar la descodificación lógica. Por último, cree la extensión en la base de datos. Puede utilizar la AWS Management Console o la AWS CLI para estas tareas.

Debe tener permisos como el rol rds_superuser para realizar estas tareas.

En los pasos siguientes se supone que el clúster de ase de datos de Aurora PostgreSQL está asociado a un grupo de parámetros de clúster de base de datos. Para obtener información acerca de cómo crear el grupo de parámetros del clúster de base de datos, consulte Working with parameter groups (Trabajar con grupos de parámetros).

Para configurar la extensión pglogical

  1. Inicie sesión en la AWS Management Console y abra la consola de Amazon RDS en https://console.aws.amazon.com/rds/.

  2. En el panel de navegación, elija la instancia del escritorio del clúster de base de datos de Aurora PostgreSQL .

  3. Abra la pestaña Configuration (Configuración) para su instancia de escritor del clúster de base de datos de Aurora PostgreSQL. Entre los detalles de la instancia, busque el enlace del grupo de parámetros.

  4. Elija el enlace para abrir los parámetros personalizados asociados al clúster de base de datos de Aurora PostgreSQL.

  5. En el campo de búsqueda Parametes (Parámetros), escriba shared_pre para buscar el parámetro shared_preload_libraries.

  6. Seleccione Edit parameters (Editar parámetros) para acceder a los valores de las propiedades.

  7. Añada pglogical a la lista en el campo Values (Valores). Utilice una coma para separar los elementos de la lista de valores.

    Imagen del parámetro shared_preload_libraries con pglogical añadido.

  8. Busque el parámetro rds.logical_replication y configúrelo en 1 para activar la replicación lógica.

  9. Reinicie la instancia de escritor de su clúster de base de datos de Aurora PostgreSQL para que surtan efecto los cambios.

  10. Cuando la instancia esté disponible, puede usar psql (o pgAdmin) para conectarse a la instancia de escritor de su clúster de base de datos de Aurora PostgreSQL. 

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

  11. Para comprobar que pglogical esté inicializado, ejecute el siguiente comando.

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

  12. Compruebe la configuración que permite la descodificación lógica, de la siguiente manera.

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

  13. Cree la extensión de la siguiente manera.

    CREATE EXTENSION pglogical;
EXTENSION CREATED

  14. Elija Guardar cambios.

  15. Abra la consola de Amazon RDS en https://console.aws.amazon.com/rds/.

  16. Elija la instancia de escritura del clúster de RDS for PostgreSQL en la lista de bases de datos para seleccionarla y, a continuación, elija Reboot (Reiniciar) en el menú Actions (Acciones).

Para configurar la extensión pglogical

Para configurar pglogical mediante la AWS CLI, llame a la operación modify-db-parameter-group para modificar determinados parámetros de su grupo de parámetros personalizado, tal como se muestra en el siguiente procedimiento.

  1. Utilice el siguiente comando AWS CLI para añadir pglogical al parámetro shared_preload_libraries.

    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. Utilice el siguiente comando AWS CLI para configurar rds.logical_replication en 1 y activar la función de descodificación lógica para la instancia de escritor del clúster de base de datos de Aurora PostgreSQL. 

    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. Utilice el siguiente comando AWS CLI para reiniciar la instancia de escritor de la instancia de base de datos de Aurora PostgreSQL para que se inicialice la biblioteca de pglogical.

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

  4. Cuando la instancia esté disponible, use psql para conectarse a la instancia de escritor de su clúster de base de datos de Aurora PostgreSQL. 

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

  5. Cree la extensión de la siguiente manera.

    CREATE EXTENSION pglogical;
EXTENSION CREATED

  6. Reinicie la instancia de escritor de su clúster de bases de datos Aurora PostgreSQL mediante el siguiente comando AWS CLI.

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

Configuración de la replicación lógica para el clúster de base de datos de Aurora PostgreSQL

En el siguiente procedimiento, se muestra cómo iniciar la replicación lógica entre dos clústeres de base de datos de Aurora PostgreSQL. En los pasos, se asume que tanto el origen (publicador) como el destino (suscriptor) tienen la extensión pglogical configurada como se detalla en Configuración de la extensión pglogical.

Para crear el nodo publicador y definir las tablas que se van a replicar

En estos pasos se asume que el clúster de base de datos de Aurora PostgreSQL tiene una instancia de escritor con una base de datos que tiene una o más tablas que desea replicar en otro nodo. Debe volver a crear la estructura de tablas del publicador en el suscriptor, así que primero debe obtener la estructura de la tabla si es necesario. Para ello, utilice el metacomando de psq1 \d tablename y, a continuación, cree la misma tabla en la instancia del suscriptor. El siguiente procedimiento crea una tabla de ejemplo en el publicador (origen) con fines de demostración.

  1. Utilice psql para conectarse a la instancia que tiene la tabla que desea usar como origen para los suscriptores. 

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

    Si no dispone de una tabla existente que desee replicar, puede crear una tabla de ejemplo de la siguiente manera.

    1. Cree una tabla de ejemplo con la siguiente instrucción SQL.

      CREATE TABLE docs_lab_table (a int PRIMARY KEY);

    2. Rellene la tabla con los datos generados mediante la siguiente instrucción SQL.

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

    3. Compruebe que los datos existen en la tabla mediante la siguiente instrucción SQL.

      SELECT count(*) FROM docs_lab_table;

  2. Identifique este clúster de base de datos de Aurora PostgreSQL como nodo publicador de la siguiente manera.

    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. Añada la tabla que desea replicar al conjunto de replicación predeterminado. Para obtener más información sobre los conjuntos de replicación, consulte Replication sets (Conjuntos de replicación) en la documentación de pglogical. 

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

Se ha completado la configuración del nodo publicador. Ahora puede configurar el nodo suscriptor para recibir las actualizaciones del publicador.

Para configurar el nodo suscriptor y crear una suscripción para recibir actualizaciones

En estos pasos se asume que el clúster de base de datos de Aurora PostgreSQL se ha configurado con la extensión pglogical. Para obtener más información, consulte Configuración de la extensión pglogical.

  1. Utilice psql para conectarse a la instancia en la que desea recibir actualizaciones del publicador.

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

  2. En el clúster de base de datos de Aurora PostgreSQL, del suscriptor, cree la misma tabla que existe en el publicador. En este ejemplo, la tabla es docs_lab_table. Puede crear la tabla tal y como se indica a continuación.

    CREATE TABLE docs_lab_table (a int PRIMARY KEY);

  3. Compruebe que esta tabla esté vacía.

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

  4. Identifique este clúster de base de datos de Aurora PostgreSQL como nodo suscriptor de la siguiente manera.

    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. Cree la suscripción. 

    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)

    Al completar este paso, los datos de la tabla del publicador se crean en la tabla del suscriptor. Para comprobar que ha ocurrido esto, utilice la siguiente consulta SQL.

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

A partir de este momento, los cambios realizados en la tabla del publicador se replicarán en la tabla del suscriptor.

Restablecimiento de la replicación lógica después de una actualización principal

Para poder realizar una actualización de una versión principal de un clúster de base de datos de Aurora PostgreSQL que se haya configurado como nodo publicador para la replicación lógica, debe eliminar todas las ranuras de replicación, incluso las que no estén activas. Se recomienda desviar temporalmente las transacciones de la base de datos del nodo publicador, eliminar las ranuras de replicación, actualizar el clúster de base de datos de Aurora PostgreSQL, y, a continuación, restablecer y reiniciar la replicación.

Las ranuras de replicación se alojan únicamente en el nodo publicador. El nodo suscriptor de Aurora PostgreSQL en un escenario de replicación lógica no tiene ranuras que eliminar. El proceso de actualización de la versión principal de Aurora PostgreSQL permite actualizar el suscriptor a una nueva versión principal de PostgreSQL independiente del nodo publicador. Sin embargo, el proceso de actualización interrumpe el proceso de replicación e interfiere con la sincronización de los datos WAL entre el nodo publicador y el nodo suscriptor. Debe restablecer la replicación lógica entre el publicador y el suscriptor después de actualizar el publicador, el suscriptor o ambos. En el procedimiento siguiente se muestra cómo determinar que se ha interrumpido la replicación y cómo resolver el problema.

Determinación de que la replicación lógica se ha interrumpido

Puede determinar que el proceso de replicación se ha interrumpido consultando el nodo publicador o el nodo suscriptor de la siguiente manera.

Para comprobar el nodo publicador

  • Utilice psql para conectarse al nodo publicador y, a continuación, consulte la función pg_replication_slots. Anote el valor de la columna activa. Normalmente, esto devolverá t (true) y mostrará que la replicación está activa. Si la consulta devuelve f (false), indica que la replicación en el suscriptor se ha detenido. 

    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)
Para comprobar el nodo suscriptor

En el nodo suscriptor, puede comprobar el estado de la replicación de tres maneras diferentes.

  • Revise los registros de PostgreSQL en el nodo suscriptor para encontrar los mensajes de error. El registro identifica el error con mensajes que incluyen el código de salida 1, como se muestra a continuación.

    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

  • Consulte la función pg_replication_origin. Conéctese a la base de datos en el nodo suscriptor mediante psql y consulte la función pg_replication_origin de la siguiente manera.

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

    Un conjunto de resultados vacío significa que la replicación se ha interrumpido. Debería ver una salida como la siguiente.

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

  • Consulte la función pglogical.show_subscription_status tal y como se muestra en el siguiente ejemplo.

    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)

    Este resultado muestra que la replicación se ha interrumpido. Su estado es down. Normalmente, la salida muestra el estado como replicating.

Si el proceso de replicación lógica se ha interrumpido, puede restablecerla siguiendo estos pasos.

Para restablecer la replicación lógica entre los nodos publicador y suscriptor

Para restablecer la replicación, primero debe desconectar el suscriptor del nodo publicador y, a continuación, restablecer la suscripción, tal como se describe en estos pasos.

  1. Conéctese al nodo suscriptor con psql de la siguiente manera.

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

  2. Desactive la suscripción mediante la función pglogical.alter_subscription_disable.

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

  3. Obtenga el identificador del nodo publicador consultando el pg_replication_origin de la siguiente manera.

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

  4. Utilice la respuesta del paso anterior con el comando pg_replication_origin_create para asignar el identificador que podrá utilizar la suscripción cuando se restablezca. 

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

  5. Para activar la suscripción, introduzca su nombre con un estado de true, tal como se muestra en el ejemplo siguiente.

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

Compruebe el estado del nodo. Su estado debería ser replicating, tal y como se muestra en este ejemplo.

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)

Compruebe el estado de la ranura de replicación del suscriptor en el nodo publicador. La columna active de la ranura debe devolver t (true), lo que indica que se ha restablecido la replicación.

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)

Administración de ranuras de replicación lógica para Aurora PostgreSQL

Para poder realizar una actualización de una versión principal en una instancia de escritor de base de datos de Aurora PostgreSQL que se utilice como nodo publicador en un escenario de replicación lógica, debe eliminar todas las ranuras de replicación de la instancia. El proceso de comprobación previa de la actualización de la versión principal le indica que la actualización no puede continuar hasta que se eliminen las ranuras disponibles.

Para identificar las ranuras de replicación que se crearon con la extensión pglogical, inicie sesión en cada base de datos y obtenga el nombre de los nodos. Al consultar el nodo suscriptor, aparecen los nodos publicador y suscriptor en el resultado, tal como se muestra en este ejemplo. 

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

Puede obtener los detalles de la suscripción con la siguiente consulta.

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)

Ahora puede eliminar la suscripción de la siguiente manera.

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

Después de eliminar la suscripción, puede eliminar el nodo.

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

Puede comprobar que el nodo ya no existe de la siguiente manera.

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

Referencia de parámetros para la extensión pglogical

En la tabla verá los parámetros asociados a la extensión pglogical. Parámetros como pglogical.conflict_log_level y pglogical.conflict_resolution se utilizan para gestionar los conflictos de actualización. Pueden surgir conflictos cuando los cambios se realizan localmente en las mismas tablas que están suscritas a los cambios del publicador. Los conflictos también pueden producirse en varios escenarios, como la replicación bidireccional o cuando varios suscriptores replican desde el mismo publicador. Para obtener más información, consulte PostgreSQL bi-directional replication using pglogical (Replicación bidireccional de PostgreSQL mediante pglogical).

Parámetro Descripción

pglogical.batch_inserts

Inserciones por lotes si es posible. No establecido de manera predeterminada. Se cambia a 1 para activarlo y a 0 para desactivarlo.

pglogical.conflict_log_level

Establece el nivel de registro utilizado para registrar los conflictos resueltos. Los valores permitidos son debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal y panic.

pglogical.conflict_resolution

Establece el método que se utilizará para resolver conflictos si es posible resolverlos. Los valores de cadena admitidos son error, apply_remote, keep_local, last_update_wins y first_update_wins.

pglogical.extra_connection_options

Opciones de conexión a añadir a todas las conexiones de los nodos pares.

pglogical.synchronous_commit

valor de confirmación sincrónica específico de pglogical

pglogical.use_spi

Utilice la SPI (interfaz de programación de servidores) en lugar de la API de nivel inferior para aplicar los cambios. Se establece en 1 para activarlo y en 0 para desactivarlo. Para obtener más información acerca de la SPI, consulte Server Programming Interface (Interfaz de programación de servidores) en la documentación de PostgreSQL.