Bi-directional replication in OCI Database for PostgreSQL using pglogical

Introduction

Logical replication in OCI PostgreSQL allows for replicating data objects and their changes based on a table’s replication identity, typically the primary key. It uses a publish-subscribe model, where one or more subscribers subscribe to publications defined on a publisher node. Subscribers pull data from these publications and can optionally re-publish the changes to enable cascading replication or more complex topologies.

Logical replication streams row-level changes—INSERT, UPDATE, and DELETE operations—by decoding the WAL (Write-Ahead Log) using logical decoding. These decoded changes are independent of OCI PostgreSQL’s physical storage format and represent only the logical data operations, providing greater flexibility for downstream systems.

In this tutorial, we’ll discuss how to use the pglogical extension for logical replication and how it enables bi-directional replication between two Database Systems in OCI Database for PostgreSQL.

Bi-directional replication

Bi-directional replication (BDR) with pglogical means that two or more OCI PostgreSQL Database Systems both publish and subscribe to each other’s changes. In other words, each OCI PostgreSQL Database System can read and write, and all changes are replicated in both directions. Consequently, any modifications performed on either of the OCI PostgreSQL Database Systems will be mirrored and implemented on both.

Bi-directional replication is typically recommended when you need a regional or global database architecture that delivers low-latency read and write access for users in multiple locations. By co-locating read/write instances near users in each region, changes can be made locally and then replicated to other regions—ensuring data is synchronized across all nodes.

In practice, most use cases only require a small subset of tables to be writable from multiple regions, while the majority of the database can remain read-only or managed centrally through unidirectional replication.

However, bi-directional replication introduces complexity. Maintaining data consistency and managing the replication topology can become challenging as the system grows.

If your application’s main goals are read scalability and resilience to regional outages, a simpler and more robust approach is to deploy a high availability OCI Database for PostgreSQL instance with multiple read replicas, either in the same region or across regions.

How does bi-directional pglogical Work

In pglogical, you can configure multiple subscriptions and publications like this:

• Database System 1 publishes changes → Database System 2 subscribes

• Database System 2 publishes changes → Database System 1 subscribes

Each node:

• Sends its changes to the other

• Applies changes from the other

• Handles potential conflicts using pglogical’s conflict resolution settings

To learn more about uni-directional pglogical replication across regions in OCI PostgreSQL, please refer to this blog: Unidirectional pglogical across region.

Managing Conflicts and Configuration

The publisher (the source database) defines one or more publications and sends change data (DML operations) to the subscriber (the target database). A subscriber can connect to multiple publishers and apply their changes to its local database.

The pglogical extension provides advanced conflict resolution through the pglogical.conflict_resolution parameter, which supports the following modes:

• apply_remote (default in OCI Database for PostgreSQL)

• error

• keep_local

• last_update_wins

• first_update_wins

For more details on conflict_resolution in pglogical, please refer to the official documentation on github: 2nd Quadrant Configuration options.

When using keep_local, last_update_wins, or first_update_wins, track_commit_timestamps must be enabled on both the publisher and the subscriber. Keep in mind that enabling this setting can introduce measurable performance overhead, depending on your workload. It is strongly recommended to evaluate the performance impact in your production environment before deploying.

Setting up bi-directional replication in OCI PostgreSQL

To enable pglogical extension in OCI Database for PostgreSQL, please refer to this tutorial: Enable pglogical extension in OCI PostgreSQL.

Task 1: Prerequisites

Once the pglogical extension is created in your OCI Database for PostgreSQL, Grant the following permission on the database system #1 and database system #2,

ALTER ROLE psql WITH REPLICATION;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_replication_origin_session_reset() TO psql; 
GRANT EXECUTE ON FUNCTION pg_catalog.pg_replication_origin_session_setup TO psql; 
GRANT ALL ON FUNCTION pg_catalog.pg_replication_origin_session_setup TO psql;

Note: psql is a sample replication user created specifically for demonstration purposes in this tutorial.

Task 2: Configuring Uni-Directional logical Replication

On Database System 1:

Once you have completed the prerequisites outlined earlier, create a test table and insert a few records to verify the pglogical workflow.

CREATE TABLE test_table (
    id serial PRIMARY KEY,
    data text
);
 
INSERT INTO test_table
    (id, data)
VALUES
    (generate_series(1, 1000), 'Test');

Create the provider node:

The next step is to create a node using the pglogical function create_node.

