最近嘗試把一個(gè)Oracle數(shù)據(jù)庫(kù)，連同構(gòu)建在這個(gè)數(shù)據(jù)庫(kù)上的Java應(yīng)用移植到PostgreSQL環(huán)境。在移植過(guò)程中，總結(jié)了一些要點(diǎn)，一方面作為筆記備忘，一方面也給有類(lèi)似任務(wù)需要處理而又無(wú)從下手的朋友作為參考。
1- 首先是準(zhǔn)備PostgreSQL環(huán)境。有條件的話，最好是找一臺(tái)空閑的PC機(jī)作為測(cè)試服務(wù)器，安裝Linux或BSD，然后從源碼編譯最新的PostgreSQL 8.3.0。編譯時(shí)，通過(guò)configure指定--with-perl和--with-python以支持PL/Perl和PL/Python。因?yàn)榻^大多數(shù)Linux發(fā)行版都已自帶Perl和Python，不必額外安裝。
2- 如果是Windows環(huán)境，又需要Perl和Python，則必須額外安裝，Python的話，可以方便的找到2.5 for Windows的安裝包，Perl的話，推薦ActivePerl，相對(duì)麻煩一點(diǎn)，為了后面用到的一些便利的功能順利加載，Perl版本盡量選5.8.8。
3- 創(chuàng)建數(shù)據(jù)庫(kù)和用戶(hù)。通過(guò)initdb初始化數(shù)據(jù)目錄，配置postgresql.conf指定主機(jī)IP、端口等等信息，配置pg_hba.conf指定訪問(wèn)權(quán)限，通過(guò)pg_ctl -D <數(shù)據(jù)目錄> -l <日志文件> start啟動(dòng)postmaster，然后createdb、createuser創(chuàng)建數(shù)據(jù)庫(kù)和用戶(hù)。數(shù)據(jù)庫(kù)建好之后，就可以createlang -d <數(shù)據(jù)庫(kù)名> [plperl|plperlu|plpython|plpythonu]開(kāi)啟PL/Perl和PL/Python。具體命令行參數(shù)可通過(guò)各命令加--help查看。
4- 安裝PostgreSQL客戶(hù)端pgAdminIII，最新版是1.8.2，有條件的話，也可以下載源碼自己編譯。
5- 安裝Oracle客戶(hù)端，需要在PostgreSQL同一臺(tái)機(jī)器，以便Perl用于連接數(shù)據(jù)庫(kù)的DBI和Oracle驅(qū)動(dòng)DBD::Oracle模塊順利安裝。如果是Windows上的ActivePerl，則可以通過(guò)ppm install DBD-Oracle，如果是Linux/BSD，則可以通過(guò)CPAN來(lái)安裝，如perl -MCPAN -e shell進(jìn)入CPAN Shell，通過(guò)install <模塊名>或force install <模塊名>安裝DBI和DBD::Oracle。
6- 數(shù)據(jù)庫(kù)的移植，可以選擇ora2pg來(lái)幫忙，目前的版本是4.7。ora2pg是一個(gè)用于讀取Oracle數(shù)據(jù)庫(kù)schema、數(shù)據(jù)，并生成PostgreSQL腳本或直接導(dǎo)入PostgreSQL數(shù)據(jù)庫(kù)的Perl工具。用法很簡(jiǎn)單，就是通過(guò).conf文件指定數(shù)據(jù)庫(kù)連接信息包括NLS_LANG、需要導(dǎo)出導(dǎo)入的schema、table、view、data等等，然后執(zhí)行一個(gè)pl腳本。這是目前相對(duì)比較成熟的一個(gè)方案，但是遇到schema復(fù)雜、約束較強(qiáng)的數(shù)據(jù)庫(kù)，需要手工處理的地方還是不少。建議不要直接寫(xiě)入PostgreSQL，而是生成SQL腳本，驗(yàn)證無(wú)誤后再執(zhí)行。ora2pg默認(rèn)會(huì)把Oracle中名稱(chēng)的大寫(xiě)轉(zhuǎn)換成小寫(xiě)，因?yàn)镻ostgreSQL在解析SQL時(shí)，除非""括起來(lái)，默認(rèn)都是轉(zhuǎn)成小寫(xiě)。schema、table、view、sequence、data等等，基本用ora2pg，加上一些手工調(diào)整即可搞定。至于function、stored procedure等，還是手工移吧，偷不得懶。除了ora2pg，其實(shí)也可以配置DBI-Link，將Oracle數(shù)據(jù)庫(kù)掛到PostgreSQL數(shù)據(jù)庫(kù)作為一組獨(dú)立的"schema"，然后用create table xxx as select ... from ...這樣的語(yǔ)法來(lái)倒表和數(shù)據(jù)。PostgreSQL的contrib包也附帶有一個(gè)dblink，不過(guò)是連接其他PostgreSQL數(shù)據(jù)庫(kù)的，如果需要連接非PostgreSQL，
還是考慮DBI-Link，任何可以通過(guò)Perl的DBI接口訪問(wèn)的數(shù)據(jù)庫(kù)，都能link進(jìn)PostgreSQL。
7- 接下來(lái)就是Java應(yīng)用本身了，我這次移的這個(gè)應(yīng)用是Spring+iBatis架構(gòu)的，很多SQL語(yǔ)句都是明文，好在DAO層的基礎(chǔ)部分(CRUD)的SQLMap是工具自動(dòng)生成，且都是符合ANSI SQL92標(biāo)準(zhǔn)的，不需要修改即可使用。其余的高級(jí)查詢(xún)SQL，需要調(diào)整的地方不少，一些常見(jiàn)的修改列舉如下：
i. SELECT出來(lái)的column（包括子查詢(xún)），如果有別名，必須加AS，比如 select null as some_column from some_table;
ii. PostgreSQL沒(méi)有dual表，類(lèi)似select 0 from dual的語(yǔ)句，寫(xiě)成select 0即可;
iii. DECODE函數(shù)需要重構(gòu)成(case when some_column = 'some_value' then 'some_other_value' when ... then ... else 'some_default_value' end ) as some_column;
iv. NVL()函數(shù)，PostgreSQL中相對(duì)應(yīng)的是coalesce()，其實(shí)幾乎所有主流DBMS都支持coalesce，包括Oracle，這才是標(biāo)準(zhǔn)寫(xiě)法;
v. 比較日期，在PostgreSQL中，建議使用date_trunc('day', SOME_DATE) = date_trunc('day', #enteredDate#)這樣的寫(xiě)法，其中'day'位置可選字段包括有year、month、week、hour、minute、second等等;
vi. SYSDATE，對(duì)應(yīng)到PostgreSQL是current_timestamp，可以根據(jù)需要使用current_date;
vii. ROWNUM，通常我們用ROWNUM都是為了限制查詢(xún)出來(lái)的記錄數(shù)，PostgreSQL沒(méi)有這個(gè)關(guān)鍵字，需要改成在SELECT語(yǔ)句最后添加 LIMIT語(yǔ)句，如LIMIT 100;
viii. (+)這樣的外連接寫(xiě)法需要調(diào)整為SQL標(biāo)準(zhǔn)的 table1 [LEFT|RIGHT|FULL] OUTER JOIN table2 ON (...);
ix. CONNECT BY ... START WITH ... 遞歸查詢(xún)可以參考 http: //www.postgresql.org/docs/8.3/static/tablefunc.html 的connectby()函數(shù).
最后再多提一點(diǎn)，PostgreSQL自帶的過(guò)程語(yǔ)言是PL/pgSQL，在PostgreSQL上寫(xiě)function，除了用plpqsql，還支持sql、plperl(u)、plpython(u)等等。如果你對(duì)SQL天生過(guò)敏，看類(lèi)似PL/pgSQL的代碼都很吃力，別說(shuō)是寫(xiě)了，你完全可以用你喜歡的語(yǔ)言來(lái)表達(dá)函數(shù)和存儲(chǔ)過(guò)程的邏輯。有了PL/Python，你還怕什么呢？你幾乎能做任何事。
[更新 20080313] 把JDBC驅(qū)動(dòng)的部分漏掉了，移植Java應(yīng)用時(shí)，除了改SQL，還需要拿PostgreSQL的JDBC驅(qū)動(dòng)放到classpath下面，如WEB-INF/lib，然后修改數(shù)據(jù)庫(kù)連接URL，改成jdbc:postgresql://<ip>:<port>/<dbname>即可。
[更新 20080323] 移植schema和數(shù)據(jù)時(shí)，比ora2pg更方便的一種方式是利用EnterpriseDB的Migration Tool，將Oracle的JDBC驅(qū)動(dòng)ojdbc14.jar拷貝到EnterpriseDB安裝路徑下的jre/lib/ext下后，啟動(dòng)Developer Studio即可建立Oracle連接，選中schema后，可以通過(guò)右鍵Online Migration將schema、數(shù)據(jù)、函數(shù)包等等一次性通通導(dǎo)入EnterpriseDB。如果要繼續(xù)往"純"PostgreSQL移，從EDB做backup，然后到PostgreSQL下做restore，這樣會(huì)丟掉函數(shù)包，因?yàn)楫吘笶DB在PostgreSQL基礎(chǔ)上做了相當(dāng)改造以和Oracle兼容，不過(guò)函數(shù)包之類(lèi)還是手工移比較穩(wěn)妥。