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:
-
Retrieves the
last_value
of each relevant sequence from the primary database. -
Updates the corresponding sequence on the DR side to match the primary.
-
Executes automatically every one minute using the
pg_cron
extension.
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
- Managing sequences in pglogical replication for DR setup in OCI Database with PostgreSQL.
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.
-
pg_cron
-
dblink
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:
- Replace
<hostname>
,<username>
, and<password>
with actual values securely.- Use the
<hostname>
to represent the FQDN of the DB system.
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:
- Ensure the
pg_cron
job is enabled only on the DR system.- Adjust the job schedule if required.
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:
-
In the new DR (formerly primary), enable the
pg_cron
job. -
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:
Useful commands:
-- List all scheduled jobs and get job ID SELECT jobid, schedule, command, active FROM cron.job; -- Disable a job SELECT cron.alter_job(jobid := <job_id>, active := false); -- Enable a job SELECT cron.alter_job(jobid := <job_id>, active := true);
Adjust the procedure and job definition based on your environment-specific requirements.
Thoroughly test the procedure in a development environment before deploying it to production.
Related Links
Acknowledgments
- Author - Arvind Yadav (Principal Member Technical Staff)
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.
Manage Sequences in pglogical Replication for Disaster Recovery Setup in OCI Database with PostgreSQL
G39321-01
Copyright ©2025, Oracle and/or its affiliates.