PPAS 8.4 Upgrade to PPAS 9.1

產品升級本應該是件容易的事，卻沒想到一個觀念不清楚，過程就跌跌撞撞，^_^還好只是Lab~~

為獲得更多資料庫相容特性(紅色預言)的支持，不得不更新資料庫版本，版本升級功能在 PPAS 9.x 以前是叫 pg_migrator，在 PPAS 9.x 版以後改名為 pg_upgrade ，首先你會有一個 PPAS 8.4(or 8.x) 版本的資料庫，處理過程如下:

1. 將 PPAS 8.4 客戶認證設定檔 ($PGDATA/data/pg_hba.conf) 開放，使得 pg_upgrade 在處理可以順利進行。

# TYPE  DATABASE       USER            ADDRESS                 METHOD
   local    all                         all                                                     trust       

2. 安裝 PPAS 9.1 產品，安裝步驟簡單，請自行安裝，並且修改 port 為 5445 (暫時upgrade用，待upgrade完後，修改回 5444)。

3. 停止 PPAS 9.1 資料庫。

# service ppas-9.1 stop

4. 刪除 PPAS 9.1 cluster db，由於產品在安裝後會自動建立 cluster db，所以要刪除 cluster db，並且重新建立目錄。

$ rm -rf /opt/PostgresPlus/9.1AS/data
$ mkdir /opt/PostgresPlus/9.1AS/data
$ chmod 0700 /opt/PostgresPlus/9.1AS/data

5. 建立新版本 PPAS 9.1 cluster db。

$ initdb -D /opt/PostgresPlus/9.1AS/data -E UTF-8
$ mkdir -p /opt/PostgresPlus/9.1AS/data/pg_log

6. 修改 PPAS 9.1 客戶認證設定檔 ($PGDATA/data/pg_hba.conf)，做法同步驟1。

# TYPE DATABASE USER ADDRESS METHOD local all all trust

7. 確認 pg_upgrade 功能是否正常運作。

$ cd /opt/PostgresPlus/9.1AS/bin $ pg_upgrade -d "/opt/PostgresPlus/8.4AS/data" \ # old cluster data dir -D "/opt/PostgresPlus/9.1AS/data" \ # new cluster data dir -b "/opt/PostgresPlus/8.4AS/bin" \ # old cluster bin dir -B "/opt/PostgresPlus/9.1AS/bin" \ # new cluster bin dir -p 5444 \ # old edb port -P 5445 \ # new edb port --check # check pg_upgrade

如果執行結果成功無誤，則訊息會如下所示。

Performing Consistency Checks ---------------------------- Checking current, bin, and data directories ok Checking cluster versions ok Checking database user is a superuser ok Checking for prepared transactions ok Checking for reg* system oid user data types ok Checking for contrib/isn with bigint-passing mismatch ok Checking for large objects ok Checking for prepared transactions ok Checking for presence of required libraries ok *Clusters are compatible*

8. 執行 pg_upgrade ，終於可以開始升級了~~

$ cd /opt/PostgresPlus/9.1AS/bin $ pg_upgrade -d "/opt/PostgresPlus/8.4AS/data" \ # old cluster data dir -D "/opt/PostgresPlus/9.1AS/data" \ # new cluster data dir -b "/opt/PostgresPlus/8.4AS/bin" \ # old cluster bin dir -B "/opt/PostgresPlus/9.1AS/bin" \ # new cluster bin dir -p 5444 \ # old edb port -P 5445 # new edb port

如果執行結果成功無誤，則訊息會如下所示。

Performing Consistency Checks ----------------------------- Checking current, bin, and data directories ok Checking cluster versions ok Checking database user is a superuser ok Checking for prepared transactions ok Checking for reg* system oid user data types ok Checking for contrib/isn with bigint-passing mismatch ok Checking for large objects ok Creating catalog dump ok Checking for prepared transactions ok Checking for presence of required libraries ok | If pg_upgrade fails after this point, you must | re-initdb the new cluster before continuing. | You will also need to remove the ".old" suffix | from /opt/PostgresPlus/8.4AS/data/global/pg_control.old. Performing Upgrade ------------------ Adding ".old" suffix to old global/pg_control ok Analyzing all rows in the new cluster ok Freezing all rows on the new cluster ok Deleting new commit clogs ok Copying old commit clogs to new server ok Setting next transaction id for new cluster ok Resetting WAL archives ok Setting frozenxid counters in new cluster ok Creating databases in the new cluster ok Adding support functions to new cluster ok Restoring database schema to new cluster ok Removing support functions from new cluster ok Restoring user relation files ok Setting next oid for new cluster ok Creating script to delete old cluster ok Checking for large objects ok Upgrade complete ---------------- | Optimizer statistics are not transferred by pg_upgrade | so consider running: | vacuumdb --all --analyze-only | on the newly-upgraded cluster. | Running this script will delete the old cluster's data files: | /opt/PostgresPlus/9.1AS/bin/delete_old_cluster.sh

9. 啟動 PPAS 9.1 。

# service ppas-9.1 start

10. 執行 VACUUMDB。

$ vacuumdb --all --analyze-only -U enterprisedb -p 5445

11. 完工，接下來就利用 PEM 來檢視 upgrade 的資料及程式是否有問題。

結論： 步驟雖只有 10 做動作，因為文件沒有看清楚，卻花費我一些時間來try，咦~~~~讀文件要細仔唸哦~~艾注意哦~ ^_^...