Replicación lógica y descodificación lógica en Azure Database for PostgreSQL con servidor flexible

SE APLICA A: Servidor flexible: Azure Database for PostgreSQL

El servidor flexible de Azure Database for PostgreSQL admite las siguientes metodologías lógicas de extracción y replicación de datos:

1. Replicación lógica

   1. Uso de la replicación lógica nativa de PostgreSQL para replicar objetos de datos. La replicación lógica permite un control preciso sobre la replicación de datos, incluida la replicación de datos de nivel de tabla.
   2. El uso de la extensión pglogical que proporciona replicación lógica de streaming y funcionalidades adicionales, como la copia del esquema inicial de la base de datos, la compatibilidad con TRUNCATE, la capacidad de replicar DDL, etc.

2. La descodificación lógica que se implementa mediante la descodificación del contenido del registro de escritura previa (WAL).

Comparación de la replicación lógica y la descodificación lógica

La replicación lógica y la descodificación lógica tienen varias similitudes. Ambas funcionalidades:

• Permiten replicar datos de Postgres.

• Usan el registro de escritura previa (WAL) como origen de los cambios

• Usan ranuras de replicación lógica para enviar datos. Una ranura representa una secuencia de cambios.

• Usan la propiedad REPLICA IDENTITY de una tabla para determinar qué cambios se pueden enviar.

• No replique cambios de DDL.

Las dos tecnologías tienen sus diferencias:

Replicación lógica:

• permite especificar una tabla o un conjunto de tablas que se van a replicar;

Descodificación lógica:

• extrae los cambios de todas las tablas de una base de datos;

Requisitos previos para la comparación de la replicación lógica y la descodificación lógica

1. Vaya a la página de parámetros de servidor en el portal.

2. Configure el parámetro wal_level del servidor como logical.

3. Si quiere usar la extensión pglogical, localice los parámetros shared_preload_libraries y azure.extensions, y seleccione pglogical en el cuadro de la lista desplegable.

4. Actualice el valor del parámetro max_worker_processes a 16, como mínimo. De lo contrario, es posible que encuentre problemas como WARNING: out of background worker slots.

5. Guarde los cambios y reinicie el servidor para aplicar los cambios.

6. Confirme que la instancia de servidor flexible de Azure Database for PostgreSQL permite el tráfico de red desde el recurso de conexión.

7. Conceda al usuario administrador permisos de replicación.

   ALTER ROLE <adminname> WITH REPLICATION;
   

8. Es posible que quiera asegurarse de que el rol que usa tenga privilegios en el esquema que va a replicar. De lo contrario, puede encontrarse con errores, como Permission denied for schema.

Uso de la replicación lógica y la descodificación lógica

El uso de la replicación lógica nativa es la manera más sencilla de replicar datos fuera del servidor flexible de Azure Database for PostgreSQL. Puede usar la interfaz SQL o el protocolo de streaming para consumir los cambios. También puede usar la interfaz SQL para consumir cambios mediante la descodificación lógica.

Replicación lógica nativa

La replicación lógica utiliza los términos "publicador" y "suscriptor".

• El publicador es la base de datos del servidor flexible de Azure Database for PostgreSQL desde la que está enviando datos.
• El suscriptor es la base de datos del servidor flexible de Azure Database for PostgreSQL a la que se envían los datos.

A continuación se muestra código de ejemplo que puede usar para probar la replicación lógica.

1. Conéctese a la base de datos del publicador. Cree una tabla y agregue algunos datos.

   CREATE TABLE basic (id INTEGER NOT NULL PRIMARY KEY, a TEXT);
   INSERT INTO basic VALUES (1, 'apple');
   INSERT INTO basic VALUES (2, 'banana');
   

2. Cree una publicación para la tabla.

   CREATE PUBLICATION pub FOR TABLE basic;
   

3. Conéctese a la base de datos del suscriptor. Cree una tabla con el mismo esquema que en el publicador.

   CREATE TABLE basic (id INTEGER NOT NULL PRIMARY KEY, a TEXT);
   

4. Cree una suscripción que se conecte a la publicación que creó anteriormente.

   CREATE SUBSCRIPTION sub CONNECTION 'host=<server>.postgres.database.azure.com user=<rep_user> dbname=<dbname> password=<password>' PUBLICATION pub;
   

5. Ahora puede consultar la tabla en el suscriptor. Verá que ha recibido los datos del editor.

   SELECT * FROM basic;
   

   Puede agregar más filas a la tabla del publicador y ver los cambios en el suscriptor.

   Si no puede ver los datos, habilite el privilegio de inicio de sesión para azure_pg_admin y compruebe el contenido de la tabla.

   ALTER ROLE azure_pg_admin login;
   

