X

Welcome to All Things Data Integration: Announcements, Insights, Best Practices, Tips & Tricks, and Trend Related...

How to replicate data using GoldenGate PostgreSQL

Nisharahmed Soneji
Senior Principal Product Manager, Oracle GoldenGate

Although every detailed information is available in the Oracle GoldenGate for PostgreSQL documentation that includes Installation, Using, and reference guide, however, sometimes the handy cook-book helps start with the product faster. With that thought, I am writing the series of blogs to help you using the various functionalities provided by Oracle GoldenGate PostgreSQL.

This blog shall help you replicating the data between PostgreSQL to PostgreSQL and PostgreSQL to Oracle databases. It would also mention how to setup the bi-directional replication for GoldenGate PostgreSQL and how to configure the GoldenGate for PostgreSQL and the Database in detail.

What Do You Need?

GoldenGate:

The pre-requisite to start with Oracle GoldenGate PostgreSQL is that you have some knowledge of GoldenGate and you have installed the GoldenGate for PostgresQL beforehand. The installation is up and running. You may download the GoldenGate product from MOS portal. You can install Oracle GoldenGate on a supported Linux 64-bit server. Please see the certification matrix for more details.

You may choose to setup the Remote capture and delivery using the hub configurations. In the hub configuration, you may have installed GoldenGate on a separate machine, and your database is installed on different machines (on a different endianness architecture), and you would configure the Remote capture and delivery for the replication purpose. It greatly helps you normalize your development platform. For example, GoldenGate is installed on Linux 64-bit server and your database is up and running on Sun Solaris or AIX or Windows system, you can still capture and delivery the data using the Remote technology (Cross Endianness supported) to all these Database systems.

Database:

The database configurations pre-requisite are very minimal. The following attributes in the PostgreSQL configuration file, located at $PG_INSTALL_HOME/data/postgresql.conf file, need to be modified as follows:

wal_level = logical                 #minimal, replica, or logical

max_replication_slots = 10   # max number of replication slots

Note: After any kind of changes made to the postgresql.conf configuration file, the database needs to be restarted.

Create the corresponding source database, tables, and a PostgreSQL authenticated user with super user privileges.Create the corresponding source database, tables, and a PostgreSQL authenticated user with super user privileges.

Few restrictions to consider:

  • The Extract group name needs to be unique across a single deployment. 
  • System databases are not supported for capture and delivery.
  • Database Version must be 10 or higher

Before starting Oracle GoldenGate PostgreSQL Extract, you need to register it with the corresponding database of interest and unregister once done. 

Configuring Extract from PostgreSQL and Replicating to PostgreSQL or Oracle Target Database

This section contains details on how to set up Oracle GoldenGate Extract from the PostgreSQL database and how to apply the captured data on PostgreSQL target database or an Oracle target database.

Note: All the paths used in this document are sample paths. You need to these paths as per the Oracle GoldenGate replication environment.

Extract & Replicat Pre-requisites on PostgreSQL:

  • Create a database user, having replication user privileges, that is dedicated to Oracle GoldenGate.
  • To preserve the security of your data, and to monitor Oracle GoldenGate processing accurately, do not permit other users, applications, or processes to log on as, or operate as, the Oracle GoldenGate database user.

Configuring Oracle GoldenGate PostgreSQL Extract 

Before performing the Extract setup, consider that the PostgreSQL’s LIBPQ($PG_INSTALL_HOME/lib) library and the DataDirect ODBC driver ($OGG_HOME/lib) are required to capture data from PostgreSQL database. The DataDirect ODBC driver is shipped with the OGG shiphome. Hence, no need to separately download the ODBC driver.

Here are the steps to configure the Extract:

  • Set the LD_LIBRARY_PATH:

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

where 

$PG_HOME is the PostgreSQL database installation directory and 

$OGG_HOME is the Oracle GoldenGate installation directory.

  • Set the following environment variables:

export ODBCINI=full-path to the ODBC.ini file

Example:

Export ODBCINI=/scratch/abc/work/Postgres/odbc.ini

export PG_HOME=/scratch/abc/work/postgresinstal

  • Create the ODBC.ini file. See the following sample $ODBCINI file and

“/scratch/abc/work/Postgres/7.1” is the Oracle GoldenGate installation directory used in the sample.

abc@slc100 Postgres> cat odbc.ini

[ODBC Data Sources]

pgdsn=DataDirect 7.1 PostgreSQL Wire Protocol postgres=DataDirect 7.1 PostgreSQL Wire Protocol jitiwari=DataDirect 7.1 PostgreSQL Wire Protocol

[ODBC]

IANAAppCodePage=4

