Postgresql備份和恢複–SQL轉儲篇

您是否遇到過因為不小心而刪除了某個資料庫表，或者是存放資料庫的磁碟損壞了的情況呢？ 如果碰到這種情況，我相信您會覺得非常的沮喪，因為您努力了幾個星期的工作成果可能就付之東流了。和任何包含珍貴資料的東西一樣，PostgreSQL數 據庫也應該經常備份。如果您將資料存放在 PostgreSQL 表中，您就可以定時的進行Database Backup，從而避免發生上面的悲劇。PostgreSQL 內建有內建工具來執行備份工作，而且在系統出現損壞或是意外時，您可以通過這些工具進行“復原”，並通過以前儲存的快照將系統復原到其初始的狀態。

1. 綜述;

PostgreSQL資料庫從備份 PostgreSQL 資料有三種完全不同的方法：

SQL 轉儲

檔案系統層級備份

線上備份

每種備份都有自己的優點和缺點，下面主要介紹SQl轉儲的方法;

2. 資料備份;

SQL 轉儲的方法是建立一個文字檔，這個文本裡面都是 SQL 命令，當把這個檔案回饋給伺服器時，將重建與轉儲時狀態一樣的資料庫。

2.1 pg_dump;

PostgreSQL 內建的內建備份工具叫做 pg_dump。這個工具是通過一系列的SQL語句讀取某個指定的資料庫並複製其中的內容，以它作為快照並用於日後的資料恢複。用戶端到伺服器端的串連是 用於執行備份工作。這些備份檔案就是前面所講的快照並可以用於日後的資料恢複。而保證用戶端與伺服器端的串連是執行備份所必需的。這條命令的基本用法是：

pg_dump dbname > outfile

註：在進行上述工作之前，首先請確保您具有登入到伺服器並訪問您將要備份的資料庫或表的許可權。您可以通過使用PostgreSQL 命令列用戶端——psql，進行伺服器登入。在用戶端中輸入主機名稱（-h），使用者名稱（-u）和密碼（-p），以及資料庫名，然後就可以校正您是否被授權訪 問。

使用 pg_dump 非常的簡單——只需要在命令提示字元後面輸入將要匯出的資料庫名就可以進行備份工作了，如下例所示（根據您自己的安裝路經更改PostgreSQL 路徑）：

xiaop@xiaop-laptop:~$ /usr/lib/postgresql/8.2/bin/pg_dump -D -h localhost -U xiaop(使用者名稱) mydb(資料庫名稱) > mydb.bak

通過上面的命令就會建立一個名為 mydb.bak的檔案，檔案中記錄了用於恢複資料庫的SQL命令。

正如您所見，pg_dump 把結果輸出到標準輸出。 我們下面就可以看到這樣做有什麼好處。

pg_dump 是一個普通的 PostgreSQL 用戶端應用（儘管是個相當聰明的東西。）這就意味著您可以從任何可以訪問該資料庫的遠端主機上面進行備份工作。 但是請記住 pg_dump 不會以任何特殊許可權運行。具體說來， 就是它必須要有您想備份的表的讀許可權，因此，實際上您幾乎總是要成為資料庫超級使用者。

要聲明 pg_dump 應該以哪個使用者身份進行聯結，使用命令列選項 -h host 和 -p port。預設主機是本地主機或您的環境變數PGHOST聲明的值。類似，預設連接埠是環境變數PGPORT或（如果它不存在的話）編譯好了的預設值。（服 務器通常有相同的預設，所以還算方便。）

和任何其他 PostgreSQL 用戶端應用一樣， pg_dump 預設時用與當前作業系統使用者名稱同名的資料庫使用者名稱進行聯結。要覆蓋這個名字，要麼聲明 -U 選項， 要麼設定環境變數PGUSER。 請注意 pg_dump 的聯結也和普通客戶應用一樣要通過客戶認證機制。

由 pg_dump 建立的備份在內部是一致的， 也就是說，在pg_dump啟動並執行時候對資料庫的更新將不會被轉儲。 pg_dump 工作的時候並不阻塞其他的對資料庫的操作。 （但是會阻塞那些需要排它鎖的操作，比如 VACUUM FULL。）

註：: 如果您的資料庫結構依賴於 OID （比如說用做外鍵），那麼您必須告訴 pg_dump 把 OID 也倒出來。 要倒 OID，可以使用 -o 命令列選項。 預設時也不會轉儲"大對象"。如果您使用大對象，請參考 pg_dump 的命令手冊頁。

2.2 pg_dumpall;

如果您希望對整個系統中所有的資料庫進行備份的話（而不是只對某一個資料庫進行備份），您可以使用命令pg_dumpall 而不是pg_dump。執行這個命令可以對PostgreSQL 所能識別的所有的資料庫（包括其自身的系統資料庫）備份到一個檔案中。下面給出了一個使用執行個體：

