oracle_fdw安装部署(yum方式)

环境:
OS:Centos 7
PGDB:13.8
Oralce:11.2.0.4

 

1.安装oralce瘦客户端
参考链接(root账号下安装部署)
https://www.cnblogs.com/hxlasky/p/16616404.html

root账号和postgres账号环境变量

[root@localhost ~]# more .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs
PGHOME=/usr/pgsql-13
export PGHOME
PGDATA=/opt/pg13/data
export PGDATA

export ORACLE_HOME=/usr/lib/oracle/21/client64 
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH:/usr/pgsql-13/lib 
export PATH=$ORACLE_HOME/bin:$PATH:$PGHOME/bin
export TNS_ADMIN=$ORACLE_HOME/network/admin

 

2.安装oracle_fdw
我这里采用yum方式进行安装
[root@localhost]# yum install https://download.postgresql.org/pub/repos/yum/reporpms/non-free/EL-7-x86_64/pgdg-redhat-nonfree-repo-latest.noarch.rpm
[root@localhost]# yum list oracle_fdw* ##查看当前各版本
[root@localhost]# yum install oracle_fdw_13.x86_64 ##安装与PG版本一致的版本

 

安装完成后可以看到extension相应的控制文件
[root@localhost extension]# ls -al |grep oracle
-rw-r--r--. 1 root root 231 Oct 28 05:43 oracle_fdw--1.0--1.1.sql
-rw-r--r--. 1 root root 240 Oct 28 05:43 oracle_fdw--1.1--1.2.sql
-rw-r--r--. 1 root root 1244 Oct 28 05:43 oracle_fdw--1.2.sql
-rw-r--r--. 1 root root 133 Oct 28 05:43 oracle_fdw.control
[root@localhost extension]# pwd
/usr/pgsql-13/share/extension

 

3.创建oracle_fdw

postgres=# create extension oracle_fdw;
CREATE EXTENSION
postgres=# \dx
                                                                       List of installed extensions
             Name             | Version |   Schema   |                                                     Descriptio
n                                                     
------------------------------+---------+------------+---------------------------------------------------------------
------------------------------------------------------
 address_standardizer         | 3.2.3   | public     | Used to parse an address into constituent elements. Generally 
used to support geocoding address normalization step.
 address_standardizer_data_us | 3.2.3   | public     | Address Standardizer US dataset example
 dblink                       | 1.2     | public     | connect to other PostgreSQL databases from within a database
 fuzzystrmatch                | 1.1     | public     | determine similarities and distance between strings
 mysql_fdw                    | 1.1     | public     | Foreign data wrapper for querying a MySQL server
 oracle_fdw                   | 1.2     | public     | foreign data wrapper for Oracle access
 plpgsql                      | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis                      | 3.2.3   | public     | PostGIS geometry and geography spatial types and functions
 postgis_raster               | 3.2.3   | public     | PostGIS raster types and functions
 postgis_sfcgal               | 3.2.3   | public     | PostGIS SFCGAL functions
 postgis_tiger_geocoder       | 3.2.3   | tiger      | PostGIS tiger geocoder and reverse geocoder
 postgis_topology             | 3.2.3   | topology   | PostGIS topology spatial types and functions
 postgres_fdw                 | 1.0     | public     | foreign-data wrapper for remote PostgreSQL servers
(13 rows)

 

4.创建oralce fdw
上面步骤创建了就不要创建了
create extension oracle_fdw;

 

5.创建服务
postgres=# create server ser_oracle_fdw foreign data wrapper oracle_fdw options (dbserver '192.168.1.100:1521/slnngk');
CREATE SERVER

 

6.创建映射
postgres=# create user mapping for public server ser_oracle_fdw options (user 'hxl', password 'oracle');
CREATE USER MAPPING

 

7.创建外部表
postgres=# create foreign table frg_oracle_tb_test(id int,name varchar(32)) server ser_oracle_fdw options (schema 'HXL',table 'TB_TEST' );
CREATE FOREIGN TABLE
postgres=# select * from frg_oracle_tb_test;
id | name
----+-------
1 | name1
2 | name2
(2 rows)