Note:

Cross-Region Synchronization of OCI Database with PostgreSQL using the pglogical Extension

Introduction

The pglogical extension enables logical replication across multiple Oracle Cloud Infrastructure (OCI) Database with PostgreSQL instances. It supports cross-version PostgreSQL replication and facilitates data synchronization between databases deployed across both Regional and Local Availability Domains within OCI. Additionally, it allows replication between databases hosted on multi cloud provider’s managed PostgreSQL service and self-managed community PostgreSQL instances, whether running in the cloud or on-premises, offering a highly flexible and interoperable replication solution.

Utilizing a publish-subscribe model, pglogical enables logical streaming replication to replicate changes to tables, sequences, and other objects from a publisher to a subscriber. This extension is ideal for a variety of use cases, including:

Limitations

The pglogical extension may not be suitable for certain use cases due to the following limitations:

For more information, see Limitations and Restrictions.

Objectives

Prerequisites

Task 1: Establish Communication between Databases

To connect to the DB system across regions, VCN peering must be set up.

To create VCN Peering using Dynamic Routing Gateway(DRG), follow the sub-tasks:

Task 1.1: Attach DRG to VCNs

  1. Create VCN in source region and target region as per your requirement.

  2. Create dynamic routing gateways (DRG) in source and target regions as per your requirement.

  3. To attach DRG to VCN, select your DRG in source region. click VCN attachments and Create virtual cloud network attachment.

  4. In the Create VCN attachment page, enter the attachment name and select VCN-A (Source Region VCN), then click Create VCN attachment.

  5. Repeat the same steps to attach your target VCN.

For more information, see Dynamic Routing Gateways.

Task 1.2: Create Remote Peering Connection (RPC)

  1. Create source region RPC (SOR-RPC).

  2. Go to the SOURCE DRG detail page and click Remote peering connection attachments.

  3. Click Create remote peering connection.

  4. In the Create remote peering connection page, enter the connection name and select compartment, then click Create remote peering connection.

  5. To create target region RPC (TAR-RPC), repeat the same procedure done above to create the target region RPC.

Task 1.3: Establish RPC Connection

Establish the connection from the Source region to the Target region through the SOR-RPC connection.

  1. Go to the SOURCE DRG detail page and click Remote peering connection attachments.

  2. View the details of SOR-RPC by clicking the name of the connection in the Remote Peering Connection column.

  3. In the connection details page, click Establish Connection, enter the connection name, and select compartment.

  4. In the Establish connection page, select the your target region name region and enter the OCID of target RPC (TAR-RPC). When The connection is established, the RPC’s state changes to PEERED.

    Hence, TAR-RPC peering state also changes to PEERED.

Task 1.4: Configure Route Table in VCNs to Send Traffic Destined to DRG Attachment

  1. Configure route table in Source-VCN to send traffic to Target-VCN private subnet CIDR.

  2. Go to the Source-VCN detail page and click Route Tables.

  3. Under the list of route tables, click route table for private subnet-Source-VCN.

  4. In the route table page, click Add Route Rules and enter the route rule information as per your requirement.

  5. To configure route table in Target-VCN to send traffic to Source-VCN’s private subnet CIDR, repeat the same steps done above to configure the route table for Source-VCN’s private subnet.

Task 1.5: Add security Ingress Rule to Allow Traffic between VCNs’ Private Subnets through DRG

  1. Add ingress rule to security list for private subnet-Source-VCN of the source VCN to allow traffic coming from Target-VCN-private subnet to Source-VCN-private subnet

  2. Go to the Source-VCN detail page and click Security List and Security list for private subnet-Source-VCN.

  3. In the Security List page, click Add Ingress Rules and enter ingress rule information as per your requirement.

  4. To add ingress rule to security list for private subnet-Target-VCN of the Target-VCN to allow traffic coming from Source-VCN-private subnet to Target-VCN-private subnet, repeat the same steps performed above to add the Ingress rule for Target-VCN, but use the appropriate Ingress rule.

Task 1.6: Cross-Region Database Connectivity Testing and Validation

Connect to the Source-VM to verify connectivity to the target database, and simultaneously validate the connectivity of the source database from the target VM to ensure bidirectional communication.

Task 2: Configure A-record Entry

An A-record entry must be configured to resolve the Fully Qualified Domain Name (FQDN) of the database system across regions. In this tutorial, the Mumbai region serves as the target region.

  1. Switch to the target region (Mumbai), enter DNS in the search bar and select Private Views.

  2. The VCN list for the current region will be displayed. Select the VCN where DRG is already configured.

  3. The list of DNS zones will appear, including the FQDN of the database system in Mumbai.

  4. Add an entry for the remote database system.

    1. Click Create Zone.

      image

    2. In Zone Name, enter the FQDN of the database system in the other region (Ashburn).

    3. Click Create to add the new zone entry.

  5. Configure the A-record entry.

    1. Open the newly created zone, click Manage Records and select Add Record.

    2. Enter the following information.

      • Name: Enter a name.

      • Type: Select A (IPv4 Address).

      • TTL: Enter 3600 seconds (modify as per your application requirement).

      • RDATA Mode: Select Basic.

      • Address: Enter the primary endpoint IP address of the database system in the other region.

  6. Click Save Changes and then Publish Changes to apply the configuration.

    With this setup, database system B (Mumbai) can now resolve and connect to database system A (Ashburn) using the FQDN, enabling seamless cross-region database communication.

