Data replication between Oracle & PostgreSQL using Golden Gate.
Posted by FatDBA on June 24, 2021
Hi Mates,
Recently I was doing one migration where Golden Gate was used for data movement/migration between this source which is was Oracle 19.1 database to target, that was PostgreSQL 12. This was the first time I was doing this for PostgreSQL, I earlier tried with MySQL, Teradata and Cassandra and they were little tricky, specially the one with Cassandra. Let’s see how this one with PostgreSQL goes …
Okay, so this post is all about setting a test replication setup between Oracle and PostgreSQL database. Without further ado, let’s get started!
Details:
Source: Oracle 19c (19.3.0) database
Target: Vanilla/Open-Source PostgreSQL 12.6.7
Golden Gate Version: Golden Gate 19.1 for Oracle (Source), Golden Gate 19.1 for PostgreSQL Database (Target Host).
Source Hostname: canttowinsec.quebecdomain (IP 192.168.154.129)
Target Hostname: canttowin.ontadomain (192.168.154.128)
ON SOURCE:
Let’s first create the user and one test table on the source database which we will use to do this demo.
sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 23 15:21:01 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0
SQL> create user opeth identified by opeth;
User created.
SQL> grant connect to opeth;
Grant succeeded.
SQL> conn opeth/opeth
Connected.
SQL> create table testtable (col1 number, col2 varchar2(20));
Table created.
SQL> alter table testtable add primary key (col1);
Table altered.
Next I will install Golden Gate using a response file (silent method to install).
[oracle@canttowin Disk1]$ ./runInstaller -silent -nowait -responseFile /home/oracle/ggdirpsql/Disk1/response/oggcore.rsp
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 120 MB. Actual 6549 MB Passed
Checking swap space: must be greater than 150 MB. Actual 3945 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2021-06-18_04-33-50AM. Please wait ...[oracle@canttowin Disk1]$ You can find the log of this install session at:
/u01/app/oraInventory/logs/installActions2021-06-18_04-33-50AM.log
The installation of Oracle GoldenGate Core was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2021-06-18_04-33-50AM.log' for more details.
Successfully Setup Software.
Next, I will set the environmental variables to avoid any errors while calling the GG cli and will create the manager process.
[oracle@canttowinsec ggdirpsql]$ pwd
/home/oracle/ggdirpsql
[oracle@canttowinsec ggdirpsql]$ export PATH=$PATH:/home/oracle/ggdirpsql
[oracle@canttowinsec ggdirpsql]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/home/oracle/ggdirpsql
[oracle@canttowinsec ggdirpsql]$
[oracle@canttowin ggdir]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 19c on Oct 17 2019 21:16:29
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
GGSCI (canttowinsec.quebecdomain) 7> view param mgr
PORT 7810
GGSCI (canttowinsec.quebecdomain) 1> start mgr
Manager started
GGSCI (canttowinsec.quebecdomain) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
Now when manager process is running, next will login to the source database using Golden Gate and will list the table what we have created in the beginning, this is just to assure if everything is okay at the source.
GGSCI (canttowinsec.quebecdomain) 6> dblogin userid opeth, password opeth
Successfully logged into database.
GGSCI (canttowinsec.quebecdomain as opeth@dhavaldb) 10> list tables opeth.*
opeth.testtable
Found 1 tables matching list criteria.
All good, lets's create the EXTRACT process on the source system.
GGSCI (canttowinsec.quebecdomain) 2> edit param EORPSQL
GGSCI (canttowinsec.quebecdomain) 1> view params EORPSQL
EXTRACT EORPSQL
USERID opeth, password opeth
RMTHOST 192.168.154.128, MGRPORT 7810
RMTTRAIL ./dirdat/ep
TABLE opeth.testtable;
GGSCI (canttowinsec.quebecdomain) 4> add extract EORPSQL, tranlog, begin now
EXTRACT added.
GGSCI (canttowinsec.quebecdomain) 5> add exttrail ./dirdat/ep, extract EORPSQL, megabytes 5
EXTTRAIL added.
GGSCI (canttowinsec.quebecdomain) 6> start EORPSQL
Sending START request to MANAGER ...
EXTRACT EORPSQL starting
GGSCI (canttowinsec.quebecdomain) 11> info EORPSQL
EXTRACT EORPSQL Last Started 2021-06-23 15:10 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:07 ago)
Process ID 10714
Log Read Checkpoint Oracle Redo Logs
2021-06-23 15:11:11 Seqno 15, RBA 31941120
SCN 0.2711866 (2711866)
ON TARGET:
Now we are done with all prerequisites, installation and configurations at the source end, let’s move to the target system now where we have our PostgreSQL database running. I will create the same table what we have created on Oracle database (source).
[postgres@canttowin ggdirpsql]$ psql -p 5432
psql (12.6.7)
Type "help" for help.
postgres=# \c opeth opeth
You are now connected to database "opeth" as user "opeth".
opeth=# CREATE TABLE "public"."testtable"
opeth-# (
opeth(# "col1" integer NOT NULL,
opeth(# "col2" varchar(20),
opeth(# CONSTRAINT "PK_Col111" PRIMARY KEY ("col1")
opeth(# )
opeth-# ;
CREATE TABLE
opeth=#
opeth=#
opeth=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+--------+-------+----------+---------+-------------
public | testtable | table | postgres | 0 bytes |
(1 row)
Next comes the most important part, that is to create the odbc.ini file, Golden Gate uses an ODBC connection to connect to the Postgres database. The ODBC driver is shipped with the installation and on Unix you have to create the ODBC configuration file which is commonly called odbc.ini on your own. You need to create this file in GG_HOME directory on the target system.
[postgres@canttowin ggdirpsql]$ more odbc.ini
[ODBC Data Sources]
PostgreSQL on pgsql
[ODBC]
IANAAppCodePage=4
InstallDir=/home/postgres/ggdirpsql
[pg12db]
Driver=/home/postgres/ggdirpsql/lib/GGpsql25.so
Description=Postgres driver
Database=opeth
HostName=canttowin.ontadomain
PortNumber=5432
LogonID=opeth
Password=opeth
Reference:
Driver=<your goldengate home directory>/lib/GGpsql25.so
InstallDir= <where you have installed your GG software on target server.
IANAAppCodePage= <The value 4 represents the ISO-8859-1 character set>
Database=<Postgres Database Name>
HostName=<Hostname of the Postgres database>
PortNumber=<Port number of the Postgres database>
LogonID=<Username of the Postgres database>
Password=<Password of the Postgres database>
Now, let’s set the environmental variables to point locations for odbc file along with lib directory and installation dir and create all required GG specific directories on the target database server.
[postgres@canttowin ggdirpsql]$ export ODBCINI=/home/postgres/ggdirpsql/odbc.ini
[postgres@canttowin ggdirpsql]$ export PATH=$PATH:/home/postgres/ggdirpsql
[postgres@canttowin ggdirpsql]$ export LD_LIBRARY_PATH=/home/postgres/ggdirpsql/lib
[postgres@canttowin ggdirpsql]$ ./ggsci
Oracle GoldenGate Command Interpreter for PostgreSQL
Version 19.1.0.0.200714 OGGCORE_19.1.0.0.0OGGBP_PLATFORMS_200628.2141
Linux, x64, 64bit (optimized), PostgreSQL on Jun 29 2020 03:59:15
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
GGSCI (canttowin.ontadomain) 1> create subdirs
Creating subdirectories under current directory /home/postgres/ggdirpsql
Parameter file /home/postgres/ggdirpsql/dirprm: created.
Report file /home/postgres/ggdirpsql/dirrpt: created.
Checkpoint file /home/postgres/ggdirpsql/dirchk: created.
Process status files /home/postgres/ggdirpsql/dirpcs: created.
SQL script files /home/postgres/ggdirpsql/dirsql: created.
Database definitions files /home/postgres/ggdirpsql/dirdef: created.
Extract data files /home/postgres/ggdirpsql/dirdat: created.
Temporary files /home/postgres/ggdirpsql/dirtmp: created.
Credential store files /home/postgres/ggdirpsql/dircrd: created.
Masterkey wallet files /home/postgres/ggdirpsql/dirwlt: created.
Dump files /home/postgres/ggdirpsql/dirdmp: created.
Now, time to create all GG related processes, will start with manager process, followed by replicat
GGSCI (canttowin.ontadomain) 2> edit param mgr
GGSCI (canttowin.ontadomain) 3> start mgr
Manager started.
GGSCI (canttowin.ontadomain) 2> dblogin sourcedb pg12db userid opeth
Password:
2021-06-23 15:00:58 INFO OGG-03036 Database character set identified as UTF-8. Locale: en_US.UTF-8.
2021-06-23 15:00:58 INFO OGG-03037 Session character set identified as UTF-8.
Successfully logged into database.
GGSCI (canttowin.ontadomain as opeth@pg12db) 5> list tables public.*
public.testtable
Found 1 tables matching list criteria.
GGSCI (canttowin.ontadomain as opeth@pg12db) 15> view params RORPSQL
REPLICAT RORPSQL
SOURCEDEFS ./dirdef/testtable.def
SETENV ( PGCLIENTENCODING = "UTF8" )
SETENV (ODBCINI="/home/postgres/ggdirpsql/odbc.ini" )
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
TARGETDB pg12db, USERID opeth, PASSWORD opeth
DISCARDFILE ./dirrpt/diskg.dsc, purge
MAP opeth.testtable, TARGET public.testtable, COLMAP (COL1=col1,COL2=col2);
GGSCI (canttowin.ontadomain as opeth@pg12db) 7> add replicat RORPSQL, NODBCHECKPOINT, exttrail ./dirdat/ep
REPLICAT added.
GGSCI (canttowin.ontadomain as opeth@pg12db) 8> start RORPSQL
Sending START request to MANAGER ...
REPLICAT RORPSQL starting
GGSCI (canttowin.ontadomain as opeth@pg12db) 9> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RORPSQL 00:00:59 00:00:03
GGSCI (canttowin.ontadomain as opeth@pg12db) 13>
GGSCI (canttowin.ontadomain as opeth@pg12db) 13> info RORPSQL
REPLICAT RORPSQL Last Started 2021-06-23 15:20 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:03 ago)
Process ID 97138
Log Read Checkpoint File ./dirdat/ep000000000
2021-06-23 15:21:41.005117 RBA 29169
All set, we have all processes running at both source and target system. Now, if you running on Golden gate version 12.3 and above, no need to do generate the definition of tables, as the metadata is now present in the trail file itself, like in this demo I am using GG 19.1.
So, only if you running on GG version < 12.3 you need to generate the definition (using defgen file) of the table on source system and move it to the DIRDAT directory on target so that replicat considers that.
Now, let’s do some testing to see if records are flowing from source to target database.
ON SOURCE:
[oracle@canttowinsec dirdef]$ !sql
sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 23 15:21:01 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0
SQL> conn opeth/opeth
Connected.
SQL> insert into testtable values (1,'hello world!');
1 row created.
SQL> select * from testtable;
COL1 COL2
---------- --------------------
1 hello world!
SQL>
SQL> commit;
Commit complete.
Let’s see if the same record is reached the target side.
ON TARGET:
[postgres@canttowin ggdirpsql]$ psql -p 5432
psql (12.6.7)
Type "help" for help.
postgres=# \c opeth opeth
You are now connected to database "opeth" as user "opeth".
psql (12.6.7)
Type "help" for help.
opeth=# select * from testtable;
col1 | col2
------+---------
10 | hello world!
(1 rows)
opeth=#
Cool! it’s pretty easy to setup this solution to migrate your data from Oracle to PostgreSQL database.
Few things to note here
–> You don’t need to install Golden Gate for PostgreSQL, on source system which is where your oracle database is, requires Golden Gate for Oracle.
–> Only on target database is where you have to install Golden Gate for PostgreSQL
–> odbc.ini is the file which you have to create on target end (where your postgresql server is).
–> Always before calling ggsci, set PATH and LD_LIBRARY_PATH, else you might get an error message while calling the cli.
–> Be very careful while creating the odbc.ini file otherwise you will end up with some obscure errors,
Hope It Helped!
Prashant Dixit
Tales From A Lazy Fat DBA 
Leave a Reply