Success use oracle_fdw create oracle foreign table in PostgreSQL

前几天拿0.9的版本测试的，测试没有成功，记录在BLOG http://blog.163.com/digoal@126/blog/static/16387704020118951953408/ 里面。

9月14号，作者上传了新版本0.9.1。测试成功。

下面是详细的测试过程。

测试环境：

操作系统：

 RHEL 5.2 64 bit
 2.6.18-92.el5 #1 SMP Tue Jun 10 18:51:06 EDT 2008 x86_64 x86_64 x86_64 GNU/Linux

PG数据库：

PostgreSQL 9.1.0 正式版.

字符集：UTF-8

PG数据库服务器所需ORACLE软件：

 Oracle 10.2.0.4 software

被访问的Oracle数据库版本：

10.2.0.4

数据库字符集：

NLS_CHARACTERSET                                                 UTF8
NLS_NCHAR_CHARACTERSET       

PostgreSQL 9.1.0 编译参数:

./configure --prefix=/opt/pgsql --with-pgport=1921 --with-wal-segsize=64 --with-perl --with-python --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --enable-thread-safety && gmake world
gmake install-world

# 这里使用了--without-ldap

Oracle10.2.0.4 安装目录：

/app/oracle/product/10.2.0/db_1

# 这里省去了我上一篇BLOG写的下载和安装 oracle sdk,oracle instant client.的步骤，只使用oracle 10.2.0.4这个企业版软件.

chown -R postgres:postgres /app/oracle

postgres操作系统用户环境变量：

.bash_profile

# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
export PS1="$USER@`/bin/hostname -s`-> "
export PGPORT=1921
export PGDATA=/home/pgdata/pg_root
export PGARCHIVE=/home/pgdata/pg_arch
export ORACLE_HOME=/app/oracle/product/10.2.0/db_1
export LANG=en_US.utf8
export PGHOME=/opt/pgsql
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib
export DATE=`date +"%Y%m%d%H%M"`
export PATH=$ORACLE_HOME/bin:$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
alias rm='rm -i'
alias ll='ls -lh'

#　新加了环境变量ORACLE_HOME

#   修改环境变量 PATH 和 LD_LIBRARY_PATH

所以需要重启postgresql让他认到oracle的LIB目录。

pg_ctl stop -D $PGDATA
pg_ctl start -D $PGDATA

接下来下载oracle_fdw-0.9.1.tar.gz

解压缩到postgresql源码的contrib目录下面.

chown -R postgres:postgres 
su - root

# 需要用到pg_config和ORACLE_HOME目录，因此直接执行postgres用户的环境变量。

. /home/postgres/.bash_profile

cd pg9.1源码/contrib/oracle_fdw-0.9.1

# 终于没有0.9的报错了。

root@digoal -# make install
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I/app/oracle/product/10.2.0/db_1/sdk/include -I/app/oracle/product/10.2.0/db_1/oci/include -I/app/oracle/product/10.2.0/db_1/rdbms/public -I. -I. -I/opt/pgsql/include/server -I/opt/pgsql/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o oracle_fdw.o oracle_fdw.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I/app/oracle/product/10.2.0/db_1/sdk/include -I/app/oracle/product/10.2.0/db_1/oci/include -I/app/oracle/product/10.2.0/db_1/rdbms/public -I. -I. -I/opt/pgsql/include/server -I/opt/pgsql/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o oracle_utils.o oracle_utils.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -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/10.2.0/db_1 -L/app/oracle/product/10.2.0/db_1/bin -L/app/oracle/product/10.2.0/db_1/lib -lclntsh 
/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/'

然后使用超级用户进入postgresql的目标数据库加载extension.

su - postgres

psql -h 127.0.0.1 digoal postgres

digoal=# create extension oracle_fdw;

接下来去需要被访问的ORACLE数据库创建测试表：

sqlplus /nolog
conn digoal/digoal_123
create table tbl_oracle_fdw (id int,firstname varchar2(32),lastname varchar2(32),crt_time date);
# 插入测试数据
insert into tbl_oracle_fdw values(1,'zhou','digoal',sysdate);
insert into tbl_oracle_fdw values(2,'周','德哥',sysdate);
commit;

接下来使用超级用户进入postgresql数据库的需要访问到oracle的库下面.

psql -h 127.0.0.1 digoal postgres
digoal=# create server ora foreign data wrapper oracle_fdw options (dbserver '//172.16.x.xxx:1521/digoal');
# 下面options使用的是oracle数据库的用户名和密码。
digoal=# create user mapping for digoal server ora options (user 'digoal',password 'digoal_123');
# 下面options使用的是oracle的信息，plan_costs如果为true，需要ORACLE用户有查询v$SQL的权限，后面会有解释。
digoal=# create FOREIGN table tbl_oracle_fdw (id int,firstname varchar(32),lastname varchar(32),crt_time timestamp without time zone) server ora options (table 'tbl_oracle_fdw',schema 'digoal',plan_costs 'true');
# 把这个FOREIGN表赋予给digoal的ALL权限。
digoal=# grant all on tbl_oracle_fdw to digoal;
# 进入digoal用户查询。
digoal=> \c digoal digoal
# 这里报错了，原因是用于连接ORACLE的ORACLE用户digoal没有读取v$sql的权限。
digoal=> select * from tbl_oracle_fdw ;
ERROR:  no SELECT privilege on V$SQL in the remote database
DETAIL:  ORA-00942: table or view does not exist
# 到ORACLE给予digoal用户读取v$SQL的权限
sqlplus "/ as sysdba"
grant select_catalog_role to digoal;

# 再次到POSTGRESQL查询这个foreign table.还是报错，原因是刚才的从pg到oracle的数据库连接没有断开，oracle用户权限生效需要重新登陆。因此我们需要断开postgresql到oracle的连接再次尝试。

digoal=> select * from tbl_oracle_fdw ;
ERROR:  no SELECT privilege on V$SQL in the remote database
DETAIL:  ORA-00942: table or view does not exist

# 进入超级用户断开连接。

\c digoal postgres
digoal=# select oracle_close_connections();

# 再次到POSTGRESQL查询这个foreign table.正常。中午显示正常。

\c digoal digoal
digoal=> select * FROM tbl_oracle_fdw;
 id | firstname | lastname |      crt_time       
----+-----------+----------+---------------------
  1 | zhou      | digoal   | 2011-09-15 10:55:32
  2 | 周        | 德哥     | 2011-09-15 11:18:41
(2 rows)

# 由于foreign表是只读的，所以删除记录报错。

digoal=> delete FROM tbl_oracle_fdw;
WARNING:  column number 0 of foreign table "tbl_oracle_fdw" does not exist in foreign Oracle table, will be replaced by NULL
ERROR:  cannot change foreign table "tbl_oracle_fdw"

【参考】

http://blog.163.com/digoal@126/blog/static/16387704020118951953408/
http://pgfoundry.org/docman/view.php/1000600/13802/README.txt
http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html