從 Oracle 到 Postgres: 最完整的轉移指引五步驟 3/4篇:步驟二－架構轉移

EDB 產品資訊 2022 年 12 月 12 日 最後更新時間 : 2024 年 1 月 17 日

內容目錄

從 Oracle 遷移至 Postgres 計畫的階段和步驟為何？

Oracle 到 Postgres 的遷移被劃分為 5 個步驟或階段，內容如下：

評估

Schema 轉移

功能測試

效能測試

資料遷移

本系列由 4 篇文章組成，分別為 Oracle 與 Postgres 的比較、資料庫評估、架構遷移(本篇)與資料庫功能測試。歡迎閱讀系列中的其他文章，以了解完整的資料庫遷移步驟。

在 Oracle 和 Postgres 中 Schema 的差別

Schema 在 Oracle 中相當於「帳號」，並且與用戶的名字相同。每個 Oracle 帳號預設都擁有自己專屬的 Schema。而 Postgres 與 Oracle 的差異在於，如果您沒有特別指定 Schema，新產生的物件就會預設指定到 Public Schema。

利用同一個名字建立帳號和 Schema

在預設中， $user 是 Schema search_path 中第一個物件

Postgres 的 Schema 設定的其中一個好處是，允許一個帳號能建立多個 Schema，無需為了不同 Schema 創建對應帳號；並能夠授權其他帳號在 Schema 內建立物件。

從 Oracle 到 Postgres 的架構遷移工具有哪些？

市面上有數個能輔助使用者自動移轉 Schema 的遷移工具。以下工具能免費取得或免費試用的平台，協助您將 Oracle 物件遷移至 Postgres 資料庫。

Ora2pg－穩固的遷移工具，能夠直接連接 Oracle 資料庫，轉換 Oracle 的表格與物件，產生能夠適用 Postgres 的 SQL 腳本。

Ora_migrator－基於 oracle_fdw foreign data wrapper 的擴充套件，用以移轉 Oracle 內的資料。

Orafce－該擴充套件可以在 Postgres 提供許多 Oracle 相容函式，並支援 Oracle 風格的日期格式與數個相容 Oracle 的資料類型。

EDB Migration Portal－EDB 公司提供的 Oracle 遷移至 EDB Postgres Advanced Server 雲端服務，為使用者提供詳盡的資料庫移轉評估、資料庫物件轉移和資料庫移轉相容性診斷。

除了上述的免費工具，市面上也存在其他商業版的遷移工具，如：亞馬遜的 AWS SCT。

Oracle 到 Postgres 遷移工具比較表

以下提供的遷移工具比較表，全面比較不同移轉工具在資料庫移轉任務中可以支援的程度。

Oracle to Postgres migration tools comparison matrix

在遷移過程中，Oracle 和 Postgres 之間有哪些 Schema 架構差異需留意？

在計畫移轉 Oracle 資料庫物件時，請特別注意 Oracle 和 Postgres 之間的以下差異。

1.從 Oracle 遷移至 Postgres 的欄位結構轉換

在 Postgres 12 版以前，Postgres 沒有支援任何虛擬欄位功能，因此過往建議使用者將這些虛擬列改寫為視觀表（view）。當前版本 Postgres 已具備生成欄位（generated columns）功能，並與 Oracle 的虛擬欄位有許多相同之處。

2.從 Oracle 遷移至 Postgres 的欄位約束功能（Constraints）

在這兩種資料庫系統中，主鍵（Primary Key）、外鍵（ Foreign Key）、空值檢查（Check Not-Null）和唯一性約束的運作模式都大同小異。

3.從 Oracle 遷移到 Postgres 時的物件名稱差異

Oracle 預設將 Schema、表格、欄位和函數等物件名字轉為大寫（如果名字被雙引號標記則不受影響）。相反地，Postgres 物件名稱預設轉換為小寫 (如果名字被雙引號標記則不受影響)。只要您應用程式中的 SQL 物件一致地套用雙引號，或是完全未使用雙引號標記，基本上不會有太大問題。

4.從 Oracle 遷移到 Postgres 時的索引支援

- - Postgres 同 Oracle 支援功能相同的二元樹索引（B-tree index）和降冪排序索引（descending index）
  - PostgreSQL 尚未支援返向索引（Reverse key index）、點陣索引（Bitmap index）、結合索引（Join index）
  - Postgres 尚未支援跨分割表全域索引（Global index）

