Replicación bidireccional en OCI Database para PostgreSQL mediante pglogical

Introducción

La replicación lógica en OCI PostgreSQL permite replicar objetos de datos y sus cambios en función de la identidad de replicación de una tabla, normalmente la clave primaria. Utiliza un modelo de publicación-suscripción, donde uno o más suscriptores se suscriben a publicaciones definidas en un nodo de editor. Los suscriptores extraen datos de estas publicaciones y, opcionalmente, pueden volver a publicar los cambios para permitir la replicación en cascada o topologías más complejas.

Los flujos de replicación lógica cambios de nivel de fila (operaciones INSERT, UPDATE y DELETE) mediante la descodificación del WAL (log de escritura anticipada) mediante la descodificación lógica. Estos cambios descodificados son independientes del formato de almacenamiento físico de OCI PostgreSQL y representan solo las operaciones de datos lógicas, lo que proporciona una mayor flexibilidad para los sistemas descendentes.

En este tutorial, analizaremos cómo utilizar la extensión pglogical para la replicación lógica y cómo activa la replicación bidireccional entre dos sistemas de base de datos en OCI Database para PostgreSQL.

Replicación bidireccional

La replicación bidireccional (BDR) con lógica significa que dos o más sistemas de base de datos PostgreSQL de OCI publican y se suscriben a los cambios de cada uno. En otras palabras, cada sistema de base de datos PostgreSQL de OCI puede leer y escribir, y todos los cambios se replican en ambas direcciones. En consecuencia, cualquier modificación realizada en cualquiera de los sistemas de base de datos PostgreSQL de OCI se duplicará e implementará en ambos.

Por lo general, se recomienda la replicación bidireccional cuando se necesita una arquitectura de base de datos regional o global que ofrezca acceso de lectura y escritura de baja latencia para los usuarios de varias ubicaciones. Al coubicar instancias de lectura/escritura cerca de los usuarios de cada región, los cambios se pueden realizar localmente y luego replicarse en otras regiones, lo que garantiza que los datos se sincronicen en todos los nodos.

En la práctica, la mayoría de los casos de uso solo requieren que se pueda escribir en un pequeño subjuego de tablas desde varias regiones, mientras que la mayoría de la base de datos puede permanecer como de solo lectura o gestionarse de forma centralizada mediante la replicación unidireccional.

Sin embargo, la replicación bidireccional introduce complejidad. Mantener la consistencia de los datos y gestionar la topología de replicación puede resultar difícil a medida que el sistema crece.

Si los principales objetivos de tu aplicación son la escalabilidad de lectura y la resiliencia a las interrupciones regionales, un enfoque más sencillo y robusto consiste en desplegar una instancia de OCI de alta disponibilidad para PostgreSQL con varias réplicas de lectura, ya sea en la misma región o entre regiones.

Cómo funciona el pglogical bidireccional

En pglogical, puede configurar varias suscripciones y publicaciones de la siguiente manera:

• Sistema de base de datos 1 publica cambios → Sistema de base de datos 2 suscribe

• El sistema de base de datos 2 publica cambios → El sistema de base de datos 1 se suscribe

Cada nodo:

• Envía sus cambios al otro

• Aplica cambios del otro

• Maneja posibles conflictos mediante la configuración de resolución de conflictos de pglogical

Para obtener más información sobre la replicación pgógica unidireccional en todas las regiones de OCI PostgreSQL, consulte este blog: Pglogical unidireccional en toda la región.

Gestión de conflictos y configuración

El editor (la base de datos de origen) define una o más publicaciones y envía datos de cambio (operaciones DML) al suscriptor (la base de datos de destino). Un suscriptor puede conectarse a varios editores y aplicar sus cambios a su base de datos local.

La extensión pglogical proporciona una resolución avanzada de conflictos mediante el parámetro pglogical.conflict_resolution, que admite los siguientes modos:

• apply_remote (valor por defecto en OCI Database para PostgreSQL)

• error

• keep_local

• last_update_wins

• first_update_wins

Para obtener más información sobre conflict_resolution en pglogical, consulte la documentación oficial sobre github: 2nd Quadrant Configuration options.

Al utilizar keep_local, last_update_wins o first_update_wins, track_commit_timestamps debe estar activado tanto en el editor como en el suscriptor. Tenga en cuenta que la activación de esta configuración puede provocar una sobrecarga de rendimiento medible, en función de la carga de trabajo. Se recomienda evaluar el impacto en el rendimiento en el entorno de producción antes del despliegue.

