oracle goldengate实现postgresql 到 postgresql的实时同步
源端：postgresql1 -> postgresql2 流复制主备同步
目标端：postgresql
数据库版本：postgresql 12.14
ogg版本：21.3
数据库安装以及流复制主备同步配置忽略。以下直接进入OGG的配置
odbc配置：
[root@oradb1 ogg213]# yum list |grep postgresql-odbc.x86_64
Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
postgresql-odbc.x86_64 09.03.0100-2.el7 @centos7
[root@oradb1 ogg213]#
[root@oradb1 ogg213]# cat /etc/odbcinst.ini
# Example driver definitions
# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/lib64/psqlodbcw.so
Setup = /usr/lib64/libodbcpsqlS.so
Driver64 = /usr/lib64/psqlodbcw.so
Setup64 = /usr/lib64/libodbcpsqlS.so
FileUsage = 1
# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/libmyodbc5.so
Setup = /usr/lib/libodbcmyS.so
Driver64 = /usr/lib64/libmyodbc5.so
Setup64 = /usr/lib64/libodbcmyS.so
FileUsage = 1
[root@oradb1 ogg213]# cat /etc/odbc.ini
#Sample DSN entries
[ODBC Data Sources]
PG_src=DataDirect 12.14 PostgreSQL Wire Protocol
PG_tgt=DataDirect 12.14 PostgreSQL Wire Protocol
[ODBC]
IANAAppCodePage=4
InstallDir=/ogg213
[pg_src1]
Driver=/ogg213/lib/GGpsql25.so
Description=DataDirect 12.14 PostgreSQL Wire Protocol
Database=testdb
HostName=192.168.56.11
PortNumber=5432
LogonID=ggadmin
Password=Bai_yun123
TransactionErrorBehavior=2
[pg_src]
Driver=/ogg213/lib/GGpsql25.so
Description=DataDirect 12.14 PostgreSQL Wire Protocol
Database=testdb
HostName=192.168.56.12
PortNumber=5432
LogonID=ggadmin
Password=Bai_yun123
TransactionErrorBehavior=2
[pg_tgt]
Driver=/ogg213/lib/GGpsql25.so
Description=DataDirect 12.14 PostgreSQL Wire Protocol
Database=testdb
HostName=192.168.56.101
PortNumber=5432
LogonID=ggadmin
Password=Bai_yun123
TransactionErrorBehavior=2
[root@oradb1 ogg213]#
验证登录成功：
GGSCI (oradb1) 6> dblogin sourcedb pg_src userid ggadmin password Bai_yun123
2025-05-30 21:45:31 INFO OGG-03036 Database character set identified as UTF-8. Locale: en_US.UTF-8.
2025-05-30 21:45:31 INFO OGG-03037 Session character set identified as UTF-8.
Successfully logged into database.
GGSCI (oradb1 as ggadmin@pg_src) 7> dblogin sourcedb pg_tgt userid ggadmin password Bai_yun123
2025-05-30 21:45:38 INFO OGG-03036 Database character set identified as UTF-8. Locale: en_US.UTF-8.
2025-05-30 21:45:38 INFO OGG-03037 Session character set identified as UTF-8.
Successfully logged into database.
GGSCI (oradb1 as ggadmin@pg_tgt) 8>
OGG解压：
[root@oradb1 soft]# cd oggsoft/
[root@oradb1 oggsoft]# ll
total 467684
-rwxrwxr-x 1 postgres postgres 97040905 May 30 16:04 213000_ggs_Linux_x64_PostgreSQL_64bit.zip
-rw-rw-rw- 1 postgres postgres 381552640 Aug 5 2021 ggs_Linux_x64_PostgreSQL_64bit.tar
-rw-r--r-- 1 postgres postgres 2409 Aug 11 2021 OGG-21.3.0.0-README.txt
-rw-r--r-- 1 postgres postgres 306395 Aug 11 2021 oracle-goldengate-release-notes_21.3.pdf
[root@oradb1 oggsoft]#
$ tar -xvf ggs_Linux_x64_PostgreSQL_64bit.tar -C /ogg213
$ cd /ogg213
$ ./ggsci
$ create subdirs
链接到备库注册抽取进程，失败。
GGSCI (oradb1 as ggadmin@pg_src) 9> register extract ep1
2025-05-30 21:47:59 WARNING OGG-00552 Database operation failed: Executing statement to create replication slot. Called from PGUTIL_registerExtract:178. ODBC error: SQLSTATE S1C00 native database error 466560. [Oracle][ODBC PostgreSQL Wire Protocol driver][PostgreSQL]ERROR: VERROR; logical decoding cannot be used while in recovery(File logical.c; Line 113; Routine CheckLogicalDecodingRequirements;.
链接到主库注册抽取进程，成功。
GGSCI (oradb1 as ggadmin@pg_src) 10> dblogin sourcedb pg_src1 userid ggadmin password Bai_yun123
2025-05-30 21:52:04 INFO OGG-03036 Database character set identified as UTF-8. Locale: en_US.UTF-8.
2025-05-30 21:52:04 INFO OGG-03037 Session character set identified as UTF-8.
Successfully logged into database.
GGSCI (oradb1 as ggadmin@pg_src1) 11> register extract ep1
2025-05-30 21:52:16 INFO OGG-25355 Successfully created replication slot 'ep1_831e5a3f6113dbb2' for Extract group 'EP1' in database 'testdb'.
GGSCI (oradb1 as ggadmin@pg_src1) 12> edit param ep1
GGSCI (oradb1 as ggadmin@pg_src1) 13> view param ep1
extract ep1
setenv(PGCLIENTENCODING = "UTF8")
setenv(ODBCINI="/etc/odbc.ini" )
sourcedb testdb, userid ggadmin, password Bai_yun123
exttrail ./dirdat/e1
discardfile ./dirrpt/ep1.dsc
table public.tab1;
启动进程失败：
2025-05-30 22:13:22 ERROR OGG-00551 Database operation failed: Couldn't connect to testdb. ODBC error: SQLSTATE IM002 native database error 0. [DataDirect][ODBC lib] D
ata source name not found and no default driver specified.
2025-05-30 22:13:22 ERROR OGG-01668 PROCESS ABENDING.
修改
sourcedb pg_src1, userid ggadmin, password Bai_yun123
2025-05-30 22:16:24 ERROR OGG-25516 Oracle GoldenGate capture using LIBPQ client library version as 90,401 which is incompatible with the connected PostgreSQL server v
ersion 121,400 for streaming the log record. The LIBPQ client library version must be the same as or bigger than, that of the connected PostgreSQL server library version.
Source Context :
SourceModule : [ggvam.gen]
SourceID : [../gglib/ggvam/cvamgen.cpp]
SourceMethod : [vamInitialize]
SourceLine : [536]
ThreadBacktrace : [14] elements
: [/ogg213/libgglog.so(CMessageContext::AddThreadContext())]
: [/ogg213/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...))]
: [/ogg213/libgglog.so(_MSG_String_Int32(CSourceContext*, int, char const*, int, CMessageFactory::MessageDisposition))]
: [/ogg213/extract(com_goldengate_vam::CVamGen::vamInitialize())]
: [/ogg213/extract()]
: [/ogg213/extract(ggs::er::VAMDataSource::finalizeConfiguration())]
: [/ogg213/extract(ggs::gglib::ggapp::ReplicationContext::finalizeConfiguration())]
: [/ogg213/extract()]
: [/ogg213/extract(ggs::gglib::MultiThreading::MainThread::ExecMain())]
: [/ogg213/extract(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*))]
: [/ogg213/extract(ggs::gglib::MultiThreading::MainThread::Run(int, char**))]
: [/ogg213/extract(main)]
: [/lib64/libc.so.6(__libc_start_main)]
: [/ogg213/extract()]
2025-05-30 22:16:24 ERROR OGG-00145 Call to VAMInitialize returned with error status 16002.
2025-05-30 22:16:24 ERROR OGG-01668 PROCESS ABENDING.