Replicating PostgreSQL data using Oracle GoldenGate

Something that has been brewing for years – since late 2017 – is the constant request from Oracle customers on how to capture data from PostgreSQL databases.  Many of Oracle’s customers are either moving towards PostgreSQL and off of Oracle or looking for another way to pull data from PostgreSQL into their Oracle Data Warehouses.  In either case, Oracle has finally gotten around to releasing a fully supported version of Oracle GoldenGate for PostgreSQL, meaning that customers can now capture (extract) and apply (replicat) data from PostgreSQL 10 and later.

The downside to this is that Oracle released it in “Classic” architecture instead of the “Microservices” architecture.  Meaning customers still do not have access to remote administration capabilities and will have to rely on server access.  A positive though is that this version of Oracle GoldenGate can be used in a hub-n-spoke architecture and for bi-directional replication.  I will not be covering those topics though.

Let’s get started …

Prerequisites:

As with anything there is always a set of prerequisites that are needed before you can proceed with an installation.  In this cases, it holds true – the Oracle docs don’t even cover a good bit of these, but with a little help I was able to resolve what was needed.  

Linux Packages:

PostgreSQL#-contrib – this package is needed to allow you to register the extract with the PostgreSQL database

PostgreSQL.conf file changes:

These items were interesting and documented in Oracle docs, but remember, after changes the PostgreSQL database must be restarted.

wal_level = logicial
max_replication_slots = 10 (default)
max_wal_sender = 10 (default)
wal_receiver_status_interval (optional)
wal_sender_timeout (optional)
track_commit_timestamp = false (optional)

As you can tell Oracle is leveraging the Write Ahead Logging (WAL) parameters to ensure everything is read from the database and transaction logs correctly.

Users:

Just like typical Oracle GoldenGate setup, you must define a user that will be used for Oracle GoldenGate.  In my setups, I typically name the user “ggate” but this user can be named anything. The key here is to ensure the right privileges are assigned.  To setup an Oracle GoldenGate user, the following can be ran against a PostgreSQL database:

create user ggate with password ‘ggate’ login;

alter user ggate with replication;

Environment Variables:

There are a slew of environment variables that will need to be set to ensure that replication works.  What is listed below is what I had to setup to ensure it all worked.  Mileage will vary.

export PG_HOME=/usr/pgsql-12
export OGG_HOME=/opt/app/oracle/19.1.0/oggcore_1
export ODBCINI=/home/oracle/odbc.ini
export LD_LIBRARY_PATH=$PG_HOME/lib:$OGG_HOME/lib:$LD_LIBRARY_PATH
export PATH=$ODBCINI:$PATH

A couple of things to note here.  The $PG_HOME must be in the $LD_LIBRARY_PATH.  The $ODBCINI file location must be in the $PATH.  These items can be easily setup in the .bashrc or .bash_profile for your environment.

ODBC.ini file:

Oracle GoldenGate uses the ODBC.ini file to connect to the PostgreSQL database.  This file has to be configured and located by using the $ODBCINI environment variable.  For simplicity of this blog, a little, I’m not going to post what it should look like.  You can fine details within the Oracle docs -> here.

Setting up replication:

With all the prerequisites established, replication can be setup the exact same as any other Oracle GoldenGate “Classic” environment.  The steps are similar to these below.

Extract:

1. Install Oracle GoldenGate software – unzip/untar in the $OGG_HOME of choice.  Then open GGSCI and run CREATE SUBDIRS (./GGSCI) + (CREATE SUBDIRS)

2. Edit the MGR parameter file (EDIT PARAMS MGR), assign port number (default 7809), and start manager (START MGR)

3. Login to the PostgreSQL databases as your GoldenGate user (DBLOGIN SOURCEDB <dsn> USERID <user> PASSWORD <passwd>)

4. Register extract with PostgreSQL database (REGISTER EXTRACT <extract_name>)

5. Add extract (ADD EXTRACT <extract_name>, TRANLOG, BEGIN NOW)

6. Add trail file (ADD EXTTRAIL ./dirdat/aa, EXTRACT <extract_name>)

7. Edit parameter file for extract (EDIT PARAMS <extract_name>)

8. Add Trandata (ADD TRANDATA <schema>.<table>)

9. Start MGR and EXTRACT (START MGR) + (START EXTRACT <extract_name>)

10. Validate that MGR and EXTRACT have been started (INFO ALL)

Replicat:

1. Create the GoldenGate schema inside of the PostgreSQL database (CREATE SCHEMA <ggate>)

2. Login to the PostgreSQL database (DBLOGIN SOURCEDB <dsn> USERID <user> PASSWORD <passwd>)

3. Add Checkpoint Table (ADD CHECKPOINTTABLE <ggate>.<checkpointtable_name>)

4. Add Replicat (ADD REPLICAT <replicat_name>, EXTTRAIL ./dirdat/aa, CHECKPOINTTABLE <ggate>.<checkpointtable_name>)

5. Start Replicat (START REPLICAT <replicat_name>)

6. Check status of Replicat (INFO ALL)

What you didn’t see/Summary:

In the examples above, what was missing is the Data Pump Extract.  Since I was configuring the movement of data on a single server with a single PostgreSQL database to two (2) different schemas, the Data Pump Extract was not needed.  If you plan on moving data between sites, the Data Pump Extract is needed to route the trail files correctly.  Another option is to configure Oracle GoldenGate in the hub-n-spoke architecture and the same approach as above can be followed and simply use the remote-capture/remote-apply functionality.

In closing, Oracle has finally answered the call from customers by allowing them to capture and apply within a PostgreSQL environment.  This brings the replication standard of reliable replication to a development platform as well as enabling organizations to maximize their data movement strategies.

Enjoy!!

Please follow and like: