Note:

• This tutorial requires access to Oracle Cloud. To sign up for a free account, see Get started with Oracle Cloud Infrastructure Free Tier.
• It uses example values for Oracle Cloud Infrastructure credentials, tenancy, and compartments. When completing your lab, substitute these values with ones specific to your cloud environment.

Migrate Amazon RDS for PostgreSQL Database to OCI Database with PostgreSQL using OCI GoldenGate

Introduction

Oracle Cloud Infrastructure Database with PostgreSQL (OCI Database with PostgreSQL) is a fully managed service that automatically scales storage as database tables are created or dropped. This simplifies management and optimizes storage costs for you. Data is encrypted both in-transit and at rest. OCI GoldenGate supports logical online migration enabling seamless transitions and near zero downtime migration for OCI Database with PostgreSQL service databases.

The following images shows the architecture for migrating an Amazon Relational Database Service PostgreSQL (Amazon RDS for PostgreSQL) database to OCI Database with PostgreSQL service using OCI GoldenGate, the pg_dump and the pg_restore utility.

OCI Database for PostgreSQL currently supports version 14 and 15. In this tutorial, we are using version 15. Metadata migration will be done using the pg_dump and pg_restore utility. Initial load and data replication will be done using OCI GoldenGate.

Note: Make sure connectivity is established between OCI GoldenGate deployment, source and target databases.

Objectives

• Configure Amazon RDS for PostgreSQL.

• Modify parameters in Amazon RDS for PostgreSQL for OCI GoldenGate configuration.

• Configure OCI Database with PostgreSQL.

• Deploy OCI GoldenGate for PostgreSQL deployment type.

• Migrate schema metadata from Amazon RDS for PostgreSQL to OCI Database with PostgreSQL.

• Create connection from OCI GoldenGate deployment for both source Amazon Web Services (AWS) and target OCI.

• Create Extract for initial load.

• Create Extract for data sync and register.

• Start initial load and data sync extract.

• Create Replicat for initial load and start.

• Create Replicat for delta sync.

• Test data synchronization with data insert on source database.

• Issue and troubleshooting.

Prerequisites

• Provision OCI environment like (compartment, virtual cloud network (VCN), subnet and so on).

• Provision Amazon RDS for PostgreSQL.

• Provision OCI Compute VM for PostgreSQL client.

• Provision OCI Database with PostgreSQL.

• Provision OCI GoldenGate big deployment.

Task 1: Create Amazon RDS for PostgreSQL Instance

Log in to the AWS console and create RDS instance for PostgreSQL. Enter the required information as shown in the following images.

Task 2: Create Custom Parameter Group and Modify Required Parameter

Connect to RDS instance and go to the Configuration tab. Find DB instance parameter group, it should be set to default. Now, click Create parameter group to create separate parameter group and enter the required information as shown in the following images.

Task 3: Create OCI Database with PostgreSQL Database

Log in to the OCI Console and go to Databases and click Create database.

Task 4: Create OCI Compute Instance and Install PostgreSQL Client for Connectivity

We need to create OCI Compute instance in the OCI Console and install PostgreSQL using the following command.

sudo dnf install -y postgresql15*
psql --version

Task 5: Test Connectivity from OCI Compute Instance to both Source and Target

1. Go to the AWS console and get the AWS Endpoint for connectivity.

   

2. Run the following command to connect to Amazon RDS for PostgreSQL instance.

   

3. Go to the OCI Console and get the OCI Database with PostgreSQL Endpoint for connectivity.

   

4. Run the following command to connect to OCI Database with PostgreSQL instance.

   

Task 6: Create OCI GoldenGate Deployment and Test Connection

1. Go to the OCI Console and navigate to Oracle Database and click GoldenGate to create OCI GoldenGate deployment.

   

2. Enter the following information and ensure to Select a technology as PostgreSQL.

   

3. Select Compartment and private subnet as Subnet. To access the deployment console, make sure you enable public access. This needs to be enabled during deployment creation, else it will be disabled.

   

4. Click Create password secret to create password secret in advance. OCI GoldenGate deployment asks for secret instead of plain text password during creation.

   

   Deployment should be available after creation.

   

5. Create connection for both Amazon RDS for PostgreSQL and OCI Database with PostgreSQL databases using the required information as shown in the following images.

   

   

   

   

6. Assign these connections to OCI GoldenGate deployment under Assigned connection and test the connection if they are successful.

   

   

   

Task 7: Migrate Schema Metadata from Amazon RDS for PostgreSQL Instance to OCI Database with PostgreSQL

We are using one test schema for testing migration.

1. Run the pg_dump command to take metadata schema export.

   

   

2. Run the following command to restore this metadata dump to target OCI Database with PostgreSQL database.

   

3. Run the following command to check if all objects are created in the target database.

   

Task 8: Configure OCI GoldenGate Extract and Replicat Process for Migration

1. Go to the OCI GoldenGate Console, navigate to Administration and click Extracts to create initial load extract process.

   

2. In Add Extract, select source as File and Initial load require file as Source type.

   

   

3. Enter and modify the parameter file for filename from 3 character to 2 as Replicat parameter supports 2 character only.

   

4. Create extract process for delta sync and register it using the required information as shown in the following images.

   

   

5. Start initial load extract process and check the report file for initial load and LSN.

   

   

6. Start Change Data Capture (CDC) process with the Commit Sequence Number (CSN) obtained from initial load extract.

   

   

7. Create and start Replicate process for initial load using the required information as shown in the following images.

   

   

   

   

8. Check report of initial report Replicat process.

   

9. Create replicate for data sync after initial load and start using the required information as shown in the following images.

   

   

   

   

10. Run the following commands to check on the source and target if data is getting replicated.

    

    

    

    

    

Task 9: Issues and Troubleshooting

• Issue1: When parameters are not defined appropriately on source Amazon RDS for PostgreSQL instance related to OCI GoldenGate, then you may get the following error:

  

  You need to make sure parameter logical_replication is set to 1 on Amazon RDS for PostgreSQL instance.

• Issue 2: OCI GoldenGate deployment version 21c is having data type limitation for array. You may get the following error:

  

Related Links

• Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL

• Seamlessly migrate an on-premise PostgreSQL database to OCI Database with PostgreSQL using OCI GoldenGate

• Importing, Exporting, and Migrating Databases - Using PostgreSQL Utilities Such as pg_dump

Acknowledgments

• Author - Ashish Srivastava (Principal Cloud Architect, Oracle North America Cloud Services - NACIE)

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

Migrate Amazon RDS for PostgreSQL Database to OCI Database with PostgreSQL using OCI GoldenGate

G33767-01

Copyright ©2025, Oracle and/or its affiliates.