Manage Sequences in pglogical Replication for Disaster Recovery Setup in OCI Database with PostgreSQL

Introduction

This tutorial outlines a custom solution for synchronizing PostgreSQL sequence values in a pglogical bidirectional replication setup between primary and Disaster Recovery (DR) systems.

Problem Statement:

In our current setup, we have configured pglogical bidirectional replication. Some of the replicated tables rely on sequences; however, we have observed that the last_value of these sequences is not replicated between the primary and DR environments.

This is expected behavior and is not considered a bug in pglogical. By design, sequence values are not replicated, as sequences are considered non-transactional and inherently node-specific to avoid conflicts.

To address this gap, we have implemented a custom solution to synchronize the sequence values between the primary and DR systems. Specifically, we are developing a procedure that:

This approach ensures that the sequence values remain consistent across both systems, thereby preventing potential data integrity or conflict issues mainly in the DR environment.

Objectives

Task 1: Set up pglogical Bidirectional Replication

Ensure that pglogical bidirectional replication is already configured between the primary and DR systems.

If not yet configured, follow the official documentation to set up pglogical accordingly: Set up pglogical Extension Replication in the Source Database.

Task 2: Enable Required Extensions

In both primary and DR systems, enable the following extensions by creating the configuration from the OCI Console.

Once you have applied the configuration file on DBSystems, connect to the postgres database on each DBSystem and run the following query.

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

Task 3: Create the Procedure

Create the procedure on both primary and DR systems in the postgres database.

Procedure Definition:

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;
$$;

Note:

Task 4: Create a pg_cron Job in DR System

Run the following query to create a pg_cron job and schedule the procedure to run every one minute on the DR database system.

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

Notes:

Task 5: Create a pg_cron Job in Primary System and Keep It Disabled

Run the following query to create a pg_cron job and disable it.

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: Handle the Failover

In the event of a failover, follow these steps:

  1. In the new DR (formerly primary), enable the pg_cron job.

  2. In the new primary (formerly DR), disable the pg_cron job.

This ensures the synchronization process always runs only on the current DR system.

Note:

Acknowledgments

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.