5.從 Oracle 遷移到 Postgres 時的分區表類型支援

Postgres 支援等同 Oracle 的 Hash、List 和 Range 的分區表功能

6.從 Oracle 遷移到 Postgres 的表（Table）

CREATE TABLE 指令在多數情況下語法都是相容的，但仍有些許例外：

- - Postgres 不支援 Oracle 全局暫存表（global temporary tables），須改用一般臨時表（LOCAL TEMP）。
  - 分區表（Partioning）：可使用繼承（Inheritance）、觸發器（Triggers）、和檢查限制式（CHECK Constraints ）功能實現。（譯注：Postgres 10 之後支援原生的分區表語法，非常建議採用）
  - Oracle 特有的儲存參數（INITRANS, MAXEXTENTS）在 Postgres 中無法識別，因此需要移除
  - 使用 Postgres 的 fillfactor 取代在 Oracle 使用的 PCTFREE 參數

7.從 Oracle 遷移至 Postgres 時的表空間（Tablespace）差異

儘管 Oracle 和 Postgres 的表空間功能有所差異，但都可以支援相同的用途。（譯注：分散資料儲存到不同磁碟區）

8.從 Oracle 遷移至 Postgres 的資料型態

下表列出了 Oracle 和 Postgres 資料型態的顯著差異

[wptb id="18582" not found ]

Oracle	Postgres	EDB Postgres Advanced Server	建議
VARCHAR2(n)	VARCHAR(n)	VARCHAR2(n), VARCHAR(n)	請注意不要被 Oracle 和 Postgres 資料類型的 ‘n’混淆。在 Oracle 中，它代表 bytes 的大小，在 Postgres 中，它代表資料字元數量
NVARCHAR, NVARCHAR2	VARCHAR or TEXT	NVARCHAR, NVARCHAR2, VARCHAR or TEXT
CHAR(n), NCHAR(n)	CHAR(n)	CHAR(n), NCHAR(n),	請注意不要被 Oracle 和 Postgres 資料類型的 ‘n’混淆。在 Oracle 中，它代表 bytes 的大小，在 Postgres 中，它代表資料字元數量
NUMBER(n, m)	NUMERIC(n,m)	NUMERIC(n,m) NUMBER(n, m)	NUMBER 資料型態可以轉換為 NUMERIC 數值型態。儘管 NUMERIC 的數值範圍不受限制，但 SMALLINT、INT、 BIGINT、 REAL 和 DOUBLE PRECISION 資料類型卻提供更好的效能。
NUMBER(4)	SMALLINT	NUMBER(4) SMALLINT
NUMBER(9)	INT	NUMBER(9) INT
NUMBER(18)	BIGINT	NUMBER(18) BIGINT
NUMBER(n)	NUMERIC(n)	NUMBER(n) NUMERIC(n)	當 n>=19
BINARY_INTEGER, BINARY_FLOAT	INTEGER, FLOAT	BINARY_INTEGER, INTEGER, FLOAT
DATE	TIMESTAMP(0)	DATE TIMESTAMP(0)	在 Oracle 中 DATE 資料類型會同時回傳日期和時間，但在 Postgres 中，DATE 資料類型只返回日期不含時間。
TIMESTAMP WITH LOCAL TIME ZONE	TIMESTAMPTZ	TIMESTAMP WITH LOCAL TIME ZONE TIMESTAMPTZ	Oracle 擁有 TIMESTAMP WITH TIME ZONE 和 TIMESTAMP WITH LOCAL TIME ZONE 這兩種資料類型。Postgres 的 TIMESTAMPTZ 與 Oracle 的 TIMESTAMP WITH LOCAL TIME ZONE 相互對應。
CLOB, LONG	TEXT	CLOB, LONG TEXT	Postgres 的 TEXT 資料類型能夠儲存 1 GB 大小的文字格式資料。
BLOB, RAW(n), LONG RAW	BYTEA(1 GB limit), Large object	BLOB, RAW(n), LONG RAW BYTEA(1 GB limit) Large Object	在 Oracle 中，BLOB 資料類型適用於儲存非結構化二進位資料，在儲存上幾乎沒有大小限制（能夠儲存將近 128 TB 的二進制資料），而 Postgres 的 BYTEA 資料類型能儲存將近 1GB 的二進位資料。如果您的資料量超過儲存上限，可以考慮 Large Object（他們被存於不同的表格中）。
NLS_DATE_FORMAT	DateStyle	DateStyle	這些是設置顯示日期資訊格式的參數。 Postgres 的預設日期格式 DateStyle 參數為 ISO。而 Oracle 的預設日期格式則是由NLS_TERRITORY 參數繼承而來。