Task 3: Create Database Systems Across the Region

Deploy a PostgreSQL database across regions in OCI to establish a robust Disaster Recovery (DR) strategy. For more information about creating a PostgreSQL database in OCI, see Creating a Database System.

Task 4: Enable the pglogical Extension Across the Region

This tutorial demonstrates the implementation of DR by designating Ashburn as the source region and Mumbai as the target region.

  1. In the Ashburn region, log in to the OCI Console, navigate to the PostgreSQL database, select your database system, and modify the configuration settings by accessing the configuration file.

    image

  2. You will be redirected to the complete configuration file. Select Copy Configuration to proceed, which will display the following page.

    image

  3. Enter the required information based on your specifications. Then, navigate to User Variables (Read/Write), click Add Another Variable, and select Variable name as wal_level and track_commit_timestamp and Variable value as logical and 1 respectively.

  4. In Configure Extensions, select pglogical as PostgreSQL extensions.

  5. Click Create to generate the configuration file.

    image

  6. Navigate to your database system, select Edit under Configuration, and apply the previously created configuration to your database system.

  7. The database system will show Updating state. Wait until it becomes Active before proceeding.

  8. Once active, log in to your database system and verify the enabled extensions using the following query.

    SHOW oci.admin_enabled_extensions;

  9. Create the pglogical extension using the following command.

    CREATE EXTENSION pglogical;

    The following is a sample output of the commands.

    pglogical_source=> show oci.admin_enabled_extensions ;
oci.admin_enabled_extensions
------------------------------
pglogical
(1 row)

pglogical_source=> create extension pglogical;
CREATE EXTENSION

  10. Repeat the same procedure in the other (Mumbai) region database to enable the pglogical extension.

Task 5: Configure the Source Database

Run the following query to grant permissions on the source database to enable logical replication.

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;

Please note that ‘psql’ is the sample user created during the database setup process.

Task 6: Set up pglogical Extension Replication in the Source Database

  1. Create the publisher node on the source database.

    SELECT pglogical.create_node(node_name := 'provider1',dsn :='host=primary.xxxxxxxxx.postgresql.us-ashburn-1.oci.oraclecloud.com port=5432 user=xxxx password=xxxxx dbname=pglogical_source');
    • node_name: Specify the name of the publisher to be created on the source database.
    • host: Enter the fully qualified domain name (FQDN) of the source database.
    • port_number: Provide the port on which the source database is running.
    • database_name: Specify the database where the publication will be created.

  2. Include all tables in the public schema to the default replication set.

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

Task 7: Configure Target Database

Run the following query to grant permissions on the target database to enable logical replication.

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;

Please note that ‘psql’ is the sample user created during the database setup process.

After granting the necessary permissions, replicate all required objects in the target database to match the source database structure.

Task 8: Set up the pglogical Replication in Target Database

  1. Create the subscriber node on target database.

    SELECT pglogical.create_node(node_name := 'subscriber1',dsn :='host=primary.xxxxxxx.postgresql.ap-mumbai-1.oci.oraclecloud.com port=5432 user=xxxx password=xxxxx dbname=pglogical_target');
    • node_name: Define the name of the subscriber on the target database.
    • host: Enter the fully qualified domain name (FQDN) of the target database.
    • port_number: Enter the port on which the target database is running.
    • database_name: Provide the name of the database where the subscription will be created.

  2. Create the subscription on the subscriber node, which will initiate the background synchronization and replication processes.

    SELECT pglogical.create_subscription(subscription_name := 'subscription1',provider_dsn := 'host=primary.xxxxxxxx.postgresql.us-ashburn-1.oci.oraclecloud.com port=5432 user=xxxx password=xxxxxx dbname=pglogical_source sslmode=verify-full sslrootcert=/etc/opt/postgresql/ca-bundle.pem');

SELECT pglogical.wait_for_subscription_sync_complete('subscription1');
    • subscription_name: Provide the name of the subscription.
    • host: Provide the FQDN of the source database.
    • port_number: Provide the port on which the target database is running.
    • database_name: Provide the name of the source database.

    Note: Ensure to use sslmode=verify-full and sslrootcert = /etc/opt/postgresql/ca-bundle.pem in subscription creation string to prevent any connection failures.

Task 9: Verify the Subscription Status

Run the following statement to check the status of your subscription on your target database.

select * from pglogical.show_subscription_status();

Task 10: Verify the Data Replication is Running

Run the following statement to check the status of your replication on your source database.

SELECT * FROM pg_stat_replication;

Task 11: (Optional) Stop or Start the Replication

Note: In subscription_name, enter the name of the subscription created at target.

Task 12: (Optional) Drop the Subscription

To drop the subscription on your target database, run the following command.

select pglogical.drop_subscription('subscription_name');

Note: In subscription_name, enter the name of the subscription created at target.

Troubleshooting

Next Steps

This tutorial provides a comprehensive approach in implementing a DR solution across regions in OCI Database with PostgreSQL using the pglogical extension. It covers the configuration process, data replication, and troubleshooting common issues.

Additionally, this method can be applied to PostgreSQL upgrades, ensuring minimal downtime and enabling smooth application cutover once the target database is fully synchronized with the source.

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.