實作建立新的 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
與您分享囉.....