参考：https://blogs.oracle.com/dataintegration/post/how-to-replicate-data-using-goldengate-postgresql

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?

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:

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

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。