1、需要的介质

https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html //oracle客户端下载地址
https://pgxn.org/dist/oracle_fdw //oracle_fdw下载地址

instantclient-basic-linux.x64-12.2.0.1.0.zip
instantclient-sdk-linux.x64-12.2.0.1.0.zip
instantclient-sqlplus-linux.x64-12.2.0.1.0.zip
oracle_fdw-2.4.0.zip

2、安装插件

[root@myoracle opt]# pwd
/opt
[root@myoracle opt]# mkdir oracle
[root@myoracle opt]# cd oracle
[root@myoracle oracle]# ll
total 68900
-rw-r--r--. 1 root root 68965195 Mar 10 11:33 instantclient-basic-linux.x64-	12.2.0.1.0.zip
-rw-r--r--. 1 root root   674743 Mar 10 11:33 instantclient-sdk-linux.x64-12.2.0.1.0.zip
-rw-r--r--. 1 root root   904309 Mar 10 11:33 instantclient-sqlplus-linux.x64-12.2.0.1.0.zip
[root@myoracle oracle]# 
[root@myoracle oracle]# unzip instantclient-basic-linux.x64-11.2.0.4.0.zip 
Archive:  instantclient-basic-linux.x64-11.2.0.4.0.zip
inflating: instantclient_11_2/BASIC_README  
inflating: instantclient_11_2/adrci  
inflating: instantclient_11_2/genezi  
inflating: instantclient_11_2/libclntsh.so.11.1  
inflating: instantclient_11_2/libnnz11.so  
inflating: instantclient_11_2/libocci.so.11.1  
inflating: instantclient_11_2/libociei.so  
inflating: instantclient_11_2/libocijdbc11.so  
inflating: instantclient_11_2/ojdbc5.jar  
inflating: instantclient_11_2/ojdbc6.jar  
inflating: instantclient_11_2/uidrvci  
inflating: instantclient_11_2/xstreams.jar  
[root@myoracle oracle]# 
[root@myoracle oracle]# unzip instantclient-sdk-linux.x64-11.2.0.4.0.zip 
Archive:  instantclient-sdk-linux.x64-11.2.0.4.0.zip
creating: instantclient_11_2/sdk/
creating: instantclient_11_2/sdk/include/
inflating: instantclient_11_2/sdk/include/occi.h  
inflating: instantclient_11_2/sdk/include/occiCommon.h  
inflating: instantclient_11_2/sdk/include/occiControl.h  
inflating: instantclient_11_2/sdk/include/occiData.h  
inflating: instantclient_11_2/sdk/include/occiObjects.h  
inflating: instantclient_11_2/sdk/include/occiAQ.h  
inflating: instantclient_11_2/sdk/include/oci.h  
inflating: instantclient_11_2/sdk/include/oci1.h  
inflating: instantclient_11_2/sdk/include/oci8dp.h  
inflating: instantclient_11_2/sdk/include/ociap.h  
inflating: instantclient_11_2/sdk/include/ociapr.h  
inflating: instantclient_11_2/sdk/include/ocidef.h  
inflating: instantclient_11_2/sdk/include/ocidem.h  
inflating: instantclient_11_2/sdk/include/ocidfn.h  
inflating: instantclient_11_2/sdk/include/ociextp.h  
inflating: instantclient_11_2/sdk/include/ocikpr.h  
inflating: instantclient_11_2/sdk/include/ocixmldb.h  
inflating: instantclient_11_2/sdk/include/ocixstream.h  
inflating: instantclient_11_2/sdk/include/odci.h  
inflating: instantclient_11_2/sdk/include/oratypes.h  
inflating: instantclient_11_2/sdk/include/ori.h  
inflating: instantclient_11_2/sdk/include/orid.h  
inflating: instantclient_11_2/sdk/include/orl.h  
inflating: instantclient_11_2/sdk/include/oro.h  
inflating: instantclient_11_2/sdk/include/ort.h  
inflating: instantclient_11_2/sdk/include/xa.h  
inflating: instantclient_11_2/sdk/include/nzt.h  
inflating: instantclient_11_2/sdk/include/nzerror.h  
inflating: instantclient_11_2/sdk/include/ldap.h  
creating: instantclient_11_2/sdk/demo/
inflating: instantclient_11_2/sdk/demo/demo.mk  
inflating: instantclient_11_2/sdk/demo/cdemo81.c  
inflating: instantclient_11_2/sdk/demo/occidemo.sql  
inflating: instantclient_11_2/sdk/demo/occidemod.sql  
inflating: instantclient_11_2/sdk/demo/occidml.cpp  
inflating: instantclient_11_2/sdk/demo/occiobj.cpp  
inflating: instantclient_11_2/sdk/demo/occiobj.typ  
inflating: instantclient_11_2/sdk/SDK_README  
extracting: instantclient_11_2/sdk/ottclasses.zip  
inflating: instantclient_11_2/sdk/ott  
[root@myoracle oracle]# 
[root@myoracle oracle]# unzip instantclient-sqlplus-linux.x64-11.2.0.4.0.zip 
Archive:  instantclient-sqlplus-linux.x64-11.2.0.4.0.zip
inflating: instantclient_11_2/SQLPLUS_README  
inflating: instantclient_11_2/glogin.sql  
inflating: instantclient_11_2/libsqlplus.so  
inflating: instantclient_11_2/libsqlplusic.so  
inflating: instantclient_11_2/sqlplus  
[root@myoracle oracle]# mv instantclient_11_2/ instantclient
[root@myoracle oracle]# ll
total 68900
drwxr-xr-x. 3 root root     4096 Mar 14 16:32 instantclient
-rw-r--r--. 1 root root 68965195 Mar 10 11:33 instantclient-basic-linux.x64-12.2.0.1.0.zip
-rw-r--r--. 1 root root   674743 Mar 10 11:33 instantclient-sdk-linux.x64-12.2.0.1.0.zip
-rw-r--r--. 1 root root   904309 Mar 10 11:33 instantclient-sqlplus-linux.x64-12.2.0.1.0.zip
[root@myoracle oracle]# 
//配置环境变量
[root@myoracle oracle]# vi /etc/profile
...
...
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
export PATH=$GCC_HOME/bin:$CMAKE_HOME/bin:$PROTOBUF_HOME/bin:$PROTOBUFC_HOME/bin:$ORACLE_HOME:$PATH
...
...
[root@myoracle oracle]# source /etc/profile
[root@myoracle oracle]# cd instantclient
[root@myoracle instantclient]# pwd
/opt/oracle/instantclient
[root@myoracle instantclient]# ln -sv libclntshcore.so.12.1 libclntshcore.so 
//这里若没有配置，make oracle_fdw时会报错
/bin/ld: cannot find -lclntsh
collect2: error: ld returned 1 exit status
make: *** [oracle_fdw.so] Error 1

