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;