novo_land / DB / [Tips] 移植Oracle数据库到PostgreSQL的要...

0 0

   

[Tips] 移植Oracle数据库到PostgreSQL的要点

2011-10-24  novo_land

[Tips] 移植Oracle数据库到PostgreSQL的要点


最近尝试把一个Oracle数据库,连同构建在这个数据库上的Java应用移植到PostgreSQL环境。在移植过程中,总结了一些要点,一方面作为笔记备忘,一方面也给有类似任务需要处理而又无从下手的朋友作为参考。

1- 首先是准备PostgreSQL环境。有条件的话,最好是找一台空闲的PC机作为测试服务器,安装Linux或BSD,然后从源码编译最新的 PostgreSQL 8.3.0。编译时,通过configure指定--with-perl和--with-python以支持PL/Perl和PL/Python。因为绝 大多数Linux发行版都已自带Perl和Python,不必额外安装。

2- 如果是Windows环境,又需要Perl和Python,则必须额外安装,Python的话,可以方便的找到2.5 for Windows的安装包,Perl的话,推荐ActivePerl,相对麻烦一点,为了后面用到的一些便利的功能顺利加载,Perl版本尽量选 5.8.8。

3- 创建数据库和用户。通过initdb初始化数据目录,配置postgresql.conf指定主机IP、端口等等信息,配置pg_hba.conf指定访 问权限,通过pg_ctl -D <数据目录> -l <日志文件> start启动postmaster,然后createdb、createuser创建数据库和用户。数据库建好之后,就可以createlang -d <数据库名> [plperl|plperlu|plpython|plpythonu]开启PL/Perl和PL/Python。具体命令行参数可通过各命令加 --help查看。

4- 安装PostgreSQL客户端pgAdminIII,最新版是1.8.2,有条件的话,也可以下载源码自己编译。

5- 安装Oracle客户端,需要在PostgreSQL同一台机器,以便Perl用于连接数据库的DBI和Oracle驱动DBD::Oracle模块顺利 安装。如果是Windows上的ActivePerl,则可以通过ppm install DBD-Oracle,如果是Linux/BSD,则可以通过CPAN来安装,如perl -MCPAN -e shell进入CPAN Shell,通过install <模块名>或force install <模块名>安装DBI和DBD::Oracle。

6- 数据库的移植,可以选择ora2pg来帮忙,目前的版本是4.7。ora2pg是一个用于读取Oracle数据库schema、数据,并生成 PostgreSQL脚本或直接导入PostgreSQL数据库的Perl工具。用法很简单,就是通过.conf文件指定数据库连接信息包括 NLS_LANG、需要导出导入的schema、table、view、data等等,然后执行一个pl脚本。这是目前相对比较成熟的一个方案,但是遇到 schema复杂、约束较强的数据库,需要手工处理的地方还是不少。建议不要直接写入PostgreSQL,而是生成SQL脚本,验证无误后再执行。 ora2pg默认会把Oracle中名称的大写转换成小写,因为PostgreSQL在解析SQL时,除非""括起来,默认都是转成小写。schema、 table、view、sequence、data等等,基本用ora2pg,加上一些手工调整即可搞定。至于function、stored procedure等,还是手工移吧,偷不得懒。除了ora2pg,其实也可以配置DBI-Link,将Oracle数据库挂到PostgreSQL数据 库作为一组独立的"schema",然后用create table xxx as select ... from ...这样的语法来倒表和数据。PostgreSQL的contrib包也附带有一个dblink,不过是连接其他PostgreSQL数据库的,如果需 要连接非PostgreSQL, 还是考虑DBI-Link,任何可以通过Perl的DBI接口访问的数据库,都能link进PostgreSQL。

7- 接下来就是Java应用本身了,我这次移的这个应用是Spring+iBatis架构的,很多SQL语句都是明文,好在DAO层的基础部分(CRUD)的 SQLMap是工具自动生成,且都是符合ANSI SQL92标准的,不需要修改即可使用。其余的高级查询SQL,需要调整的地方不少,一些常见的修改列举如下:

i. SELECT出来的column(包括子查询),如果有别名,必须加AS,比如 select null as some_column from some_table;
ii. PostgreSQL没有dual表,类似select 0 from dual的语句,写成select 0即可;
iii. DECODE函数需要重构成(case when some_column = 'some_value' then 'some_other_value' when ... then ... else 'some_default_value' end ) as some_column;
iv. NVL()函数,PostgreSQL中相对应的是coalesce(),其实几乎所有主流DBMS都支持coalesce,包括Oracle,这才是标准写法;
v. 比较日期,在PostgreSQL中,建议使用date_trunc('day', SOME_DATE) = date_trunc('day', #enteredDate#)这样的写法,其中'day'位置可选字段包括有year、month、week、hour、minute、second等 等;
vi. SYSDATE,对应到PostgreSQL是current_timestamp,可以根据需要使用current_date;
vii. ROWNUM,通常我们用ROWNUM都是为了限制查询出来的记录数,PostgreSQL没有这个关键字,需要改成在SELECT语句最后添加 LIMIT语句,如LIMIT 100;
viii. (+)这样的外连接写法需要调整为SQL标准的 table1 [LEFT|RIGHT|FULL] OUTER JOIN table2 ON (...);
ix. CONNECT BY ... START WITH ... 递归查询可以参考 http: //www.postgresql.org/docs/8.3/static/tablefunc.html 的connectby()函数.

最 后再多提一点,PostgreSQL自带的过程语言是PL/pgSQL,在PostgreSQL上写function,除了用plpqsql,还支持 sql、plperl(u)、plpython(u)等等。如果你对SQL天生过敏,看类似PL/pgSQL的代码都很吃力,别说是写了,你完全可以用你 喜欢的语言来表达函数和存储过程的逻辑。有了PL/Python,你还怕什么呢?你几乎能做任何事。

[更新 20080313] 把JDBC驱动的部分漏掉了,移植Java应用时,除了改SQL,还需要拿PostgreSQL的JDBC驱动放到classpath下面,如WEB- INF/lib,然后修改数据库连接URL,改成jdbc:postgresql://<ip>:<port> /<dbname>即可。

[更新 20080323] 移植schema和数据时,比ora2pg更方便的一种方式是利用EnterpriseDB的Migration Tool,将Oracle的JDBC驱动ojdbc14.jar拷贝到EnterpriseDB安装路径下的jre/lib/ext下后,启动 Developer Studio即可建立Oracle连接,选中schema后,可以通过右键Online Migration将schema、数据、函数包等等一次性通通导入EnterpriseDB。如果要继续往"纯"PostgreSQL移,从EDB做 backup,然后到PostgreSQL下做restore,这样会丢掉函数包,因为毕竟EDB在PostgreSQL基础上做了相当改造以和 Oracle兼容,不过函数包之类还是手工移比较稳妥。

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。如发现有害或侵权内容,请点击这里 或 拨打24小时举报电话:4000070609 与我们联系。

    来自: novo_land > 《DB》

    猜你喜欢

    0条评论

    发表

    请遵守用户 评论公约

    类似文章
    喜欢该文的人也喜欢 更多