PostgreSQL SQL轉(zhuǎn)儲(chǔ)

pg_dump生成的文本文件可以由psql程序讀取。 從轉(zhuǎn)儲(chǔ)中恢復(fù)的常用命令是：

psql dbname < dumpfile

其中dumpfile就是pg_dump命令的輸出文件。這條命令不會(huì)創(chuàng)建數(shù)據(jù)庫dbname，你必須在執(zhí)行psql前自己從template0創(chuàng)建（例如，用命令 createdb -T template0 dbname ）。psql支持類似pg_dump的選項(xiàng)用以指定要連接的數(shù)據(jù)庫服務(wù)器和要使用的用戶名。參閱psql的手冊(cè)獲取更多信息。 非文本文件轉(zhuǎn)儲(chǔ)可以使用 pg_restore 工具來恢復(fù)。

在開始恢復(fù)之前，轉(zhuǎn)儲(chǔ)庫中對(duì)象的擁有者以及在其上被授予了權(quán)限的用戶必須已經(jīng)存在。如果它們不存在，那么恢復(fù)過程將無法將對(duì)象創(chuàng)建成具有原來的所屬關(guān)系以及權(quán)限（有時(shí)候這就是你所需要的，但通常不是）。

默認(rèn)情況下，psql腳本在遇到一個(gè)SQL錯(cuò)誤后會(huì)繼續(xù)執(zhí)行。你也許希望在遇到一個(gè)SQL錯(cuò)誤后讓psql退出，那么可以設(shè)置ON_ERROR_STOP變量來運(yùn)行psql，這將使psql在遇到SQL錯(cuò)誤后退出并返回狀態(tài)3：

psql --set ON_ERROR_STOP=on dbname < infile

不管怎樣，你將只能得到一個(gè)部分恢復(fù)的數(shù)據(jù)庫。作為另一種選擇，你可以指定讓整個(gè)恢復(fù)作為一個(gè)單獨(dú)的事務(wù)運(yùn)行，這樣恢復(fù)要么完全完成要么完全回滾。這種模式可以通過向psql傳遞-1或--single-transaction命令行選項(xiàng)來指定。在使用這種模式時(shí)，注意即使是很小的一個(gè)錯(cuò)誤也會(huì)導(dǎo)致運(yùn)行了數(shù)小時(shí)的恢復(fù)被回滾。但是，這仍然比在一個(gè)部分恢復(fù)后手工清理復(fù)雜的數(shù)據(jù)庫要更好。

pg_dump和psql讀寫管道的能力使得直接從一個(gè)服務(wù)器轉(zhuǎn)儲(chǔ)一個(gè)數(shù)據(jù)庫到另一個(gè)服務(wù)器成為可能，例如：

pg_dump -h host1 dbname | psql -h host2 dbname

一旦完成恢復(fù)，在每個(gè)數(shù)據(jù)庫上運(yùn)行ANALYZE是明智的舉動(dòng)，這樣優(yōu)化器就有有用的統(tǒng)計(jì)數(shù)據(jù)了，更多信息參見第 24.1.3 節(jié)和 第 24.1.6 節(jié)。更多關(guān)于如何有效地向PostgreSQL里裝載大量數(shù)據(jù)的建議， 請(qǐng)參考第 14.4 節(jié)。

pg_dump每次只轉(zhuǎn)儲(chǔ)一個(gè)數(shù)據(jù)庫，而且它不會(huì)轉(zhuǎn)儲(chǔ)關(guān)于角色或表空間（因?yàn)樗鼈兪羌胤秶模┑男畔?。為了支持方便地轉(zhuǎn)儲(chǔ)一個(gè)數(shù)據(jù)庫集簇的全部內(nèi)容，提供了pg_dumpall程序。 pg_dumpall備份一個(gè)給定集簇中的每一個(gè)數(shù)據(jù)庫，并且也保留了集簇范圍的數(shù)據(jù)，如角色和表空間定義。該命令的基本用法是：

pg_dumpall > dumpfile

轉(zhuǎn)儲(chǔ)的結(jié)果可以使用psql恢復(fù)：

psql -f dumpfile postgres

