Postgresql備份與還原
- PostgreSQL資料庫有以下三種備份方法，每種備份都有自己的優點和缺點:
- SQL轉儲: pg_dump的輸出通常可以重新載入PostgreSQL新版本。
- 檔案系統級別備份
- 線上備份
SQL轉儲
- SQL轉儲的方法是創建一個檔，裡面都是SQL命令， 當把這個檔回饋給伺服器時，將重建與轉儲時狀態一樣的資料庫。 PostgreSQL為這個用途提供了pg_dump工具。
pg_dump dbname > outfile
# 備份且壓縮
pg_dump dbname | gzip > filename.gz
pg_dump是一個普通的PostgreSQL 用戶端應用(儘管是個相當聰明的東西)。 這就意味著你可以從任何可以訪問該資料庫的遠端主機上面進行備份工作。 但是請記住pg_dump不會以任何特殊許可權運行。
- 具體說來， 就是它必須要有你想備份的表的讀許可權，因此， 為了恢復整個資料庫你幾乎總是要成為資料庫超級使用者。 （如果你沒有足夠的許可權恢復整個資料庫，你仍然可以使用選項，比如 -n schema或 -t table，恢復你有許可權的部分資料庫。）
- 要聲明pg_dump應該以哪個用戶身份進行連接，應使用命令列選項-hhost和-p port。 缺省主機是本地主機或環境變數PGHOST聲明的值。類似的，缺省埠是環境變數PGPORT或編譯好了的缺省值
- 和任何其它PostgreSQL用戶端應用一樣，pg_dump缺省時用 與當前作業系統用戶名同名的資料庫用戶名進行連接。 要覆蓋這個名字，要麼聲明-U選項， 要麼設置環境變數PGUSER。請注意pg_dump 的連接也和普通客戶應用一樣要通過客戶認證機制
pg_dump超過後邊描述的其它備份方法的一個重要優點是pg_dump的輸出通常可以重新載入PostgreSQL新版本， 然而檔級別備份和連續歸檔都因伺服器版本而異。pg_dump是 將傳輸資料庫到另一台機器體系結構工作時唯一的方法， 如從32位變到64位伺服器。
pg_dump創建的備份在內部是一致的，也就是說，在pg_dump運行的時候轉儲的是資料庫的快照。pg_dump工作的時候並不阻塞其它的對資料庫的操作 (但是會阻塞那些需要排它鎖的操作，比如ALTER TABLE)。
從轉儲中恢復
- 這裡的infile 就是通過pg_dump命令的檔輸出。 這條命令不會創建dbname資料庫， 你必須在執行psql前自己從 template0創建(也就是用createdb -T template0 dbname命令)。
- psql支援類似pg_dump的選項用以控制資料庫伺服器位置和用戶名。非文字檔轉儲使用pg_restore工具。
psql dbname < infile
# 從壓縮檔還原，方法1
gunzip -c filename.gz | psql dbname
# 從壓縮檔還原，方法2
cat filename.gz | gunzip | psql dbname
在開始運行恢復之前，目標庫和所有在轉儲出來的庫中擁有物件的使用者，以及曾經在某些物件上被賦予許可權的用戶都必須已經存在。如果這些不存在，那麼恢復將失敗，因為恢復過程無法把這些物件恢復成原有的所有權和/或許可權。 有時候你希望恢復許可權，不過通常你不需要這麼做。
缺省時，psql腳本將在遇到錯誤的時候仍然繼續執行。 你可能希望運行帶有ON_ERROR_STOP變數設置的 psql以改變操作，並且如果發生SQL錯誤則帶有 退出狀態碼3的psql退出。
psql --set ON_ERROR_STOP=on dbname < infile
不管上述哪種方法都只能得到部分恢復了的資料庫。另外， 你可以將整個恢復過程當成一個單獨的事務，這樣就能夠保證要麼全部恢復成功， 要麼全部回滾。可以通過向psql傳遞-1或者--single-transaction命令列 參數達到此目的。使用這個模式的時候即使一個很微小的錯誤也將導致已經運行 了好幾個小時的恢復過程回滾。儘管如此，這種模式也比手動清除哪些不完整的恢復資料強。
pg_dump和psql可以通過管道讀寫， 這樣我們就可能從一台主機上將資料庫目錄轉儲到另一台主機上
- 重要: pg_dump生成的轉儲輸出是相對於template0的。 這就意味著任何加入到template1的語言、 過程等都會經由pg_dump轉儲。 這樣在恢復的時候，如果你使用的是自訂的template1， 那麼你必須從template0中創建空的資料庫。
pg_dump -h host1 dbname | psql -h host2 dbname
使用pg_dumpall
- pg_dump在一個時間只轉儲一個單獨的資料庫， 它不轉儲有關角色或表空間資訊（因為這些是集群範圍，而不是每個資料庫）。
- 為了支援方便轉儲整個資料庫集群的全部內容。因此我們提供了pg_dumpall程式。
- pg_dumpall備份一個給出的集群中的每個資料庫，同時還確保保留像角色和表空間這樣的全域資料狀態。
# 備份
pg_dumpall > outfile
# 還原
psql -f infile postgres
- 實際上，你可以聲明任意現有的資料庫進行連接， 但是如果你是向一個空的資料庫集群裝載， 那麼postgres應該是比較好的選擇。 恢復pg_dumpall的轉儲的時候通常需要資料庫超級使用者許可權， 因為我們需要它來恢復角色和表空間資訊。如果使用了表空間， 需要注意轉儲中的表空間路徑必須適合新的安裝。
檔案系統級別備份
- 另一個備份的策略是直接拷貝PostgreSQL用於存放資料庫資料的檔。 你可以用自己喜歡的任何常用檔案系統備份的方法。
tar -cf backup.tar /var/lib/postgresql
不過，你要受到兩個限制，令這個方法不那麼實用，或者至少比pg_dump的方法遜色一些：
- 為了進行有效的備份，資料庫伺服器必須被關閉。 像拒絕所有連接這樣的折衷的方法是不行的。同樣在恢復資料之前， 同樣必須關閉伺服器。
- 你可能試圖從對應的檔或目錄裡備份幾個表或者資料庫。 這樣做是沒用的，因為包含在這些檔裡的資訊只是部分資訊。 還有一半資訊在提交日誌檔pg_clog/*裡面， 它包含所有事務的提交狀態。 只有擁有這些資訊，表檔的資訊才是可用的。當然，試圖只恢復表和相關的pg_clog資料也是徒勞的， 因為這樣會把資料庫集群裡的所有其它沒有用的表的資訊都拿出來。所以檔案系統的備份只適用于一個資料庫集群的完整恢復。
另外一個檔案系統備份的方法是給資料目錄做一個"一致的快照"， 條件是檔案系統支持這個功能(並且你願意相信它是實現正確的)。 典型的過程是製作一個包含資料庫的卷的"凍結快照"， 然後把整個資料庫目錄(不僅僅是部分，見上文)從快照拷貝到備份設備， 然後釋放凍結快照。這樣甚至在資料庫伺服器在運行的時候都可以運轉。 不過，這樣創建的備份會把資料庫檔保存在一個沒有恰當關閉資料庫伺服器的狀態下； 因此，如果你在這個備份目錄下啟動資料庫伺服器， 它就會認為資料庫伺服器經歷過崩潰並且重放WAL日誌。 這不是個問題，只要意識到它即可(並且確信在自己的備份中包含WAL檔)。 在採用快照減少恢復時間之前，你可以執行CHECKPOINT。
線上備份以及即時恢復(PITR)
在任何時候，PostgreSQL都在集群的資料目錄的pg_xlog/ 子目錄裡維護著一套預寫日誌(WAL)。
- 這些日誌記錄著每一次對資料庫的修改細節。
- 這些日誌存在是為了防止崩潰： 如果系統崩潰，資料庫可以通過"重放"上次檢查點以來的日誌記錄以恢復資料庫的完整性。
- 但是，日誌的存在讓它還可以用於第三種備份資料庫的策略： 我們可以組合檔案系統備份與WAL檔的備份。如果需要恢復，我們就恢復備份， 然後重放備份了的WAL檔，把備份恢復到當前的時間。
這個方法對管理員來說， 明顯比以前的方法更複雜，但是有非常明顯的優勢：
- 在開始的時候我們不需要一個非常完美的一致的備份。 任何備份內部的不一致都會被日誌重放動作修改正確 (這個和崩潰恢復時發生的事情沒什麼區別)。因此我們不需要檔案系統快照的功能， 只需要tar或者類似的歸檔工具。
- 因為我們可以把無限長的WAL檔序列連接起來，所以連續的備份簡化為連續地對WAL檔歸檔來實現。 這個功能對大資料庫特別有用，因為大資料庫的全備份可能並不方便。
- 我們可沒說重放WAL記錄的時候我們必須重放到結尾。 我們可以在任意點停止重放，這樣就有一個在任意時間的資料庫一致的快照。因此，這個技術支持即時恢復： 我們可以把資料庫恢復到你開始備份以來的任意時刻的狀態。
如果我們持續把WAL檔序列填充給其它裝載了同樣的基礎備份檔案的機器， 我們就有了一套熱備份系統：在任何點我們都可以啟動第二台機器，而它擁有近乎當前的資料庫拷貝。
注意: pg_dump和 pg_dumpall沒有產生檔案系統級別備份，並且不能作為連續歸檔解決方案的一部分。比如備份是符合邏輯的並且不包含 WAL重放使用的足夠資訊。
和簡單的檔案系統備份技術一樣，這個方法只能支援整個資料庫集群的恢復， 而不是一個子集。同樣，它還要求大量的歸檔存儲：基礎備份量可能很大， 而且忙碌的系統將生成許多兆需要備份的的WAL流量。但是， 它仍然時在需要高可靠性的場合下的最好的備份技術。