Step-by-Step Guide: Configuring a Real Time Data Replication, between OCI Database for PostgreSQL (source) and OCI Database for PostgreSQL (target) on Oracle Cloud with Oracle OCI GoldenGate
Introduction:
Oracle Goldengate 23ai Microservices provides a comprehensive platform for doing real-time replication from multiple source and target databases.
Goldengate 23ai for PostgreSQL provides remote capture and remote apply for PostgreSQL running in OCI, AWS, GCP, Azure or on-prem.
In this blog, we show you how to set up a OCI Database for PostgreSQL on Oracle Cloud Infrastructure (OCI) using OCI GoldenGate 23ai (OCI OGG) Deployment for real-time data replication. From user creation to data synchronization, we explain the key steps to implement this powerful solution in your infrastructure.
We can build this solution using below components:
- Source -> OCI Database for PostgreSQL in Region A
- Target -> OCI Database for PostgreSQL in Region B
- OCI OGG 23ai Deployment for PostgreSQL
- OCI OGG Connection to source database
- OCI OGG Connection to target database
OCI Replication Architecture
Source database: source
schema: src_ociggll
- src_ociggll.SRC_CITY
- src_ociggll.SRC_REGION
- src_ociggll.SRC_CUSTOMER
- src_ociggll.SRC_ORDERS
- src_ociggll.SRC_ORDER_LINES
- src_ociggll.SRC_PRODUCT
Target database: target
schema: trg_ociggll
- trg_ociggll.trg_CITY
- trg_ociggll.trg_REGION
- trg_ociggll.trg_CUSTOMER
- trg_ociggll.trg_ORDERS
- trg_ociggll.trg_ORDER_LINES
- trg_ociggll.trg_PRODUCT
OCI Databases for PostgreSQL configuration
Create the source OCI Database for PostgreSQL you MUST follow these steps
Pay attention in the step 11 and follow the steps to Creating a Configuration. In this new configuration you should modify these two parameters:
wal_level= logical
track_commit_timestamp = on
We called this new configuration with the GoldenGate parameters: “postgresql_goldengate”.
We should create the source OCI Database for PostgreSQL database with this new configuration: (postgresql_goldengate)
Check in the PSQL:
postgres=> SELECT name,setting from pg_catalog.pg_settings where name in (‘wal_level’,’track_commit_timestamp’);
name | setting
————————+———
track_commit_timestamp | on
wal_level | logical
(2 rows)
NOTE: Target OCI PostgreSQL could be created with the default configuration.
Prepare Database Users and Privileges for PostgreSQL (source & target)
OCI Database for PostgreSQL do not have the SUPERUSER role.
For GoldenGate replication, we need to create a user in the bbdd “ggadmin” with:
- role ADMIN
- role REPLICATION
Create the OCI GoldenGate resources
Create a PostgreSQL Deployment
You can create a PostgreSQL deployment for the source and target PostgreSQL databases using the OCI Console, CLI, or APIs. From the OCI Console menu, go to Oracle Database, click GoldenGate and click Create deployment.
Go through the deployment creation screens, and select PostgreSQL when prompted for a technology. Finally, click Create to start the creation of the deployment.
Create a Connection to PostgreSQL (source and target)
To create a Connection, go to the Overview or Connections page and click Create connection. Pick OCI PostgreSQL as the Type and click Next.
We can name the connections:
- OCI_PostgreSQL_Source
- OCI_PostgreSQL_Target
Provide the database name, its host and port (5432 by default), then enter a username and password. Finally specify the SSL details.
Enable Network connectivity via private endpoint. OCI PostgreSQL database can only be accessed using a private IP address.
- Host: Endpoint IP OCI PostgreSQL database
- Port: 5432
- User: ggadmin user with ADMIN and REPLICA roles
- SSL details: TLS – Require
- Dedicate Endpoint: Enable Network connectivity via private endpoint because PostgreSQL database can only be accessed using a private IP address.
Repeat these steps for TARGET connection.
IMPORTANT: You should add the Connection Ingress IP to the Ingress Rules of the PRIVATE VNC where the OCI Postgres Database is attached.
- Private Subnet Details page, click an existing security list that is assigned to this subnet.
Alternatively, you can create a security list and assign it to this subnet.
- Click Add Ingress Rules.
- For Source CIDR, enter a CIDR block that includes the Connection Ingress IP of the Connection.
For example, the CIDR block Ingress Ip /32 includes only the Connection IP address.
- For IP Protocol, select TCP.
- For Destination Port Range, enter the port number on the target resource: 5432.
- Click Add Ingress Rules.
Assign the Connection to the PostgreSQL Deployment (source and target)
A Connection must be assigned to a Deployment before it can be used. The assignment can be done from the Deployment or the Connection. You don’t need to wait for the Connection to be Active to assign it to a Deployment.
Open your Deployment, click Assigned connections, then click Assign connection.
Select your Connection in the list and click Assign connection to create the assignment.
Repeat procedure to target database.
Configuring an Initial Synchronization for a OCI PostgreSQL Source Database using Precise Instantiation
Data synchronization from a source PostgreSQL database to an Oracle GoldenGate target can be accomplished with the optional method of using precise instantiation. This method was introduced with Oracle GoldenGate 21c (21.8.0).
Precise instantiation has the advantage of not requiring any collision handling in the target Replicat. This is important for targets that do not support collision handling, such as flat files. This method uses a database snapshot to synchronize the output of the initial load Extract with the starting position of the Change Data Capture Extract. This snapshot is managed by the initial load Extract, so it is not possible for multiple initial load Extracts to use the same snapshot. Therefore, this method is not supported when using multiple intial load Extracts to parallelize the workload.
About Extract
For Oracle GoldenGate for PostgreSQL, there are two types of Extracts that can be created.
- Initial Load Extract
An Initial Load Extract is used to read all records from a table and write them to an EXTFILE or RMTFILE. Initial load Extracts are created with the SOURCEISTABLE option of the ADD EXTRACT command and do not maintain checkpointing for recovery.
- Change Data Capture Extract
A Change Data Capture Extract is used to capture transactional data changes from that point in time at which it is created or positioned into the write-ahead log.
The Oracle GoldenGate Extract process for PostgreSQL receives logical records from the PostgreSQL test_decoding database plugin and writes them in commit order into trail files for downstream consumption by a Replicat.
Go to your Deployment details page and click Launch console in the PostgreSQL to start the OCI GoldenGate Console and login
Prepare PostgreSQL tables for GoldenGate:
- Disabling Triggers and Cascade Constraints on the Target
- Ensuring Row Uniqueness for Tables
- Enabling Table-Level Supplemental Logging
ADD TRANDATA in SOURCE db.
Click in DB Connections, click OCI_PostgreSQL_Source, click Trandata, and click “+”:
Perform the following steps to set up end-to-end initial load and synchronization processes using the precise instantiation method
- Create, Register a Change Data Capture (CDC) Extract with the source PostgreSQL database. DO NOT START the Extract
In the EXTRACT, click “+”, select Change Data Capture Extract, write the name, and click NEXT
Extract Trail: “ea”
Alias: OCI_PostgreSQL_Source
And click REGISTER, and you should see this message:
Configure Auto Start and Auto Restart; Click Next,
Use this parameter file:
EXTRACT EXTCDC
USERIDALIAS OCI_PostgreSQL_Source, DOMAIN OracleGoldenGate
EXTTRAIL ea
TABLE src_ociggll.*;
NOTE: Replace “src_ociggll.*” for your schema and tables.
Click on CREATE do not click on Create and Run.
- Create and Start an Initial Load Extract.
Source: Trail
Trail Name: ia
Source Credentials: OCI_PostgreSQL_Source
Click Next,
Use the parameter file below, and click “Create and -Run”
EXTRACT EXTINI
USERIDALIAS OCI_PostgreSQL_Source, DOMAIN OracleGoldenGate
INITIALLOADOPTIONS USESNAPSHOT
EXTFILE ia , PURGE
TABLE src_ociggll.*;
NOTE: See documentation for INITIALLOADOPTIONS USESNAPSHOT parameter
- When the initial load Extract has completed and stopped, review its report file to determine the positioning LSN to be used by the CDC Extract.
- Create and start an initial load Replicat that reads the trail from the initial load Extract.
About Replicat
The Oracle GoldenGate Replicat for PostgreSQL reads data from Oracle GoldenGate source trail files and delivers the data to a target OCI PostgreSQL database. The source trail data can be from any database that Oracle GoldenGate capture supports.
Available Replicats for PostgreSQL are Classic, Coordinated, and Parallel Replicat.
- Prerequisites for Creating a Replicat
- Creating a Checkpoint Table
A checkpoint table is used by a Replicat in the target database for recovery positioning when restarting a Replicat. A checkpoint table is optional (but recommended) for a Classic Replicat and required for Coordinated and Parallel Replicats.
The checkpoint table needs to be created under an existing schema in the database
Create and Start the Initial Load Replicat:
Click Next;
Replicat Trail: ia. (trailfile from EXTINI)
Checkpoint Table: ggadmin.chkpoint
Credentials: OCI_PostgreSQL_Target
Click Next;
Configure Auto Start and Auto Restart;
Click Next;
Parameter file:
REPLICAT REPINI
USERIDALIAS Oci_PostgreSQL_Target DOMAIN OracleGoldenGate
MAP src_ociggll.SRC_CITY, TARGET trg_ociggll.trg_CITY;
MAP src_ociggll.SRC_REGION, TARGET trg_ociggll.trg_REGION;
MAP src_ociggll.SRC_CUSTOMER, TARGET trg_ociggll.trg_CUSTOMER;
MAP src_ociggll.SRC_ORDERS, TARGET trg_ociggll.trg_ORDERS;
MAP src_ociggll.SRC_ORDER_LINES, TARGET trg_ociggll.trg_ORDER_LINES;
MAP src_ociggll.SRC_PRODUCT, TARGET trg_ociggll.trg_PRODUCT;
Click Create and Run.
Compares with the Initial Load EXTINI, report:
***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************
Report at 2024-09-08 18:30:02 (activity since 2024-09-08 18:30:01)
Output to ia:
From table src_ociggll.src_city:
# inserts: 51
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table src_ociggll.src_customer:
# inserts: 35
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table src_ociggll.src_order_lines:
# inserts: 236
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table src_ociggll.src_orders:
# inserts: 80
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table src_ociggll.src_product:
# inserts: 15
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table src_ociggll.src_region:
# inserts: 22
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
(REPINI could be STOPED)
- Alter & Start the CDC Extract (EXTCDC) using the consistent LSN value referred to in the initial load Extract report file.
Start the EXTCDC;
- When the initial load Replicat completes, (same rows inserts in Initial Load EXTINI that in Replicat Init) you must STOP the Replicat.
Then, add and start a CDC Replicat that reads the trail from the CDC Extract
Click Next;
Replicat Trail: ea. (trailfile from EXTCDC)
Checkpoint Table: ggadmin.chkpoint
Credentials: OCI_PostgreSQL_Target
Click Next
REPCDC parameter file:
REPLICAT REPCDC
USERIDALIAS Oci_PostgreSQL_Target DOMAIN OracleGoldenGate
MAP src_ociggll.SRC_CITY, TARGET trg_ociggll.trg_CITY;
MAP src_ociggll.SRC_REGION, TARGET trg_ociggll.trg_REGION;
MAP src_ociggll.SRC_CUSTOMER, TARGET trg_ociggll.trg_CUSTOMER;
MAP src_ociggll.SRC_ORDERS, TARGET trg_ociggll.trg_ORDERS;
MAP src_ociggll.SRC_ORDER_LINES, TARGET trg_ociggll.trg_ORDER_LINES;
MAP src_ociggll.SRC_PRODUCT, TARGET trg_ociggll.trg_PRODUCT;
Click Create and Run.
- Monitor the lag in both the CDC Extract and the CDC Replicat, and when they are both close to zero seconds, then the data stream from source to target database should be close to real-time.
Test SOURCE LOAD:
Insert into src_ociggll.src_city (CITY_ID,CITY,REGION_ID,POPULATION) values (1000,’Houston’,20,743113);
Insert into src_ociggll.src_city (CITY_ID,CITY,REGION_ID,POPULATION) values (1001,’Dallas’,20,822416);
Insert into src_ociggll.src_city (CITY_ID,CITY,REGION_ID,POPULATION) values (1002,’San Francisco’,21,157574);
Insert into src_ociggll.src_city (CITY_ID,CITY,REGION_ID,POPULATION) values (1003,’Los Angeles’,21,743878);
Insert into src_ociggll.src_city (CITY_ID,CITY,REGION_ID,POPULATION) values (1004,’San Diego’,21,840689);
Insert into src_ociggll.src_city (CITY_ID,CITY,REGION_ID,POPULATION) values (1005,’Chicago’,23,616472);
Insert into src_ociggll.src_city (CITY_ID,CITY,REGION_ID,POPULATION) values (1006,’Memphis’,23,580075);
Insert into src_ociggll.src_city (CITY_ID,CITY,REGION_ID,POPULATION) values (1007,’New York City’,22,124434);
Insert into src_ociggll.src_city (CITY_ID,CITY,REGION_ID,POPULATION) values (1008,’Boston’,22,275581);
Insert into src_ociggll.src_city (CITY_ID,CITY,REGION_ID,POPULATION) values (1009,’Washington D.C.’,22,688002);
