Provision OCI Database with PostgreSQL and Migrate from On-Premises with Minimum Downtime using OCI GoldenGate
Introduction
Oracle Cloud Infrastructure Database with PostgreSQL (OCI Database with PostgreSQL) is fully managed PostgreSQL-compatible service which provides 3 times higher performance through database optimized storage, half cost compared to Amazon Web Service (AWS), provides automated backups, high availability, scheduled maintenance, dynamically storage scale, vertical scaling and so on.
Example: Business Continuity
The OCI Database with PostgreSQL service offers a 99.99% uptime Service Level Agreement (SLA), a Recovery Time Objective (RTO) of less than 2 minutes for multi-node database systems, and a Recovery Point Objective (RPO) of 0 with no data loss. For single-node database systems in single Availability Domains (AD) regions, the service offers a 99.9% uptime SLA, an RTO of less than 20 minutes, and an RPO of 0.
OCI Database with PostgreSQL leverages OCI Block Volumes to store user data. So the same durability, security, and performance guarantees apply. For more information, see Overview of OCI Database with PostgreSQL and First Principles: Optimizing PostgreSQL for the cloud.
This tutorial focuses on migration from an on-premises PostgreSQL database to OCI Database with PostgreSQL using OCI GoldenGate with minimal downtime.
Environment Details:
- OCI GoldenGate console username:
oggadmin
. - OCI GoldenGate version:
23ai
. - OCI Database with PostgreSQL admin name:
admin
.
Source On-Premises | Target OCI | |
---|---|---|
DB Name | product_inventory | targetdb |
User | ordermanager | admin |
Schema | data_analyze | data_analyze |
DB Version | 15.12 | 15.6 |
Objectives
-
Create OCI Database with PostgreSQL database System.
-
Provision OCI GoldenGate deployment.
-
Create on-premises database and OCI database connection on deployment and assign connections.
-
Prepare source database server and database.
-
Create initial load Extract.
-
Backup source database metadata using the
pg_dump
utility. -
Import metadata into OCI Database with PostgreSQL.
-
Create initial load Replicat using trail file.
-
Create online or Change Data Capture (CDC) Extract altering to starting Log Sequence Number (LSN) for capture.
-
Create online Replicat giving CDC Extract trail file.
Task 1: Provision OCI Database with PostgreSQL Database System
This tasks explains how to create database system and the following policies are required before you provision. For more information, see Managing a Database System.
-
Add the following Oracle Cloud Infrastructure Identity and Access Management (IAM) policies.
Allow group <postgresql-admin-group> to read compartments in tenancy Allow group <postgresql-admin-group> to manage postgres-db-systems in [ tenancy | compartment <compartment_name> | compartment id <compartment_OCID> ] Allow group <postgresql-admin-group> to manage postgres-backups in [ tenancy | compartment <compartment_name> | compartment id <compartment_OCID> ] Allow group <postgresql-admin-group> to read postgres-work-requests in [ tenancy | compartment <compartment_name> | compartment id <compartment_OCID> ] Allow group <postgresql-admin-group> to manage postgres-configuration in [ tenancy | compartment <compartment_name> | compartment id <compartment_OCID> ] Allow group <postgresql-admin-group> to manage virtual-network-family in [ tenancy | compartment <compartment_name> | compartment id <compartment_OCID> ] Allow group <postgresql-admin-group> to read secret-family in [ tenancy | compartment <compartment_name> | compartment id <compartment_OCID> ] Allow group <postgresql-admin-group> to read vaults in [ tenancy | compartment <compartment_name> | compartment id <compartment_OCID> ] Allow group <postgresql-admin-group> to read metrics in [ tenancy | compartment <compartment_name> | compartment id <compartment_OCID> ] Allow group <postgresql-admin-group> to use tag-namespaces in tenancy
-
Create or select a vault for the database administrator password. For more information, see Managing Vaults.
-
Create a key for the database password. For more information, see Managing Keys.
-
Create a secret for the database password. For more information, see Managing Vault Secrets.
-
Log in to the OCI Console, navigate to Database, PostgreSQL, DB systems, Create new database system and click Next.
-
Select Hardware configuration.
-
Select Virtual Cloud Network and Subnet and enter Database system administrator credentials. In this example, Username will be admin.
Once creation completed and the database system shows Active state.
-
Run the following command to verify the connection to OCI Database with PostgreSQL from compute node.
-bash-4.2$ psql "sslmode=verify-full sslrootcert=c.pub host=<OCI DB server ip address> user=admin dbname=postgres" Password for user admin: psql (15.12, server 15.6) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off) Type "help" for help. postgres=>
Task 2: Create an OCI GoldenGate Deployment for Migration
-
Go to the OCI Console, navigate to Oracle Database, GoldenGate, Deployments and click Create Deployment.
-
Enter the required information specified in the following images and select PostgreSQL as Select a Technology.
-
In Configure your deployment, use
oggadmin
as Username to log in to the OCI GoldenGate Console as administrator. -
Click Create to start the creation and deployment. Once successfully completed, it will show Active state.
Task 3: Create On-Premises Database Connection, OCI Database Connections on Deployment and Assign Connections
OCI GoldenGate requires the both source, target database connection and assign both connections to deployment.
-
Create source database connection.
-
Click the deployment name (
PG_Mig_deployment
), select Connections and click Create Connection. Enter the required information as shown in the following images. -
Select Type as PostgreSQL Server, Security Protocol as Plain and click Create.
-
-
Create target database connection.
-
Click the deployment name, select Connections and click Create Connection. Enter the required information as shown in the following images.
-
Select Type as OCI PostgreSQL.
-
Select Security Protocol as TLS and SSL Mode as Require.
-
-
Assign the database connections to deployment.
-
Go to Deployments, click the deployment name (
PG_Mig_deployment
), select Assign connections and click Assign Connection. -
Enter the required information as shown in the following images.
You will see Active state after assign connection completes.
-
Task 4: Prepare Source Database Server and Database
-
Install the
postgresql15-contrib
package.OCI GoldenGate requires the
postgresql14-contrib
package on source database server in order to to extract from PostgreSQL server database. Package can be installed using thesudo yum install postgresql15-contrib
command (source database version is 15).Note: It is recommended that the version of the client libraries should match the PostgreSQL database version. Client versions below 10 are not supported.
-
Prepare database users creation on source database and required privileges.
product_inventory=# create user ggreplication with password 'abc1234'; ERROR: role "ggreplication" already exists product_inventory=# product_inventory=# select current_database(); current_database ------------------- product_inventory (1 row) product_inventory=# GRANT CONNECT ON DATABASE product_inventory TO ggreplication; GRANT product_inventory=# ALTER USER ggreplication WITH REPLICATION; ALTER ROLE product_inventory=# ALTER USER ggreplication WITH SUPERUSER; ALTER ROLE product_inventory=# GRANT USAGE ON SCHEMA ordermanager TO ggreplication; GRANT product_inventory=# GRANT USAGE ON SCHEMA data_analyze TO ggreplication; GRANT product_inventory=# GRANT SELECT ON ALL TABLES IN SCHEMA ordermanager TO ggreplication; GRANT product_inventory=# GRANT SELECT ON ALL TABLES IN SCHEMA data_analyze TO ggreplication; GRANT product_inventory=# GRANT CREATE ON DATABASE product_inventory TO ggreplication; GRANT product_inventory=# GRANT CREATE, USAGE ON SCHEMA ordermanager TO ggreplication; GRANT product_inventory=# GRANT CREATE, USAGE ON SCHEMA data_analyze TO ggreplication; GRANT product_inventory=# GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA ordermanager TO ggreplication; GRANT product_inventory=# GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA data_analyze TO ggreplication; GRANT product_inventory=# GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA ordermanager TO ggreplication; GRANT product_inventory=# GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA data_analyze TO ggreplication; GRANT product_inventory=#
For more information about specific purpose of each privileges, see Prepare Database Users and Privileges for PostgreSQL.
-
The following are the parameters in the PostgreSQL database configuration file. The location of the configuration file is
$PGDATA/postgresql.conf
.wal_level = logical **# set to logical for Capture** max_replication_slots = 1 **# max number of replication and one slot for Extract/Client** max_wal_senders = 1 **# one sender per max repl slot** track_commit_timestamp = on **# optional, correlates tx commit time** with begin tx log record (useful for # timestamp- based positioning)
Note:
- After making any of the preceding changes, restart the database.
- OCI GoldenGate replication supported PostgreSQL data types and limitation on the supported data types, supported objects and operations can be verified from the documentation. For more information, see Prepare Databases.
Task 5: Create Initial Load Extract
We need to create initial load Extract and this will provide the LSN on OCI GoldenGate Extract report section.
-
Go to the OCI Console, navigate to Database, GoldenGate, Deployments and click the deployment name created in Task 2.
-
Click Launch Console and enter login credentials.
-
Go to DB Connections and this should display the assigned source and OCI Database with PostgreSQL database connections and verify the connectivity check.
Successful connection is good to start initial load at this stage.
-
Go to the OCI GoldenGate Console, navigate to Extract and click +.
Once the initial load Extract is stopped, verify the OCI GoldenGate report files and note the LSN details and keep it handy to use in Task 9.
Task 6: Backup Source Database Metadata using the pg_dump
Command
The pg_dump
command is used to dump the source database product_inventory
metadata only.
pg_dump -h localhost -U ggreplication -d product_inventory -F c -v -s --file=/tmp/exp_source_db.dump
Task 7: Import Metadata into OCI Database with PostgreSQL
Use the pg_restore binary to import into target OCI Database with PostgreSQL, here the OCI database name is targetdb
.
Run the restore command from OCI Compute node in which we have successfully executed connection command in Task 1.8.
pg_restore -h <OCI IP address> -U admin -d targetdb -v exp_source_db.dump
postgres=> \c targetdb
psql (15.12, server 15.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off)
You are now connected to database "targetdb" as user "admin".
targetdb=> \dn
List of schemas
Name | Owner
--------------+-------------------
data_analyze | admin
ordermanager | admin
public | pg_database_owner
(3 rows)
Note: Use the
select table_name,table_schema from information_schema.tables where table_type='BASE TABLE' and table_schema in ('data_analyze','ordermanager') order by table_schema,table_name;
query to verify the table names after metadata import on target.
Task 8: Create Initial Load Replicat using Trail File
-
Go to the OCI GoldenGate Console, navigate to Replicats and click + to create Replicat.
-
Click Create and run to start initial load Replicate, once it is completed, verify the table row counts.
-
Go to Reports and verify the initial load Replicate reports. Verifying the few table rows counts at both databases.
Task 9: Create CDC Extract Altering to Starting LSN for Capture
CDC Extract is online extract used to capture ongoing changes on existing database and CDC Extract starts capturing from LSN number collected.
-
Go to the OCI GoldenGate Console, navigate to Extracts, click + and enter the required information as shown in the following images.
-
Once CDC is registered and created, click Start with Options to instruct reading from specific LSN which is collected in Task 5.
We can see zero seconds lag on CDC Extract after some time.
Task 10: Create Online Replicate giving CDC Extract Trail File
-
Go to the OCI GoldenGate Console, navigate to Replicats, click + and enter the required information as shown in the following images.
-
Click Create and Run after modifying the
MAP
command.We can see zero seconds lag on Online Replicat after some time.
Verification
Run the following queries to insert into source table data_analyze.table3
replicates to target database data_analyze.table3
table.
Target side verification table count and data.
Related Links
-
Precise Instantiation between PostgreSQL Environments Using pg_dump
-
Migrate an On-Premises PostgreSQL Database to OCI Database with PostgreSQL using OCI GoldenGate
Acknowledgment
- Author - Sivakrishna Burle (Senior Cloud Engineer, 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.
Provision OCI Database with PostgreSQL and Migrate from On-Premises with Minimum Downtime using OCI GoldenGate
G33181-02
Copyright ©2025, Oracle and/or its affiliates.