postgresql資料庫如何儲存結構的教程

如果你升級資料庫後無法啟動 postgres 進程，並且檢查日誌發現類似如下資訊：

FATAL: database files are incompatible with server DETAIL: The data directory was initialized by PostgreSQL version 9.2, which is not compatible with this version 9.3.2.

那就說明升級後的 PostgreSQL 改變了資料存放區結構，導致舊版本的資料庫完全不認了。

為什麼升級會不支援舊的資料庫檔案

PostgreSQL 的版本號碼為三位，格式為 X.Y.Z。比如寫這篇部落格時最新版為 9.3.2 。看起來很像 semantic version 吧？以為第二位是 minor version 升級也沒有多大影響吧？如果你這樣想，那悲劇就開始了……

關於版本 這篇官方文檔 有很詳細的介紹。總而言之，對 PostgreSQL 而言，X.Y 都是 major version ，Z 才是 minor version 。minor version 不會改變資料庫儲存結構，而 major version 有可能會改。這就是為什麼我從 9.2 升級到 9.3 也會出問題的原因。

解決方案

以下解決過程基於我的開發環境，所以如果你的環境有所不同（比如 Ubuntu），記住更改一些參數或目錄。但大體思路還是一致的。

本人環境：作業系統 Mac OS X 10.9.1 (Maverick), 使用 Homebrew 作為包管理器安裝 PostgreSQL 。舊版為 9.2.4 ，新版為 9.3.2 。

如果你的資料庫裡面都只是測試資料，並不在乎資料還原的問題，那大可直接刪除掉舊的資料庫檔案，然後使用 initdb 命令建立一份新的。Homebrew 預設安裝的 PostgreSQL 目錄是 /usr/local/var/postgres 。

rm -rf /usr/local/var/postgres
initdb -D /usr/local/var/postgres

或者乾脆用 Homebrew 卸載重裝，也花不了多少時間。

如果你事先使用 pg_dump 備份過資料庫，那可以執行完以上步驟後使用 pg_restore 重新匯入資料。事實上這是 PostgreSQL 官方推薦的做法，升級前先備份，升級後還原回來。

如果你沒有任何Database Backup，而且已有的資料庫需要在新版 PostgreSQL 裡使用，或者像我這樣只是想折騰，那可以試試使用 pg_upgrade 命令升級資料庫儲存結構。這個命令需要四個參數：

    舊版的資料庫目錄
    新版的資料庫目錄
    舊版的 bin 目錄
    新版的 bin 目錄

對於 bin 目錄，Homebrew 把每個版本都保留了下來，按版本號碼放在 /usr/local/Cellar/postgresql 目錄下，比如我的：

# 舊版

/usr/local/Cellar/postgresql/9.2.4/bin
# 新版

/usr/local/Cellar/postgresql/9.3.2/bin

但資料庫目錄則不同，第一次安裝 PostgreSQL 時，它建立資料庫目錄並放在 /usr/local/var/postgres 。以後的升級也會繼續使用這個目錄。所以說這個目錄就是我們需要的舊版資料庫目錄。

那新版資料庫目錄呢？我們得自己去建立一個新目錄，使用上文提到的 initdb 命令，並跟舊目錄使用同一個名字。所以我們要先把舊版目錄換個名字，再建立新目錄。

mv /usr/local/var/postgres /usr/local/var/postgres_old
initdb -D /usr/local/var/postgres

當新目錄建立好以後，準備工作就做完了。這時就可以使用 pg_upgrade 了。

# 加參數 v 只是顯示詳細資料，可以看到這個命令運行了哪些 SQL

pg_upgrade -b /usr/local/Cellar/postgresql/9.2.4/bin -B /usr/local/Cellar/postgresql/9.3.2/bin -d /usr/local/var/postgres_old -D /usr/local/var/postgres -v

這個過程稍微花點時間，取決你的資料庫大小。命令執行完了就完成資料結構升級了。

剩下來還有點掃尾工作。一是把舊版資料庫目錄幹掉。運行 pg_upgrade 命令時，它已經為我們在目前的目錄產生了一個檔案叫 delete_old_cluster.sh 。它的作用就是刪除掉 /usr/local/var/postgres_old 。你可以運行這個指令碼，也可以自己手動去刪除。最後別忘了把這個指令碼也刪除掉。

./delete_old_cluster.sh
rm ./delete_old_cluster.sh

另外還有一個用於最佳化的指令碼 analyze_new_cluster.sh 。可以自行考慮要不要用。

最後，重啟 PostgreSQL 並把它加入到開機啟動中就行。Mac OS 系統是用的 launchctl 。

launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

完工！使用 psql 測試，能串連並且查資料，就大功告成了。

如果使用 Ruby on Rails 的同學，記得同時升級 pg gem，老版本很可能導致無法建立資料庫連接。

小結

升級資料庫是一件謹慎的事情。最好事先查查資料，瞭解下新版兼不相容，升級前作好資料備份。對 PostgreSQL 這樣廣泛使用的資料庫而言，勤於升級是不會遇到問題的。即使是資料庫結構改變，按照官方的文檔也能很好的解決。升級資料庫結構最好是採用 pg_dump 加 pg_restore 的方式升級，畢竟是官方推薦並保證沒有問題的方式。而 pg_upgrade 對間隔較大的版本升級可能並不會太順利。

對於 Mac 和 Homebrew 使用者而言，使用 brew info postgresql 查看包資訊是很有必要的，它會包含一些安裝、升級問題的解答。也會給一些資料連結。