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

November 27, 2023 | 15 minute read
Sanoj Behera
Data Modernise Specialist
Text Size 100%:

Since Oracle Cloud Infrastructure (OCI) Database with PostgreSQL version 14.9 is available now, you can test migrating your data from onprem PostgreSQL to OCI Database with PostgreSQL which is a fully managed PostgreSQL-compatible service with intelligent sizing, tuning, and high durability.

For more information, see Overview of OCI Database with PostgreSQL.

This blog will show how you can do a zero-downtime migration from On-Premise PostgreSQL to OCI PostgreSQL using OCI Goldengate for PostgreSQL.

About Oracle Cloud Infrastructure GoldenGate

Learn about data replication concepts to help you start with Oracle Cloud Infrastructure GoldenGate.

Oracle Cloud Infrastructure GoldenGate is a fully managed, native cloud service that moves data in real-time, at scale. OCI GoldenGate processes data as it moves from one or more data management systems to target databases. You can also design, run, orchestrate, and monitor data replication, transform data, and analyze streaming data in real-time without allocating or managing any compute environments.

Watch a short overview video, or take an interactive service tour to learn more.


Overall Architecture

End-to-end migration to OCI Database with PostgreSQL

The end-to-end migration to OCI Database with PostgreSQL involves six key stages:

  • Take backup for source metadata
  • Import metadata file into OCI Database with PostgreSQLs
  • Create an Initial Load Extract on source onprem and note the LSN number.
  • Create an Initial Load Replicat on OCI Database with PostgreSQL target using the same trail file (initial load extract)
  • Create a CDC Extract on source onprem and start from the LSN number captured.
  • Create and run a CDC Replicat on OCI Database with PostgreSQL target using the same trail file (CDC extract)

Source Environment

  • Database Name: ociggll
  • User: admin
  • Schema: src_ociggll
  • Version: PostgreSQL 14.10

Target Environment

  • Database Name: ociggllTgt
  • User: postgres
  • Schema: src_ociggll
  • Version: PostgreSQL 14.9

Before you get started, you will need an Oracle Cloud account.
Create a VCN and subnet, a bastion (Compute image), and provision OCI PostgreSQL database instances.
Navigate to Databases and then OCI PostgreSQL DB system.
Create OCI DB with PostgreSQL

OCI PostgreSQL Creation 2

OCI PostgreSQL Creation 3

OCI PostgreSQL Creation 4

OCI PostgreSQL Creation 5

OCI PostgreSQL Creation 6

OCI PostgreSQL Creation 7

Click Submit.
OCI PostgreSQL creation - final screen

You might get the below error.
OCI PostgreSQL DB creation - possible error

