最近嘗試把一個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兼容，不過函數包之類還是手工移比較穩妥。