oracle_fdw

测试操作系统: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)