Visite la documentación de PostgreSQL para comprender mejor la replicación lógica.

Uso de la replicación lógica entre bases de datos en el mismo servidor

Cuando tenga como objetivo configurar la replicación lógica entre diferentes bases de datos que residen en la misma instancia de servidor flexible de Azure Database for PostgreSQL, es esencial seguir instrucciones específicas para evitar restricciones de implementación que están presentes actualmente. A partir de ahora, la creación de una suscripción que se conecte al mismo clúster de base de datos solo se realizará correctamente si la ranura de replicación no se crea en el mismo comando; de lo contrario, la llamada CREATE SUBSCRIPTION se bloquea en un evento de espera LibPQWalReceiverReceive. Esto sucede debido a una restricción existente en el motor de Postgres, que podría quitarse en futuras versiones.

Para configurar eficazmente la replicación lógica entre las bases de datos de "origen" y "destino" en el mismo servidor mientras se evita esta restricción, siga los pasos que se describen a continuación:

En primer lugar, cree una tabla denominada "basic" con un esquema idéntico en las bases de datos de origen y de destino:

-- Run this on both source and target databases
CREATE TABLE basic (id INTEGER NOT NULL PRIMARY KEY, a TEXT);

A continuación, en la base de datos de origen, cree una publicación para la tabla y cree por separado una ranura de replicación lógica mediante la función pg_create_logical_replication_slot, lo que ayuda a evitar el problema pendiente que se produce normalmente cuando se crea la ranura en el mismo comando que la suscripción. Debe usar el complemento pgoutput:

-- Run this on the source database
CREATE PUBLICATION pub FOR TABLE basic;
SELECT pg_create_logical_replication_slot('myslot', 'pgoutput');

Después, en la base de datos de destino, cree una suscripción a la publicación creada anteriormente, asegurándose de que create_slot está establecido en false para evitar que el servidor flexible de Azure Database for PostgreSQL cree una nueva ranura y especifique correctamente el nombre de ranura que se creó en el paso anterior. Antes de ejecutar el comando, reemplace los marcadores de posición de la cadena de conexión por las credenciales reales de la base de datos:

-- Run this on the target database
CREATE SUBSCRIPTION sub
   CONNECTION 'dbname=<source dbname> host=<server>.postgres.database.azure.com port=5432 user=<rep_user> password=<password>'
   PUBLICATION pub
   WITH (create_slot = false, slot_name='myslot');

Después de configurar la replicación lógica, ahora la puede probar insertando un nuevo registro en la tabla "basic" de la base de datos de origen y comprobando que se replica en la base de datos de destino:

-- Run this on the source database
INSERT INTO basic SELECT 3, 'mango';

-- Run this on the target database
TABLE basic;

Si todo está configurado correctamente, debe presenciar el nuevo registro de la base de datos de origen en la base de datos de destino, lo que confirma la configuración correcta de la replicación lógica.

Extensión pglogical

Este es un ejemplo de configuración de pglogical en el servidor de bases de datos de proveedor y suscriptor. Para más información, consulte la documentación de la extensión pglogical. Asegúrese también de que ha realizado las tareas de requisitos previos enumeradas anteriormente.

1. Instale la extensión pglogical en los servidores de bases de datos del proveedor y el suscriptor.

   \c myDB
   CREATE EXTENSION pglogical;
   

2. Si el usuario de replicación no es el usuario de administración del servidor (que creó el servidor), asegúrese de conceder pertenencia a un rol azure_pg_admin al usuario y asignar atributos REPLICATION e LOGIN al usuario. Para más información, consulte la documentación de pglogical.

   GRANT azure_pg_admin to myUser;
   ALTER ROLE myUser REPLICATION LOGIN;
   

3. En el servidor de bases de datos del proveedor (origen/publicador), cree el nodo de proveedor.

   select pglogical.create_node( node_name := 'provider1',
   dsn := ' host=myProviderServer.postgres.database.azure.com port=5432 dbname=myDB user=myUser password=myPassword');
   

4. Cree un conjunto de replicación.

   select pglogical.create_replication_set('myreplicationset');
   

5. Agregue todas las tablas de la base de datos al conjunto de replicación.

   SELECT pglogical.replication_set_add_all_tables('myreplicationset', '{public}'::text[]);
   

   Como método alternativo, también puede agregar tablas de un esquema específico (por ejemplo, testUser) a un conjunto de replicación predeterminado.

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