從 Oracle 遷移至 Postgres 時面臨的挑戰

這個章節將探討從 Oracle 遷移至 Postgres 時，可能面臨的挑戰。為了解決遷移至 Postgres 後面臨的挑戰和限制，這些物件必須被其他替代方案手動重寫。

1. 欄位約束功能

儘管 Oracle 允許它們的用戶隨心所欲地停用和啟用欄位約束功能，但一般不建議這種操作關聯式資料庫的作法，如果操作不夠謹慎，可能會導致資料毀損。

使用者能夠在 Postgres 中，透過 SET CONSTRAINTS 指令，指定延遲欄位約束功能（DEFERRED）。延遲欄位約束功能用來指定欄位約束的運作時機。如果在 Oracle 中欄位約束沒有預先啟用延遲功能，則必須先將該欄位約束刪除再重設啟用（部份情況下可以直接變更欄位約束啟用延遲功能設定）。切記：為避免潛在錯誤或資料損壞，建議將用於刪除或重建欄位約束的指令，以 BEGIN/COMMIT 程式區塊將其框列成一筆交易，以便在設定期間鎖定資料表。

2.資料刪除語法

在 Postgres 中，DELETE 語法必需搭配 FROM 關鍵字一起使用，但在 Oracle 中則不用。

Oracle:

Postgres:

3.刪除資料庫物件

在 Postgres 中，只有物件擁有者和「超級使用者」擁有刪除物件的權限。儘管物件擁有者的群組能透過授權被賦予，但卻無法授權刪除資料庫物件的操作。如果原先搭配 Oracle 應用程式中仰賴非擁有者刪除物件的功能，會需要重新設計/設定相關功能。

4.Dual 表

在 Oracle 中，SELECT 語法一定要搭配 FROM 子句，因此 FROM DUAL 被用於不需表名的 SELECT 指令中。但在 Postgres 中， SELECT 語法不強制搭配 FROM 子句，因此 FROM DUAL 這個語法在 Postgres 中並不必要，一般情況下能直接能移除。如果使用 Postgres 時仍需要 Dual 表，可以創建視觀表代替。

5.空字串和 NULL 值

空字串在 Oracle 中被視為 NULL ，在 Postgres 則否。在 Oracle 中，使用者能夠利用 IS NULL 條件式來檢查字串是否為空字串，但在 Postgres 中，IS NULL 對空字串的傳回值皆為 FALSE。（運算子為 NULL 時，則傳回 TRUE）

6.Oracle Federation 與 Postgres 的外部表（Foreign Data Wrappers）

Oracle Federation 功能讓使用者介接異質資料，並將其當成本地資料進行操作。不過 Postgres 外部表功能又更加多元，允許使用者介接更廣泛的資料源。

7.權限賦予

GRANT 語法在 Oracle 和 Postgres 中的作用是相似的：GRANT 語法有兩種的使用情境 — 授權資料庫物件的存取權限，以及群組管控功能。並非所有能在 Oracle 授權的權限，都能在 Postgres 中被授權。例如，在 Postgres 中，授權資料表觸發器建立權限，可以讓給定帳號對指定表格建立觸發器，但只有資料表的擁有者有刪除觸發器的權限。相同狀況在 Oracle 中則可以刪除。

8. 階層式查詢

在 Oracle 中，使用 START WITH . . . CONNECT BY 語法能夠執行階層式查詢。但 Postgres 不支援該語法，如果想在 Postgres 中使用階層式查詢，則要使用 WITH RECURSIVE 語法改寫。

Oracle:

Postgres:

9. Join 語法搭配 (+)

Oracle 支援一種特殊的速記法，只要利用 (+) 運算子就能執行 Left、Right 和 Outer Join。由於 Postgres 不支援該運算子，使用者必須在語法中明確撰寫 JOIN 修飾字。

