Johnson峰的部落格

PostgreSQL備份pg_dump與還原手記pg_restore

真沒有想到，以前一直是PostgreSQL使用者，突然需要庫移植又成了頭一招了！原來它與mysql命令列操作區別還挺大。
不用怕，但絕對要細心，因為資料庫操作是網站的核心，一旦出現損壞或丟失，後果就非常嚴重了。
我先寫了步驟，然後按計劃進行，雖然也出現了錯誤，但最終還是安全移植了。這裡記錄在案，以備後用。
備份還原方法：pg_dump和pg_restore，先仔細說明這兩個命令，再記錄我的操作方法。
pg_dump --  將一個PostgreSQL資料庫抽出到一個指令檔或者其它歸檔檔中
pg_dump [option...] [dbname]
選項option...
下面的命令列參數控制輸出的內容和格式。

dbname
聲明將要轉儲的資料庫名。 如果沒有聲明這個參數，那麼使用環境變數 PGDATABASE。 如果那個環境變數也沒聲明，那麼用發起連接的用戶名。

-a
--data-only
只輸出資料，不輸出模式（資料定義）。

這個選項只是對純文字格式有意義。對於歸檔格式，你可以在調用 pg_restore 的時候聲明選項。

-b
--blobs
在轉儲中包含大物件。必須選擇一種非文本輸出格式。

-c
--clean
輸出在創建資料庫創建命令之前先清理（刪除）該資料庫物件的命令。

這個選項只是對純文字格式有意義。對於歸檔格式，你可以在調用 pg_restore 的時候聲明選項。

-C
--create
以一條創建該資料庫本身並且與這個資料庫聯接等命令開頭進行輸出。 （如果是這種形式的腳本，那麼你在運行腳本之前和哪個資料庫聯接就不重要了。）

這個選項只對純文字格式有意義。對於歸檔格式，你可以在調用 pg_restore 的時候聲明該選項。

-d
--inserts
將資料輸出為的INSERT命令（而不是 COPY）。 這樣會導致恢復非常緩慢。 這個選項主要用於製作那種可以用於其它非 PostgreSQL 資料庫的轉儲。 請注意，如果你重新排列了欄位順序，那麼恢復可能會完全失敗。 -D 更安全，但是也更慢。

-D
--column-inserts
--attribute-inserts
把資料轉儲為帶有明確欄位名的 INSERT 命令。 （INSERT INTO table(column, ...) VALUES ...）。 這樣會導致恢復非常緩慢，它主要用於製作那種可以用於其它非 PostgreSQL 資料庫的轉儲。

-f file
--file=file
把輸出發往指定的檔。如果忽略這些，則使用標準輸出。

-F format
--format=format
選擇輸出的格式。format可以是下列之一：

p
輸出純文字SQL指令檔（缺省）

t
輸出適合輸入到 pg_restore 裡的tar歸檔檔。 使用這個歸檔允許在恢復資料庫時重新排序和/或把資料庫物件排除在外。 同時也可能可以在恢復的時候限制對哪些資料進行恢復。

c
輸出適於給 pg_restore 用的客戶化歸檔。 這是最靈活的格式，它允許對裝載的資料和物件定義進行重新排列。 這個格式缺省的時候是壓縮的。

-i
--ignore-version
忽略在 pg_dump 和資料庫伺服器之間的版本差別。

pg_dump 可以處理來自以前版本的PostgreSQL 的資料庫，但是太老的版本則不被支持了（目前是支持到 7.0）。 如果你需要跨越版本檢查時才使用這個選項（ 而且如 pg_dump 失效，別說我沒警告你）。

-n namespace
--schema=schema
只轉儲 schema 的內容。 如果沒有聲明這個選項，所有目標資料庫中的非系統模式都會被轉儲出來。

注意: 在這個模式裡，pg_dump 並不試圖轉儲任何其它選定模式可能依賴的資料庫物件。 因此，系統不保證單一的一個模式的轉儲就可以成功地恢復到一個乾淨的資料庫中去。

-o
--oids
作為資料的一部分，為每個表都輸出物件標識（OID）。 如果你的應用在某種程度上引用了OID欄位的話，（比如，在外鍵約束中用到）。 那麼使用這個選項。否則，不應該使用這個選項。