Configuración de replicación bidireccional en OCI PostgreSQL

Para activar la extensión pglogical en OCI Database para PostgreSQL, consulte este tutorial: Activar la extensión pglogical en OCI PostgreSQL.

Tarea 1: Requisitos previos

Una vez creada la extensión pglogical en la base de datos OCI para PostgreSQL, Otorgue el siguiente permiso en el sistema de base de datos n.º 1 y el sistema de base de datos n.º 2,

ALTER ROLE psql WITH REPLICATION;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_replication_origin_session_reset() TO psql; 
GRANT EXECUTE ON FUNCTION pg_catalog.pg_replication_origin_session_setup TO psql; 
GRANT ALL ON FUNCTION pg_catalog.pg_replication_origin_session_setup TO psql;

Nota: psql es un usuario de replicación de ejemplo creado específicamente para fines de demostración en este tutorial.

Tarea 2: Configuración de la replicación lógica unidireccional

En Sistema de base de datos 1:

Una vez que haya completado los requisitos descritos anteriormente, cree una tabla de prueba e inserte algunos registros para verificar el flujo de trabajo pglogical.

CREATE TABLE test_table (
    id serial PRIMARY KEY,
    data text
);
 
INSERT INTO test_table
    (id, data)
VALUES
    (generate_series(1, 1000), 'Test');

Cree el nodo de proveedor:

El siguiente paso es crear un nodo mediante la función pglogical create_node.

SELECT pglogical.create_node(
    node_name := 'provider1',
    dsn := 'host=primary.xxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
            port=5432
            user=psql
            password=xxxxx
            dbname=postgres'
);

A continuación se muestran las definiciones de parámetros para la función create_node:

• node_name: especifique el nombre del editor que se va a crear en el sistema de base de datos nº 1.
• host: introduzca el nombre de dominio completo (FQDN) del sistema de base de datos n.º 1.
• port_number: proporcione el puerto en el que se ejecuta el sistema de base de datos n.º 1.
• database_name: especifique el nombre de la base de datos en la que se configurará la replicación pglogica.

Como paso siguiente, identifique las tablas que se deben incluir en la replicación. pglogical utiliza juegos de replicación para gestionar qué tablas y acciones (INSERT, UPDATE, DELETE) se replican. Puede crear un juego de replicaciones personalizado mediante la función create_replication_set o utilizar el juego por defecto predefinido, que replica todas las acciones en las tablas asignadas. Para incluir tablas, utilice replication_set_add_table para tablas individuales o replication_set_add_all_tables para agregar todas las tablas de un esquema específico.

El siguiente comando agrega todas las tablas del esquema público al juego de replicaciones por defecto:

SELECT pglogical.replication_set_add_all_tables(
    'default',
    ARRAY['public']
);

Para obtener más información sobre los juegos de replicación, consulte Replication sets.

En Sistema de base de datos 2:

Cree una tabla coincidente como en el sistema de base de datos nº 1:

Cree la tabla de prueba en el sistema de base de datos nº 2 para que coincida con la estructura de la tabla en el sistema de base de datos nº 1.

CREATE TABLE test_table (
    id serial PRIMARY KEY,
    data text
);

Cree el nodo de suscriptor:

A continuación, cree un nodo en el sistema de base de datos n.º 2 con la función pglogical create_node, al igual que lo hizo en el sistema de base de datos n.º 1:

SELECT pglogical.create_node(
    node_name := 'subscriber1',
    dsn := 'host=primary.xxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
            port=5432
            user=psql
            password=xxxxxx
            dbname=postgres'
);

A continuación se muestran las definiciones de parámetros para la función create_node:

• node_name: defina el nombre del suscriptor en el sistema de base de datos nº 2.
• host: introduzca el nombre de dominio completo (FQDN) del sistema de base de datos n.º 2.
• port_number: introduzca el puerto en el que se está ejecutando el sistema de base de datos nº 2.
• database_name: proporcione el nombre de la base de datos en la que se creará la suscripción.

Crear suscripción pglogical:

El siguiente paso es crear la suscripción para iniciar la replicación de datos desde el sistema de base de datos n.º 1 mediante la función create_subscription.

El parámetro subscription_name puede ser cualquier nombre que elija para la suscripción. provider_dsn es la cadena de conexión para el sistema de base de datos n.º 1 y el parámetro replication_sets especifica qué juegos de replicación se van a utilizar. En este ejemplo, estamos utilizando el juego de replicaciones predeterminado.

El argumento synchronize_data indica a pglogical si copiar o no los datos existentes del origen. En nuestra prueba, se define en true porque queremos que se copien todas las filas, junto con los cambios futuros.