Oracle:

Postgres:

10. 檢查 NOT NULL

要確認 Oracle 中哪些欄位為 NOT NULL，使用的是 CHECK 指令。（CHECK <column_name> IS NOT NULL）

在 Postgres 的 pg_attribute 系統表，則已經內建了 NOT NULL constraint 設定，紀錄於該系統表 attnotnull 欄位中。

11.套件語法（package）支援

Postgres 不支援 packages 語法，但卻能夠利用 Schema 特性，將函數（user-defined function）和程序（stored procedure）組織起來。另外，您能夠使用 Orafce 相容套件，提供部份的 Oracle 內建 package，或者使用 EDB Postgres Advanced Server ，內建相容 package 與 package 語法支援。

12.PL/SQL 到 PL/pgSQL 的改寫

Postgres 的程序式語言 PL/pgSQL 與 Oracle 的 PL/SQL 在眾多層面上十分類似：這兩種語法的都具備區塊結構、皆為指令式語言，連變數賦值、迴圈和條件式的語法格式也都極為相似。Postgres 官方手冊提供詳盡的 PL/SQL 改寫 PL/pgSQL 指引。

13.遠端資料庫物件

若需要存取遠端資料庫物件，您能夠使用 DBLINK 或 Foreign Data Wrapper (例如 Oracle_fdw) 來介接其他資料庫。

ROWID、CTID 和識別欄位（Identity columns）

Postgres 不具備完全對應 Oracle ROWID 隱藏欄位的功能：Oracle ROWID 提供了資料表資料的精準儲存位置。CTID 是 Postgres 中與 Oracle ROWID 較為相似的內建欄位，然而每執行 VACUUM 時，它的值都會有所改變。然而，一般可以採用識別欄位（Identity columns）功能：識別欄位可以被指定為 GENERATED ALWAYS 或 GENERATED BY DEFAUL。其中，GENERATED BY DEFAULT 允許使用者選擇性寫入或更新值，而不強制使用系統產生的值。

14.序號物件（Sequences）

Sequence 在 Oracle 和 Postgres 中使用不同的語法，因此需要手動或利用腳本作語法修改。

Oracle:

Postgres:

15.SUBSTR 函數

SUBSTR 函數在 Oracle 和 Postgres 中的運作行為並不相同。在 Oracle 中，SELECT SUBSTR(‘ABC’,-1) FROM DUAL 語法會回傳 C，而Postgres 中的 SELECT SUBSTR(‘ABC’,-1) 則會回傳 ABC。Orafce 擴充套件提供 SUBSTR 函數，以便在 Postgres 中回傳相同 Oracle 的結果。

16.同義詞（Synonyms）

Postgres 不支援同義詞（synonyms）。為了替代 Oracle 用於存取遠端物件的 CREATE SYNONYM 語法，在 Postgres 可以用 SET search_path 語法，含括事先設定的遠端物件。

Oracle:

Postgres:

17.SYSDATE

Oracle SYSDATE 函數回傳日期和時間（伺服器的時區），雖然 Postgres 沒有對應的函數，但是仍能透過其他的方式取得日期和時間資訊，以滿足不同需求：statement_timestamp() 語法能夠回傳當前指令開始的日期與時間， now() 和 transaction_timestamp() 這兩種函數能夠回傳交易起始的日期和時間，clock_timestamp() 則能回傳該函數執行時刻的日期和時間。

18. TO_DATE 函數

雖然 Oracle 和 Postgres 中的 to_date() 函數都能返回日期資料類型。但Postgres 的資料類型提供資料的（年、月、日），而 Oracle 的資料類型則提供日期和時間（年、月、日、小時、分鐘、秒）。為了避免兩者的日期資料不相容，建議使用 Postgres 的 to_timestamp() 。

解決兩者不相容的方法就是將 TO_DATE() 函數呼叫改寫為 TO_TIMESTAMP()。如果您使用 Orafce 擴充套件，那就不必做出任何更改，因為相同行為的 TO_DATE() 已經內建於 Orafce 套件，因此能得出與 Oracle 相同的結果。

Oracle:

Postgres:

19.交易控制

