1. 云图网首页
  2. 技术专区
  3. 数据库

PostgreSQL 新特性之七 : oracle_fdw 实践

• 数据库

之前测试过 PostgreSQL 的 mysql_fdw， file_fdw ，今天有空，测试了 oracle_fdw, 什么是 oracle_fdw 呢？简单的说， 通过安装 oracle_fdw, 在 PostgreSQL 中可以访问 Oracle 库中的表，类似 dblink，下面是 oracle_fdw 的安装过程。

环境准备

1.1 环境信息
Oracle: 10.2.0.1
PostgreSQL: PostgreSQL 9.1
操作系统：虚拟机 ( Red Hat Enterprise Linux AS release 4 )
备注： Oracle ,PostgreSQL 安装部分略。

1.2 postgres 编译

1
 
./configure --prefix=/opt/pgsql --with-pgport=1921 --with-segsize=8 --with-wal-segsize=64 --with-wal-blocksize=64 --with-perl --without-openssl --without-pam without-ldap --enable-thread-safety

备注：这里使用了 –without-ldap

1.3 postgres 环境变量
修改 .bash_profile ，内容如下:

1
2
3
4
5
6
7
8
9
10
11
12
 
export PGPORT=1921  
export PGDATA=/opt/pgdata/pg_root  
export LANG=en_US.utf8
export PGHOME=/opt/pgsql  
export ORACLE_BASE=/app/oracle  
export ORACLE_HOME=/app/oracle/product/10g  
export LD_LIBRARY_PATH=$PGHOME/lib:$ORACLE_HOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib  
export DATE=`date +"%Y%m%d%H%M"`  
export PATH=$PGHOME/bin:$ORACLE_HOME/bin:$PATH:.  
export MANPATH=$PGHOME/share/man:$MANPATH
alias rm='rm -i'  
alias ll='ls -lh'

备注：环境变量 里加入了 ORACLE_BASE, ORACLE_HOME ，以及环境变量 LD_LIBRARY_PATH 增加了 $PGHOME/lib。

安装 oracle_fdw

2.1 下载 oracle_fdw
http://pgxn.org/dist/oracle_fdw/
备注：下载并解压到 /opt/soft_bak 目录。

2.2 install oraclel_fdw

1
2
3
 
[root@primary-01 oracle_fdw-0.9.3]# cd /opt/soft_bak/oracle_fdw-0.9.3  
[root@primary-01 oracle_fdw-0.9.3]# source /home/postgres/.bash_profile  
[root@primary-01 oracle_fdw-0.9.3]# source /home/oracle/.bash_profile

备注：在安装 oracle_fdw 前需要先载入 PostgreSQL 和 Oracle 用户环境变量。

1
2
3
4
5
6
7
8
9
10
11
12
 
[root@primary-01 oracle_fdw-0.9.3]# make  
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wformat-security -fno-strict-aliasing -fpic -I/app/oracle/product/10g/sdk/include -I/app/oracle/product/10g/oci/include -I/app/oracle/product/10g/rdbms/public -I. -I. -I/opt/pgsql/include/server -I/opt/pgsql/include/internal -D_GNU_SOURCE -c -o oracle_fdw.o oracle_fdw.c  
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wformat-security -fno-strict-aliasing -fpic -I/app/oracle/product/10g/sdk/include -I/app/oracle/product/10g/oci/include -I/app/oracle/product/10g/rdbms/public -I. -I. -I/opt/pgsql/include/server -I/opt/pgsql/include/internal -D_GNU_SOURCE -c -o oracle_utils.o oracle_utils.c  
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wformat-security -fno-strict-aliasing -fpic -shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o -L/opt/pgsql/lib -Wl,-rpath,'/opt/pgsql/lib',--enable-new-dtags -L/app/oracle/product/10g -L/app/oracle/product/10g/bin -L/app/oracle/product/10g/lib -lclntsh
[root@primary-01 oracle_fdw-0.9.3]# make install  
/bin/mkdir -p '/opt/pgsql/lib'  
/bin/mkdir -p '/opt/pgsql/share/extension'  
/bin/mkdir -p '/opt/pgsql/share/doc/extension'  
/bin/sh /opt/pgsql/lib/pgxs/src/makefiles/../../config/install-sh -c -m 755 oracle_fdw.so '/opt/pgsql/lib/oracle_fdw.so'  
/bin/sh /opt/pgsql/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./oracle_fdw.control '/opt/pgsql/share/extension/'  
/bin/sh /opt/pgsql/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./oracle_fdw--1.0.sql '/opt/pgsql/share/extension/'  
/bin/sh /opt/pgsql/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./README.oracle_fdw '/opt/pgsql/share/doc/extension/'

备注：如果出现上面信息，说明 oracle_fdw 编译安装成功。

2.3 test creating extension

1
2
 
postgres=# create extension oracle_fdw;  
ERROR: could not load library "/opt/pgsql/lib/oracle_fdw.so": libclntsh.so.10.1: cannot open shared object file: No such file or directory

备注：一开始创建 oraclre_fdw 时，提示少了 so 文件，这个比较好解决，只要将对应的 so 文件 copy 到 $PGHOME/lib 下即可，如下：

1
2
3
4
5
6
7
 