El parámetro forward_origins es crucial para activar la replicación bidireccional. La definición de forward_origins := '{}' garantiza que solo se reenvíen los cambios que se originan desde el nodo en sí, y no los cambios que ya se han replicado desde otros nodos. Esto evita que el proceso de replicación entre en un bucle infinito, donde los cambios se replican continuamente de un lado a otro.

SELECT pglogical.create_subscription(
    subscription_name := 'subscription1',
    provider_dsn := 'host=primary.xxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
                     port=5432
                     sslmode=require
                     dbname=postgres
                     user=psql
                     password=xxxxx',
    replication_sets := ARRAY['default'],
    synchronize_data := true,
    forward_origins := '{}'
);

Comprobar estado de suscripción:

Compruebe el estado de la suscripción y la información básica sobre el sistema de base de datos n.º 2 mediante el siguiente comando:

select * from pglogical.show_subscription_status();
-[ RECORD 1 ]-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------
subscription_name | subscription1
status            | replicating
provider_node     | provider1
provider_dsn      | host=primary.xxxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com port=5432 sslmode=require dbname=postgres user=psql password=xxxxxx
slot_name         | pgl_postgres_provider1_subscription1
replication_sets  | {default}
forward_origins   |

Tarea 3: Prueba de la replicación del sistema de base de datos n.º 1 al sistema de base de datos n.º 2

Ahora que la replicación está configurada entre el sistema de base de datos n.º 1 (origen) y el sistema de base de datos n.º 2 (destino), probemos si los cambios realizados en el sistema de base de datos n.º 1 se copian automáticamente en el sistema de base de datos n.º 2.

1. Verificar los datos iniciales

   En el sistema de base de datos n.º 2, compruebe la tabla test_table:

   postgres=> SELECT COUNT(*) FROM test_table;
    count 
   -------
     1000
   (1 row)
   

   Esto confirma que la tabla ya tiene 1000 filas.

2. Prueba de INSERT

   Agregue una nueva fila en el sistema de base de datos n.º 1:

   postgres=> INSERT INTO test_table VALUES (10000, 'initial load');
   

3. Compruébelo en el sistema de base de datos n.º 1:

   postgres=> SELECT * FROM test_table WHERE id = 10000;
     id   |     data     
   -------+--------------
   10000 | initial load
   

4. Compruebe el sistema de base de datos n.º 2. Allí aparece la misma fila, que muestra los trabajos de replicación:

   postgres=> SELECT * FROM test_table WHERE id = 10000;
     id   |     data     
   -------+--------------
   10000 | initial load
   

5. Probar un SUPR

   Suprima la misma fila en el sistema de base de datos nº 1:

   postgres=> DELETE FROM test_table WHERE id = 10000;
   

6. Verificar en ambos sistemas: la fila ha desaparecido de ambos.

   (0 rows)
   

7. Prueba de una actualización

   Actualizar una fila existente en el sistema de base de datos n.º 1:

   postgres=> UPDATE test_table SET data = 'Initial load' WHERE id = 1;
   

   Comprobar sistema de base de datos n.º 1:

     id   |     data     
   -------+--------------
     1 | Initial load
   

   Compruebe el sistema de base de datos n.º 2: el valor actualizado también está allí.

Con estas pruebas, confirmamos que las operaciones INSERT, UPDATE y DELETE se replican correctamente desde Sistema de base de datos n.º 1 → Sistema de base de datos n.º 2.

Tarea 4: Configuración de la replicación lógica bidireccional

Las tareas anteriores configuran la replicación unidireccional del sistema de base de datos nº 1 al sistema de base de datos nº 2. A continuación, ejecutaremos comandos adicionales para activar la replicación en la dirección inversa también.

En Sistema de base de datos 2:

Configure un juego de replicación como lo hizo en el sistema de base de datos n.º 1:

SELECT pglogical.replication_set_add_all_tables(
    'default',
    ARRAY['public']
);

En el sistema de base de datos nº 1:

Cree una suscripción en el sistema de base de datos n.º 1 para que empiece a recuperar los cambios del sistema de base de datos n.º 2:

SELECT pglogical.create_subscription(
    subscription_name := 'subscription2',
    provider_dsn := 'host=primary.xxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
                     port=5432
                     sslmode=require
                     dbname=postgres
                     user=psql
                     password=xxxxx',
    replication_sets := ARRAY['default'],
    synchronize_data := false,
    forward_origins := '{}'
);