6. En el servidor de bases de datos del suscriptor, cree un nodo de suscriptor.

   select pglogical.create_node( node_name := 'subscriber1',
   dsn := ' host=mySubscriberServer.postgres.database.azure.com port=5432 dbname=myDB user=myUser password=myPasword' );
   

7. Cree una suscripción para iniciar el proceso de sincronización y replicación.

   select pglogical.create_subscription (
   subscription_name := 'subscription1',
   replication_sets := array['myreplicationset'],
   provider_dsn := 'host=myProviderServer.postgres.database.azure.com port=5432 dbname=myDB user=myUser password=myPassword');
   

8. A continuación, puede comprobar el estado de la suscripción.

   SELECT subscription_name, status FROM pglogical.show_subscription_status();
   

Descodificación lógica

La descodificación lógica se puede consumir mediante el protocolo de streaming o la interfaz SQL.

Protocolo de streaming

El consumo de los cambios mediante el protocolo de streaming suele ser preferible. Puede crear su propio consumidor o conector o usar un servicio de terceros como Debezium.

Consulte la documentación de wal2json para obtener un ejemplo de uso del protocolo de streaming con pg_recvlogical.

Interfaz SQL

En el ejemplo siguiente, usamos la interfaz SQL con el complemento wal2json.

1. Cree una ranura.

   SELECT * FROM pg_create_logical_replication_slot('test_slot', 'wal2json');
   

2. Emita comandos SQL. Por ejemplo:

   CREATE TABLE a_table (
      id varchar(40) NOT NULL,
      item varchar(40),
      PRIMARY KEY (id)
   );
   
   INSERT INTO a_table (id, item) VALUES ('id1', 'item1');
   DELETE FROM a_table WHERE id='id1';
   

3. Consuma los cambios.

   SELECT data FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'pretty-print', '1');
   

   El resultado tendrá una apariencia similar a la siguiente:

   {
         "change": [
         ]
   }
   {
         "change": [
                  {
                           "kind": "insert",
                           "schema": "public",
                           "table": "a_table",
                           "columnnames": ["id", "item"],
                           "columntypes": ["character varying(40)", "character varying(40)"],
                           "columnvalues": ["id1", "item1"]
                  }
         ]
   }
   {
         "change": [
                  {
                           "kind": "delete",
                           "schema": "public",
                           "table": "a_table",
                           "oldkeys": {
                                 "keynames": ["id"],
                                 "keytypes": ["character varying(40)"],
                                 "keyvalues": ["id1"]
                           }
                  }
         ]
   }
   

4. Elimine la ranura cuando haya terminado de usarla.

   SELECT pg_drop_replication_slot('test_slot');
   

Visite la documentación de PostgreSQL para comprender mejor la descodificación lógica.

Supervisión

Debe supervisar la descodificación lógica. Se debe eliminar cualquier ranura de replicación no utilizada. Las ranuras se conservan en los registros de WAL de Postgres y los catálogos del sistema correspondientes hasta que se hayan leído los cambios. Si se produce un error en el consumidor o el suscriptor, o si no se ha configurado correctamente, los registros no consumidos se acumulan y llenan el espacio de almacenamiento. Además, los registros no consumidos aumentan el riesgo de un salto del identificador de transacción. Ambas situaciones pueden hacer que el servidor deje de estar disponible. Por lo tanto, las ranuras de replicación lógica deben consumirse continuamente. Si ya no se usa una ranura de replicación lógica, elimínela inmediatamente.

La columna "active" en la vista pg_replication_slots indica si hay un consumidor conectado a una ranura.

SELECT * FROM pg_replication_slots;

Establezca alertas para las métricas Máximo de identificadores de transacción usados y Almacenamiento usado del servidor flexible de Azure DB for PostgreSQL para recibir una notificación cuando los valores aumenten por encima de los umbrales normales.

Limitaciones

• Las limitaciones de la replicación lógica se aplican como se documenta aquí.

• Ranuras y conmutación por error de alta disponibilidad: al usar servidores habilitados para alta disponibilidad (HA) con el servidor flexible de Azure Database for PostgreSQL, tenga en cuenta que las ranuras de replicación lógica no se conservan durante los eventos de conmutación por error. Para mantener ranuras de replicación lógica y garantizar la coherencia de los datos después de una conmutación por error, se recomienda usar la extensión Pg Failover Slots. Para más información sobre cómo habilitar esta extensión, consulte la documentación.

Contenido relacionado

• Opciones de redes
• extensions
• Alta disponibilidad