SELECT pglogical.create_node(
    node_name := 'provider1',
    dsn := 'host=primary.xxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
            port=5432
            user=psql
            password=xxxxx
            dbname=postgres'
);

Below are the parameter definitions for the create_node function:

• node_name: Specify the name of the publisher to be created on the Database System #1.
• host: Enter the fully qualified domain name (FQDN) of the Database System #1.
• port_number: Provide the port on which the Database System #1 is running.
• database_name: Specify the name of the database where pglogical replication will be configured.

As a next step, identify the tables that need to be included in replication. pglogical uses replication sets to manage which tables and actions (INSERT, UPDATE, DELETE) are replicated. You can create a custom replication set using the create_replication_set function or use the predefined default set, which replicates all actions on its assigned tables. To include tables, use replication_set_add_table for individual tables or replication_set_add_all_tables to add all tables from a specific schema.

The following command adds all tables from the public schema to the default replication set:

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

To learn more about replication sets, see Replication sets.

On Database System 2:

Create a matching table as in the Database System #1:

Create the test table in Database System #2 to match the structure of the table in Database System #1.

CREATE TABLE test_table (
    id serial PRIMARY KEY,
    data text
);

Create the subscriber node:

Next, create a node on Database System #2 using the pglogical function create_node, just as you did on Database System #1:

SELECT pglogical.create_node(
    node_name := 'subscriber1',
    dsn := 'host=primary.xxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
            port=5432
            user=psql
            password=xxxxxx
            dbname=postgres'
);

Below are the parameter definitions for the create_node function:

• node_name: Define the name of the subscriber on the database system #2.
• host: Enter the fully qualified domain name (FQDN) of the database system #2.
• port_number: Enter the port on which the database system #2 is running.
• database_name: Provide the name of the database where the subscription will be created.

Create pglogical Subscription:

The next step is to create the subscription to start the replication of data from Database System #1 using the function create_subscription.

The subscription_name parameter can be any name you choose for the subscription. The provider_dsn is the connection string for Database System #1, and the replication_sets parameter specifies which replication sets to use. In this example, we are using the default replication set.

The synchronize_data argument tells pglogical whether or not to copy existing data from the source. In our test, it is set to true because we want all rows to be copied, along with any future changes.

The forward_origins parameter is crucial for enabling bi-directional replication. Setting forward_origins := '{}' ensures that only changes originating from the node itself are forwarded, and not changes that were already replicated from other nodes. This prevents the replication process from entering an infinite loop, where changes are continuously replicated back and forth.

SELECT pglogical.create_subscription(
    subscription_name := 'subscription1',
    provider_dsn := 'host=primary.xxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
                     port=5432
                     sslmode=require
                     dbname=postgres
                     user=psql
                     password=xxxxx',
    replication_sets := ARRAY['default'],
    synchronize_data := true,
    forward_origins := '{}'
);

Check subscription status:

Check the subscription status and basic information on database system #2 using the following command:

select * from pglogical.show_subscription_status();
-[ RECORD 1 ]-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------
subscription_name | subscription1
status            | replicating
provider_node     | provider1
provider_dsn      | host=primary.xxxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com port=5432 sslmode=require dbname=postgres user=psql password=xxxxxx
slot_name         | pgl_postgres_provider1_subscription1
replication_sets  | {default}
forward_origins   |

Task 3: Test the replication from Database System #1 to Database System #2

Now that replication is set up between Database System #1 (source) and Database System #2 (target), let’s test if changes made in Database System #1 are automatically copied to Database System #2.

1. Verify initial data

   On Database System #2, check the table test_table:

   postgres=> SELECT COUNT(*) FROM test_table;
    count 
   -------
     1000
   (1 row)
   

   This confirms the table already has 1000 rows.

2. Test an INSERT

   Add a new row in Database System #1:

   postgres=> INSERT INTO test_table VALUES (10000, 'initial load');
   

3. Check it in Database System #1:

   postgres=> SELECT * FROM test_table WHERE id = 10000;
     id   |     data     
   -------+--------------
   10000 | initial load
   

4. Now check Database System #2. The same row appears there, showing replication works:

   postgres=> SELECT * FROM test_table WHERE id = 10000;
     id   |     data     
   -------+--------------
   10000 | initial load
   

5. Test a DELETE

   Delete the same row in Database System #1:

   postgres=> DELETE FROM test_table WHERE id = 10000;
   

