PostgreSQL+Oracle跨库连接实操

unzip instantclient-basic-linux.x64-12.2.0.1.0.zip

unzip instantclient-sdk-linux.x64-12.2.0.1.0.zip

unzip instantclient-sqlplus-linux.x64-12.2.0.1.0.zip

export ORACLE_HOME=/opt/oracle/instantclient

export OCI_LIB_DIR=$ORACLE_HOME

export OCI_INC_DIR=$ORACLE_HOME/sdk/include

export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH

unzip oracle_fdw-2.0.0.zip

cd oracle_fdw-2.0.0

Make

Make install

--检查确认没有依赖未解决

ldd oracle_fdw.so

postgres=# create extension oracle_fdw ;

postgres=# \des

List of foreign servers

Name |  Owner   | Foreign-data wrapper

-------+----------+----------------------

oradb | postgres | oracle_fdw

postgres=# CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//192.168.217.120:1521/posdb');

postgres=# CREATE USER MAPPING FOR postgres SERVER oradb OPTIONS (user 'wen', password 'wen123');

postgres=# CREATE  FOREIGN TABLE "vol_audit_nbr_sum_qs" (

id int,

uuid character(32),

name character(32),

Minfo text) SERVER oradb OPTIONS (table 'vol_audit_nbr_sum_qs');

select * from vol_audit_nbr_sum_qs;

Time: 563775.627 ms (09:23.776)

select * from vol_audit_nbr_sum_qs;

Time: 322470.280 ms (05:22.470)

postgres=# create extension postgres_fdw;

postgres=# create server f_postgres foreign data wrapper postgres_fdw options (host '192.168.86.95',port '5433',dbname 'db_demo');

postgres=# create user mapping if not exists for postgres server f_postgres options (user 'wen',password 'wen123');

postgres=# CREATE  FOREIGN TABLE "vol_audit_nbr_sum_qs" (

id int,

uuid character(32),

name character(32),

Minfo text) SERVER f_postgres OPTIONS (table_name 'vol_audit_nbr_sum_qs');

select * from vol_audit_nbr_sum_qs;

Time: 779932.685 ms (12:59.933)

select * from vol_audit_nbr_sum_qs;

Time: 238503.016 ms (03:58.503)

--需要安装unixODBC 和 postgresql_odbc

yum install -y unixODBC.x86_64

yum install -y postgresql-odbc.x86_64

[postgresql]

Description = PostgresSQLODBC

Driver = PostgreSQL

Database = testdb

Servername = 192.168.12.123

UserName = test

Password = test123

Port = 5432

ReadOnly = 0

ConnSettings = set client_encoding to UTF8

连接成功：

[root@localoracle ~]# isql postgresql

+---------------------------------------+

| Connected!                           |

|                                    |

| sql-statement                         |

| help [tablename]                      |

| quit                                 |

|                                    |

+---------------------------------------+

[PG_LINK]

Description        = PostgreSQL connection to SallyDB

Driver             = /usr/lib64/psqlodbc.so

Setup              = /usr/lib64/libodbcpsqlS.so

Database           = testdb

Servername         = 192.168.12.123

UserName           = test

Password           = test123

Port               = 5432

Protocol           = 12.2

ReadOnly           = No

RowVersioning      = No

ShowSystemTables   = No

ConnSettings       = set client_encoding to UTF8

HS_FDS_CONNECT_INFO = PG_LINK

HS_FDS_TRACE_LEVEL = 255

HS_FDS_SHAREABLE_NAME=/usr/lib64/psqlodbc.so

HS_LANGUAGE=AMERICAN_AMERICA.ZHS16GBK

set ODBCINI=/home/oracle/.odbc.ini

PG_LINK =

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.217.120)(PORT=1521))

(CONNECT_DATA=(SID=PG_LINK))

(HS=OK)

)

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(SID_NAME=PG_LINK)

(ORACLE_HOME=/u01/app/oracle/product/12.2/db_1)       (ENV="LD_LIBRARY_PATH=/usr/lib64/:/u01/app/oracle/product/12.2/db_1/bin/")        (PROGRAM=dg4odbc)

)

)

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

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

SQL> set timing on    

SQL> set autot trace

SQL> select "billing_cycle_id" from "vol_audit_nbr_sum_qs"@to_pglink;

45201535 rows selected.

Elapsed: 00:10:37.14