Replication to GCP PostgreSQL Using Oracle GoldenGate
Use Case: Real-time replication of transactional data from an on-premises database to GCP Managed PostgreSQL instance using GoldenGate.
Architecture:
Source Database (Source Database can be any GoldenGate supported databases)
- Oracle GoldenGate (OGG) 19c/18c/12c for extraction from Source DB
- Oracle GoldenGate for PostgreSQL 12.2 or 19.1 ( I have used 12.2 for this use-case)
- Google Cloud Platform PostgreSQL managed service database
Note: We can also have a Compute in GCP and install PostgreSQL 12.2 on that.
Pre-Requisites:
- Download OGG and OGG for PostgreSQL from Oracle e-delivery.
- Installation of OGG and OGG for PostgreSQL to be done on respective
- GoldenGate at source to be configured to extract data from the source database and to pump extract trails to GoldenGate for PostgreSQL
- Spin up a PostgreSQL managed instance in Google Cloud Platform (GCP)
Source Configuration details:
Oracle Database is used as a Source for this use-case, following is the Extract Parameter file used to extract data:
Extraction is happening for table pg.
- For the rmthost use the host-name or IP of the GoldenGate for PostgreSQL
- 7821 is the Manager Port of PostgreSQL
Note: Extract is only pumping the trail files, a separate pump is not used. You can configure separate extract and pump processes.
Target Configuration Details:
Once you spin up the PostgreSQL on GCP, note down the IP address on which the instance is running.
As you can see, a PostgreSQL 11 Db has been spinned up in GCP which has a public IP address associated with it.
Note: Create a User in your Postgres Database, I have created a Postgres User.
OGG for PostgreSQL Setup (OGG4PG):
1- Setup LD_LIBRARY_PATH variable to the lib folder under OGG4PG installation If it is not done, ggsci prompt will not work.
2- Create a new odbc.ini file :
GoldenGate will connect to PostgreSQL database using ODBC connection. It is important to create the file in a proper format and with correct details, otherwise the connection to the database will not be established.
InstallDir – Installation Directory of GoldenGate for PostgreSQL
- Database – Name of Postgres DB spinned on GCP
- Hostname –Public IP of PostgreSQL Instance spinned on GCP
- LogonID – User created in the Postgres database
3- Set the Variable ODBCINI with the path of odbc.ini file
4- Test the connectivity to your GCP PostgreSQL using the below command
5- Configure Replicat for the Replication to PostgreSQL
Note: You will have to create the table first in PostgreSQL, as GoldenGate does not create it automatically. DDL Replication is not supported for heterogeneous replication. Here, Data from Scott.pg is mapped to public.pg table in PostgreSQL.
6- Start the Replicat and see to it that it comes in running mode
Test the Configuration by doing a transaction once, the Source and Target GoldenGate for PostgreSQL setup is completed.
Also, In case, GoldenGate for PostgreSQL host is not able to ping to the GCP, whitelist the host IP at the GCP side so that it allows the connections to go through.
Author : Ankit Goyal
LinkedIn : https://www.linkedin.com/in/ankit-goyal-0a72999a
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform
Loading...