[root@myoracle opt]# pwd
/opt
[root@myoracle opt]# unzip oracle_fdw-2.4.0.zip 
Archive:  oracle_fdw-2.4.0.zip
creating: oracle_fdw-2.4.0/
inflating: oracle_fdw-2.4.0/TODO   
creating: oracle_fdw-2.4.0/expected/
inflating: oracle_fdw-2.4.0/expected/oracle_join.out  
inflating: oracle_fdw-2.4.0/expected/oracle_fdw.out  
inflating: oracle_fdw-2.4.0/expected/oracle_gis.out  
inflating: oracle_fdw-2.4.0/expected/oracle_import.out  
creating: oracle_fdw-2.4.0/msvc/
inflating: oracle_fdw-2.4.0/msvc/oracle_fdw.sln  
inflating: oracle_fdw-2.4.0/msvc/oracle_fdw.vcxproj  
inflating: oracle_fdw-2.4.0/msvc/oracle_msvc.c  
inflating: oracle_fdw-2.4.0/msvc/oracle_fdw.props  
inflating: oracle_fdw-2.4.0/Makefile  
inflating: oracle_fdw-2.4.0/CHANGELOG  
inflating: oracle_fdw-2.4.0/oracle_fdw.h  
inflating: oracle_fdw-2.4.0/oracle_fdw--1.0--1.1.sql  
inflating: oracle_fdw-2.4.0/oracle_fdw--1.1--1.2.sql  
inflating: oracle_fdw-2.4.0/oracle_fdw.c  
inflating: oracle_fdw-2.4.0/oracle_fdw.control  
inflating: oracle_fdw-2.4.0/oracle_fdw--1.2.sql  
inflating: oracle_fdw-2.4.0/oracle_gis.c  
inflating: oracle_fdw-2.4.0/META.json  
creating: oracle_fdw-2.4.0/sql/
inflating: oracle_fdw-2.4.0/sql/oracle_join.sql  
inflating: oracle_fdw-2.4.0/sql/oracle_gis.sql  
inflating: oracle_fdw-2.4.0/sql/oracle_fdw.sql  
inflating: oracle_fdw-2.4.0/sql/oracle_import.sql  
inflating: oracle_fdw-2.4.0/oracle_utils.c  
inflating: oracle_fdw-2.4.0/LICENSE  
inflating: oracle_fdw-2.4.0/README.md  
inflating: oracle_fdw-2.4.0/README.oracle_fdw  
[root@myoracle opt]# 
[root@myoracle opt]# cd oracle_fdw-2.4.0
[root@myoracle oracle_fdw-2.4.0]# make
[root@myoracle oracle_fdw-2.4.0]# make install
[root@myoracle oracle_fdw-2.4.0]# vi /etc/ld.so.conf.d/oracle.conf 
/opt/oracle/instantclient

3、pg库配置连接oracle库

[root@myoracle oracle_fdw-2.4.0]# su - postgres
[postgres@myoracle ~]$ psql
psql (13.4)
Type "help" for help.

postgres=# \l
                               List of databases
 Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
