整体架构Oracle11g││ DBLINK▼DG4ODBCOracle Heterogeneous Services││ unixODBC▼PostgreSQL ODBC DriverpsqlODBC│▼PostgreSQL 14.6Oracle 11g 本身不能直接访问 PostgreSQL需要通过 ODBC 网关实现。DG4ODBC 是 Oracle 官方提供的异构数据库访问组件。一、环境假设Oracle 服务器Oracle 11.2.0.4centos7.9Linux x86_64IP: 192.192.103.113PostgreSQL 服务器PostgreSQL 14.6centos7.9IP: 192.192.103.117Port: 5432DB: postgresUser: postgresPassword: postgresSchema: publicTable: t1Schema: testTable: tb1以下所有操作都在 Oracle 服务器执行。二、Oracle服务器安装 ODBC 驱动yum install -y unixODBC unixODBC-devel[rootora113 ~]# odbcinst -junixODBC 2.3.1DRIVERS............: /etc/odbcinst.iniSYSTEM DATA SOURCES: /etc/odbc.iniFILE DATA SOURCES..: /etc/ODBCDataSourcesUSER DATA SOURCES..: /root/.odbc.iniSQLULEN Size.......: 8SQLLEN Size........: 8SQLSETPOSIROW Size.: 8[rootora113 ~]#三、安装 PostgreSQL ODBC 驱动重点yum install -y postgresql-odbc (centos7自带的pg odbc版本太低无法连接11g)建议下载安装包手动安装新的pg odbcpostgresql14-libs-14.23-2PGDG.rhel7.9.x86_64.rpmpostgresql14-odbc-16.00.0000-1PGDG.rhel7.x86_64.rpm下载链接https://download.postgresql.org/pub/repos/yum/14/redhat/rhel-7-x86_64/安装后检查find / -name psqlodbc*.so[rootora113 ~]# find / -name psqlodbc*.so/usr/lib64/psqlodbc.so/usr/lib64/psqlodbcw.so/usr/pgsql-14/lib/psqlodbc.so/usr/pgsql-14/lib/psqlodbca.so/usr/pgsql-14/lib/psqlodbcw.so我们使用新装的/usr/pgsql-14/lib四、配置 /etc/odbcinst.ini编辑vi /etc/odbcinst.ini加入[PostgreSQL]Description ODBC for PostgreSQLDriver /usr/pgsql-14/lib/psqlodbcw.soSetup /usr/pgsql-14/lib/libodbcpsqlS.soFileUsage 1五、配置 /etc/odbc.inivi /etc/odbc.ini加入[pg_dsn]Description Connection to PostgreSQLDriver PostgreSQLDatabase postgresServername 192.192.103.117UserName postgresPassword postgresPort 5432ReadOnly No六、测试 ODBC 是否正常非常重要安装yum install -y unixODBC测试[oracleora113 ~]$ isql -v pg_dsn成功会看到---------------------------------------| Connected! |如果这里不通Oracle 一定不可能访问 PostgreSQL。第一步必须测通。七、配置 Oracle DG4ODBCOracle11g 自带 DG4ODBC。进入cd $ORACLE_HOME/hs/admin复制模板cp initdg4odbc.ora initpg_link.ora编辑vi initpg_link.ora内容HS_FDS_CONNECT_INFO pg_dsnHS_FDS_TRACE_LEVEL ONHS_FDS_SHAREABLE_NAME /usr/pgsql-14/lib/psqlodbc.so关键HS_FDS_CONNECT_INFO pg_dsn必须和/etc/odbc.ini中的 DSN 名一致。八、配置 listener.ora编辑vi $ORACLE_HOME/network/admin/listener.ora增加SID_LIST_LISTENER (SID_LIST (SID_DESC (SID_NAME pg_link)(ORACLE_HOME /oracle/app/oracle/product/11.2.0.4/dbhome_1)(PROGRAM dg4odbc)))此处的SID_NAME 自定义不予oracle数据库的SID冲突即可。九、配置 tnsnames.ora编辑vi $ORACLE_HOME/network/admin/tnsnames.ora增加PG_GATEWAY (DESCRIPTION (ADDRESS (PROTOCOL TCP)(HOST ora113)(PORT 1521))(CONNECT_DATA (SID pg_link))(HS OK))SID 与配置八的SID_NAME保持一致HOST填写oracle本机的主机名或者IP皆可。十、重启监听lsnrctl stoplsnrctl start检查lsnrctl status[oracleora113 admin]$ lsnrctl statusLSNRCTL for Linux: Version 11.2.0.4.0 - Production on 22-MAY-2026 16:00:37Copyright (c) 1991, 2013, Oracle. All rights reserved.Connecting to (DESCRIPTION(ADDRESS(PROTOCOLTCP)(HOSTora113)(PORT1521)))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.4.0 - ProductionStart Date 22-MAY-2026 15:40:04Uptime 0 days 0 hr. 20 min. 32 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /oracle/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.oraListener Log File /oracle/app/oracle/diag/tnslsnr/ora113/listener/alert/log.xmlListening Endpoints Summary...(DESCRIPTION(ADDRESS(PROTOCOLtcp)(HOSTora113)(PORT1521)))(DESCRIPTION(ADDRESS(PROTOCOLipc)(KEYEXTPROC1521)))Services Summary...Service orcl has 1 instance(s).Instance orcl, status READY, has 1 handler(s) for this service...Service orclXDB has 1 instance(s).Instance orcl, status READY, has 1 handler(s) for this service...Service pg_link has 1 instance(s).Instance pg_link, status UNKNOWN, has 1 handler(s) for this service...The command completed successfully可以看到Service pg_link has 1 instance(s)十一、Oracle 创建 DBLINK登录 OracleCREATE PUBLIC DATABASE LINK PG_LINK_DB CONNECT TO postgres IDENTIFIED BY postgres USING PG_GATEWAY;十二、测试访问 PostgreSQLOracle 正确访问 PostgreSQL 的方法方法1推荐使用双引号 小写select * from t1_vPG_LINK_DB;如果在非 public schemaselect * from public.t1_vPG_LINK_DB;这是最标准方法。SQL select count(*) from t1PG_LINK_DB;COUNT(*)----------100SQL select * from test.tb1PG_LINK_DB;c1 c2---------- --------------------------------------------------------------------------------1 AA2 BB