使用PostgreSQL 9.1建立Oracle,Mysql,CouchDB,csv,等外部表.

wanzai01

【原文地址】
http://blog.163.com/digoal@126/b ... 704020118151162340/
http://blog.163.com/digoal@126/b ... 704020118951953408/
http://blog.163.com/digoal@126/b ... 040201141641148311/

【内容摘要】
这里只介绍了到Oracle的外部表.其他的感兴趣的朋友可以自行参考文档测试.
测试环境：
操作系统：
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                                           UTF8

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=$PATHHOME/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/libPGHOME/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/binPGHOME/binPATH:.
export MANPATH=$PGHOME/share/manMANPATH
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://pgxn.org/tag/fdw/
http://pgfoundry.org/docman/view.php/1000600/13802/README.txt
http://www.oracle.com/technetwor ... -64soft-092277.html

rsuhkoi

靠，居然和我硕士实验室的一个项目很相似