As per the documentation, we must use a private subnet: Creating a Database System (

OCI PostgreSQL network configuration requirements

The database is created now and should show active.
OCI PostgreSQL database details page

PostgreSQL Source on-prem Database (Monitoring using PGADMIN4)
OCI PostgreSQL monitoring using PGADMIN4

Monitoring using SQL Developer
OCI PostgreSQL monitoring using SQL Developer

Check the Source Data.
Source data query 
Run the following commands to set up the user (On the source database):
Setup DB users on source DB

OCI Database with PostgreSQL (Target)
We have created a new database for restoring the onprem database. We will not use the default database postgres for this test.
Creating DB in OCI DB with PostgreSQL

New PostgreSQL database in SQL Developer

To create the OCI Goldengate connection to the on-prem PostgreSQL database, follow this blog:

Source Connection in OCI GoldenGate

Source DB Connection in OCI GoldenGate - Part 2

Source DB Connection in OCI GoldenGate - Part 3

Source DB Connection in OCI GoldenGate - Details page

Source DB Connection in OCI GoldenGate - Active connection


Creating OCI GoldenGate Deployment for PostgreSQL


OCI GoldenGate PostgreSQL Deployment Creation - 1/3

OCI GoldenGate PostgreSQL Deployment Creation - 2/3

OCI GoldenGate PostgreSQL Deployment Creation - 3/3


Create Target Connection

OCI PostgreSQL Target Connection Creation - 1/3 
 OCI PostgreSQL Target Connection Creation - 2/3

OCI PostgreSQL Target Connection Creation - 3/3
Note: For OCI PostgreSQL connection creation, please keep in mind the following:

  • Use Dedicated endpoint as the Traffic routing method.
  • Pick a private subnet so OCI-GG creates a Private Endpoint to access the OCI PostgreSQL private IP.
  • Ensure port 5432 is open in selected subnet.
  • SSL mode should be set to 'Prefer'.

Once the OCI GoldenHate deployment has been created and the connections are assigned, go to the Deployment details page and launch the Console.
OCI GoldenGate deployment for PostgreSQL - Details page

GoldenGate Console Login Screen
In Administration Service, go to Configuration to open up the Credentials. Click on the Connect button to verify connectivity.
GoldenGate Console - Admin Service

sslMode should be set to prefer for the target OCI PostgreSQL database.

You will get the following error message if you are not using the right SSL mode. See ‘OCI Database with PostgreSQL - PSQL Error: 'FATAL: no pg_hba.conf entry for host, no encryption' when trying to Connect to Server (Doc ID 2983992.1)’ for more information.

Take Export backup Using pg_dump

Now, let’s take a metadata backup from the source database and restore it into the target database.
pg_dump -h <source-host>  -U admin -d ociggll -F c -v -s --file=sourceexport.dump
pg_dump command 
Pgdump documentation:

Import Metadata dump file into OCI PostgreSQL DB system using pg_restore

pg_restore -h <private endpoints> -U postgres -d ocigglltgt  -v sourceexport.dump
pg_restore command 
After the restore operation has been completed successfully, we can verify that the tables exist in the database.
Verify source data using SQL Developer 
Now, we will do the initial load and CDC test for actual data using OCI Goldengate.

Initial Load

You need to create the Initial load extract and write down the LSN Number.
Initial Load Extract Creation - 1/2

Initial Load Extract Creation - 2/2
Refer to Configuring an Initial Synchronization for a PostgreSQL Source Database using Precise Instantiation.

Note: Verify these requirements if your Extract fails:

Additional requirements for PostgreSQL are:

  • To capture from a PostgreSQL database, Oracle GoldenGate requires the test_decoding database plugin to be installed for the database, which may not have been installed by default.
  • Ensure that the postgresqlversion#-contrib package is installed on the database server.
    For example: sudo yum install postgresql13-contrib
    It is recommended that the version of the client libraries should match the PostgreSQL database version. Client versions below 10 are not supported.
    sudo yum install postgresql14-contrib (our database is version 14)

Once the initial load extract is stopped, verify the report files and note the LSN details.
Extract statistics 
Create a Checkpoint table (src_ociggll.ckpt) for the target OCI PostgreSQL DB system:
Create Checkpoint Table - 1/2 
 Create Checkpoint Table - 2/2
Create initial load Replicat for the target database

You can use any replicat type: Classic, Coordinated, or Parallel.
Initial Load Replication - 1/3 
 Initial Load Replication - 2/3
Initial Load Replication - 3/3

Once Replicat started successfully, we can verify the initial load data in the target database.
Verify target data in SQL Developer 
Next, create the CDC extract and start from the LSN value you previously wrote down.

Before that, we will add trandata for the source schema. Go to Administration Service > Configuration, and Connect to your source database.
Add Trandata - 1/2

Add Trandata - 2/2
Now, add the CDC extract.
Add CDC Extract - 1/3 
Add CDC Extract - 2/3 
Once you have mentioned the schema name, click Create.
Add CDC Extract - 3/3 
Once created, go to Actions -> Start with Options -> Enter the LSN -> click Start.
Start Extract - 1/3 
Start Extract - 2/3 
Start Extract - 3/3 
Now, we will generate some transactions in the source schema.

Review the Extract statistics.
CDC Extract Statistics 
Finally, create the CDC Replicat.
Add CDC Replicat - 1/3 
Add CDC Replicat - 2/3

Add CDC Replicat - 3/3 
Once the Replicat successfully started, you can check its statistics.
CDC Replicat Statistics 
Now, you can compare the Source and Target data. They are in sync.
Compare source and target data

Compare source/target data - 2/2
You can see the GoldenGate Console with all the processes running.
GoldenGate Console with end-to-end replication into OCI Database with PostgreSQL 
Validate the migration

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

OCI Goldengate for PostgreSQL enables you to seamlessly migrate databases online from on-premises to OCI Database with PostgreSQL. By following these steps and leveraging the power of OCI GoldenGate, you can ensure a successful and efficient migration process.

Oracle’s innovative managed PostgreSQL service, OCI Database with PostgreSQL, combines cutting-edge open-source technology with the robustness and speed of Oracle Cloud Infrastructure (OCI).

Currently, all default databases (template0, template1 & postgres) are owned by oci_superuser. customer_admin_user (the very first user oci creates as part of create DB system) does not have the privilege to create new schemas in any of the default databases. As a workaround, you can create a database first (with name other than postgres) and then create schema(s) by connecting to that database. Stay tuned for more updates …


Sanoj Behera

Data Modernise Specialist

Sanoj works for Oracle as a Data Modernise Specialist (Presales). He holds the responsibility for providing pre-sales technical and functional assistance to potential clients and customers, as well as building customer solutions for Database and Datawarehouse projects, assisting them in migrating and modernizing their existing database workloads to the Oracle cloud. He holds certifications in OCA, OCP, OCI, Oracle Cloud Database & Migration, Autonomous Database and Oracle Goldengate Implementation Specialist.

Previous Post

Performance Considerations for Oracle Cloud Infrastructure GoldenGate -Data/ Delta Lakes

Deniz Sendil | 1 min read

Next Post

Notebooks, Big Data and Spark Jobs in OCI - Best Practices and Examples

David Allan | 11 min read