(本文章原始記錄於 2010 年 5 月 24 日)

******* 備份及還原資料庫資料 *******

天有不測風雲,為了保護辛辛苦苦所建立的資料,
最好定期備份資料,並將備份存放到不同的地方。

簡易的備份方式可以分為以下兩種:
1.SQL 文字檔案方式備份
2.檔案系統層級備份

第一種方式可以透過 pg_dumppg_dumpall 來將資料庫的資料 dump 出來成為文字檔備份,第二種方式則是將存放資料的整個目錄備份起來,但必須先將該 Cluster 關閉才行。

******* pg_dump 備份指定的資料庫 *******

pg_dump 適用於單一資料庫的備份。由於資料庫的所有權屬於該資料庫擁有者(OWNER),
非資料庫擁有者不得刪除該資料庫或資料庫內非自己所建立的資料表,
也不得存取非自己建立的資料表內資料,
(包括 INSERT、DELETE、UPDATE、SELECT 都不行)
所以只有該資料庫及資料表的擁有者或 superuser 可以備份該資料庫及資料表;
而由於其他人也可以在自己建立的資料庫中建立資料表,而且非擁有者不得擅自更動該資料表,
故備份整個資料庫通常會使用 superuser 的身份進行。

備份時要特別注意的一點是,使用 pg_dump 時必須是 Cluster 正在運行當中,
(所以才要登入)
而在備份的同時,資料庫可能還會有一些其他人正在作建立、新增、刪除及修改的動作,
因此這一些動作或許會在備份時遺漏掉。

使用語法如下:
C:\> pg_dump [option] … [DBNAME]

其中常用的 option 有以下幾個:

-v verbose 冗長模式。可以觀看詳細的運作輸出訊息。
-F [c|t|p] 備份格式,c、t、p 三種格式,c=custom,t=tar,p=plain text

  • c 格式:

    可以在還原時自訂恢復的項目,例如還原哪些資料表、內容、資料表欄位等。此外,在備份時一定會加上壓縮的動作。若使用此項,則必須透過 pg_restore 指令並指定選項後還原。

  • t 格式:

    可以在還原時自訂恢復的項目,例如還原哪些資料表、內容、資料表欄位等。此選項會將輸出的資料打包成 tar 檔案,但是無法作壓縮的動作,所以不能跟 -Z 選項併用。而 tar 規格限制一個 tar 檔最多不能超過 8GB,如果輸出的 tar 檔會超過 8GB 的話,就改用 -F c。包起來的 tar 檔可以透過 WinRar 之類的壓縮程式解開。若使用此項,則必須透過 pg_restore 指令並指定選項後還原。

  • p 格式:

    輸出成由 SQL 指令構成的純文字資料,可用文字編輯器觀看,無法透過 pg_restore 指令還原(只支援 c 及 t 格式),只能用 psql -f 或「<」檔案導向還原。可以和 -Z 併用,輸出成 zip 或 gz 之類的壓縮檔。要特別注意的是,由於是 SQL 指令所構成的純文字檔,所以當還原時,若原本備份時沒有設定好 SQL,則會出錯,例如要先 DROP TABLE 才不會跟原本存在的 TABLE 衝突(-c),要先取消設定擁有者才不會在還原時產生 OWNER 衝突(-O)等。-F p 為預設的備份格式。

