oracle goldengate实现远程抽取postgresql 到 postgresql的实时同步【绝对无坑版，亲测流程验证】
oracle goldengate实现postgresql 到 postgresql的实时同步
源端：postgresql1 -> postgresql2 流复制主备同步
目标端：postgresql
数据库版本：postgresql 12.14
ogg版本：21.3
架构图：
数据库安装以及流复制主备同步配置忽略。以下直接进入OGG的配置
OGG服务器环境变量配置
export export OGG_HOME=/ogg213 export ODBCINI=/etc/odbc.ini export ODBCSYSINI=/etc LD_LIBRARY_PATH=/ogg213/lib:/pgdb/psql/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
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]#
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
MGR进程配置
GGSCI (oggser) 2> view param mgr PORT 7810 DYNAMICPORTLIST 7810-7820 AUTORESTART EXTRACT *, WAITMINUTES 2, RESETMINUTES 5 PURGEOLDEXTRACTS ./DIRDAT/*, USECHECKPOINTS,MINKEEPHOURS 24 AUTORESTART ER *, RETRIES 3, WAITMINUTES 2,RESETMINUTES 10 ACCESSRULE, PROG *, IPADDR 192.168.56.*, ALLOW
测试验证登录数据库成功：
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>
注册抽取进程
链接到备库注册抽取进程，失败。
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 (oggser as ggadmin@pg_src1) 10> register extract ep1 2025-05-31 11:34:52 INFO OGG-25353 The replication slot 'ep1_831e5a3f6113dbb2' for group 'EP1' already exist in the database 'testdb'. GGSCI (oggser as ggadmin@pg_src1) 11> UNREGISTER EXTRACT ep1 with DATABASE testdb ----->>> 删除之前增加的 2025-05-31 11:36:30 INFO OGG-25356 Successfully dropped the replication slot 'ep1_831e5a3f6113dbb2' for Extract group 'EP1' in database 'testdb'. GGSCI (oggser as ggadmin@pg_src1) 12> register extract ep1 2025-05-31 11:36:37 INFO OGG-25355 Successfully created replication slot 'ep1_831e5a3f6113dbb2' for Extract group 'EP1' in database 'testdb'.
在数据库查看复制曹信息
testdb=# SELECT * FROM pg_stat_replication;
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;
添加trandata
GGSCI (oggser as ggadmin@pg_src1) 2> ADD TRANDATA public.tab1 Logging of supplemental log data is already enabled for table public.tab1 with REPLICA IDENTITY set to DEFAULT
启动抽取进程失败：
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.
根据提示的版本LIBPG的版本号与postgresql的版本号不一致导致。查看postgresql lib的安装包。
[root@oggser soft]# rpm -ivh postgresql12-libs-12.21-1PGDG.rhel7.x86_64.rpm warning: postgresql12-libs-12.21-1PGDG.rhel7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 73e3b907: NOKEY Preparing... ################################# [100%] Updating / installing... 1:postgresql12-libs-12.21-1PGDG.rhe################################# [100%] [root@oggser ogg213]# yum list |grep postgres postgresql-libs.x86_64 9.2.24-1.el7_5 @local postgresql12-libs.x86_64 12.21-1PGDG.rhel7 installed
重新启动ep1进程，问题仍然出现。
尝试卸载
[root@oggser ogg213]# rpm -qa |grep postgresql-libs postgresql-libs-9.2.24-1.el7_5.x86_64 [root@oggser ogg213]# rpm -e postgresql-libs-9.2.24-1.el7_5.x86_64 [root@oggser ogg213]# rpm -qa |grep postgresql-libs
再次重启ep1进程，正常。
GGSCI (oggser) 3> start ep1 Sending START request to Manager ... Extract group EP1 starting. GGSCI (oggser) 4> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EP1 00:42:22 00:00:01 GGSCI (oggser) 9> info ep1 Extract EP1 Last Started 2025-05-31 12:19 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:05 ago) Process ID 4427 VAM Read Checkpoint 2025-05-31 11:37:21.985039 Replication Slot Unavailable(requires DBLOGIN) Slot Restart LSN Unavailable(requires DBLOGIN) Slot Flush LSN Unavailable(requires DBLOGIN) Current Log Position Unavailable(requires DBLOGIN) GGSCI (oggser) 10> stop ep1 Sending STOP request to Extract group EP1 ... Request processed.
查看进程状态的提示需要登录，尝试先登录数据库再启动。
GGSCI (oggser) 12> dblogin sourcedb pg_src1 userid ggadmin password Bai_yun123 2025-05-31 12:42:07 INFO OGG-03036 Database character set identified as UTF-8. Locale: en_US.UTF-8. 2025-05-31 12:42:07 INFO OGG-03037 Session character set identified as UTF-8. Successfully logged into database. GGSCI (oggser as ggadmin@pg_src1) 13> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT STOPPED EP1 00:00:00 00:21:55 GGSCI (oggser as ggadmin@pg_src1) 14> start ep1 Sending START request to Manager ... Extract group EP1 starting. GGSCI (oggser as ggadmin@pg_src1) 15> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EP1 00:00:00 00:00:01 GGSCI (oggser as ggadmin@pg_src1) 16> info ep1 Extract EP1 Last Started 2025-05-31 12:42 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:05 ago) Process ID 13486 VAM Read Checkpoint 2025-05-31 11:37:21.985039 Replication Slot ep1_831e5a3f6113dbb2 is active with PID 1900 in database testdb Slot Restart LSN 0/301BE30 Slot Flush LSN 0/301BE68 Current Log Position 0/301BE68 GGSCI (oggser as ggadmin@pg_src1) 17>
至此抽取进去配置完成。
添加应用进程
添加检查点表
GGSCI (oggser as ggadmin@pg_src1) 19> dblogin sourcedb pg_tgt, userid ggadmin, password Bai_yun123 2025-05-31 12:53:51 INFO OGG-03036 Database character set identified as UTF-8. Locale: en_US.UTF-8. 2025-05-31 12:53:51 INFO OGG-03037 Session character set identified as UTF-8. Successfully logged into database. GGSCI (oggser as ggadmin@pg_tgt) 20> ADD CHECKPOINTTABLE ggadmin.oggcheck 2025-05-31 12:54:06 WARNING OGG-30055 Database error 5738688 ([Oracle][ODBC PostgreSQL Wire Protocol driver][PostgreSQL]ERROR: VERROR; schema "ggadmin" does not exist(Position 14; File namespace.c; Line 3045; Routine get_namespace_oid; )). Error: Failed creating checkpoint table ggadmin.oggcheck. GGSCI (oggser as ggadmin@pg_tgt) 21> ADD CHECKPOINTTABLE public.oggcheck Successfully created checkpoint table public.oggcheck.
配置应用进程参数
edit param rp1 replicat rp1 setenv(PGCLIENTENCODING = "UTF8") setenv(ODBCINI="/etc/odbc.ini" ) targetdb pg_tgt, userid ggadmin, password Bai_yun123 map public.tab1, public.tab1; GGSCI (oggser as ggadmin@pg_tgt) 23> ADD REPLICAT rp1, EXTTRAIL ./dirdat/e1, CHECKPOINTTABLE public.oggcheck Replicat added.
启动应用进程失败
2025-05-31 12:57:24 INFO OGG-03037 Session character set identified as UTF-8.
map public.tab1, public.tab1;
Source Context :
SourceModule : [er.wildcard]
SourceID : [er/wildcard.c]
SourceMethod : [WILDCARD_get_entry]
SourceLine : [2658]
ThreadBacktrace : [12] elements
: [/ogg213/libgglog.so(CMessageContext::AddThreadContext())]
: [/ogg213/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...))]
: [/ogg213/libgglog.so(_MSG_String(CSourceContext*, int, char const*, CMessageFactory::MessageDisposition))]
: [/ogg213/replicat(WILDCARD_get_entry(ggs::gglib::ggunicode::UString const&, recopts_t*, bool, bool, unsigned int*, void*, int, int, bool&, catalogList&, bool, bool, bool))]
: [/ogg213/replicat(get_infile_params(ggs::gglib::ggapp::ReplicationContextParams&, ggs::gglib::ggdatasource::DataSourceParams&, ggs::gglib::ggdatatarget::DataTargetParams&, ggs::ggli
b::ggmetadata::MetadataContext&))]
: [/ogg213/replicat()]
: [/ogg213/replicat(ggs::gglib::MultiThreading::MainThread::ExecMain())]
: [/ogg213/replicat(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*))]
: [/ogg213/replicat(ggs::gglib::MultiThreading::MainThread::Run(int, char**))]
: [/ogg213/replicat(main)]
: [/lib64/libc.so.6(__libc_start_main)]
: [/ogg213/replicat()]
2025-05-31 12:57:24 ERROR OGG-00204 Missing TARGET specification.
2025-05-31 12:57:24 ERROR OGG-01668 PROCESS ABENDING.
原因：
参数配置中缺少target 关键字。
map public.tab1, target public.tab1;
修复后再次启动正常。
GGSCI (oggser as ggadmin@pg_tgt) 35> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EP1 00:00:00 00:00:00 REPLICAT RUNNING RP1 00:00:00 00:00:05
查看抽取进程和应用进程信息
GGSCI (oggser as ggadmin@pg_tgt) 39> info ep1 Extract EP1 Last Started 2025-05-31 12:42 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:11 ago) Process ID 13486 VAM Read Checkpoint 2025-05-31 11:37:21.985039 Replication Slot Unavailable(requires DBLOGIN) Slot Restart LSN Unavailable(requires DBLOGIN) Slot Flush LSN Unavailable(requires DBLOGIN) Current Log Position Unavailable(requires DBLOGIN) GGSCI (oggser as ggadmin@pg_tgt) 40> dblogin sourcedb pg_src1, userid ggadmin, password Bai_yun123 2025-05-31 13:04:43 INFO OGG-03036 Database character set identified as UTF-8. Locale: en_US.UTF-8. 2025-05-31 13:04:43 INFO OGG-03037 Session character set identified as UTF-8. Successfully logged into database. GGSCI (oggser as ggadmin@pg_src1) 41> info ep1 Extract EP1 Last Started 2025-05-31 12:42 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:03 ago) Process ID 13486 VAM Read Checkpoint 2025-05-31 11:37:21.985039 Replication Slot ep1_831e5a3f6113dbb2 is active with PID 1900 in database testdb Slot Restart LSN 0/301BE30 Slot Flush LSN 0/301BE68 Current Log Position 0/301BE68
数据初始化进程配置
初始化抽取进程
dblogin sourcedb pg_src1, userid ggadmin, password Bai_yun123 add ext ie1, sourceistable edit param ie1 --添加如下内容 extract ie1 setenv(PGCLIENTENCODING = "UTF8") setenv(ODBCINI="/etc/odbc.ini" ) sourcedb pg_src1, userid ggadmin, password Bai_yun123 extfile ./dirdat/i1 table public.tab1; --启动存量数据抽取 start ie1 info ie1
初始化应用进程
add rep ir1, exttrail ./dirdat/i1, nodbcheckpoint edit param ir1 --添加如下内容 replicat ir1 setenv(PGCLIENTENCODING = "UTF8") setenv(ODBCINI="/etc/odbc.ini" ) targetdb pg_tgt, userid ggadmin, password Bai_yun123 discardfile ./dirrpt/ir11.dsc map public.tab1, target public.tab1; --启动ir1进程 start ir1 info ir1
查看初始化信息
GGSCI (oggser as ggadmin@pg_src1) 50> info ir1
Replicat IR1 Last Started 2025-05-31 13:10 Status RUNNING
Checkpoint Lag 00:01:45 (updated 00:00:03 ago)
Process ID 14884
Log Read Checkpoint File ./dirdat/i1000000
2025-05-31 13:08:25.380705 RBA 1781
GGSCI (oggser as ggadmin@pg_src1) 51> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EP1 00:00:00 00:00:07
REPLICAT RUNNING IR1 00:01:45 00:00:07
REPLICAT RUNNING RP1 00:00:00 00:00:05
GGSCI (oggser as ggadmin@pg_src1) 52> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EP1 00:00:00 00:00:00
REPLICAT RUNNING IR1 00:00:00 00:00:02
REPLICAT RUNNING RP1 00:00:00 00:00:00
GGSCI (oggser as ggadmin@pg_src1) 53> stats ir1, total
Sending STATS request to Replicat group IR1 ...
Start of statistics at 2025-05-31 13:10:45.
Replicating from public.tab1 to public.tab1:
*** Total statistics since 2025-05-31 13:10:11 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
End of statistics.
GGSCI (oggser as ggadmin@pg_src1) 54>
GGSCI (oggser as ggadmin@pg_src1) 46> info ie1
Extract IE1 Last Started 2025-05-31 13:08 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Table public.tab1
2025-05-31 13:08:25 Record 1
Task SOURCEISTABLE
目标端查看数据已经初始化完成。
DML测试验证
源端主库插入变更。
testdb=# insert into tab1 values(2,'pg2'); INSERT 0 1 testdb=# testdb=# insert into tab1 values(3,'pg3'); INSERT 0 1 testdb=# insert into tab1 values(4,'pg4'); INSERT 0 1 testdb=# update tab1 set tname='pgbak' where tid=1; UPDATE 1 testdb=# delete from tab1 where tid=2; DELETE 1
OGG服务器查看进程统计信息
GGSCI (oggser as ggadmin@pg_src1) 57> stats ep1 ,total
Sending STATS request to Extract group EP1 ...
Start of statistics at 2025-05-31 13:16:46.
Output to ./dirdat/e1:
Extracting from public.tab1 to public.tab1:
*** Total statistics since 2025-05-31 13:15:08 ***
Total inserts 3.00
Total updates 1.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 5.00
End of statistics.
GGSCI (oggser as ggadmin@pg_src1) 58> stats rp1 ,total
Sending STATS request to Replicat group RP1 ...
Start of statistics at 2025-05-31 13:16:55.
Replicating from public.tab1 to public.tab1:
*** Total statistics since 2025-05-31 13:15:12 ***
Total inserts 3.00
Total updates 1.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 5.00
End of statistics.
GGSCI (oggser as ggadmin@pg_src1) 59>
参考文档：
https://docs.oracle.com/en/middleware/goldengate/core/19.1/oggmp/oracle-goldengate-classic-postgresql-server.html#GUID-15DF455B-2C8D-48B4-9158-FF4A93ACBCD8
https://docs.oracle.com/en/middleware/goldengate/core/19.1/oggmp/oracle-goldengate-classic-postgresql-server.html#GUID-98D9FC8A-D0C7-4C1B-9D5A-20CF559BC302
免责声明：我们致力于保护作者版权，注重分享，被刊用文章因无法核实真实出处，未能及时与作者取得联系，或有版权异议的，请联系管理员，我们会立即处理! 部分文章是来自自研大数据AI进行生成,内容摘自(百度百科,百度知道,头条百科,中国民法典,刑法,牛津词典,新华词典,汉语词典,国家院校,科普平台)等数据,内容仅供学习参考,不准确地方联系删除处理! 图片声明：本站部分配图来自人工智能系统AI生成,觅知网授权图片,PxHere摄影无版权图库和百度，360，搜狗等多加搜索引擎自动关键词搜索配图，如有侵权的图片，请第一时间联系我们。