交易機制在 Oracle 中為預設啟用，但在Postgres 中，交易機制需被使用者啟用。每當 Oracle 中執行指令時，新交易會被起始，並且最終以 COMMIT 語法結束。在 Postgres 中，一筆交易會以 BEGIN 開始，最終以 COMMIT 結束。 Postgres 和 Oracle 的交易隔離層級（Transaction Isolation）是相同的，Read Committed 也是兩者的預設交易隔離層級。

20.交易錯誤的例外處理

Postgres 開發的主要目的是為了支援交易控制和錯誤例外處理，並提供全面的 ACID 支援和隔離層級（Isolation levels）。Postgres 也支援執行期間的例外處理，並向 PL/pgSQL 或應用程式回傳明確的錯誤代碼和訊息。

然而 Oracle 在錯誤例外處理功能與 Postgres 的處理相差極大，因此將資料庫遷移至 Postgres 後，仍須做出一些程式調整。以下分享幾個最佳化 Postgres 錯誤處理（error handling）的建議：

- - PL/pgSQL 中的交易控管是不被允許的：您無法在預存程式（stored procedure。譯注：user-defined function）中 Commit 或 Rollback 交易。您必須從應用程式呼叫 Commit 和 Rollback ，在呼叫儲存程式的應用程式上執行交易管理 — Start 和 Commit 或 Rollback，而預存程式便於該交易內運行。若有使用 Oracle stored procedure 內的交易控制功能，相關程式就需要改寫。（譯注：Postgres 12 開始，PL/pgSQL 支援可控制交易的 stored procedure 功能，並與 user-defined function 作區分。不過 PL/pgSQL procedure 的交易控制行為仍然與 PL/SQL 有所不同，仍需對相關程式碼作測試與調整、改寫）

- - 當交易過程出現運行例外時，在使用者執行另一新條指令前，就必須先 Roll Back 當前交易。在 Postgres 的交易過程中有錯誤發生時，交易會被中止。應用程式日誌將顯示以下錯誤訊息：

- - 在 PL/pgSQL 中使用 BEGIN…EXCEPTION…END 程式區塊進行例外處理（exception handling），讓程式捕捉任何發生的錯誤。這會在 BEGIN 區塊前自動設置進度儲存點（Savepoint），並且在遭遇錯誤時 Roll Back。請切記，PL/pgSQL 處理錯誤的程式區塊會設置進度儲存點，然而進度儲存點功能副作用相對比較大，使用上需要多加留意數量。

（譯注：Savepoint 功能在 Postgres 採用 Subtransaction 機制實現，一般建議避免開啟過多與過久的 Subtransaction，相關參考資料 PostgreSQL Subtransactions Considered Harmful | Database Lab、PostgreSQL subtransactions, savepoints, and exception blocks | by Franck Pachot、Why we spent the last month eliminating PostgreSQL subtransactions | GitLab、PostgreSQL Subtransactions and performance – CYBERTEC ）

- - Oracle 與 Postgres 的錯誤代碼和例外類型對應：儘管有些錯誤代碼 Oracle 和 Postgres 中是相同的，但也有不少相異之處。程式碼也會受到對應影響 — 例如，Oracle 特有的 JDBC 例外，需要改寫為 JDBC 通用例外，或是改成 Postgres 特有的 JDBC 例外。

- - 一般而言，確保應用程式正確地處理資料庫交易行為和錯誤例外處理，是 Postgres 資料庫遷移計畫中的重要環節，一般而言開發者需要全盤地檢查您的資料庫預存程式碼和應用程式碼。

對於需要在 Postgres 中保留 Oracle 相容功能的企業，或希望遷移計畫能夠加速完成，為了避免大量的程式碼重構，可以考慮將 Oracle 資料庫遷至 EDB Postgres Advanced server：EDB Postgres Advanced server 是一個具備 Oracle 相容特性的 Postgres 企業級解決方案，具備原生的 PL/SQL 語法相容功能。

這部影片將展示如何在 EDB Postgres Advanced server 中執行 Oracle 風格的語法查詢：

延伸閱讀：本系列由 4 篇文章組成，如果對資料庫遷移有興趣，可以參考下篇文章：資料庫功能測試

EDB PostgreSQL

從 Oracle 到 Postgres: 最完整的轉移指引五步驟 3/4篇:步驟二－架構轉移

從 Oracle 遷移至 Postgres 計畫的階段和步驟為何？