PostgreSQL外部表插件oracle_fdw

原创多米爸比 2019-12-17

3966

前言：基于FDW(Foreign Data Wrappers)插件可以如本地表一般在PG里访问其他异构数据库的表，下面再次记录并分享下oracle_fdw的配置使用过程，其他fdw如tds_fdw(访问sqlserver)，mysql_fdw(访问mysql)类似。

1. 安装oracle客户端相关包

--安装基础包
# yum install oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm 
--安装开发包
# yum install oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm

本文根据目标oracle库的版本选择11.2，oracle_fdw作者推荐安装版本为10.2.0.5，
原文如下：
For maximum coverage use Oracle Client 10.2.0.5, as this will allow you to connect to every server version from 8.1.7 to 12.1.0 except 9.0.1.

2. 检查环境变量

--检查pg_config环境变量
# which pg_config

--检查oracle动态库的环境变量
# cat /etc/ld.so.conf
include ld.so.conf.d/*.conf
/usr/lib/oracle/11.2/client64/lib

# ldconfig

oracle_fdw可以使用普通用户编译安装，根据pg的安装方式灵活选择。
特别注意当系统有多个版本的pg或者多个版本的oracle客户端包存在时需要明确指定。

3. 安装oracle_fdw

-- 下载文件
# wget https://codeload.github.com/laurenz/oracle_fdw/zip/master

-- 解压master文件后直接编译安装
# make && make install

4. 数据库使用超级用户创建扩展

$ psql -Upostgres nmse

nmse=# create extension oracle_fdw with schema public;
CREATE EXTENSION

nmse=# select oracle_diag();
                         oracle_diag                         
-------------------------------------------------------------
 oracle_fdw 2.2.0, PostgreSQL 12.0, Oracle client 11.2.0.4.0
(1 row)

--赋予数据普通用户jswy使用oracle_fdw的权限
nmse=#  grant USAGE on FOREIGN data wrapper oracle_fdw to jswy;
GRANT

nmse=# \q

5.普通用户创建server，配置oracle的连接参数

$ psql -Ujswy nmse
nmse=> create server server_ora foreign data wrapper oracle_fdw options(dbserver 'ip:port/sid');
CREATE SERVER

6.普通用户创建pg和oracle之间的用户映射user mapping

nmse=> create user mapping for jswy server server_ora options(user 'scott', password 'tigger');
CREATE USER MAPPING

options里的user可以使用小写，插件做了小写转oracle大写的处理

7.创建外部表

$ psql -Ujswy nmse

CREATE FOREIGN TABLE public.f_oracle_test (
    id int,
    info character varying
)
SERVER server_ora 
OPTIONS (
    schema 'scott',
    "table" 'AA'
);

8.注意事项总结

映射表的字段顺序需要保持一致，
oracle端表字段发生变化，需要重建外部表，
另外可能会遇到错误
ORA-08177: can’t serialize access for this transaction，这是因为oracle_fdw使用的事务级别为serialized transactions，可通过修改源码，调整级别，具体参考官网

更多配置参考 https://github.com/laurenz/oracle_fdw