En el comando anterior, el argumento synchronize_data se ha definido en false, a diferencia de antes. Esto se debe a que no es necesario copiar los datos existentes del sistema de base de datos n.º 2 al sistema de base de datos n.º 1, ya que ambas tablas ya contienen datos idénticos.

Con este comando, la configuración de replicación bidireccional ya está completa y los cambios realizados en un servidor se replicarán en el otro. A continuación, compruebe el estado de la suscripción en el sistema de base de datos nº 1.

Compruebe el estado de suscripción del sistema de base de datos nº 1:

Compruebe el estado de la suscripción y la información básica en el sistema de base de datos n.º 1 con el siguiente comando:

select * from pglogical.show_subscription_status();
-[ RECORD 1 ]-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------
subscription_name | subscription2
status            | replicating
provider_node     | subscriber1
provider_dsn      | host=primary.xxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com port=5432 sslmode=require dbname=postgres user=psql password=xxxxx
slot_name         | pgl_postgres_subscriber1_subscription2
replication_sets  | {default}
forward_origins   |

Tarea 5: Prueba de replicación bidireccional

Empecemos por agregar una fila a ambos sistemas de base de datos para verificar que la replicación bidireccional funciona correctamente.

1. Insertar una fila en el sistema de base de datos n.º 1:

   Ejecute el siguiente comando en el sistema de base de datos n.º 1:

   postgres=# INSERT INTO test_table VALUES (1001, 'Initial_load');
   INSERT 0 1
   

2. Verifique la fila insertada en el sistema de base de datos número 1:

   postgres=# SELECT * FROM test_table WHERE id = 1001;
     id  |     data     
   ------+--------------
   1001 | Initial_load
   (1 row)
   

3. Verifique la replicación en el sistema de base de datos n.º 2:

   Ahora, compruebe el sistema de base de datos n.º 2 para confirmar que la fila se ha replicado:

   postgres=> SELECT * FROM test_table WHERE id = 1001;
     id  |     data     
   ------+--------------
   1001 | Initial_load
   (1 row)
   

   Esto muestra que la inserción en el sistema de base de datos n.º 1 se replicó en el sistema de base de datos n.º 2.

4. Insertar una fila en el sistema de base de datos n.º 2:

   Ejecute el siguiente comando en el sistema de base de datos n.º 2:

   postgres=> INSERT INTO test_table VALUES (1002, 'Initial_load');
   INSERT 0 1
   

5. Verifique la fila insertada en el sistema de base de datos nº 2:

   postgres=> SELECT * FROM test_table WHERE id = 1002;
     id  |     data     
   ------+--------------
   1002 | Initial_load
   (1 row)
   

6. Verifique la replicación en el sistema de base de datos n.º 1:

   Ahora, compruebe el sistema de base de datos n.º 1 para confirmar que la fila se ha replicado de nuevo:

   postgres=# SELECT * FROM test_table WHERE id = 1002;
     id  |     data     
   ------+--------------
   1002 | Initial_load
   (1 row)
   

   Esta prueba confirma que los cambios de datos se replican correctamente en ambas direcciones entre el sistema de base de datos nº 1 y el sistema de base de datos nº 2.

Supervisar demora de replicación lógica

Una vez finalizada la configuración pglogica, debe supervisar el log de replicación para realizar un seguimiento de las ranuras de replicación, el retraso y otras métricas relevantes.

select * from pg_stat_replication;

Este comando devuelve el estado actual de la replicación en OCI PostgreSQL.

Conclusión

Este tutorial le guiará a través de la configuración de la replicación bidireccional PostgreSQL con pglogical. La replicación bidireccional agrega complejidad a la infraestructura de la base de datos y requiere un esfuerzo adicional, por lo que es importante confirmar que se ajusta a su caso de uso. Si la aplicación necesita varios escritores en distintas regiones y la fragmentación de bases de datos no es factible, la replicación bidireccional es la solución ideal.

Acuses de recibo

• Autor: Kaviya Selvaraj (personal técnico de miembros superiores)

Más recursos de aprendizaje

Explore otros laboratorios en docs.oracle.com/learn o acceda a más contenido de aprendizaje gratuito en el canal YouTube de Oracle Learning. Además, visite education.oracle.com/learning-explorer para convertirse en un explorador de Oracle Learning.

Para obtener documentación sobre el producto, visite Oracle Help Center.

---

Título e Información de Copyright

Bi-directional replication in OCI Database for PostgreSQL using pglogical

G43209-01

Copyright ©2025,

Oracle y/o sus filiales.