Feb 14, 2022

5 min read

Load data from PostgreSQL into Autonomous Database using Oracle GoldenGate

Oracle GoldenGate is a comprehensive software for enabling the replication of data in heterogeneous data environments.

In this article, I’m going to show you how you can load data from a PostgreSQL into an Autonomous Data Warehouse.

Pre-requisites

Oracle GoldenGate 21c for Non-Oracle Databases (PostgreSQL)
Oracle Cloud Infrastructure (OCI) GoldenGate deployment
PostgreSQL (in my case I used 11)
Autonomous Data Warehouse

Setting up GoldenGate PostgreSQL

I’m using an Oracle Linux instance. Some configurations will change if you are on Windows. On GoldenGate instance, apply this configuration:

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql11-server
sudo /usr/pgsql-11/bin/postgresql-11-setup initdb
sudo systemctl enable postgresql-11
sudo systemctl start postgresql-11export OGG_HOME=/u01/app/ogg
export LD_LIBRARY_PATH=$OGG_HOME/lib:/usr/pgsql-11/libsudo yum install postgresql11-libs
sudo yum install postgresql11-contrib#Configure the ODBC
sudo vi /etc/odbc.ini#Paste this configuration
[ODBC Data Sources]
PG_src=DataDirect 7.1 PostgreSQL Wire Protocol[ODBC]
IANAAppCodePage=4
InstallDir=/u01/app/ogg[PG_src]
Driver=/u01/app/ogg/lib/GGpsql25.so
Description=DataDirect 7.1 PostgreSQL Wire Protocol
Database=<postgreSQL database name>
HostName=<PostgreSQL IP>
PortNumber=5432 #PostgreSQL port

On PostgreSQL database:

sudo yum install postgresql11-contribEdit the postgresql.conf:sudo vi /var/lib/pgsql/11/data/postgresql.conflisten_addresses = '*' #what IP address(es) to listen on;
wal_level = logical            # set to logical for Capturemax_replication_slots = 2     # max number of replication slotsmax_wal_senders = 1            # one sender per max repl slottrack_commit_timestamp = on    # optional, correlates tx commit time
                               # with begin tx log record
                               # timestamp-based positioning)sudo vi /var/lib/pgsql/11/data/pg_hba.confhost all all 0.0.0.0/0 md5 # can be set the IP hereMake sure the PostgreSQL is open:sudo firewall-cmd --list-ports
sudo firewall-cmd --zone=public --permanent --add-port=5432/tcp
sudo firewall-cmd --reload
#Restart to apply channges
sudo systemctl restart postgresql-11

You can use an existing database use or create a new for GoldenGate. I’m going to create one:

sudo -u postgres psql
CREATE USER oggadmin WITH PASSWORD '<password>';#Add all required permissions for extracts and replicatsGRANT CONNECT ON DATABASE MYDBELOI TO oggadmin;
ALTER USER oggadmin WITH REPLICATION;
ALTER USER oggadmin WITH SUPERUSER;
GRANT USAGE ON SCHEMA <schema> TO oggadmin;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO oggadmin;
GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLE <schema>.<table> TO oggadmin;
CREATE SCHEMA AUTHORIZATION oggadmin;

Create the ODBCINI variable for the deployment from the deployment’s Configuration tab of the Service Manager Overview page.

Restart the deployment.

Configuring Extract

First, we need to create the credentials to have access to PostgreSQL. Go to Configuration and add a credential. On Data Source Name (DNS) use the same you defined on odbc.ini.

Connect to database and add TRANDATA to schema or tables you want.

Add an extract and pick the credential you just created:

On Parameters define which table(s) or schema(s) you want to capture data.

Start the extract:

At this point, you are already capturing data from PostgreSQL but not replicating into an Autonomous Data Warehouse. For to do that, we need to configure first a distribution path between Oracle GoldenGate for PostgreSQL and OCI GoldenGate (for Oracle).

Add a Distribution Path

We need to set up a secure connection between GoldenGate PostgreSQL and OCI GoldenGate. For that, we need to have the OCI GoldenGate certificate.

Go to your OCI GoldenGate instance and download the certificate:

Duplicate the file and change the extension from .cer to .pem

Go to Service Manager on GoldenGate PostgreSQL and click on certificate manager:

Upload the OCI GG certificate (.pem):

Now, go to OCI GoldenGate and create a new user to be used for Distribution Path. Choose a username and select the role operator:

On your GoldenGate PostgreSQL and add a new credential using the same username and password you put previously:

Go to Distribution Server and create a new path. All parameters for target are the ones you defined previously (except trail name). Click create and run.