Oracle通过DBLINK访问PG13

root用户执行：

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-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

修改文件/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

创建文件/etc/odbc.ini

[postgresql]
Description = PostgresSQLODBC
Driver = PostgreSQL
Database = demo
Servername = 192.168.0.42
UserName = test
Password = test123
Port = 5432
ReadOnly = 0
ConnSettings = set client_encoding to UTF8

测试连接：

isql -v postgresql 

oracle用户执行下面的步骤：

创建文件~/.odbc.ini

[PG_LINK]
Description        = PostgreSQL connection to SallyDB
Driver             = /usr/pgsql-13/lib/psqlodbc.so
Setup              = /usr/lib64/libodbcpsqlS.so
Database           = demo
Servername         = 192.168.0.42
UserName           = test
Password           = test123
Port               = 5432
Protocol           = 13.1
ReadOnly           = No
RowVersioning      = No
ShowSystemTables   = No
ConnSettings       = set client_encoding to UTF8

创建文件： /u01/app/oracle/product/19.0.0/dbhome_1/hs/admin/initPG_LINK.ora

HS_FDS_CONNECT_INFO = PG_LINK
HS_FDS_TRACE_LEVEL = 255
HS_FDS_SHAREABLE_NAME=/usr/pgsql-13/lib/psqlodbc.so
HS_LANGUAGE=AMERICAN_AMERICA.UTF8
set ODBCINI=/home/oracle/.odbc.ini
HS_NLS_NCHAR=UCS2
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

在文件 /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora 中追加如下内容：

PG_LINK =
(DESCRIPTION=
	(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.24)(PORT=1521))
	(CONNECT_DATA=(SID=PG_LINK))
	(HS=OK)
)

在文件 /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora 中追加：

SID_LIST_LISTENER=
	(SID_LIST=
		(SID_DESC=
			(SID_NAME=PG_LINK)
			(ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1)       
			(ENV="LD_LIBRARY_PATH=/usr/lib64/:/u01/app/oracle/product/19.0.0/dbhome_1/bin/")        
			(PROGRAM=dg4odbc)

		)
	)

创建dblink

drop database link to_pglink; 
create database link to_pglink connect to "test" identified by "test123" using 'PG_LINK';

使用DBLINK连接访问

select count(*) from "gupolicymain"@to_pglink;

使用同义词访问：

create or replace synonym testx  for "gupolicymain"@to_pglink;
select count(*) from gupolicymain;