（實(shí)際上，你可以指定恢復(fù)到任何已有數(shù)據(jù)庫名，但是如果你正在將轉(zhuǎn)儲(chǔ)載入到一個(gè)空集簇中則通常要用（postgres）。在恢復(fù)一個(gè)pg_dumpall轉(zhuǎn)儲(chǔ)時(shí)常常需要具有數(shù)據(jù)庫超級(jí)用戶訪問權(quán)限，因?yàn)樗枰謴?fù)角色和表空間信息。如果你在使用表空間，請(qǐng)確保轉(zhuǎn)儲(chǔ)中的表空間路徑適合于新的安裝。

pg_dumpall工作時(shí)會(huì)發(fā)出命令重新創(chuàng)建角色、表空間和空數(shù)據(jù)庫，接著為每一個(gè)數(shù)據(jù)庫pg_dump。這意味著每個(gè)數(shù)據(jù)庫自身是一致的，但是不同數(shù)據(jù)庫的快照并不同步。

集簇范圍的數(shù)據(jù)可以使用pg_dumpall的--globals-only選項(xiàng)來單獨(dú)轉(zhuǎn)儲(chǔ)。如果在單個(gè)數(shù)據(jù)庫上運(yùn)行pg_dump命令，上述做法對(duì)于完全備份整個(gè)集簇是必需的。

在一些具有最大文件尺寸限制的操作系統(tǒng)上創(chuàng)建大型的pg_dump輸出文件可能會(huì)出現(xiàn)問題。幸運(yùn)地是，pg_dump可以寫出到標(biāo)準(zhǔn)輸出，因此你可以使用標(biāo)準(zhǔn)Unix工具來處理這種潛在的問題。有幾種可能的方法：

使用壓縮轉(zhuǎn)儲(chǔ)。.  你可以使用你喜歡的壓縮程序，例如gzip：

pg_dump dbname | gzip > filename.gz

恢復(fù)：

gunzip -c filename.gz | psql dbname

或者：

cat filename.gz | gunzip | psql dbname

使用split。.  split命令允許你將輸出分割成較小的文件以便能夠適應(yīng)底層文件系統(tǒng)的尺寸要求。例如，讓每一塊的大小為1兆字節(jié)：

pg_dump dbname | split -b 1m - filename

恢復(fù)：

cat filename* | psql dbname

使用pg_dump的自定義轉(zhuǎn)儲(chǔ)格式。.  如果PostgreSQL所在的系統(tǒng)上安裝了zlib壓縮庫，自定義轉(zhuǎn)儲(chǔ)格式將在寫出數(shù)據(jù)到輸出文件時(shí)對(duì)其壓縮。這將產(chǎn)生和使用gzip時(shí)差不多大小的轉(zhuǎn)儲(chǔ)文件，但是這種方式的一個(gè)優(yōu)勢(shì)是其中的表可以被有選擇地恢復(fù)。下面的命令使用自定義轉(zhuǎn)儲(chǔ)格式來轉(zhuǎn)儲(chǔ)一個(gè)數(shù)據(jù)庫：

pg_dump -Fc dbname > filename

            

自定義格式的轉(zhuǎn)儲(chǔ)不是psql的腳本，只能通過pg_restore恢復(fù)，例如：

pg_restore -d dbname filename

詳情請(qǐng)參閱pg_dump和pg_restore。

對(duì)于非常大型的數(shù)據(jù)庫，你可能需要將split配合其他兩種方法之一進(jìn)行使用。

使用pg_dump的并行轉(zhuǎn)儲(chǔ)特性。.  為了加快轉(zhuǎn)儲(chǔ)一個(gè)大型數(shù)據(jù)庫的速度，你可以使用pg_dump的并行模式。它將同時(shí)轉(zhuǎn)儲(chǔ)多個(gè)表。你可以使用-j參數(shù)控制并行度。并行轉(zhuǎn)儲(chǔ)只支持“目錄”歸檔格式。

pg_dump -j num -F d -f out.dir dbname

你可以使用pg_restore -j來以并行方式恢復(fù)一個(gè)轉(zhuǎn)儲(chǔ)。它只能適合于“自定義”歸檔或者“目錄”歸檔，但不管歸檔是否由pg_dump -j創(chuàng)建。