InstallDir=/scratch/abc/work/Postgres/7.1

[pgdsn]

Driver=/scratch/abc/work/Postgres/7.1/lib/GGpsql25.so

Description=DataDirect 7.1 PostgreSQL Wire Protocol

Database=postgres

HostName=localhost

PortNumber=5432

LogonID=postgres

Password=welcome

  • Start GGSCI and run the command to create the necessary sub-directories.

GGSCI> CREATE SUBDIRS

  • Create the Manager parameter file, listing an unused PORT for the Manager to use.

GGSCI> EDIT PARAMS MGR PORT 7809

Save and close the Manager parameter file.

  • Register the Extract group with the database using the REGISTER EXTRACT command. This will create a replication slot. Make sure not to add the Extract before it is registered with the database.

GGSCI> REGISTER EXTRACT  POSTEXT with database PostgreSQL

Or

GGSCI> REGISTER EXTRACT  POSTEXT

  • Connect to the source database from GGSCI and enable supplemental logging for the user tables to be captured from.

GGSCI> DBLOGIN SOURCEDB pgdsn USERID username PASSWORD password 

GGSCI> ADD TRANDATA public.table0 

GGSCI> ADD TRANDATA public.table1 [ALLCOLS]|[KEYCOLSONLY]

GGSCI> ADD TRANDATA public.table2 [ALLCOLS]|[KEYCOLSONLY]

ADD TRANDATA without any option or with ALLCOLS option would set the REPLICA IDENTITY of the table to FULL.

ADD TRANDATA with KEYCOLSONLY option would set the REPLICA IDENTITY of the table to DEFAULT if the table has an explicit primary key defined, otherwise the REPLICA IDENTITYis set to FULL.

  • Add the Extract to the Oracle GoldenGate installation.

GGSCI> ADD EXTRACT postext, TRANLOG, BEGIN NOW

GGSCI> ADD EXTTRAIL ./dirdat/pe, EXTRACT postext

  • [Optional] Alter the Extract to position it based on the requirement. 

Positioning using EOF 

GGSCI> ALTER EXTRACT postext, eof

Positioning using LSN 

GGSCI> ALTER EXTRACT postext lsn <hi/lo>

Positioning using TIMESTAMP

Command to position to the current timestamp is as follows:

GGSCI> ADD EXTRACT postext tranlog,  begin now

GGSCI> ALTER EXTRACT postext

  • Create and save a new Extract parameter file. Following is a sample of the minimum required parameters for a unidirectional implementation.

GGSCI> EDIT PARAMS postext 

EXTRACT postext 

SOURCEDB pgdsn USERID postgres PASSWORD postgres

EXTTRAIL ./dirdat/pe TABLE public.table0;

TABLE public.table1;

TABLE public.table2;

  • Start the Manager and Extract and verify that the processes are running.

GGSCI>START MGR

GGSCI>START EXTRACT postext GGSCI>INFO ALL

Replicating on PostgreSQL

This section contains instructions on how to setup a unidirectional replication environment between source PostgreSQL database and a target PostgreSQL database.

Prerequisites: Create source (PostgreSQL) and target (PostgreSQL) databases, tables, and users with required privileges.

Setting up PostgreSQL Extract

Refer to the section Configuring Oracle GoldenGate PostgreSQL Extract.

Setting up the PostgreSQL Replicat

  • Create and save the Replicat parameter file. Following is a sample of the minimum required parameters for a unidirectional implementation.

GGSCI> EDIT PARAMS postrep 

REPLICAT postrep 

TARGETDB dsn-name USERID user-name PASSWORD password

MAP public.*, TARGET username.*;

  • Create a schema in the target database that will be used for Oracle GoldenGate objects.  Oracle recommends that you create a specific schema for Oracle GoldenGate.

CREATE SCHEMA [ogg];

  • Create a checkpoint table and add the Replicat to the Oracle GoldenGate installation.

GGSCI> DBLOGIN SOURCEDB dsn-name USERID user-name PASSWORD password 

GGSCI> ADD CHECKPOINTTABLE ogg.ggcheck 

GGSCI> ADD REPLICAT postrep, EXTTRAIL ./dirdat/pe,

CHECKPOINTTABLE  ogg.ggcheck

  • Start the Replicat and verify that all processes are running.

GGSCI>START REPLICAT postrep 

GGSCI>INFO ALL             

Configuring Oracle GoldenGate Extract from PostgreSQL and Apply on Oracle

This section contains instructions on how to setup a replication environment between source PostgreSQL database and a target Oracle database.

Prerequisites: Create source and target databases, tables, and PostgreSQL and Oracle users with sysadmin rights.

