Load data from PostgreSQL into Autonomous Database using Oracle GoldenGate

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

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-11
export OGG_HOME=/u01/app/ogg
export LD_LIBRARY_PATH=$OGG_HOME/lib:/usr/pgsql-11/lib
sudo 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
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 Capture
max_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
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;

Configuring Extract

Add a Distribution Path

Creating Replicat for Autonomous Data Warehouse

--

--

--

Opinions expressed are solely my own and do not express the views or opinions of my employer Oracle. https://www.linkedin.com/in/eloilopes/

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

My Fav Web Development Resource

Chapter 3 Maps, Keywords, and Sets

10 Most Popular Programming Languages For 2020 and Beyond

34 Amazing Python Open Source Libraries for the Past Year (v.2019)

Making a RTS game in Unity (C#)

How To Install Kodi On Firestick?

ffmpeg + ImageMagick. Convert video to GIF by using the Terminal.app in macOS

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Eloi Lopes

Eloi Lopes

Opinions expressed are solely my own and do not express the views or opinions of my employer Oracle. https://www.linkedin.com/in/eloilopes/

More from Medium

talend as an ETL Tool

Create and Browse Reusable Datasets in Your Private S3 Buckets with quilt3

Running the Luigi scheduler

How to use DAG to tigger another DAG in Airflow?