實作建立新的 Tablespace

上個星期發表的 PostgreSQL 的 Tablespace 只有文字說明。事實上，我曾想用圖來解說 Oracle 的 tablespace 的架構，順便也畫一張 PostgreSQL 的架構圖，但是人懶沒畫；不是啦，其實是我不太會用繪圖軟體，等我有空再慢慢把圖補起來。但是，如果實作一下在 PostgreSQL 建立一個新的 tablespace，然後 demo 一下是做得到的。所以我就在這幾天大致上把 64 bits 版本的 PC-BSD 7.0.2 的 VM 裝好後，試著模擬新增一顆硬碟，然後在新的硬碟裏建立一個 tablespace，讓大家參考一下，順便感覺一下在 PostgreSQL 下的 tablespace 是長得什麼樣子，以便將來考量以 tablespace 來提昇效能的參考。至於 PostgreSQL 的那兩個 default tablespace，到底被系統放在什麼地方？ 過幾天我再另文介紹，今天先饒了我，打字和抓圖打得好累！ X(
這篇文章採用的例子是使用 PC-BSD，也就是 FreeBSD 7.0。為了不讓主題 focus 在 FreeBSD 系統面上，所以我儘量跳過 FreeBSD 新增硬碟的步驟。有興趣的朋友可以參考 FreeBSD 手冊，或是王俊斌先生所著「FreeBSD 6 架設管理與應用」。
在文章開始前，我必須先列出要建立 tablespace 的六個要件：

1. 你必須是資料庫的 superuser。一般來說，PostgreSQL 的資料庫內第一個帳號，也就是 postgres 就是 superuser，你也可以建立一個新的，擁有 superuser 權限的帳號。
2. PostgreSQL 必須執行在支援 symbolic links 的作業系統。這也就是之前我說 Windows 版本的 PostgreSQL 恐怕不能使用 tablespace 的原因；但是由於有一個東西叫 Cygwin，所以這一條我還得找時間在 Windows 上試一下。
3. 要建立 tablespace 的目錄必須先存在，也就是說，PostgreSQL 本身不會幫你建立這個目錄，你必須在建立 tablespace 之前先把這個目錄建好。
4. 上述這個目錄必須是空的。這也就是我等一下不直接用 mount 點建立，而另外再建立一個子目錄的原因；因為在掛載點，作業系統常常會多擺一兩個小東西，我可不敢隨便砍它們，免得系統出現出乎我預料外的慘事。
5. 這個目錄的名稱長度必須小於 991 個字元。
6. 這個目錄的擁有者，要是執行 postmaster 或 pg_ctl 程序的那個系統帳號。比如，在 FreeBSD 就是 pgsql，而 Ubuntu/RHEL/Windows 就是 postgres。這一點很容易忘掉，等一下我會特別展示，一旦忘掉，系統會怎麼回應你的。

在開始前，我打算先加上一顆硬碟，讓模擬更真實。由於我是用 VMWare 測試，所以可以很輕鬆的新增一顆「假的」硬碟。事實上真實的系統上要新增硬碟也很簡單，只需要 $$ 多一點就夠了！ 在 VMWare 上新增硬碟，必須用到 Workstation 版本，方法如下：請到 VM 選單，找到最下面一項「Settings...」按下去，這時會出現一個「Virtual Machine Settings」對話方塊，如下圖。我這張圖是已經建立好一顆 2GB 的 SCSI 虛擬硬碟的狀況。

圖一

要新增硬碟，請先按下左半邊下方的「Add...」按鍵，這時候會出現如下圖一樣的「Add Hardware Wizard」，選 Hard Disk，按 Next>。

圖一之二

這時就請遵照 Wizard 指示，一步一步做，就可以很輕鬆的新增一個「SCSI 虛擬硬碟」。在這裏我就不多做說明了。

圖一之三

加好了以後，再來就是使用 FreeBSD 的工具程式把這顆「新硬碟」Fdisk、Label，也就是建立 partition。能夠的話，修改一下 /etc/fstab，把新硬碟加上，讓系統重新開機時就會自動 mount 上。像我的例子，新硬碟叫 /dev/da1，我建立了一個 label 是 /dev/da1s1d。在 FreeBSD，SCSI 硬碟叫 da?，而 ATA/SATA 硬碟叫 ad?。我把它 mount 在 /pgdata 這個掛載點上。記得我文章一開頭說的建立 tablespace 要注意的六點的第四點嗎？這裏看來在我建立新 partition 並且把它掛在 /pgdata 時，系統在這裏塞了一個叫 .snap 的檔案。我不知道它的作用是什麼？但是為了預防萬一，我寧願不去惹它，把它留下來，所以接著我決定在它下面建立一個目錄 /pgdata/cheeren 來放 tablespace；但是，這時請注意，目前這個目錄和 /pgdata 的擁有者是 root:wheel。還記得剛才提的那六點嗎？看起來最後的第六點違背了，這在後續的動作中會產生問題。我這裏是故意這樣做，因為我發現很多人都會不小心忘記第六點，而在這裏撞牆撞半天。😛

圖二

接著建立一個 tablespace，把它放在 /pgdata/cheeren：

圖三

圖四

接著......，哇啦哇啦！哈！記得嗎？/pgdata/cheeren 的擁有者是 root，所以 pgsql 當然沒有寫入的權限：

圖五

這時候該怎麼辦？切回到 SHELL，用 su 把自己的權限切成 root，然後把 /pgdata 和它下面的所有檔案和目錄的擁有者全部改一下：

圖六

這時再切回到剛才圖四的「新表空間」對話方塊，再按一次「確定」，這次應該可以順利建立了。再來就可以把一些物件存放在這個 tablespace 裏了。這裏我建立一個 database，就放在這個新的 tablespace 'cheeren' 裏。注意看圖八，新的 database 'cheeren' 我是把它建立在 'cheeren' 表空間，而不是 pg_default：

圖七

圖八

來，我們先來看看圖九，建立成功。

圖九

再來再回到主題來。
記得我在 PostgreSQL 的 Tablespace 這篇文章中有提到說，tablespace 是以 symbolic link 連結到新的硬碟上的目錄。喔，但是看來看去，我都只有在新的硬碟上動手腳，並沒有看到原來的 $PGDATA 下有什麼更動呀？別急，我們先看看我們剛才建立的東西的 OID，回想一下，我曾經在 PostgreSQL 如何組織資料 這篇文章中提到，PostgreSQL 會以 OID 為檔名，將物件以一個一個實體檔案儲存在檔案系統中。至於資料庫，則是以和 OID 同名的目錄名稱存在。而在資料庫中的所有物件，則存在該目錄下。那我們來看看我們自己建立的新 tablespace，PostgreSQL 是不是如我們所預期的儲存呢？先來看看下一張圖：

圖十

參考一下圖十，我先切換成 pgsql，然後用客戶端程式 psql 連進去 cheeren database。然後我想查看一下 PostgreSQL 的系統表格 pg_tablespace。這個系統表格裏，正是存著整個 PostgreSQL cluster 的所有 tablespace 的清單。在圖十我先看看這個表格的定義。接著我打算 tablespace 清單列出 OID (實際檔案名，在這裏是在 $PGDATA 下的 symbolic link 的檔名)、tablespace 名和 tablespace 座落位置，正如圖十一所示：

圖十一

在圖十一裏，我多做了一件無聊的事，在 tablespace 'cheeren' 裏建立一個表格名為 foo 的資料表。事實上我用不著加上後面的 tablespace 'cheeren' 特別註明，因為我目前就在 database 'cheeren' 裏，而 database 'cheeren' 就存在於 tablespace 'cheeren'；因此，這是多此一舉。無所謂，這樣定義得更清楚。接著我從系統資料表 pg_class 裏查詢 table 'foo' 的 OID，再從系統資料表 pg_database 裏查詢 database ' cheeren' 的 OID。請先來看看圖十二，先來看看在 $PGDATA 下是不是有一個 symbolic link 連到 /pgdata/cheeren？

圖十二

在 FreeBSD 上的 PostgreSQL 的 $PGDATA 是在 /usr/local/pgsql/data。我在 PostgreSQL 如何組織資料 這篇文章中曾說過，PostgreSQL 會把所有資料放在 $PGDATA 下。tablespace 這東西看起來這裏好像違反了我之前說的話，其實沒有。在圖十二中，我們可以看到，在 $PGDATA 下有一個子目錄 pg_tblspc，它裏面存放的，就是兩個預設的 tablespace 以外的 tablespace，而在這個子目錄裏，放的都只有 symbolic link。請看一下圖十二，目前 pg_tblspc 裏只有一個子目錄 16403，而 16403 就是 tablespace 'cheeren'，所以我們看到 16403 其實是一個 symbolic link，連結到 /pgdata/cheeren 這個實際目錄。剛才在圖十一，可以看到 tablespace 'cheeren' 的 OID 正是 16403！

圖十三

我們再來看看圖十三，/pgdata/cheeren 裏，除了標示建立該 tablspace 的 PostgreSQL 版本的 PG_VERSION 以外，只有一個目錄 16404。從圖十一的最後一段，我們可以知道 database 'cheeren' 的 OID 正是 16404，也就是說，這個子目錄正是 database 'cheeren' 實際在檔案系統中的位置。再往下看，由於目錄 16404 之下有很多屬於 database 'cheeren' 的系統物件，所以我只列出 16xxx 的檔案，注意一下，是不是有一個 16411？從圖十一我們可以知道這個空空的檔案正是 table 'foo'！
好了，介紹到這裏，我快打不下去了。希望這樣一個 demo 可以讓您了解 PostgreSQL 的 tablespace 實際運作的情況，將來在考慮用 tablespace 來提昇效能時可以比照建立。還有，等我打完這篇文章我才想起，事實上同一個 database 裏的資料表格或 index 可以存在不同的 tablespace 裏，方法就像我在圖十一那樣，在 create table 或 create index 時附加一個 tablespace 子句特別註明。這樣子就能把資料表格和索引放在不同的硬碟，來避開兩者的存取競爭，提昇效能。

---

2010-3-18 補充：就在我伺服器硬碟掛掉的前兩天，William Tsai 網友留了以下的兩篇留言。因為我看都還沒看到這兩篇留言，硬碟就掛了，所以我也還沒時間測試。但是我特在此將他這份很有價值的留言留下(一字不改)，請各位參考一下。待我驗證之後(無論結果如何)，尤其是有關 NTFS 的 Junction 的功能後，再來寫一篇文章報告。

週五, 02/12/2010 - 10:24 — william Tsai (未確認)

真是用心的好文啊

其實我也是用大象用很多年了
只是大部份的IT都仍選用Mysql為主
剛剛讀了您的PostgreSQL在 Linux/Unix下的 tablespace實作
源于我正好在測試windows 版的Postgresql 8.4版本 tableSapce管理
(其實我習慣用Linux.但這次被迫測試Windows)
因為我正在把Mysql 143Gb的data 移轉到Postgresql 上來測試大象的實力
但卻發現一個很不可思議的問題
postgresql裝在C:
所以我建了 一個新的在E: disk (size比較大) .要先建好table space的目錄 (e:\pg_big)CREATE TABLESPACE pg_big
OWNER XXXXX
LOCATION 'e:/pg_big';
這樣是ok的.也會build table 使用該tablespace 該有table 也會建立在另一顆disk .
本來還很開心
卻發現一個很恐怖的問題
您有提到Postgresql 的資枓都base on $PG_DATA
因為window沒有symbolic links
沒錯~~~~
但它的確可以work 在另一顆disk E:上
更可怕的是它居然在 c: 的$PG_DATA 中 build 出 一模一樣的資料
而且 做 DML 處理時 兩塊的資料均會同步異動
形成一種另類的 mirror disk
PostgreSQL的tablespace 在windows底下果然是個很令人失望的不滿意的地方
而且沒法子做到像oracle 那種tablespace
(一整個data file tablespace 有利于減少 inode 或是 windows 資料碎塊的問題 , 我做完 大量insert 後 disk io 因碎塊問題 performace大減)
連Mysql 都可以有連似的結構了 postgresql 居然沒有
與您分享

週五, 02/12/2010 - 11:25 — william Tsai (未確認)

上篇補註

後來發現那個不是Mirror
如果你是用NTFS來玩
現在NTFS有新玩具我發現在做e : tablespace
postgresql 會在 $PG_DATA 中的tablespace 目錄建立一樣的目錄並且...
做了 類似 symbolic links 的功能
在NTFS中
有了新的結構 junction
http://en.wikipedia.org/wiki/NTFS_junction_point
所以在$PG_DATA 內建立的是對應其它disk 的 junction link
用來實現與 linux/unix 一樣的 symbolic links
與您分享囉.....

PostgreSQL 的 Tablespace

這一篇文章原本是三個月前就開始寫，原先預定是剛好九月底時出刊，但是在發展的過程，突然覺得似乎應該先介紹一下 PostgreSQL 如何來放置、維護資料，再來聊聊 Tablespace 可能比較適當，接著生了一場重病再加上發神經買了一台新電腦，所以就放到現在才發表。
PostgreSQL 在進入 8.0 版時，整套系統的架構大變，新增了不少功能和架構， Tablespace 就是其中一項。當我第一次在 pgAdmin III 上看到 Tablespace 這個字眼出現時，我是非常興奮的，因為要在較大型的資料庫中，才會有這種概念。尤其是之前有 Oracle 的實作經驗，所以看到熟悉的字眼出現時，備感親切，而內心對它充滿期待。但是在進一步深入研究後，就有如被潑了一桶冷水一樣，PostgreSQL 的 Tablespace 和 Oracle 的 Tablespace 功能上差距還不小，實作上更是不一樣。
首先來說明一下，什麼是 Tablespace？Tablespace 一般都翻譯成表格空間。而表格空間，按照手冊上的定義，是整個系統的資料主體。其實呢，用白話一點的說法，Tablespace 是實體資料儲存的基本單位。以官方的說法來說，Tablespace，也就是表格空間，當初設計的目的是為了 DBMS 整體調校之用。這個目的和 Oracle 的 Tablespace 的設計目的很像，但是實作上卻是有不少差距。
在作業系統層面，Tablespace 是以目錄的形式存在檔案系統中。而所有存放在該 Tablespace 的物件，則是以子目錄或檔案的形式儲存在硬碟中。還記得前一陣子我所發表的 PostgreSQL 如何組織資料 的內容吧！PostgreSQL 把它所有的物件都以目錄和檔案的形式，用物件的 OID 為識別，分別儲存起來。Tablespace 身為 Databses、Tables 等等物件的 container，當然一定也是以類似的方式儲存在檔案系統當中。不同的是，除了預設的兩個 Tablespaces 以外，其餘自訂的 Tablespace 在檔案系統中是以 symbolic links 的形式存在的。就是因為它在檔案系統中，是以 symbolic links 的形式存在，因此，Windows 版本的 PostgreSQL，由於作業系統的功能限制，「理論上」使用者無法建立自己的 tablespace。我不清楚 PostgreSQL Win32 版是否設計了一個新的方式，能夠讓 Windows 2003/2008 打破這個理論上的藩籬。但是我確定在 Windows XP/Vista 上，很不幸的，可說是「幾乎」沒有可能性，讓使用者使用這部份的功能(或許加裝 Cygwin 能夠解決，但是這一點我還要測試一下)。
至於為什麼 Tablespace 要以 Symbolic Links 形式存在於檔案系統中，要知道原因，我想先從介紹 Oracle 的架構說起。Oracle 的資料物件，並不像 PostgreSQL 一樣，是以各別的檔案儲存起來的。Oracle 的 DBMS 底層是一堆黑盒子。Oracle 的底層是由一堆 Datafiles 和 Control files 組合而成。資料物件全部統一儲存在 Datafiles 中，而儲存的方式和細部，由 Control files 的定義來設定。而 Datafile 的內部儲存方式，則是一個黑盒子。也由於還要搭配 Control files 才能存取資料，所以你無法用簡單而有效率的方式來備份、維護資料庫，而要去準備一堆認證課程，用一堆工具；不管是 Oracle 的或第三方的；才能好好維護 Oracle 中的資料。而 Tablespace 在 Oracle 當中，雖然也是 DBMS 的最基本的 container，但是它卻是由一堆 Datafiles 和 Control files 組合而成，自成一個戰鬥單位。所以當你要做系統調校時，你可以讓該 Tablespace 所屬 Datafiles 和 Control files 分別儲存在不同的硬碟中，或是其它的安排。在這些實體儲存單位的妥善安排，來提高系統的效能。再來是當原本的儲存空間不足的時候，你也可以再找一顆新的硬碟來掛上，再在這顆新硬碟中新增新的 Datafiles，這樣就可以輕鬆的解決儲存空間不足的問題。當然，Oracle 如何把資料分散在這些 Datafiles 中，手冊故意寫得很神祕，感覺上 DBMS 會很神奇的幫你找出最有效率的儲存方式來安排在這些 Datfiles 當中，但是，who knows？sales talk 吧！
至於 PostgreSQL，我在前一篇 PostgreSQL 如何組織資料 已經展示了，PostgreSQL 是以各別的目錄和檔案方式儲存在實體儲存媒介上。這個方法的優點是開放、透明，缺點就是吃掉不少 inode(以 Unix 來說)。由於每個物件都是以各別檔案存在，所以就以邏輯上而言，身為它們的 container，Tablespace 必然要以目錄的形式存在。但是，要如何做到像 Oracle 一樣，如何使用 Tablespace 把資料物件分散到另一顆硬碟或以 NFS 掛上另一台機器的檔案系統？這時就出了邏輯上的問題了。在檔寀系統中，同一台機器中，在目錄以上已經沒有更大的 container 單位了。那麼如果要在 PostgreSQL 上實作出類似的功能，恐怕對 Tablespace 的定義就要有所改變。因此，在 PostgreSQL 上，Tablespace 不但是邏輯的存取容器，也是基本的實體儲存容器；不像 Oracle，Tablespace 只是邏輯上的容器，實體則是那些 Datafiles 和 Control files。而要做到跨硬碟或 NFS mount，那麼就用到了 Unix 的 Symbolic links，因為用 Symbolic links，可以在 $PGDATA 下留下一條連結，連結到另一顆硬碟或另一台機器分享出來的空間。這樣做又簡單、又有效率。
但是問題就出在跨到 Windows 下時，由於 Windows 沒有 Symbolic links 的概念，只有一個很難用的「捷徑」，這下子問題就來了，因為「捷徑」並不等於 symbolic links，兩者的使用和定義完全不同。把 Tablespace 轉移到 Windows 的實作上，看樣子還有不少努力的空間。
接著，我們來聊聊那兩個預設的 Tablespace：pg_global 和 pg_default。官方文件中對於這兩個 Tablespace 的用途是這樣說的：「PostgreSQL 使用 pg_global 來存放和系統有關的物件；使用 pg_default 表格空間來存放使用者建立的資料庫物件」。也就是說，在 Oracle 當中，每個 Tablespace 的 Control files 當中的內容，在 PostgreSQL 是由一個專屬的 Tablespace pg_global 來統一管理；而預設是你所建立的所有物件會存放在 pg_default 這個表格空間；當然如果你指定存放在你所建立的自訂表格空間，那麼物件就會存到你指定的表格空間裏；若是沒指定，PostgreSQL 就當作你打算使用預設位置，所以就會存在 pg_default。
在這篇文章的最後，我們來看看在官方的文件定義上，有關 Tablespace 的文字。在中文化文件中，有下列幾行文字：

表格空間決定著DBMS整體效能的重點調校之一

，還有：

在以下的情況 DBA 就有必要考量 Tablespace 的分割與配置：
資料間具有競爭系統效能.
大型物件資料表與小型物件資料表應該分別在不同的 Tablespace.
分離資料和索引的存放空間是不錯的效能強化

以上是中國 PostgreSQL 社群翻的「葡萄牙文式」的中文化文件的內容。只要是台灣人，大概都懂得我在說些什麼。其實這段文字主要是在說明為什麼要有表格空間。PostgreSQL 社群加入表格空間這個物件的目的，和 Oracle 是一樣的，主要目的在於效能的調校。所以當發生單一硬碟中的 transaction logs 和資料本身存取的存取競爭時(這東西要說明還要再寫一篇文章！)，你就必須考慮把資料物件存到另一顆硬碟上，因為存取競爭勢必降低效能。另外，大型物件和小型物件並存在一個表格空間時，由於硬碟存取資料是「隨機」的，所以小型物件勢必淹沒在大型物件中，這樣小型物件的存取效能也一定會降低。再來就是資料本身和索引也是會產生類似存取競爭的情況，所以一個存取量大的資料表格，或許要考慮把它的索引放到另一個硬碟中。而要把物件存到另一個硬碟中的方法，就是採用 tablespace。
至於如何建立 pg_global 和 pg_default 之外的 tablespace，以及這兩個預設的 tablespace 又存在哪裏呢？就留在下一篇文章中介紹，本文就先在此打住。

PostgreSQL 如何組織資料

原本上個月，打算寫另一篇文章，但是寫到一半，臨時改變計劃，決定先把這篇文章推出來。
在教授 PostgreSQL 時，我發覺，要讓稍微有點基礎的其它資料庫使用者，最容易上手 PostgreSQL 的方法，就是從說明 PostgreSQL 如何在檔案系統中放置你的資料著手。PostgreSQL 存放資料的邏輯相當簡單，所以進階使用者在了解這個邏輯後，很容易就能了解如何備份、擴充功能等等進階功能，不像 Oracle 等商用資料庫，把資料包在一層厚厚的黑盒子裏，不讓你接觸和了解它的行為；但是很不幸的，它也相當程度的依賴檔案系統。這會出現什麼問題呢？等我介紹完 tablespace 後，我想不用我多做解釋你也自然立刻能夠了解。
在開始介紹前，我要介紹一個環境變數。這個環境變數是 $PGDATA，將來我會找個時間把 PostgreSQL 會用到的環境變數做個整理，再另外發表一篇文章來介紹。垷在先稍微介紹一下 $PGDATA。$PGDATA 的值指向某個目錄，而這個目錄其實就是 PostgreSQL 存放資料的目錄。在 FreeBSD 中，$PGDATA 通常是設成 /usr/local/pgsql/data，而在 RHEL/CentOS 則是指向 /var/lib/pgsql/data。但是，在剛安裝好 PostgreSQL Server 時，安裝程式並不會很雞婆的自動幫你把這個環境變數設好；雖然在安裝程式內部的確有一個內部變數 pgdata 指向上述目錄，所以當你在做 initdb 時，如果那時你的 $PGDATA 這個環境變數還沒設定，PostgreSQL 仍然會把資料放在上述目錄中。安裝程式不那麼雞婆的原因在於，PostgreSQL 的開發社群預留給你改變的機會。所以你在安裝好了之後，最好手動把 $PGDATA 設好。如果你是和我一樣，shell 習慣用 BASH，那麼請直接在 /etc/profile 最後面加上一行：

export PGDATA=/usr/local/pgsql/data    <- 如果你是 FreeBSD 的使用者的話
或者是
export PGDATA=/var/lib/pgsql/data        <- 如果是 RHEL/CentOS 的使用者

至於其它 OS 使用者我先略過。至於 $PGDATA 和 initdb，我會找時間另文介紹。 好，讓我們來看看圖一。

圖一
PostgreSQL 有很多系統資料表和系統視觀表(就是 Views，好怪的翻譯詞)，就像 ISO 標準的 system catalog，可以讓管理者從文字模式下，沒有使用任何圖形界面工具，仍然可以做管理。pg_database 就是其中之一。pg_database 內儲存了系統中現有資料庫的清單。
psql 是 PostgreSQL 最好用的前端工具，它很像是 Oracle 的 SQL*Plus，當然沒有 SQL*Plus 那麼複雜，功能那麼多。\d 就像 SQL*Plus 的 DESC (describ) 一樣，可以顯示資料表的架構，所以 \d pg_database 就像在 Oracle 的 SQL*Plus 中下達 DESC pg_databse 一樣，可以秀出 pg_database 這個系統資料庫的欄位清單，順便秀出它專屬的索引(indexes)和觸發器(triggers)。我目前想要秀一下 datname 這個欄位個欄位，還有一個隱藏的欄位 oid。datname 是資料庫名稱，oid 則是資料庫的 OID (Object ID)。PostgreSQL 給予它所有的每一個物件一個獨享的 ID，就做 OID，用來識別各個物件，也就是說，每個物件都有它自己獨有的 OID。換句話說，我可以下達 SELECT datname, oid FROM pg_database; SQL 指令，秀出系統中所有現存 database 的名稱和 OID 的對照表：

圖二
從上表可以看到，目前系統只有三個 database，分別是 template0、template1 和 postgres。它們的 OID 分別是 11510、1 和 11511。任何人裝的 PostgreSQL 的編號第一號 OID 的物件一定是 template1。至於這三個資料庫是怎麼來的，它們分別是幹什麼用的，我會另文介紹。
對了，讓我從主題叉開一下，先來談談 PostgreSQL 的系統帳號及資料庫帳號和預設資料庫。
預設安裝的 PostgreSQL 不是由 root 啟動，PostgreSQL process 的 owner 也不是 root，這是為了安全性的緣故。所以你在安裝的時候，安裝程式會幫你建立一個 PostgreSQL 專屬的系統帳號。在 RHEL/CentOS 是 postgres，Win32 版也是 postgres (如果你有安裝 Windows 版本的 PostgreSQL，你可以試著叫出工作管理員看看，postgres.exe 和 pg_ctl.exe 的使用者名稱是什麼)，至於 FreeBSD 則是 pgsql；FreeBSD 這樣做會讓使用者很麻煩。
預設的 PostgreSQL 無法由遠端進入，只能從本機登入 psql，而且不能用 root 登入。安裝的預設值是：只要系統中有相同帳號即會「信任」同一使用者。所謂「信任」，代表你不需輸入密碼就可以由 psql 登入資料庫伺服器。還有，postgres 或 pgsql 的密碼你無從得知，因為它是在安裝時亂數隨機取得的。聽到這裏，你可能會問：那麼我要如何切換成 postgres 或 pgsql？不用擔心，在 Unix 系統中，root 的權限很大，你可以先用 su 切成超級使用者模式，這時只需要下達 su - pgsql，不輸入密碼立刻可以切到 pgsql。
如果你是使用 RHEL/CentOS，那麼，當你切成 postgres 帳號時，要登入 postgres，不用下任何參數，只要下達：

$ psql

就可以登入，而 postgres 正是 PostgreSQL 預設 DBA；剛好系統剛裝好預設資料庫也叫 postgres，所以不用另外指定登入資料庫，你就可以直接登入預設而且同名的資料庫 postgres。
但是如果你用的是 FreeBSD，那麼就有點麻煩。在 FreeBSD 下的 PostgreSQL 的預設 DBA 是 pgsql，但是預設資料庫卻是 postgres。所以除非你另外在 FreeBSD 的 shell 中，下達 creadb，手動建立一個屬於 pgsql 的同名資料庫，然後用 dropdb postgres，砍掉預裝的預設資料庫，讓 pgsql 變成新預設資料庫，否則就請你煩麻一點，在執行 psql 時指定登入資料庫：

$ psql -d postgres

這樣才能順利登入 PostgreSQL 伺服器。
好，讓我們回到主題。
我們現在知道這三個 database 的 OID，接著跳出 psql，跳出的指令是 \q。跳出後請在系統中跳到 $PGDATA 這個目錄：

$ cd $PGDATA

注意：你必須先定義過 $PGDATA 才行，否則，你就得：

$ cd /usr/local/pgsql/data    <- 如果你是 FreeBSD 的使用者的話
或者是
$ cd /var/lib/pgsql/data        <- 如果是 RHEL/CentOS 的使用者

跳到 $PGDATA 後，接著跳到 base 子目錄，如圖三所示：

圖三
從圖三中我們看到 $PGDATA 下有不少目錄，有空我再來寫一篇文章介紹當中比較重要的幾個，現在回到 base 目錄。當你下達 ls -l 指令後，我們看到在 base 子目錄下只有三個子目錄，而且目錄名稱都是由數字所構成，它們分別是：1、 11510 及 11511！好巧喔！XD
沒錯！就如你所猜想的，PostgreSQL 把所有的物件都以目錄和檔案名稱儲存在檔案系統中。就剛才看到的，1 這個子目錄中儲存的，正是 database template1 的所有物件：

圖四
而同理，11510 子目錄下存的正是 database template0 的物件，11511 則是 database postgres 的物件。
就是因為 PostgreSQL 是以這種方式安排資料維護的方式，所以當開發社群想把它變得「更企業使用化」時，就會遭遇一些問題，尤其是在跨作業系統時。第一個問題就出在 tablespace，它在 Windows 上看起來似乎是沒有辦法使用的。這就等我繼續討論 Tablespace 時再來聊了。

PostgreSQL簡史

正如 Wikipedia 的 PostgreSQL 條目所說的，PostgreSQL 已有相當的歷史。要追溯其起源，要先談到它的前身 ─ Ingres。1970 年代初期，IBM 公司展開 System R 專案。System R 專案到底有哪些內容？說實在的，我也搞不清楚，但是它所遺留下的最大資產就是 SQL 這個語言。另外，像著名的幾位關連式資料庫權威，比如 E.F.Codd、C.J.Date 、J.N.Gray 等等這幾位大師，都曾經在早期投入這個專案。如果對這個專案有興趣的朋友可以參考下面這篇文章：A History and Evaluation of System R。System R 這個專案，在 1973 年有一些成果出來了，所以研究團隊就出版了一串文章，描述這個系統是如何架構起來的。這時候，U.C.Berkely，也就是我們台灣通稱的柏克萊大學有兩位科學家 Michael Stonebraker 和 Eugene Wong，在研讀了這串文章之後，突然想，為何我們不也來弄一套屬於我們自己的關連式資料庫的專案？

其實，那時候他們兩位仁兄已經有一個專案在手裏了，那個專案的名稱就是 Ingres ─ 全名是 INteractive Graphics REtrieval System，就是由那幾個粗體字頭組成的名字。這個專案原來的目標是打算搞一套地理資料庫系統，那時候還沒有 GIS 這個名詞，野心也還沒那麼大。但是就在這一年，這兩位先生似乎到了一個瓶頸，所以想利用這個靈感，把專案目標弄得更大一點，方向則轉成發展一套就像 System R 一樣的系統，最名就叫做 Ingres。所以先挪用原專案的經費，而且再提 Proposal，向幾個基金會尋求研究經費。Stonebraker 一開始找上 DARPA。DARPA 就是支持 Internet 或 TCP/IP 最早期發展的美國國防部先進研究專案局，那時候全美國有關電腦計算方面的研發經費，最大的經費來源就是它，所以 Stonebraker 一開始也是先找上 DARPA。可惜的是，那時候 DARPA 已有支持其它相同屬性的專案，所以並沒有核准經費給 Stonebraker，而且還好心勸 Stonebraker 改變專案目標，可是 Stonebraker 不死心，轉向其它的經費來源尋求支持，最後有四個機構同意出錢支持，分別是著名的 NFS (The National Science Foundation，美國國家科學基金會，是 DARPA 結束後支持 Internet 和 TCP/IP 繼續發展的單位) 和另外三個美國軍方單位：the Air Force Office of Scientific Research, the Army Research Office, and the Navy Electronic Systems Command，很巧的是陸海空三軍各有一個單位。拿到經費後，大約在 1974 年，就釋出了第一個 prototype 版本的 Ingres，這個時間點也被大家認為是 Ingres 和 PostgreSQL 的最早起點。

整個 1970 年代，整個系統一直都在緊貼著 IBM 的 System R 的發展，一直修改、重寫整個 prototype，研究團隊來來去去，一面累積相關的技術和經驗，一方面也把整套系統漸漸轉往當初 DEC 的迷你電腦和一套新的且漸漸受歡迎的作業系統 ─ Unix 上，提供了一套「便宜甚至算是幾乎免費」的 System R 取代方案。而且早期 Ingres 就好像 Unix 早期一樣，是以極低的烤貝費用提供原始碼給有興趣的人或研究單位使用，所以很多相關領域的學生，就從這些原始碼中得到靈感，紛紛出來開設相關的公司，賣的就是 Ingres 的複製品，這些公司有的後來很有名，比如 Sybase。目前的 Microsoft SQL Server 其實就是由 Sybase 重新改寫而成的。這些都算是 Ingres 的徒子徒孫。

就像一些美國院校研究開發出來可用的專案，最後一定會轉成商品化，後來 Michael Stonebraker 和 Eugene Wong 兩位再加上一位柏克萊教授 ─ Lawrence A. Rowe，三位成立了一家名為 RTI (Relational Technology, Inc.) 的公司，除了專門以便宜的價格販售 Ingres 原始碼，也提供商業性服務，最後甚至改寫 Ingres，出售 Ingres 後續的版本(商業化版本)。最後這家公司甚至在 1980 年代終期，改名為 Ingres，但是在 1990 年十一月被 ASK Corporation 所併購。最後 ASK/Ingres 在 1994 年被 CA (Computer Associates) 所併，成為 CA 的一個資料庫主力產品，一直到現在。2004 年，CA 把 Ingres r3 釋出為 open source 軟體，但是是以社群版的形式，商業版本還是繼續發行，有興趣的朋友可以去下載來參考看看。

其實 Michael Stonebraker 在 1982 年成立 Ingres Corporation 後沒多久，在 1985 年就離開了這家公司，回到柏克萊。他回到柏克萊後，就想要立刻新啟一個新的專案，而且要和 Ingres 的原始碼完全切割、重寫。他成立了 Postgres 計劃，告訴大家它是 Ingres 的後續者。1986 年時，該專案小組先寫了幾篇 paper，主要是描述這個新的 RDB 的基礎想法和概念，而以這個概念在 1988 年寫出一個可以跑的 prototype，在 1989 年六月釋出很少人用過的第一版，隨後很快的，在 1990 年六月把整個 rules system 重寫後釋出第二版。1991 年又重寫了一次 rules system，釋出第三版，這個版本還增加了多重的儲存管理系統，有點初步的 RDBMS 的架式了，也改進了查詢引擎的速度。這時候開始，Postgres 的使用者開始多起來了，大家提出的需求也越來越多，專案人員開始受不了了。終於 1993 年，在釋出第四版後，Postgres 計劃宣布結束！

雖然官方版本的專案結束了，但是由於 Postgres 的 License 是 BSD License，所以每個人都可以拿了原始碼後，再把它拿來做成自己的專案或商品。所以只要有人願意繼續以社群的方式維護這個專案，它就可以以另一個形式存在。所以在 1994 年，柏克萊的兩位博士候選人，Andrew Yu and Jolly Chen，把原本使用專屬於 Ingres 的查詢語言 QUEL 改寫成以 SQL 查詢語言後，成立 Postgres95 專案，而且在網站上釋出。次年，在幾位有識之士的努力下，在 1996 年八月一日，正式將 Postgres 以非大學版本的 open source 版本釋出。而且為了反映 Postgres95 是以 SQL 為其查詢語言，同時也正式將 Postgres95 正名為 PostgreSQL。發音的方式請參考這個 MP3 檔，不要再念錯了。

1997 年元月，PostgreSQL 的正式的第一個版本 6.0 版釋出。從這個時候開始，PostgreSQL 是一個全球性的 open source 社群，維護及開發原始的的任務是利用 Internet，由分散在全球各地社群會員維護。

另一方面，Postgres 商業版本由 Paula Hawthorn 和 Michael Stonebraker 成立的 Illustra Information Technologies 支援和繼續開發，而且改名為 Illustra。Illustra 後來被 Informix 所併購，而 Michael Stonebraker 也曾經成為 Informix Corporation 的 CTO，而 Informix 在併購 Illustra 之後，把它整合到 Informix Universal Server 中，一直到 2001 年資料庫產業所發生的最大新聞，IBM 以 10 億美金併購 Informix 為止。

自從 PostgreSQL 變成 open source 專案後，由於來自全球各角落的專家投入，所以加入了不少較先進的技術，比如 MVCC，這個技術是有關同時性 (Concurrency) 問題解決技術，將來我會另外抽空專文介紹。

時序進入第二個千禧年，在 2000 年五月，PostgreSQL 釋出 7.0 版，版號正式進入 7。7.x 版的改進不少，最大的改變是增加 schema 的概念，預存查詢，outer joins，加入保全功能，等等。

2005 年元月，PostgreSQL 釋出 8.0 版，版號進入 8。8.x 版本有幾個很重要的演進，比如像 Oracle 一樣的 tablespaces，Java Store Procedure ─ PL/Java，某個時點的資料庫復原，巢狀交易，savepoint 等等。最重要的，大概是原生版本的 Windows 版 PostgreSQL。MySQL 之所以可以在台灣的開放源碼資料庫市場占這麼大的市占率，就是因為最早期學習 PHP 的人，十個裏面有九個只熟悉 Windows 環境，所以在 IIS/PHP 的環境中，當時只有 MySQL 是 Windows 下的開放源碼 DBMS。由於台灣的 Internet 產業，充斥著只願看 1-2-3 這類書籍的開發者。所以在不能完全從微軟的系統斷奶，以及習慣速食文化，台灣書商就很聰明的，一窩蜂出了一堆 PHP/MySQL 的書，以致於 MySQL 充斥於 open source 市場。8.0 版之後，PostgreSQL 也終於進入這個市場，搭配新版本的 Windows 版本管理工具 pgadmin III 後，變成一套可以和商業版本 RDBMS 競爭的資料庫系統。

PostgreSQL 在 FreeBSD 上的大版本升級

唉！升級...升級...升級...這一陣子一直升級，這次輪到 PostgreSQL 大升級。PostgreSQL 不像其它軟體，即使是 8.2 升級到 8.3，也必須大改，所以無法直接用 upgrade 工具直接升。即使是 Windows 版本，也必須勞心費力一番。這次我用 FreeBSD 版本的升級做為例子，來說明最簡單也是最正確的大改版升級步驟。

首先，請先把原本資料庫的資料完全備份下來。如果你是 Windows 的使用者或是 Linux 的使用者，你可以考慮使用 pgAdmin III 的「工具」選單中的「備份」或「備份全域」來做，這樣簡單很多，但是由於 pgAdmin III 的說明太少，我實在看不懂「備份」對話方塊中選項的含意，所以乾脆不用。最正式的方法是使用在 console 下的工具：pg_dump 和 pg_dumpall。兩者的差別在於，前者可以針對某個特定的 database 做備份，而後者一般用來把整個伺服器資料做完整的備份。我建議直接使用 pg_dumpall 比較方便和快速。方法如下：

$ pg_dumpall > filename

而 filename 你可以自己取，比如 host_a.20080206.full.dump 之類的。名稱中加入日期可以讓你更清楚知道這個備份檔備份的日期。加入 full 字眼，可以提醒自己，這是完整備份，而非一般維護時所做的連續性部份備份的一份。如果你所管理的 PostgreSQL 有超過一台以上，那麼最好連資料庫主機名稱都註明。

對了，差點忘了一件極重要的事！在 Windows 上，只要你使用 administrators 群組中的任何一位使用者登入，然後從開始功能表→PostgreSQL 8.3→Command Prompt，就可以打開「命令提示字元」，而且可以下達 pg_dumpall 指令；但是，在 FreeBSD，一般安裝時，預設 root 是不允許執行 PostgreSQL 的相關指令的，所以請先切換成 pgsql 用戶，如果你沒更動 pgsql 的密碼，那麼預設的密碼很「隨機」，也就是任你怎麼猜也都猜不出來，但是你可以先用 su 切成 root，再用 su - pgsql 切換成 pgsql 使用者，不用輸入任何密碼即可，這時就可以執行上述指令。但是請注意，這時最好把這個備份檔移到安全位置，以免繼續進行的安裝過程中不傎誤砍。所以這時候你最好是先跳回 root，請下 exit 指令，然後用 cp 指令把剛才那個 dump 檔 copy 到任何你暫存的位置。

接著正式執行安裝。在安裝新版本之前，必須先移除舊版本，否則兩個 daemon 會搶同一個 TCP port，開機時會有無法預知的狀況出現。首先先看一下你的伺服器中，目前裝了哪些和 PostgreSQL 相關的 package：

# pkg_info | grep postgresql

這時候你至少可以看到兩個 package 出現在清單中，而我多了一個：

postgresql-client-8.2.6_1 PostgreSQL database (client)
postgresql-contrib-8.2.6 The contrib utilities from the PostgreSQL distribution
postgresql-server-8.2.6 The most advanced open-source database available anywhere

一般來說，如果你沒有安裝Server instrumentation 功能，那麼應該只有 client 和 server 兩個 package。但是這個小清單並不完整，如果你還有安裝 php 和 PostgreSQL 相關的驅動程式，那麼這時候並不會出現在這個清單當中。不用擔心，你若移除的順序不對，出現相依的套件，那麼把相依套件移除，再來移除目標套件即可。只是這時候你最好拿隻筆記下你一共移除了哪些套件，將來才能再一一重新安裝回去。我們先以這三個 package 來說好了，如果你沒移除舊版本的 server，那麼直接移除 client 會被提醒，而且停止進行安裝動作。同樣的，如果你沒先移除舊版本的 contrib 和 php 相關 package，你在移除 client 時也會因為相依套件尚未被移除，而被停止移除 client 的動作。所以若以上述三者為例，則移除的順序為：

# /usr/local/etc/rc.d/postgresql stop <-- 先停掉目前的 PostgreSQL server
# pkg_delete postgresql-contrib-8.2.6
# pkg_delete postgresql-server-8.2.6
# pkg_delete postgresql-client-8.2.6_1

這時請把 /usr/local/pgsql/data 整個目錄移走，像我只是把它改個名稱，而讓它繼續留著，說不定哪天還會用到目錄中的某些檔案：mv /usr/local/pgsql/data /usr/local/pgsql/data_8.2。然後就可以開始使用 port 來安裝 PostgreSQL 8.3 了。在安裝前，請先確定你的伺服器上的 port tree 是否已是最新的版本，如果你的 port tree 是在 2008/2/5 天亮前更新的，可能 postgresql-server-8.3 還不是最新版本，請先手動更新 port tree。如果已是最新版本，那麼切到該 port 目錄：

# cd /usr/ports/databases/postgresql83-server
# make install clean

FreeBSD 會自動幫你將 8.3 版的 server 和 client 都一併裝好。接著把 contrib package 也裝起來：

# cd ../postgresql-contrib
# make install clean

如果你曾經把 php5 的 driver 移除掉或 JDBC 等等等之前移除掉的套件，現在也請用同樣的方法把所有新的版本一一裝回去。這樣就好了嗎？還早，請先看一下你的 /usr/local/pgsql 下是否已經建好新的資料庫？

# ls /usr/local/pgsql

如果這個目錄下只出現一個剛才更名的 data_8.2，那麼代表 Makefile 並沒有自動幫你做 initdb，請執行：

# /usr/local/etc/rc.d/postgresql initdb ^[1]

這時你再下 ls /usr/local/pgsql 指令時，就可以看到多出一個 data 目錄了，這樣才能正確啟動 PostgreSQL：

# /usr/local/etc/rc.d/postgresql start ^[2]

如果沒出現任何錯誤訊息，那麼先恭喜你，安裝好了。但是工作才完成一半，再來是把原來備份的資料回存回資料庫中：

# su - pgsql
$ psql -f filename postgres

也就是先切成 pgsql 使用者，接著執行 PostgreSQL 的互動式指令輸入工具：psql，然後把剛才你的 dump 檔的名稱(這裏當然是完整路徑名稱)取代上面的 filename。接著一些訊息代表著資料庫回存的動作，正在做什麼動作。如果一切錯誤訊息都沒有出現過，那麼恭喜你，工作完成了 80%。對於 Windows 的使用者來說，只要記住最後一條指令，就可以把剛才 pg_dumpall 所備份的資料回存了。Windows 的使用者真幸福。

什麼？才完成 80%？是的，有兩個檔可能之前的版本你有更動過，分別是 postgresql.conf 和 pg_hba.conf，它們在 FreeBSD 中應該位於 /usr/local/pgsql/data 目錄下，請把之前曾經更動過的改回去，但是請把 release notes 先讀過一遍，因為 postgresql.conf 中很多管理項目都更動過了，可能之前你所更改的項目，8.3 版已經把該項和其它項目合併，並取了新的項目名稱了，先讀過 release notes 的 E.1.2.2. Configuration Parameters 是很重要的。還有，剛才如果把舊的 8.2 版的 /usr/local/pgsql/data 目錄直接刪掉，那此刻就糗了，你必須用你的記憶力回想你曾經更動過的項目和內容，所以我才會建議只需把它更名，保留下來。

若是你對 PostgreSQL 的備份和回存有興趣，英文程式也還可以，那麼請進一步參考 PostgreSQL 的線上說明：Chapter 24. Backup and Restore。

　

---

附註：
[1] 當然囉，如果新的 data 目錄已經生成，也就是 Makefile 已經自動幫你執行 initdb 的動作了，那麼 initdb 這個動作就可以省了。我會特別這樣提的原因是，我現這兩天的搶先版本的 Makefile 怪怪的，有時不會自動執行 initdb 動作。
[2] 如果你沒有像我這麼麻煩的用 pg_dumpall 和 psql 來備份回存，直接升級(還是得先移除舊版本再安裝新版本)，表面上看起來還是可以升級，但是，事實上 Makefile 會把你舊版本的 postgresql.conf 和 pg_hba.conf 複製一份到新的 data 目錄中。如我文中所說的，postgresql.conf 在這個版本中有大更動，因此，當你打算啟動資料庫時，會出現 FATAL ERROR！解決方法很簡單，用不著重灌，把新的 data 目錄移走(一樣用更名的)，執行 /usr/local/etc/postgresql initdb，重做一次 initdb，就會有一個全新版本的 data 了。
[3] 這篇文章僅適用於負載不大的伺服器上，倘若你的伺服器是 mission critical 的大用量伺服器，雖然基本的升級概念是一樣的，也就是資料庫大升級等同重新安裝，所以服務勢必一定得中斷，但是事前的演練和良好的計劃，可以讓停機服務的時間減少。在這種情況，建議必須得增加備援伺服器，在主伺服器升級的同時，讓備援伺服器先暫時服務。說到這裏，就得牽涉到「資料庫複製」(replication) 的主題了。等我有空再來寫一篇 PostgreSQL 和 replication 相關的文章。