6. Verify in both systems—the row is gone from both.

   (0 rows)
   

7. Test an UPDATE

   Update an existing row in Database System #1:

   postgres=> UPDATE test_table SET data = 'Initial load' WHERE id = 1;
   

   Check Database System #1:

     id   |     data     
   -------+--------------
     1 | Initial load
   

   Check Database System #2—the updated value is also there.

With these tests, we confirm that INSERT, UPDATE, and DELETE operations are correctly replicated from Database System #1 → Database System #2.

Task 4: Configure bi-directional logical replication

The previous tasks set up unidirectional replication from Database System #1 to Database System #2. Next, we will run additional commands to enable replication in the reverse direction as well.

On Database System 2:

Set up a replication set just as you did on Database System #1:

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

On Database System #1:

Create a subscription on Database System #1 so that it starts fetching the changes from Database System #2:

SELECT pglogical.create_subscription(
    subscription_name := 'subscription2',
    provider_dsn := 'host=primary.xxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
                     port=5432
                     sslmode=require
                     dbname=postgres
                     user=psql
                     password=xxxxx',
    replication_sets := ARRAY['default'],
    synchronize_data := false,
    forward_origins := '{}'
);

In the previous command, the synchronize_data argument has been set to false, unlike before. This is because we don’t need to copy existing data from Database System #2 to Database System #1, as both tables already contain identical data.

With this command, the bi-directional replication setup is now complete, and any changes made on one server will be replicated to the other. Next, check the subscription status on Database System #1.

Check the subscription status of Database System #1:

Check the subscription status and basic information on Database System #1 using the following command:

select * from pglogical.show_subscription_status();
-[ RECORD 1 ]-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------
subscription_name | subscription2
status            | replicating
provider_node     | subscriber1
provider_dsn      | host=primary.xxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com port=5432 sslmode=require dbname=postgres user=psql password=xxxxx
slot_name         | pgl_postgres_subscriber1_subscription2
replication_sets  | {default}
forward_origins   |

Task 5: Test bi-directional replication

Let’s begin by adding a row to both Database Systems to verify that bi-directional replication is functioning correctly.

1. Insert a Row in Database System #1:

   Run the following command on Database System #1:

   postgres=# INSERT INTO test_table VALUES (1001, 'Initial_load');
   INSERT 0 1
   

2. Verify the inserted row in Database System #1:

   postgres=# SELECT * FROM test_table WHERE id = 1001;
     id  |     data     
   ------+--------------
   1001 | Initial_load
   (1 row)
   

3. Verify Replication on Database System #2:

   Now, check Database System #2 to confirm that the row was replicated:

   postgres=> SELECT * FROM test_table WHERE id = 1001;
     id  |     data     
   ------+--------------
   1001 | Initial_load
   (1 row)
   

   This shows that the insert on Database System #1 was replicated to Database System #2.

4. Insert a Row in Database System #2:

   Run the following command on Database System #2:

   postgres=> INSERT INTO test_table VALUES (1002, 'Initial_load');
   INSERT 0 1
   

5. Verify the inserted row in Database System #2:

   postgres=> SELECT * FROM test_table WHERE id = 1002;
     id  |     data     
   ------+--------------
   1002 | Initial_load
   (1 row)
   

6. Verify Replication on Database System #1:

   Now, check Database System #1 to confirm that the row was replicated back:

   postgres=# SELECT * FROM test_table WHERE id = 1002;
     id  |     data     
   ------+--------------
   1002 | Initial_load
   (1 row)
   

   This test confirms that data changes replicate successfully in both directions between Database System #1 and Database System #2.

Monitor logical replication lag

Once the pglogical setup is complete, you should monitor the replication log to track replication slots, delay, and other relevant metrics.

select * from pg_stat_replication;

This command returns the current status of replication in OCI PostgreSQL.

Conclusion

This tutorial walks you through the configuration of PostgreSQL bi-directional replication with pglogical. Bi-directional replication adds complexity to your database infrastructure and requires additional effort, so it’s important to confirm that it fits your use case. If your application requires multiple writers across different regions and database sharding is not feasible, bi-directional replication is the ideal solution.

Acknowledgments

• Author - Kaviya Selvaraj (Senior 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.

---

Title and Copyright Information

Bi-directional replication in OCI Database for PostgreSQL using pglogical

G43104-01

Copyright ©2025,

Oracle and/or its affiliates.