在PostgreSQL中使用oracle_fdw访问Oracle数据库
1、安装oracle_fdw
可以参照:oracle_fdw in github
编译安装oracle_fdw之前,需要安装Oracle的客户端程序,通常可以安装轻量级客户端包:oracle instant client,下载地址为:oracle instant client,具体的安装步骤也可以参考文档末尾的安装介绍。
这里下载了instantclient-basic-linux.x64-19.17.0.0.0dbru.zip和instantclient-sdk-linux.x64-19.17.0.0.0dbru.zip,如果需要sqlplus也可以一起下载
instantclient-sqlplus-linux.x64-19.17.0.0.0dbru.zip 这里都解压到/opt/oracle/instantclient_19_17 同一个目录下(系统用户要对该目录有访问权限),同时配置如下:
sudo sh -c "echo /opt/oracle/instantclient_19_17 > \
/etc/ld.so.conf.d/oracle-instantclient.conf"
sudo ldconfig
可以使用sqlplus测试一下客户端有没有安装ok
sqlplus user/passwd@//localhost:1521/DEVDB
安装oracle_fdw,可以到网站上: oracle_fdw in github](https://github.com/laurenz/oracle_fdw “”)下载安装包,或直接使用git clone下载源,或者下载响应的zip并上传
#用和pg编译安装同一个用户登录
cd <postgresql源码目录>/contrib
git clone https://github.com/laurenz/oracle_fdw.git
#由于网络问题git clone 下载不下来,这里采用下载zip源码包并上传。
在编译安装前,还需要设置Oracle的环境变量,如在.bash_profile中增加:
export ORACLE_HOME=/opt/oracle/instantclient_19_17
export LD_LIBRARY_PATH=/opt/oracle/instantclient_19_17:$LD_LIBRARY_PATH
export PATH=/opt/oracle/instantclient_19_17:$PATH
然后进入到oracle_fdw源码目录进行编译安装:
#用和pg编译安装同一个用户登录
cd <postgresql源码目录>/contrib/oracle_fdw
make
make insttall
安装完后,需要重启数据库才能生效。
2、创建oracle_fdw外部表
oracle_fdw是通过oci接口访问Oracle了,所以需要配置$ORACLE_HOME/network/admin/tnsnames.ora,内容如下:
oradev =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DEVDB)
)
)
在PostgreSQL建张外部表,在psql中,使用超级用户:
#超级用户
CREATE EXTENSION oracle_fdw;
CREATE SERVER ora_dev_ser FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver 'oradev',isolation_level 'read_committed');
GRANT USAGE ON FOREIGN SERVER ora_dev_ser TO scott;
注意上面命令中“dbserver ‘oradev’”中的"oradev"就是前面tnsnames中设置的TNSNAMES。 上面的SQL中把访问Oracle外部服务的权限赋给了用户scott,现在使用scott用户登录pg:
#登录scott用户
CREATE USER MAPPING FOR current_user SERVER ora_dev_ser
OPTIONS (user 'scott', password 'tiger');
CREATE FOREIGN TABLE t_test_dept_f(dept_id bigint options(key 'true'), dept_name varchar(32), dept_parentid
bigint) SERVER ora_dev_ser OPTIONS (schema 'SCOTT', table 'T_TEST_DEPT');
测试一下,在psql中查询t_test_dept_f
select * from t_test_dept_f;
dept_id |dept_name |dept_parentid|
--------+--------------------------------+-------------+
36535687|1f2ec9a23a2a347ce38df59c51733097| 8|
oracle_fdw外部表也可以支持插入、更新、删除。