-O
--no-owner
不 把對象的所有權設置為對應來源資料庫。 通常， pg_dump 發出（psql特有的） ALTER OWNER 或者 SET SESSION AUTHORIZATION 語句以設置創建的資料庫物件的所有權。 又見 -R 和 -X use-set-session-authorization 選項。 請注意 -O 並不防止所有對資料庫的重新聯接， 只是防止那些為調整許可權進行的排它聯接。

這個選項只是對純文字格式有意義。對於歸檔格式，在你調用 pg_restore 的時候你可以聲明該選項。

-R
--no-reconnect
這個選項已經過時，但是出於向下相容的考慮，仍然接受這個選項。

-s
--schema-only
只輸出物件定義（模式），不輸出資料。

-S username
--superuser=username
聲明關閉觸發器時需要用到的超級用戶名。 它只有使用了 --disable-triggers 的時候才有關係。 （通常，我們最好不要輸入這個參數，而是用超級用戶啟動生成的腳本。）

-t table
--table=table
只輸出表 table的資料。 很可能是在不同模式裡面有多個同名表；如果這樣，那麼所有匹配的表都將被轉儲出來。 同時聲明 --schema 和 --table 則只選擇一個表。

注意: 在這個模式裡，pg_dump 並不試圖轉儲任何其它選定表可能依賴的資料庫物件。 因此，系統不保證單一的一個表的轉儲就可以成功地恢復到一個乾淨的資料庫中去。

-v
--verbose
聲明冗餘模式。 這樣將令 pg_dump 輸出詳細的物件評注以及轉儲檔的啟停時間和進度資訊到標準輸出上。

-x
--no-privileges
--no-acl
避免輸出 ACL（賦予/撤銷 命令）和表的所有者關係資訊。

-X disable-dollar-quoting
--disable-dollar-quoting
這個選項關閉使用美元符包圍函數體。強制它們用 SQL 標準的字串語法的引號包圍。

-X disable-triggers
--disable-triggers
這個選項只是和創建僅有資料的轉儲相關。它告訴 pg_dump 包含在恢復資料時，臨時關閉目標表上面的觸發器的命令。 如果你在表上有參考完整性檢查或者其它觸發器，而恢復資料的時候你不想重載他們，那麼你就應該使用這個選項。

目前，為 --disable-triggers 發出的命令必須用超級用戶來做。 因此，你應該同時用 -S 聲明一個超級用戶名，或者最好是用一個超級用戶的身份來啟動這個生成的腳本。

這個選項只對純文字格式有意義。對於歸檔格式，你可以在調用 pg_restore 的時候聲明這個選項。

-X use-set-session-authorization
--use-set-session-authorization
輸出 SQL 標準 SET SESSION AUTHORIZATION 命令而不是 OWNER TO 命令。 這樣的轉儲結果更加複合標準，但是依賴轉儲中的物件的歷史，可能不能正確恢復。

-Z 0..9
--compress=0..9
聲明在那些支持壓縮的格式中使用的壓縮層級。 （目前只有客戶化格式支援壓縮）。

下面的命令列參數控制資料庫為聯接參數。
-h host
--host=host
聲明運行伺服器的機器的主機名稱。 如果數值以斜杠開頭，則它被用做到 Unix 域通訊端的路徑。 缺省是從 PGHOST 環境變數中取得的，如果設置了這個環境變數的話，否則，嘗試一個 Unix 域通訊端連接。

-p port
--port=port
聲明伺服器正在偵聽並等待聯接的 TCP 埠或本地 Unix 主控通訊端檔案控制代碼。 缺省時使用環境變數 PGPORT 的值（如果存在），或者是編譯時的缺省值。

-U username
以給出用戶身分聯接。

-W
強制口令提示。如果伺服器需要口令認證，那麼這個動作應該自動發生。

pg_restore --  從一個由 pg_dump 創建的備份檔案中恢復 PostgreSQL 資料庫。
pg_restore 接受下列命令列參數。

filename
聲明要恢復的備份檔案的位置。如果沒有聲明，則使用標準輸入。

