configure Oracle Golden Gate between Oracle - Postgres

 Source: Oracle 19c 

Target: PostgreSQL 15

Golden Gate Version: Golden Gate 19.1 for Oracle (Source), Golden Gate 19.1 for PostgreSQL Database (Target ).

Source Hostname: oracle 

Target Hostname: postgres 

Run below commands on Target Postgres database by root , postgres and Oracle users :- 

install postgress dev binary :-

rpm -ivh libtermcap-devel

rpm -ivh readline-devel

rpm -ivh gcc*

rpm -ivh zlib

rpm -ivh postgresql93-odbc

yum search postgre | grep odbc

give full permission to /tmp folder ( chmod -R 777 /tmp)

download and install postgress 15 

https://www.postgresql.org/ftp/source/v15.1/

postgresql-15.1.tar.gz

tar -xvf above file 

./configure

mkdir build_dir

cd build_dir

/path/to/source/tree/configure [options go there]

make

make install

make install-docs

adduser postgres

mkdir /u02/pgsql/data

chown postgres /u02/pgsql/data

su - postgres

/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

You can now start the database server using:

/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start

ps -ef | grep postgres

create posgres database :-

/usr/local/pgsql/bin/createdb test

/usr/local/pgsql/bin/psql test

Install the Oracle golden gate for postgres on target by Oracle user and specify below environment parameters :-

export PG_HOME=/usr/local/pgsql

export OGG_HOME=/u01/app/gg

export ODBCINI=/u01/app/gg/odbc.ini

export LD_LIBRARY_PATH=$PG_HOME/lib:$OGG_HOME/lib:$LD_LIBRARY_PATH

export PATH=$ODBCINI:$PATH

export ODBCINI=/u01/app/gg/odbc.ini

create odbc.ini file like below and give permission 777 :-

cat odbc.ini

[ODBC Data Sources]

test=DataDirect 7.1 PostgreSQL Wire Protocol

[ODBC]

IANAAppCodePage=4

InstallDir=/u01/app/gg

[test]

Driver=/u01/app/gg/lib/GGpsql25.so

Description=DataDirect 7.1 PostgreSQL Wire Protocol

Database=test

HostName=192.168.254.137

PortNumber=5432

LogonID=postgres

Password=nishusingh

[oracle@primary ~]$

On Oracle Source server :-

sqlplus / as sysdba

alter system set log_archive_dest='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both sid='*';

shutdown immediate

startup mount

alter database archivelog;

alter database open;

 

SELECT SUPPLEMENTAL_LOG_DATA_MIN,supplemental_log_data_pk, supplemental_log_data_ui FROM V$DATABASE;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA(ALL) COLUMNS;

ALTER DATABASE FORCE LOGGING;

Show parameter enable_goldengate_replication

ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;

 

create user postgres identified by postgres;

 

grant connect to postgres;

 

exec dbms_goldengate_auth.grant_admin_privilege('postgres');

conn postgres/postgres

 

exec dbms_goldengate_auth.grant_admin_privilege('postgres');

CREATE TABLE "public"."ggtest"

(

  "col1" integer NOT NULL,

  "col2" varchar(20),

  CONSTRAINT "PK_Col111" PRIMARY KEY ("col1")

);

alter table ggtest add primary key (col1);

./ggsci

edit param mgr

 

PORT 7811

 

start mgr

info all

dblogin userid postgres, password postgres

list tables postgres.*

view params EORPSQ

EXTRACT EORPSQ

USERID postgres, password postgres

RMTHOST 192.168.254.137, MGRPORT 7809

RMTTRAIL ./dirdat/ep

TABLE postgres.ggtest;

register extract EORPSQ, database

 

add extract  EORPSQ, integrated tranlog, begin now

add exttrail ./dirdat/ep , extract EORPSQ

edit params defgen1

DEFSFILE ./dirdef/ggtest.def

USERID postgres PASSWORD postgres

TABLE POSTGRES.GGTEST;

./defgen paramfile /u01/app/ggnew/dirprm/defgen1.prm

copy this defgen file to target gghome :- 

scp -r ggtest.def oracle@postgres:/u01/app/gg/dirdef/

start extract  EORPSQ

info EORPSQ

view report EORPSQ

stats EORPSQ

On target postgres Host :-

NOTE: POSTGRES SPECIAL

Security at Postgres may deny connections from other hosts, so check the Postgres config files:

Postgres conf file pg_hba.conf needs this config line:

         host    all             all             0.0.0.0/0           md5

so that ALL clients can connect. The sample above just means that all clients can connect to the Postgres database. Commonly this could show  more restrictive setting depending on business rules, so it is always worth to have a look at this file when clients can not connect to the Postgres database using the ODBC driver.

 

A second config file is the Listener which is configured in the  postgresql.conf. The parameter:

listen_addresses '*'

just means that Postgres listens on all available addresses. The settings here could be more restrictive and specify in the postgresql.conf for example :

listen_addresses = 'localhost'         # what IP address(es) to listen on;

so only localhost connections are possible.

export PG_HOME=/usr/local/pgsql

export OGG_HOME=/u01/app/gg

export ODBCINI=/u01/app/gg/odbc.ini

export LD_LIBRARY_PATH=$PG_HOME/lib:$OGG_HOME/lib:$LD_LIBRARY_PATH

export PATH=$ODBCINI:$PATH

export ODBCINI=/u01/app/gg/odbc.ini

./ggsci

view param mgr

PORT 7809

start mgr

su - postgres

/usr/local/pgsql/bin/psql -U postgres

\c test

CREATE TABLE "public"."ggtest"

(

  "col1" integer NOT NULL,

  "col2" varchar(20),

  CONSTRAINT "PK_Col111" PRIMARY KEY ("col1")

);

./ggsci

dblogin sourcedb test userid postgres, password nishusingh

list tables public.*

capture tabledef "public"."ggtest"

view params RORPSQL

REPLICAT RORPSQL

SOURCEDEFS ./dirdef/ggtest.def

SETENV ( PGCLIENTENCODING = "UTF8" )

SETENV (ODBCINI="/u01/app/gg/odbc.ini" )

SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")

TARGETDB test, USERID postgres, PASSWORD nishusingh

DISCARDFILE ./dirrpt/diskg.dsc, purge

MAP postgres.ggtest, TARGET public.ggtest, COLMAP (COL1=col1,COL2=col2);

start *

info RORPSQL , detail

info all

on source :-

sqlplus postgres/postgres

insert into testtable values (1,'hello world!');

insert into ggtest values (2,'nishu');

commit;

on target:

su - postgres

/usr/local/pgsql/bin/psql -U postgres

\c test

select * from ggtest;