Setting up PostgreSQL Extract

Refer to the section Configuring Oracle GoldenGate PostgreSQL Extract.

Setting up the Oracle Replicat

For more information on how to set up Replicat on Oracle, refer to Oracle GoldenGate documentation for Oracle.

  • Create and save the Replicat parameter file. Following is a sample of the minimum required parameters for a unidirectional implementation.

GGSCI> EDIT PARAMS postrep 

REPLICAT postrep 

USERID username@db-name, PASSWORD password

MAP public.*, TARGET username.*;

  • Create a schema in the target database that will be used for Oracle GoldenGate objects. Oracle recommends that you create a specific schema for Oracle GoldenGate.

CREATE SCHEMA [ogg];

  • Create a checkpoint table and add the Replicat to the Oracle GoldenGate installation.

GGSCI> DBLOGIN USERID username@dbname PASSWORD password 

GGSCI> ADD CHECKPOINTTABLE ogg.ggcheck 

GGSCI> ADD REPLICAT postrep, EXTTRAIL ./dirdat/pe,

CHECKPOINTTABLE  ogg.ggcheck

  • Start the Replicat and verify that all processes are running.

GGSCI>START REPLICAT postrep 

GGSCI>INFO ALL

Configuring Bi-directional Replication between Two PostgreSQL Instances 

The Oracle GoldenGate bidirectional replication is supported on PostgreSQL. This support is provided using the TRANLOGOPTIONS FILTERTABLE parameter.

The following is a sample of bi-directional replication between two PostgreSQL instances:

The configuration is to run Extract (EBIS1) on machine M1 and apply the data onto machine M2 using Replicat (RBIT1). There is another Extract (EBIT1) running on machine M2, and applying data onto machine M1 using Replicat (RBIS1).

  M1    -----    M2

EBIS1 ---> RBIT1

RBIS1 <--- EBIT1

The Extract EBIT1, running on M2 should not capture the transactions applied by Replicat RBIT1(as the corresponding transactions were originated from Machine M1 itself). This is achieved by using theTRANLOGOPTIONS in the param file of the Extract EBIT1) running on machine M2.

Extract EBIS1 param file, running on Machine M1: 

extract EBIS1 

sourcedb postgres userid postgres, password postgres discardfile ./dirrpt/EBIS1.dsc, purge gettruncates 

rmthost docker.adc00rkm, mgrport 7101 rmttrail ./dirdat/Bidirection/bi 

TRANLOGOPTIONS filtertable postgres.checkpointa table postgres.bitble; 

Replicat  RBIT1 Configuration, running on Machine M2: 

Replicat Setup 

dblogin sourcedb qatarget userid qatarget, password qatarget delete checkpointtable public.checkpointb! 

pause 5

add checkpointtable public.checkpointb 

ADD REPLICAT RBIT1,EXTTRAIL ./dirdat/Bidirection/bi, checkpointtable public.checkpointb START REPLICAT RBIT1 pause 5 INFO RBIT1* 

Replicat Param file, running on Machine 2 

replicat RBIT1 

targetdb qatarget userid qatarget, password qatarget discardfile ./dirrpt/RBIT1.dsc, purge, megabytes 50 MAP postgres.bitble, TARGET public.bitble; 

Extract on Machine 2 

extract EBIT1 

sourcedb  qatarget userid qatarget, password qatarget discardfile ./dirrpt/EBIT1.dsc, purge gettruncates 

rmthost docker.adc00rkm, mgrport 7101 rmttrail ./dirdat/Bidirection/mi 

TRANLOGOPTIONS filtertable public.checkpointb table public.bitble; 

Replicat Setup on Machine M1: 

Replicat Setup  

dblogin sourcedb postgres userid postgres, password postgres delete checkpointtable postgres.checkpointa! pause 5 

add checkpointtable postgres.checkpointa 

ADD REPLICAT RBIS1,EXTTRAIL ./dirdat/Bidirection/mi, checkpointtable postgres.checkpointa

START REPLICAT RBIS1 pause 5 INFO RBIS1* 

Replicat param file, running on Machine M2: 

replicat RBIS1 

targetdb postgres userid postgres, password postgres discardfile ./dirrpt/RBIS1.dsc, purge, megabytes 50

MAP public.bitble, TARGET postgres.bitble; 

The instructions shall help you setup the GoldenGate for PostgreSQL in no time. You may also refer my Hands-on Youtube video where I have shown how to capture the data from Amazon RDS PostgreSQL data to Oracle Autonomous Database Service in the cloud. Let me know if you have any questions.

Please find the other important resources for GoldenGate PostgreSQL.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.