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 an On-Premises 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. This feature supports the migration of on-premises PostgreSQL databases on VM to OCI Database with PostgreSQL service Versions 14, 15 databases and future releases.

This tutorial outlines how OCI GoldenGate facilitates the migration of on-premises PostgreSQL databases on VM version 14 to OCI Database with PostgreSQL service databases version 14. Initial load is performed using the pg_dump (pg_dumpall)/pg_restore backup utility. This will create a backup or snapshot of the source database, which will be restored on the target system followed by OCI GoldenGate Change Data Capture (CDC) replication to sync up the source (on-premises VM PostgreSQL databases) and target (OCI Database with PostgreSQL service).

The following image shows the architecture for migrating an on-premises PostgreSQL database on VM to OCI Database with PostgreSQL service using OCI GoldenGate.

Objectives

• Migrate to OCI Database with PostgreSQL service using OCI GoldenGate.

Prerequisites

Note: These prerequisites are for an on-premises PostgreSQL database on VM (source).

• test_decoding Plugin: OCI GoldenGate requires the test_decoding database plugin to be installed on the PostgreSQL database. This plugin may not be installed by default.

• PostgreSQL Contrib Package: Ensure that the postgresqlversion#-contrib package is installed on the database server.

• Client Library Compatibility: The PostgreSQL client libraries should match the database version. Note that client versions below 10 are not supported.

  sudo yum install postgresql14-contrib (our database is version 14)
  

Task 1: Prepare Source Database Environment

1. Run the following query to create replication user, this user should be used while we create a connection in the OCI Console.

   create user ggadmin with password ‘<<password>>’;
   GRANT ALL PRIVILEGES ON DATABASE campaign TO ggadmin;
   alter user ggadmin replication;
   alter user ggadmin with superuser;
   

2. The following parameter should be changed and database system should be restarted for wal_level to get modified.

   ALTER SYSTEM SET wal_level = logical;
   ALTER SYSTEM SET track_commit_timestamp = on;
   

Task 2: Provision OCI GoldenGate and Configure Connections

1. Log in to the OCI Console and create deployment of OCI GoldenGate for PostgreSQL database.

   

   

   

   

   

2. Create the OCI GoldenGate connection for OCI PostgreSQL.

   1. Enter source connection details and click Create.

      

      

   2. Enter target connection details and click Create.

      

      

   Note: If connection is created for VM, SSL protocol should be Plain and for OCI Database with PostgreSQL service, SSL protocol should be Require.

3. Click Create to create the connection.

Task 3: Set up Source Database Replication

1. Create Extract in OCI GoldenGate.

   

   Note: Add schema trandata in the source database for all eligible schemas and click Submit.

   

   

   

   Note: Click CREATE and do not click CREATE and RUN.

2. Create a temporary replication slot in the source database that will be used to create a starting point for our Extract process and for positioning the PostgreSQL snapshot. This can be done using the CREATE_REPLICATION_SLOT command. Follow the steps to configure the temporary replication slot:

   1. Log in to the PostgreSQL database using psql as a user with the replication role, such as postgres or ggadmin user.

   2. Use the replication=database option.

      psql "dbname=pgsource replication=database user=postgres" 
      CREATE_REPLICATION_SLOT tslot TEMPORARY LOGICAL test_decoding EXPORT_SNAPSHOT;
      slot_name | consistent_point | snapshot_name | output_plugin
      ----------+------------------+---------------+--------------------- 
          tslot | 1/4232A6B0 | 00000007-00001142-1 | test_decoding 
      

   3. Note the snapshot_name and the consistent_point details.

      Note:

      • consistent_point should be used for positioning the OCI GoldenGate Extract and start the Extract to capture the CDC transactions.
      • snapshot_name should be used for the initial load from source to target database using pg_dump and pg_restore.

3. Position the OCI Goldengate Extract to consistent_point Log Sequence Number (LSN).

   The CDC extract created will be in stopped state.

   

   Under Actions, click the … icon and select Alter.

   

   Alter the position of LSN to consistent_point captured in Task 3.2.

   

4. Click Submit and start the extract process.

   Extract will be in running state and will start the CDC capture from the altered position.

   

Task 4: Run Backup and Restore on Source and Target Database

1. Run the following command to backup the source primary database.

   nohup /u01/pgsql-14/bin/pg_dump -h <<Sourceip>> -p 5432 -U <<superuser/ggadmin user>> –snapshot=00000007-00001142-1 -F c -b -v -f latestdump.db pgsource &
   

2. Run the following command to restore the dumps to target database.

   nohup /u01/pgsql-14/bin/pg_restore -h <<Targetip>> -p 5432 -U admin -d pgtarget -v latestdump.db &
   

Task 5: Set up Target Database Replication

1. Create Checkpoint Table in any available schema on OCI GoldenGate target database and click Submit.

   

2. Create the Replicat process with target database connection to apply the CDC data to target database.

   

   

   

   

3. Click submit and start the Replicat. Allow it to sync up.

   

Task 6: Perform the Actual Migration

Cut-over from on-premises VM PostgreSQL database to OCI Database with PostgreSQL service.

• Perform application ramp down connected to source VM database.

• Ensure there are no database connections to source VM database.

• Ensure there is no lag in Extract/Replicat process and perform the cut-over pointing the application to target OCI Database with PostgreSQL service stopping the Extract/Replicat process.

Task 7: Validate the Migration

Verify the transferred database objects and data on the target database system in OCI Database with PostgreSQL to ensure that the migration was successful and there are no issues or discrepancies.

Related Links

• Seamlessly migrate an on-premise PostgreSQL database to OCI Database with PostgreSQL using OCI GoldenGate
• Importing, Exporting, and Migrating Databases
• Precise Instantiation between PostgreSQL Environments Using pg_dump

Acknowledgments

• Author - Saravanadurai Rajendran (Lift Implementation Specialist - Database)

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 an On-Premises PostgreSQL Database to OCI Database with PostgreSQL using OCI GoldenGate

G29103-01

Copyright ©2025, Oracle and/or its affiliates.