-a
--data-only
只恢復資料，而不恢復表模式（資料定義）。

-c
--clean
創建資料庫物件前先清理（刪除）它們。

-C
--create
在恢復資料庫之前先創建它。（如果出現了這個選項，和 -d 在一起的資料庫名只是用於發出最初的CREATE DATABASE命令。 所有資料都恢復到名字出現在歸檔中的資料庫中去。）

-d dbname
--dbname=dbname
與資料庫 dbname 聯接並且直接恢復到該資料庫中。

-e
--exit-on-error
如果在向資料庫發送 SQL 命令的時候碰到錯誤，則退出。 缺省是繼續執行並且在恢復結束時顯示一個錯誤計數。

-f filename
--file=filename
聲明生成的腳本的輸出檔，或者出現-l 選項時用於清單的檔，缺省是標準輸出。

-F format
--format=format
聲明備份檔案的格式。因為pg_restore 會自動判斷格式，所以如果聲明了，它可以是下面之一：

t
備份是一個 tar 歸檔。 使用這個格式允許在恢復資料庫的時候重新排序和/或把表模式元素排除出去。 同時還可能在恢復的時候限制裝載的資料。

c
備份的格式是來自pg_dump的客戶化格式。 這是最靈活的格式，因為它允許重新對資料排序，也允許重載表模式元素。 缺省時這個格式是壓縮的。


-i
--ignore-version
忽略資料庫版本檢查。

-I index
--index=index
只恢復命名的索引。

-l
--list
列出備份的內容。這個操作的輸出可以用 -L 選項限制和重排所恢復的項目。

-L list-file
--use-list=list-file
只恢復在 list-file 裡面的元素，以它們在檔中出現的順序。 你可以移動各個行並且也可以通過在行開頭放 ';' 的方式注釋。（見下文獲取例子。）

-O
--no-owner
不 要輸出設置物件的許可權，以便與最初的資料庫匹配的命令。 缺省時，pg_restore 發出 ALTER OWNER 或 SET SESSION AUTHORIZATION 語句設置創建出來的模式元素的所有者許可權。 如果最初的資料庫連接不是由超級使用者（或者是擁有所有創建出來的物件的同一個使用者）發起的，那麼這些語句將失敗。 使用 -O，那麼任何用戶都可以用於初始的連接，並且這個用戶將擁有所有創建出來的物件。

-P function-name(argtype [, ...])
--function=function-name(argtype [, ...])
只恢復指定的命名函數。請注意仔細拼寫函數名及其參數，應該和轉儲的內容清單中的完全一樣。

-R
--no-reconnect
這個選項已經廢棄了，但是為了保持向下相容仍然接受。

-s
--schema-only
只恢復表結構（資料定義）。不恢復資料，序列值將重置。

-S username
--superuser=username
設置關閉觸發器時聲明超級用戶的用戶名。 只有在設置了 --disable-triggers 的時候才有用。

-t table
--table=table
只恢復表指定的表的定義和/或資料。

-T trigger
--trigger=trigger
只恢復指定的觸發器。

-v
--verbose
聲明冗餘模式。

-x
--no-privileges
--no-acl
避免 ACL 的恢復（grant/revoke 命令）。

-X use-set-session-authorization
--use-set-session-authorization
輸出 SQL 標準的 SET SESSION AUTHORIZATION 命令，而不是 OWNER TO 命令。 這樣令轉儲與標準相容的更好，但是根據轉儲中物件的歷史，這個轉儲可能不能恰當地恢復。

-X disable-triggers
--disable-triggers
這個選項只有在執行僅恢復資料的時候才相關。它告訴 pg_restore 在裝載資料的時候執行一些命令臨時關閉在目標表上的觸發器。 如果你在表上有完整性檢查或者其它觸發器， 而你又不希望在裝載資料的時候啟動它們，那麼可以使用這個選項。

目 前，為 --disable-triggers 發出的命令必須以超級用戶發出。 因此，你應該也要用 -S 聲明一個超級用戶名，或者更好是設置 --use-set-session-authorization 並且以 PostgreSQL 超級用戶身份運行 pg_restore。

pg_restore 還接受下麵的命令列參數做為聯接參數：

