oracle_fdw使用,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

12export 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

2postgres=# 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

9skytf=# 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

12SQL> CREATEUSERread_only IDENTIFIED BY "read_only"

2DEFAULTTABLESPACE TB_skytf

3 TEMPORARY TABLESPACE TEMP

4PROFILE 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

5SQL> 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

4skytf=# 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

TNSPingUtility 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

6skytf=> 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

8skytf=# \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

6skytf=> select * from ft_test_1;

id | name

----+------

1 | a

2 | b

(2 rows)

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

常见问题

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

2

3skytf=> 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 项目提供了又一种方法，至于迁数据的效率今天没有环境具体测试。

参考