使用oracle goldengate同步postgresql到postgresql
在之前的文档中，有测试验证postgresql到oracle的同步。如果想了解更多，请参考连接：
https://blog.csdn.net/bing_yuan/article/details/129613238?sharetype=blogdetail&sharerId=129613238&sharerefer=PC&sharesource=bing_yuan&spm=1011.2480.3001.8118
在这篇文章中将测试验证从postgresql到postgresql的同步，同步工具使用的是oracle goldengate（OGG）。
测试环境：
ogg 21.3
postgresql 9.4.1
目标端postgresql 12.14
测试的同步是从postgresql的备库同步数据到目标库
|192.168.56.11 |pg主库
|192.168.56.12 |pg只读备库
|192.168.56.101 |pg目标库 ogg端
软件的安装过程在这篇文章中忽略，具体请参考上面的链接。
以下是具体的的测试过程
GGSCI (oradb1) 2> dblogin sourcedb pg_src userid postgres password postgres
2025-05-09 09:37:53 WARNING OGG-00552 Database operation failed: Couldn't connect to pg_src. ODBC error:
SQLSTATE IM002 native database error 0. [DataDirect][ODBC lib] Driver Manager Message file not found.
Please check for the value of InstallDir in your odbc.ini.
Error: Database operation failed: Couldn't connect to pg_src. ODBC error: SQLSTATE IM002 native database error 0.
[DataDirect][ODBC lib] Driver Manager Message file not found. Please check for the value of InstallDir in your
odbc.ini.Failed to open data source pg_src for user postgres
处理方法：
Description=DataDirect 10.3 PostgreSQL Wire Protocol
修改为
Description=DataDirect 9.4 PostgreSQL Wire Protocol
GGSCI (oradb1) 2> dblogin sourcedb pg_src userid postgres password postgres
2025-05-09 10:17:53 WARNING OGG-00552 Database operation failed: Executing query:
SET idle_in_transaction_session_timeout=0. Called from execute:2008.
ODBC error: SQLSTATE 37000 native database error 6820852. [Oracle][ODBC PostgreSQL Wire Protocol driver][PostgreSQL]
ERROR: unrecognized configuration parameter "idle_in_transaction_session_timeout"(File guc.c; Line 5558; Routine set_config_option;.
Error: Failed to execute SQL statement 'SET idle_in_transaction_session_timeout=0'Failed to open data source pg_src for user postgres
主库创建数据库同步用户
create user ggadmin with password 'Bai_yun123';
ALTER USER ggadmin WITH REPLICATION;
ALTER USER ggadmin WITH SUPERUSER;
GRANT USAGE ON SCHEMA public TO ggadmin;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ggadmin;
GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLE tbl_test TO ggadmin;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO ggadmin;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO ggadmin;
GRANT ALL PRIVILEGES ON DATABASE testdb TO ggadmin;
GRANT CONNECT ON DATABASE testdb TO ggadmin;
GRANT CREATE ON DATABASE testdb TO ggadmin;
create user ogguser superuser login password 'ogguserP2021';
dblogin sourcedb pg_src userid ggadmin password Bai_yun123
尝试更换用户重新登录。问题仍然出现。经过查询 idle_in_transaction_session_timeout 参数在 postgres 9.6版本开始提供。测试版本是postgresql 9.4.1
https://postgresqlco.nf/doc/zh/param/idle_in_transaction_session_timeout/9.6/
目标端登录成功。
dblogin sourcedb pg_tgt userid ggadmin password Bai_yun123
GGSCI (oradb1) 2> dblogin sourcedb pg_tgt userid ggadmin password Bai_yun123
2025-05-09 10:38:18 WARNING OGG-00552 Database operation failed: Couldn't connect to pg_tgt.
ODBC error: SQLSTATE 08001 native database error 0. [Oracle][ODBC PostgreSQL Wire Protocol driver]Connection refused.
Verify Host Name and Port Number.
Error: Database operation failed: Couldn't connect to pg_tgt. ODBC error: SQLSTATE 08001 native database error 0.
[Oracle][ODBC PostgreSQL Wire Protocol driver]Connection refused.
Verify Host Name and Port Number.Failed to open data source pg_tgt for user ggadmin
检查ODBC.INI配置发现是端口不对，修改数据库端口之后重新登录正常。
GGSCI (oradb1) 3> dblogin sourcedb pg_tgt userid ggadmin password Bai_yun123
2025-05-09 10:39:28 INFO OGG-03036 Database character set identified as UTF-8. Locale: en_US.utf8.
2025-05-09 10:39:28 INFO OGG-03037 Session character set identified as UTF-8.
Successfully logged into database.
尝试更换OGG版本，ogg从21.3版本才开始有postgresql版本。目前出了一个最新的版本23.4.
后续。。