[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兼容，不过函数包之类还是手工移比较稳妥。