Gestionar secuencias en replicación pgógica para configuración de recuperación ante desastres en OCI Database with PostgreSQL

Introducción

En este tutorial se describe una solución personalizada para sincronizar los valores de secuencia PostgreSQL en una configuración de replicación bidireccional lógica entre los sistemas principal y de recuperación ante desastres (DR).

Explicación del problema:

En nuestra configuración actual, hemos configurado la replicación bidireccional lógica. Algunas de las tablas replicadas se basan en secuencias; sin embargo, hemos observado que el last_value de estas secuencias no se replica entre los entornos principal y de DR.

Este es el comportamiento esperado y no se considera un error en pglogical. Por diseño, los valores de secuencia no se replican, ya que las secuencias se consideran no transaccionales e inherentemente específicas del nodo para evitar conflictos.

Para hacer frente a esta brecha, hemos implementado una solución personalizada para sincronizar los valores de secuencia entre los sistemas principal y DR. En concreto, estamos desarrollando un procedimiento que:

Este enfoque garantiza que los valores de secuencia permanezcan consistentes en ambos sistemas, lo que evita posibles problemas de integridad de datos o conflictos, principalmente en el entorno de DR.

Objetivos

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

Asegúrese de que la replicación bidireccional pgógica ya esté configurada entre los sistemas principal y de DR.

Si aún no está configurado, siga la documentación oficial para configurar pglogical según corresponda: Configurar la replicación de extensión pglogical en la base de datos de origen.

Tarea 2: Activar extensiones necesarias

En los sistemas principal y de DR, active las siguientes extensiones mediante la creación de la configuración desde la consola de OCI.

Una vez que haya aplicado el archivo de configuración en DBSystems, conéctese a la base de datos postgres en cada DBSystem y ejecute la siguiente consulta.

CREATE EXTENSION IF NOT EXISTS pg_cron;
CREATE EXTENSION IF NOT EXISTS dblink;

Tarea 3: Creación del procedimiento

Cree el procedimiento en los sistemas principal y de DR en la base de datos postgres.

Definición de procedimiento:

CREATE OR REPLACE PROCEDURE sync_sequences_from_remote()
LANGUAGE plpgsql
AS $$
DECLARE
  -- Update the connection string with correct credentials and hostname
  conn_str TEXT := 'host=<hostname> user=<username> password=<password> dbname=postgres';

  row RECORD;
  remote_seq_query TEXT :=
    $_$SELECT schemaname, sequencename, last_value
       FROM pg_sequences
       WHERE schemaname !~ 'pg_catalog|cron|information_schema|pg_toast|pg_temp'
         AND last_value IS NOT NULL$_$;
  full_seq_name TEXT;
  setval_sql TEXT;
BEGIN
  FOR row IN
    SELECT * FROM dblink(conn_str, remote_seq_query)
      AS t(schemaname TEXT, sequencename TEXT, last_value BIGINT)
  LOOP
    full_seq_name := quote_ident(row.schemaname) || '.' || quote_ident(row.sequencename);
    setval_sql := FORMAT('SELECT setval(%L, %s, true);', full_seq_name, row.last_value);
    RAISE NOTICE 'Setting sequence: %', setval_sql;
    EXECUTE setval_sql;
  END LOOP;
END;
$$;

Nota:

Tarea 4: Creación de un trabajo pg_cron en el sistema de DR

Ejecute la siguiente consulta para crear un trabajo pg_cron y programar el procedimiento para que se ejecute cada minuto en el sistema de base de datos de DR.

SELECT cron.schedule('UPDATE SEQUENCE', '*/1 * * * *', $$ CALL sync_sequences_from_remote(); $$);

Notas:

Tarea 5: Crear un trabajo pg_cron en el sistema principal y mantenerlo desactivado

Ejecute la siguiente consulta para crear un trabajo pg_cron y desactivarlo.

SELECT cron.schedule('*/1 * * * *', $$ CALL sync_sequences_from_remote() $$);

-- Disable the job (on Primary)
SELECT cron.alter_job(jobid := <job_id>, active := false);

Tarea 6: Manejo del failover

En caso de failover, siga estos pasos:

  1. En la nueva DR (anteriormente principal), active el trabajo pg_cron.

  2. En el nuevo trabajo principal (anteriormente DR), desactive el trabajo pg_cron.

Esto garantiza que el proceso de sincronización siempre se ejecute solo en el sistema de DR actual.

Nota:

Acuses de recibo

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.