How to replicate data using GoldenGate PostgreSQL(使用OGG同步PG数据库数据)

0    805    2


👉 本文共约4273个字,系统预计阅读时间或需17分钟。


How to replicate data using GoldenGate PostgreSQL

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?


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.


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:

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:



$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


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


How to replicate data using GoldenGate PostgreSQL(使用OGG同步PG数据库数据)后续精彩内容已被小麦苗无情隐藏,请输入验证码解锁本站所有文章!


Avatar photo





  • 18509239930
  • 个人微信

  • DB宝
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部