xiaop@xiaop-laptop:~$ /usr/lib/postgresql/8.2/bin/pg_dumpall -D -h localhost -U xiaop(使用者名稱) > all.bak

這樣就會將localhost的所有Database Backup到all.bak檔案中了;

2.3 計劃任務;

為了保證您的備份時刻保持更新，您可以通過往cron table中加入pg_dump或者是pg_dumpall命令來定期執行備份工作。這裡給出了兩個cron entries的例子。第一個是在每天淩晨3點對test資料庫進行備份，而第二個是在每個星期五的晚上9點對所有的資料庫進行備份：

xiaop@xiaop-laptop:~$ 0 3 * * * /usr/lib/postgresql/8.2/bin/pg_dump -D -h localhost -U xiaop(使用者名稱) mydb(資料庫名稱) > /home/xiaop/mydb.bak0 21 * *
xiaop@xiaop-laptop:~$ 5 /usr/lib/postgresql/8.2/bin/pg_dumpall -D -h localhost -U xiaop(使用者名稱) > /home/xiaop/all.bak

3. 從轉儲中恢複

3.1 用pg_dump恢複;

從備份中恢複資料的工作比執行備份甚至更簡單——您所要做的就是通過執行備份檔案中的SQL命令來對資料庫進行恢複。如果您是使用pg_dump對某一個 資料庫進行了備份，那麼備份中就會有CREATE TABLE 的語句來對源表進行複製。當然，您首先要新建立一個空資料庫來存放這些資料表。您可以使用createdb 這個工具來完成這一步工作，這個工具也是PostgreSQL 套件中的一部分：
xiaop@xiaop-laptop:~$ /usr/lib/postgresql/8.2/bin/createdb mydb(資料庫名稱)

現在您就可以執行備份檔案中的SQL命令來對資料庫進行恢複了， pg_dump 產生的文字檔可以由 psql 程式讀取。 從轉儲中恢複的常用命令格式是:

psql dbname < infile

如下例所示：

xiaop@xiaop-laptop:~$ /usr/lib/postgresql/8.2/bin/psql -h localhost -U xiaop(使用者名稱) -d mydb(資料庫名稱) < mydb.bak

3.2 用pg_dumpall恢複;

如果您是使用pg_dumpall對所有的資料庫進行備份的，就沒有必要先建立一個資料庫，因為備份檔案中已經包含了完成CREATE DATABASE工作的相關的調用。在這裡，只需要在psql命令列用戶端中輸入對應的備份檔案就可以了，而不需要指定目標資料庫：

xiaop@xiaop-laptop:~$ /usr/lib/postgresql/8.2/bin/psql -h localhost -U xiaop(使用者名稱 ) < all.bak

一旦資料恢複完成後，您就可以登入到伺服器並查看到已恢複的資料。

3.3 ANALYZE;

一旦完成恢複，在每個資料庫上運行 ANALYZE 是明智的舉動， 這樣最佳化器就有有用的統計資料了。您總是可以運行 vacuumdb -a -z 來 VACUUM ANALYZE 所有資料庫；這個等效於手工運行 VACUUM ANALYZE;

4. 處理大資料庫;

4.1 輸出大資料庫;

因為 PostgreSQL 允許表的大小大於您的系統允許的最大檔案大小， 可能把錶轉儲到一個檔案會有問題，因為產生的檔案很可能比您的系統允許的最大檔案大。 因為 pg_dump 輸出到標準輸出，您可以用標準的 Unix 工具繞開這個問題：
使用壓縮的轉儲. 使用您熟悉的壓縮程式，比如說 gzip。

xiaop@xiaop-laptop:~$ pg_dump mydb(資料庫名) | gzip > mydbBACK.gz

4.2 恢複大資料庫;

用下面命令恢複：
xiaop@xiaop-laptop:~$ createdb mydbNEW(新資料庫名)
xiaop@xiaop-laptop:~$ gunzip -c mydbBACK.gz | psql mydbNEW

或者
xiaop@xiaop-laptop:~$ cat mydbBACK.gz | gunzip | psql mydbNEW

4.3 使用 split;

4.3.1 分割;

split 命令允許您用下面的方法把輸出分解成作業系統可以接受的大小。 有關split的用法可以在《檔案的切分split和結合工具cat 介紹》中查詢。比如，讓每個塊大小為 1 MB：
xiaop@xiaop-laptop:~$ pg_dump dbname | split -b 1m - filename

4.3.2 合并;

分割後可以用下面的命令恢複：
xiaop@xiaop-laptop:~$createdb dbname
xiaop@xiaop-laptop:~$cat filename* | psql dbname

5. 關於本文;

有關PostgreSQlDatabase Backup和恢複的另外兩個方法“檔案系統層級的備份”和“線上備份”，我們以後再討論，本文大部分資料都是參照中文文檔，目的是讓兄弟們尋找方便一些，詳細的東西在中文文檔都有，多謝各位弟兄們指點 ：）