Gestisci le sequenze nella replica pglogica per l'impostazione del disaster recovery nel database OCI con PostgreSQL

Introduzione

Questa esercitazione descrive una soluzione personalizzata per la sincronizzazione dei valori di sequenza PostgreSQL in un'impostazione bidirezionale pglogica di replica tra sistemi primari e di disaster recovery (DR).

Esposizione del problema:

Nella configurazione corrente, è stata configurata la replica bidirezionale pglogica. Alcune delle tabelle replicate si basano su sequenze; tuttavia, abbiamo osservato che il file last_value di queste sequenze non viene replicato tra gli ambienti primari e DR.

Questo comportamento è previsto e non è considerato un bug in pglogical. Per impostazione predefinita, i valori di sequenza non vengono replicati, poiché le sequenze vengono considerate non transazionali e intrinsecamente specifiche dei nodi per evitare conflitti.

Per colmare questa lacuna, abbiamo implementato una soluzione personalizzata per sincronizzare i valori di sequenza tra i sistemi primari e DR. In particolare, stiamo sviluppando una procedura che:

Questo approccio garantisce che i valori di sequenza rimangano coerenti in entrambi i sistemi, evitando così potenziali problemi di integrità dei dati o conflitti principalmente nell'ambiente DR.

Obiettivi

Task 1: Impostazione replica bidirezionale pglogica

Assicurarsi che la replica bidirezionale pglogica sia già configurata tra i sistemi primario e DR.

Se non è ancora configurato, attenersi alla documentazione ufficiale per impostare pglogical di conseguenza: Imposta replica estensione pglogical nel database di origine.

Task 2: Abilita estensioni obbligatorie

Sia nei sistemi primari che in quelli DR, abilitare le estensioni riportate di seguito creando la configurazione da OCI Console.

Dopo aver applicato il file di configurazione su DBSystems, connettersi al database postgres su ogni DBSystem ed eseguire la query seguente.

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

Task 3: Creazione della procedura

Creare la procedura sia sui sistemi primari che su quelli DR nel database postgres.

Definizione procedura:

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:

Task 4: Creare un job pg_cron nel sistema DR

Eseguire la query seguente per creare un job pg_cron e pianificare la procedura per l'esecuzione ogni minuto nel sistema di database DR.

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

Note

Task 5: Creare un job pg_cron nel sistema primario e mantenerlo disabilitato

Eseguire la query seguente per creare un job pg_cron e disabilitarlo.

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

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

Task 6: Gestione del failover

In caso di failover, effettuare le operazioni riportate di seguito.

  1. Nel nuovo DR (precedentemente principale), abilitare il job pg_cron.

  2. Nel nuovo job primario (in precedenza DR), disabilitare il job pg_cron.

Ciò garantisce che il processo di sincronizzazione venga sempre eseguito solo sul sistema DR corrente.

Nota:

Conferme

Altre risorse di apprendimento

Esplora altri laboratori su docs.oracle.com/learn o accedi a più contenuti di formazione gratuiti sul canale YouTube di Oracle Learning. Inoltre, visitare education.oracle.com/learning-explorer per diventare Oracle Learning Explorer.

Per la documentazione del prodotto, visitare Oracle Help Center.