--------------+----------+----------+-------------+-------------+-----------------------
dblucifer    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
          |          |          |             |             | postgres=CTc/postgres+
          |          |          |             |             | pubuser=CTc/postgres +
          |          |          |             |             | jzshuser=CTc/postgres
deydb        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
          |          |          |             |             | postgres=CTc/postgres+
          |          |          |             |             | jzshuser=CTc/postgres+
          |          |          |             |             | pubuser=CTc/postgres
deytest      | dey      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =T/dey               +
          |          |          |             |             | dey=CTc/dey
mytest       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
postgres     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
replica_demo | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
template0    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
          |          |          |             |             | postgres=CTc/postgres
template1    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
          |          |          |             |             | postgres=CTc/postgres
test         | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(9 rows)

postgres=# \c mytest 
You are now connected to database "mytest" as user "postgres".
mytest=# \dx
                                List of installed extensions
  Name      | Version |   Schema   |                        Description                         
----------------+---------+------------+------------------------------------------------------------
pgrouting      | 2.6.3   | public     | pgRouting Extension
plpgsql        | 1.0     | pg_catalog | PL/pgSQL procedural language
postgis        | 3.1.3   | public     | PostGIS geometry and geography spatial types and functions
postgis_raster | 3.1.3   | public     | PostGIS raster types and functions
postgis_sfcgal | 3.1.3   | public     | PostGIS SFCGAL functions
(5 rows)

mytest=# create extension oracle_fdw;
CREATE EXTENSION
mytest=# \dx
                                List of installed extensions
  Name      | Version |   Schema   |                        Description                         
----------------+---------+------------+------------------------------------------------------------
oracle_fdw     | 1.2     | public     | foreign data wrapper for Oracle access
pgrouting      | 2.6.3   | public     | pgRouting Extension
plpgsql        | 1.0     | pg_catalog | PL/pgSQL procedural language
postgis        | 3.1.3   | public     | PostGIS geometry and geography spatial types and functions
postgis_raster | 3.1.3   | public     | PostGIS raster types and functions
postgis_sfcgal | 3.1.3   | public     | PostGIS SFCGAL functions
(6 rows)

mytest=# CREATE SERVER deydb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//192.168.101.137:1521/deydb');
CREATE SERVER
mytest=# \dew
               List of foreign-data wrappers
Name    |  Owner   |      Handler       |      Validator       
------------+----------+--------------------+----------------------
oracle_fdw | postgres | oracle_fdw_handler | oracle_fdw_validator
(1 row)

mytest=# GRANT USAGE ON FOREIGN SERVER deydb TO postgres;
GRANT
mytest=# GRANT USAGE ON FOREIGN SERVER deydb TO dey;
GRANT
mytest=# \deu
List of user mappings
Server | User name 
--------+-----------
(0 row)

//创建映射
CREATE USER MAPPING
FOR POSTGRES
SERVER deydb
OPTIONS (USER 'deng', password 'deng');

mytest=# \des
     	List of foreign servers
Name  |  Owner   | Foreign-data wrapper 
-------+----------+----------------------
deydb | postgres | oracle_fdw
(1 row)

mytest=# \des+
                                                     List of foreign servers
Name  |  Owner   | Foreign-data wrapper |  Access privileges  | Type | Version |                FDW options                | Description 
-------+----------+----------------------+---------------------+------+---------+-------------------------------------------+-------------
deydb | postgres | oracle_fdw           | postgres=U/postgres+|      |         | (dbserver '//192.168.101.137:1521/deydb') | 
   |          |                      | dey=U/postgres      |      |         |                                           | 
(1 row)

mytest=# set search_path=dey;
SET
mytest=# show search_path;
search_path 
-------------
dey
(1 row)

mytest=# IMPORT FOREIGN SCHEMA "DENG" LIMIT TO (TEST) FROM SERVER deydb INTO dey;
IMPORT FOREIGN SCHEMA
mytest=# \det
List of foreign tables
Schema | Table | Server 
--------+-------+--------
dey    | test  | deydb
(1 row)

mytest=# \det+
                     	List of foreign tables
Schema | Table | Server |           FDW options           | Description 
--------+-------+--------+---------------------------------+-------------
dey    | test  | deydb  | (schema 'DENG', "table" 'TEST') | 
(1 row)

mytest=# select * from test ;
id | name 
----+------
1 | a
(1 row)

mytest=# drop foreign table TEST;
DROP FOREIGN TABLE
mytest=# IMPORT FOREIGN SCHEMA "deng" LIMIT TO (TEST) FROM SERVER deydb INTO dey;
ERROR:  remote schema "deng" does not exist
HINT:  Enclose the schema name in double quotes to prevent case folding.
//这里注意大小写！！！
mytest=# IMPORT FOREIGN SCHEMA "DENG" LIMIT TO (TEST) FROM SERVER deydb INTO dey;
IMPORT FOREIGN SCHEMA
mytest=# select * from test ;
id | name 
----+------
1 | a
(1 row)