[root@primary-01 oracle_fdw-0.9.3]# cd /app/oracle/product/10g/lib  
[root@primary-01 lib]# ll libclntsh.so.10.1  
-rwxr-xr-x 1 oracle oinstall 18M Jan 10 2010 libclntsh.so.10.1
[root@primary-01 lib]# cp libclntsh.so.10.1 /opt/pgsql/lib/  
[root@primary-01 lib]# chown postgres:postgres /opt/pgsql/lib/libclntsh.so.10.1
[root@primary-01 lib]# ll /opt/pgsql/lib/libclntsh.so.10.1  
-rwxr-xr-x 1 postgres postgres 18M Mar 3 14:29 /opt/pgsql/lib/libclntsh.so.10.1

2.4 再次创建 oracle_fdw

1
2
3
4
5
6
7
8
9
 
skytf=# create extension oracle_fdw;  
CREATE EXTENSION  
skytf=# /dx  
 List of installed extensions  
 Name | Version | Schema | Description  
------------+---------+------------+----------------------------------------  
oracle_fdw | 1.0 | public | foreign data wrapper for Oracle access  
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language  
(2 rows)

备注：再次测试 oracle_fdw，终于成功了。

Oracle 库创建只读用户

3.1 oracle 创建只读用户并赋只读权限 ( On Oracle)

1
2
3
4
5
6
7
8
9
10
11
12
 
SQL> CREATE USER read_only IDENTIFIED BY "read_only"  
 2 DEFAULT TABLESPACE TB_skytf  
 3 TEMPORARY TABLESPACE TEMP  
 4 PROFILE DEFAULT  
 5 ACCOUNT UNLOCK;
User created.
SQL> GRANT CONNECT TO read_only;
Grant succeeded.
SQL> grant select on skytf.test_1 to read_Only;
Grant succeeded.
SQL> conn read_only/read_only;  
Connected.

3.2 测试 read_only 用户

1
2
3
4
5
 
SQL> select * from skytf.test_1;
 ID R  
---------- -  
 1 a  
 2 b

备注： oracle 库中的 read_only 等下用于在 PG 中连接 Oracle 的只读用户。

部署 oracle_fdw 外部表

4.1 create foreign server

1
2
3
4
 
skytf=# CREATE SERVER oracle_srv  
skytf-# FOREIGN DATA WRAPPER oracle_fdw  
skytf-# OPTIONS (dbserver 'primary_1');  
CREATE SERVER

4.2 tnsping test

1
2
3
4
5
6
7
8
9
 
[postgres@primary-01 ~]$ tnsping primary_1
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 03-MAR-2012 15:55:15
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
  
Used TNSNAMES adapter to resolve the alias  
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=192.168.1.30) (PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=MANUA)))  
OK (10 msec)  
[postgres@primary-01 ~]$

备注： 以 postgres 用户 tnsping 测试下，并且也可以 sqlplus 测试下，看看是否能连 oracle 库。

4.3 create mapping users

1
2
3
4
5
6
 
skytf=> CREATE USER MAPPING FOR skytf  
skytf-> SERVER oracle_srv  
skytf-> OPTIONS (user 'read_only', password 'read_only');  
CREATE USER MAPPING
skytf=# grant usage on foreign server oracle_srv to skytf;  
GRANT

4.4 create foreign table

1
2
3
4
5
6
7
8
 
skytf=# /c skytf skytf  
You are now connected to database "skytf" as user "skytf".
skytf=> CREATE FOREIGN TABLE ft_test_1 (  
skytf(> id integer,  
skytf(> name character varying(20)  
skytf(> ) SERVER oracle_srv  
skytf-> OPTIONS (schema 'skytf', table 'test_1');  
CREATE FOREIGN TABLE

4.5 查询测试

1
2
3
4
5
6
 
skytf=> select * from ft_test_1;  
id | name  
----+------  
 1 | a  
 2 | b  
(2 rows)

备注：终于可以查询到对端 Oracle 库了，到了这步，说明 oracle_fdw 配置成功！

常见问题

常见问题一: 查询外部表报错

1
2
3
 
skytf=> select * from ft_test_1;  
ERROR: error connecting to Oracle: OCIEnvCreate failed to create environment handle  
DETAIL:

备注： 在成功安装 orcle_fdw 后，查询外部表时老遇到上面 ERROR， 后来网上查了下，可能是在 PostgreSQL 的环境变量中没有下正确设置 Oracle 的环境变量 ，但检查了下，环境变量设置没问题，为了操作方便，后来将 PostgreSQL 的 group 设置成 oinstall ，这样 Oracle 用户和 postgres 用户属于同一组了，之后再重启 PostgreSQL 就正常了。

解决方法

1
2
3
 
[root@primary-01 bin]# usermod -g oinstall postgres  
[postgres@primary-01 ~]$ id  
uid=501(postgres) gid=500(oinstall) groups=500(oinstall) context=user_u:system_r:unconfined_t

常见问题二: 创建扩展报错

缺少 so 文件，如下： 

postgres=# create extension oracle_fdw;  
ERROR: could not load library "/opt/pgsql/lib/oracle_fdw.so": libclntsh.so.10.1:

备注： 一开始创建 oraclre_fdw 时，提示少了 so 文件。

解决方法
只要将对应的 so 文件 copy 到 $PGHOME/lib 下就行，详见 2.3 步骤。

总结

oracle_fdw 的出现为 Oracle 转 PostgreSQL 项目提供了又一种方法，至于迁数据的效率今天没有环境具体测试。

参考

原创文章，作者：kirin，如若转载，请注明出处：https://blog.ytso.com/237831.html

赞 (0)
0
上一篇 2022年1月29日
下一篇 2022年1月29日

相关推荐

发表回复

登录后才能评论