-Z [0-9] 設定備份輸出時要加上壓縮及設定壓縮程度。值越大則壓縮檔越小。
只支援 -F c 及 -F p 格式,不支援 -F t 格式
-f FILENAME 設定輸出後的檔名。若是 -F p,習慣上副檔名會是 .sql 檔,
加上壓縮則是 .sql.gz 或 .sql.zip;
若是 -F t,習慣上副檔名會是 .tar ,若是 -F c 則皆可,
可以使用 .dbf 或 .bak 等。-F c 的備份檔無法作解壓縮的動作,
但是可以用文字編輯器觀看(不過會有亂碼問題)
[-t|-T] TBLNAME 選擇要備份的資料表。「選擇」用小寫的 t,「排除」則用大寫的 T,
兩者不可並存。若只想備份少數幾個資料表,用 t 比較方便,
若整個資料庫中只有少數幾個資料表不備份,用 T 比較方便。
指定複數資料表的話,要重複加上選項,例如 -t table1 -t table2 …
若資料庫中有幾個資料表並非自己所建立,則可以透過 -T 來排除。
-c 指定輸出的 SQL 要加入清除原有物件的指令(如 DROP TABLE)再建立。
此選項僅對純文字模式輸出有意義(-F p)
-E ENCODING 指定用哪一種編碼輸出
-O 輸出時不加入設定 OWNER 的 SQL 指令,以免由其他使用者還原時出錯。此選項僅對純文字模式輸出有意義(-F p)
--inserts 將資料重建的 SQL 指令設定成 INSERT 而非 COPY,輸出速度會很慢。
主要用於要將 PostgreSQL 的資料轉存入其他資料庫系統時。
如果重新排列過 Column 的前後順序,則還原時可能會失敗。
使用 --column-inserts 會更安全,但也更慢。
--column-inserts 將 SQL 指令設定成詳細的 INSERT INTO table(column,..) VALUES(…),這樣輸出的速度會比 --inserts 慢更多(因為要輸出的 SQL 文字更多)。主要用於要將 PostgreSQL 的資料轉存入其他資料庫系統時。
=== 以下為連線選項,表示要指定到哪一個 PostgreSQL Cluster 上 ===
-h HOST 指定主機名稱
-p PORT 指定主機 port
-U USER 指定由哪位使用者備份此資料庫(必須為該資料庫擁有者或 superuser)
-W 強制使用者輸入密碼作確認,若 initdb 時指定 -A password 的話,
則不管是否有此選項都會要求使用者輸入密碼

範例:
C:\> pg_dump norm > C:/postgresql8.4.31/backups/norm.sql
備份整個 norm 資料庫成一 SQL 文字檔,並存放在 C:/postgresql8.4.31/backups/norm.sql

C:\> pg_dump -t table1 tester > C:/postgresql8.4.31/backups/table1.sql
只備份 tester 資料庫中的 table1 資料表,生成 SQL 文字檔存放在 C:/postgresql8.4.31/backups/table1.sql

C:\> pg_dump -T table2 -T table3 -f C:/postgresql8.4.31/backups/norm.sql norm
備份 norm 資料庫中 table2 及 table3 以外的所有資料表,
並將生成的 SQL 文字檔存放在 C:/postgresql8.4.31/backups/norm.sql

C:\> pg_dump -c -O -v -f C:/postgresql8.4.31/backups/norm.sql norm
備份 norm 整個資料庫,並設定輸出的 SQL 指令要取消設定擁有者、建立資料前先刪除已存在的資料(如 DROP TABLE 後再 CREATE TABLE),觀看備份過程的詳細資訊,並將生成的 SQL 文字檔存放在 C:/postgresql8.4.31/backups/norm.sql

C:\> pg_dump -F t -v -f C:/postgresql8.4.31/backups/tester.sql.tar tester
以 tar 格式將 tester 資料庫備份成 tar 檔案,觀看備份過程的詳細資訊,
並將包裝的 tar 檔案存放在 C:/postgresql8.4.31/backups/tester.sql.tar

C:\> pg_dump -U norm -Z 9 -F p -T table4 -c -O
-f C:/postgresql8.4.31/backups/norm.sql.zip norm
以 norm 帳號登入 Cluster,備份 norm 資料庫中除了 table4 以外的所有資料表,
設定輸出的 SQL 指令要取消設定擁有者、建立資料前先刪除已存在的資料,
並將生成的 SQL 文字檔經過等級 9 的壓縮之後,
存放在 C:/postgresql8.4.31/backups/norm.sql.zip

C:\> pg_dump -U admin -F c -v -f C:/postgresql8.4.31/backups/tester.bak tester
以 admin 帳號登入 Cluster,用 custom 格式備份資料庫 tester,觀看備份過程的詳細資訊,並將備份出來的檔案存放到 C:/postgresql8.4.31/backups/tester.bak

關於 pg_dump 的用法可以參考 C:\> pg_dump --help
更詳細的操作說明可以參考
http://twpug.net/docs/postgresql-doc-8.0-zh_TW/app-pgdump.html