测试操作系统:Centos 7.6
测试IP :192.168.112.88
操作系统安装信息:最小化安装
官方网站:
https://github.com/laurenz/oracle_fdw
http://laurenz.github.io/oracle_fdw/
Requirements:
PostgreSQL 9.3 or better
Oracle client 11.2 or better
For EXPLAIN VERBOSE, SELECT privileges on V$SQL and V$SQL_PLAN
You need to install Oracle's C header files as well (SDK package for Instant Client).
If you use the Instant Client ZIP files provided by Oracle,
you will have to create a symbolic link from libclntsh.so to the actual shared library file yourself.
1.准备oracle环境
根据Requirements, fdw 明确要求需要 instanct client 以及 sdk package.
instantclient-sdk-linux.x64-11.2.0.4.0.zip
instantclient-basic-linux.x64-11.2.0.4.0.zip
instantclient-sqlplus-linux.x64-11.2.0.4.0.zip (可选)
[root@PGHOST u01]# unzip instantclient-basic-linux.x64-11.2.0.4.0.zip
[root@PGHOST u01]# unzip instantclient-sdk-linux.x64-11.2.0.4.0.zip
[root@PGHOST u01]# unzip instantclient-sqlplus-linux.x64-11.2.0.4.0.zip
文件均解压到目录
/u01/instantclient_11_2
PATH=$PATH:$HOME/bin:/pgsoft/bin:/u01/pgbadger-11.3:/u01/instantclient_11_2
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/pgsoft/lib:/u01/instantclient_11_2
export PATH
2.上传安装 fdw
[root@PGHOST u01]# unzip oracle_fdw-master.zip
[root@PGHOST u01]# cd oracle_fdw-master
[root@PGHOST oracle_fdw-master]# make
[root@PGHOST oracle_fdw-master]# make install
3.验证fdw
[root@PGHOST oracle_fdw-master]# ldd oracle_fdw.so
linux-vdso.so.1 => (0x00007fff084ef000)
libclntsh.so.11.1 => u01/instantclient_11_2/libclntsh.so.11.1 (0x00007f812b6c9000)
libc.so.6 => lib64/libc.so.6 (0x00007f812b2fc000)
libnnz11.so => u01/instantclient_11_2/libnnz11.so (0x00007f812af2f000)
libdl.so.2 => lib64/libdl.so.2 (0x00007f812ad2b000)
libm.so.6 => lib64/libm.so.6 (0x00007f812aa29000)
libpthread.so.0 => lib64/libpthread.so.0 (0x00007f812a80d000)
libnsl.so.1 => lib64/libnsl.so.1 (0x00007f812a5f3000)
libaio.so.1 => lib64/libaio.so.1 (0x00007f812a3f1000)
lib64/ld-linux-x86-64.so.2 (0x00007f812e25c000)
4.配置oracle_fdw
4.1 基本配置
Then configure oracle_fdw as PostgreSQL superuser like this:
pgdb=# CREATE EXTENSION oracle_fdw;
pgdb=# CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//192.168.112.77:1521/testdb');
pgdb=# GRANT USAGE ON FOREIGN SERVER oradb TO pguser;
Then you can connect to PostgreSQL as pguser and define:
pgdb=> CREATE USER MAPPING FOR pguser SERVER oradb OPTIONS (user 'system', password 'oracle');
4.2 手动创建外部表
pgdb=> CREATE FOREIGN TABLE TEST (
id int,
text character varying(30)
) SERVER oradb OPTIONS (schema 'SYSTEM', table 'TEST');
postgres=# select * from TEST;
id | text
-----+------
100 | sss
(1 row)
4.3 使用import FOREIGN SCHEMA 一键创建外部表
postgres=# drop user mapping for postgres server oradb;
postgres=# CREATE USER MAPPING FOR postgres SERVER oradb OPTIONS (user 'myuser', password 'oracle');
postgres=# create schema r1;
postgres=# import FOREIGN SCHEMA "MYUSER" from server oradb into r1 ;
postgres=# select * from r1.test;
id | text
-----+------
300 | sss
(1 row)
