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

******* pg_dumpall 備份所有的資料庫 *******

pg_dumpall 用來將 Cluster 中的所有資料庫備份起來,由於 Cluster 中可能有多名使用者,而且 postgres、template0、template1 三個資料庫的擁有者為建立 Cluster 時的 superuser,所以一定要透過 superuser 才有足夠的權限來備份整個 Cluster。
另外,pg_dumpall 一定是將整個 Cluster 的資料輸出成 SQL 文字檔,
而沒有像 pg_dump 一樣可以備份成 tar 或 custom 的格式。
除了 pg_dump 特有的 -Z 及 -F 之類的選項外,pg_dumpall 大部份選項都類似 pg_dump。

pg_dumpall 的動作會每備份一個資料庫就呼叫一次 pg_dump,直到備份完全部的資料庫,
所以會反覆登入,如果有設定登入要輸入密碼的話,就必須每備份一個資料庫就輸入一次密碼。
為了避免多次輸入密碼的繁複動作以及輸入錯誤,可以設定一個名為 pgpass.conf 的文字檔,置於 %APPDATA%\postgres\ 目錄中,
( %APPDATA% 可以透過 C:\> echo %APPDATA% 查詢)
檔案的每一行設定成「hostname:port:database:username:password」的格式,
除了 password 項以外,都可以用萬用字元「*」來代替(一般只有 database 項會用 *),
如此一來,只要備份時詢問密碼,就會自動參照這個 pgpass.conf 裡面所設定的資料。
而 pg_dumpall 指令要下 -U 指定 superuser 才有辦法對照正確。

pgpass.conf 的相關資料可以參考說明文件之 Chapter 30.14 或
http://twpug.net/docs/postgresql-doc-8.0-zh_TW/libpq-pgpass.html

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

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

-c 指定輸出的 SQL 要加入清除原有物件的指令(如 DROP ROLE)再建立。由於還原時一定要以某一位使用者登入,故其中若有 DROP ROLE,而要刪除的使用者名字剛好跟登入的使用者名字一樣的話,就會出現錯誤訊息(但不影響後續動作)。
-O 輸出時不加入設定 OWNER 的 SQL 指令,但是在 pg_dumpall 時,會備份整個 Cluster,其中每個使用者可能擁有不同資料庫,資料庫中的每個資料表的擁有者或許也都不同,若加上這個選項的話,當還原時,由於沒有指定擁有者,因此資料表的擁有者會設成同一人,也就是還原資料庫的 superuser。若要完整還原 Cluster 的狀態,最好不要加上這個選項。
--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 備份所有資料庫(必須為 superuser)
-W 強制使用者輸入密碼作確認,若 initdb 時指定 -A password 的話,
則不管是否有此選項都會要求使用者輸入密碼

範例:
C:\> pg_dumpall > C:/postgresql8.4.31/backups/all.sql
將 Cluster 中的所有資料庫資料備份成 SQL 文字檔存到 C:/postgresql8.4.31/backups/all.sql。

C:\> pg_dumpall -c > C:/postgresql8.4.31/backups/all.sql
將 Cluster 中的所有資料庫資料備份成 SQL 文字檔,並加入建立資料前先刪除資料的 SQL 指令,然後將檔案存到 C:/postgresql8.4.31/backups/all.sql。

C:\> pg_dumpall -p 5432 -U root -f C:/postgresql8.4.31/backups/all.sql
以 root 帳號登入 port 為 5432 的 Cluster,將 Cluster 中的所有資料庫資料備份成 SQL 文字檔,並加入建立資料前先刪除資料的 SQL 指令,然後存檔到 C:/postgresql8.4.31/backups/all.sql。
(登入主機預設為 localhost)

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