具體步驟如下：

1.安裝 unixODBC,postgresql13-odbc
root用戶執(zhí)行：

yum install -y?https://download.postgresql.org/ ... o-latest.noarch.rpm
yum install -y unixODBC.x86_64
yum - y install postgresql13-odbc.x86_64
unlink /usr/lib64/libpq.so.5
ln -s /usr/pgsql-13/lib/libpq.so.5.13 /usr/lib64/libpq.so.5

2.修改文件/etc/odbcinst.ini，將Driver改成pg13的

[PostgreSQL]
Description? ???= ODBC for PostgreSQL
Driver? ?? ?? ? = /usr/pgsql-13/lib/psqlodbcw.so
Setup? ?? ?? ???= /usr/lib/libodbcpsqlS.so
Driver64? ?? ???= /usr/pgsql-13/lib/psqlodbcw.so
Setup64? ?? ?? ?= /usr/lib64/libodbcpsqlS.so
FileUsage? ?? ? = 1

3.創(chuàng)建文件/etc/odbc.ini

[PG]
Description? ?? ?? ?= PostgreSQL connection to SallyDB
Driver? ?? ?? ?? ???= PostgreSQL
Database? ?? ?? ?? ?=zltj
Servername? ?? ?? ? =1.1.8.96
UserName? ?? ?? ?? ?=postgres
Password? ?? ?? ?? ?=zltj2021
Port? ?? ?? ?? ?? ? = 5432
UseDeclareFetch? ???= 1
CommLog? ?? ?? ?? ? = /tmp/pgodbclink.log
Protocol? ?? ?? ?? ?=12.8
ReadOnly? ?? ?? ?? ?=No
RowVersioning? ?? ? =yes
ShowSystemTables? ? =YES
ShowOidColumn? ?? ? =yes
FakeOidIndex? ?? ???=yes
ConnSettings = set client_encoding to gbk
Debug = 0
LowerCaseIdentifier = 1


4.測試連接：

isql -v pg
+---------------------------------------+
|?Connected!? ?? ?? ?? ?? ?? ?? ?? ?? ? |
|? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?|
| sql-statement? ?? ?? ?? ?? ?? ?? ?? ? |
| help [tablename]? ?? ?? ?? ?? ?? ?? ? |
| quit? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ? |
|? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?|
+---------------------------------------+
SQL>help? ???-- 會把POSTGRES用戶下的所有表列出來


oracle用戶執(zhí)行下面的步驟：

創(chuàng)建文件~/.odbc.ini


5.創(chuàng)建監(jiān)聽
??在文件 /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora 中追加：

SID_LIST_LISTENER =
??(SID_LIST =
? ?(SID_DESC=
? ?? ?(SID_NAME=PG)
? ?? ?(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
? ?? ?? ?(ENV="LD_LIBRARY_PATH=/usr/pgsql-13/lib/:/lib64:/usr/lib64/:/u01/app/oracle/product/11.2.0/db_1/lib")
? ?? ?? ?(PROGRAM=dg4odbc)
? ? )
??)

6.配置tnsnames.ora文件
在文件 /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora 中追加如下內(nèi)容：

PG =
??(DESCRIPTION=
? ? (ADDRESS=(PROTOCOL=tcp)(HOST=hisdb01)(PORT=1521))
? ? (CONNECT_DATA=(SID=PG))
? ? (HS=OK)
??)


7.配置透明網(wǎng)關(guān)
創(chuàng)建文件： /u01/app/oracle/product/11.2.0/db_1/hs/admin/initPG.ora

HS_FDS_CONNECT_INFO = PG
HS_FDS_TRACE_LEVEL = 255
HS_FDS_SHAREABLE_NAME=/usr/pgsql-13/lib/psqlodbcw.so
HS_NLS_NCHAR=UCS2
HS_LANGUAGE=AMERICAN_AMERICA.zhs16gbk
HS_KEEP_REMTE_COLUMN_SIZE=ALL
HS_FDS_DEFAULT_SCHEMA_NAME = public
set ODBCINI=/etc/odbc.ini


8.創(chuàng)建dblink


create public database link to_pglink connect to "postgres" identified by "zltj2021" using 'PG';

9.使用DBLINK連接訪問PG

select count(*) from "drcs"@to_pglink;
??COUNT(*)
----------
? ?? ?3079

10.使用同義詞訪問：

create or replace synonym drcs??for "drcs"@to_pglink;
select count(*) from drcs;