-h host
--host=host
聲明伺服器運行的機器的主機名稱。 如果數值以斜杠開頭，那麼它被用做 Unix 域通訊端的目錄。 缺省是從 PGHOST 環境變數中獲取的（如果設置了）， 否則將嘗試進行 Unix 域通訊端。

-p port
--port=port
聲明伺服器偵聽的 TCP 埠或者本地的 Unix 域通訊端檔擴展。 缺省是環境變數 PGPORT 的值（如果設置了的話）， 否則就說編譯的缺省。

-U username
以給出用戶身分聯接。

-W
強制給出口令提示。如果伺服器要求口令認證，那麼這個應該自動發生。

理論說完了，有了上面的知識下面進行實戰變得容易：

DBコッピ
/usr/local/pgsql/bin/pg_dump -Ft -b zhoz > /home/zhoz/db_zhoz_081121.tar

移動
scp -v /home/zhoz/db_zhoz_081121.tar zhoz@zhoz.com:/home/zhoz/

SCP也是新學到的，很強大！參數也收集了一下：
-v 和大多數linux命令中的-v意思一樣,用來顯示進度.可以用來查看連接,認證,或是配置錯誤.
-C 使能壓縮選項.
-r 複製資料夾
-P 選擇埠.注意-p已經被rcp使用.
-4 強行使用IPV4位址.
-6 強行使用IPV6位址.

エクスポート
/usr/local/pgsql/bin/pg_restore -d zhoz -U zhoz -W /home/zhoz/logs/db_zhoz_081121.tar
這裡如果不指定-U會提示資料庫不存在或導入非指定的庫中，有危險性。

至此，打完收工！又掌握了一種實戰技術。

「2009/06/23補充：」
pg_dumpall > outfile
生成的轉儲可以用 psql 恢復：

psql template1 < infile
（實際上，你可以聲明任意現有的資料庫進行連接，但是如果你是向一個空的資料庫裝載，那麼 template1 是你唯一的選擇。） 恢復pg_dumpall的轉儲的時候通常需要資料庫超級使用者許可權，因為我們需要它來恢復使用者和組資訊。

   處理大資料庫

     因為 PostgreSQL 允許表的大小大於你的系統允許的最大檔大小， 可能把表轉儲到一個檔會有問題，因為生成的檔很可能比你的系統允許的最大檔大。 因為 pg_dump 輸出到標準輸出，你可以用標準的 Unix 工具繞開這個問題：

使用壓縮的轉儲. 使用你熟悉的壓縮程式，比如說 gzip。

pg_dump dbname | gzip > filename.gz
用下麵命令恢復：

createdb dbname
gunzip -c filename.gz | psql dbname
或者

cat filename.gz | gunzip | psql dbname

split 命令允許你 你用下面的方法把輸出分解成作業系統可以接受的大小。 比如，讓每個塊大小為 1 百萬位元組：

pg_dump dbname | split -b 1m - filename
用下麵命令恢復：

createdb dbname
cat filename* | psql dbname

使 用客戶化轉儲格式. 如果PostgreSQL是在一個安裝了zlib 壓縮庫的系統上製作的，那麼客戶化轉儲格式將在寫入輸出檔的時候壓縮資料。 它會生成和使用 gzip 類似大小的轉儲檔，但是還附加了一個優點：你可以有選擇地恢復庫中的表。 下面的命令用客戶化轉儲格式轉儲一個資料庫：

pg_dump -Fc dbname > filename
客戶化格式的轉儲不是腳本，不能用於 psql， 而是需要使用 pg_restore 轉儲。 請參考 pg_dump 和 pg_restore 的手冊獲取細節。

備份：pg_dump -h localhost -p 5432 -U tradesns -W -F c -b -v -f "/home/tradeworkwangbin/us2010.backup" us2010
恢復：pg_restore -h 192.168.0.100 -p 5432 -U postgres -W -d us2011 -v "/root/us2010.backup"

注意
處於向下相容的考慮，缺省的時候 pg_dump 並不轉儲大物件。 要轉儲大物件，你必須使用客戶化或者 tar輸出格式， 並且在 pg_dump 中使用-b選項。