點(diǎn)擊上方“IT那活兒”，關(guān)注后了解更多內(nèi)容，不管IT什么活兒，干就完了?。?！

邏輯備份和恢復(fù)介紹

pg_dump是一個(gè)用來對(duì)PostgreSQL數(shù)據(jù)庫(kù)進(jìn)行邏輯備份的工具。即使數(shù)據(jù)庫(kù)正在被并發(fā)使用，它也能創(chuàng)建一致的備份。

由pg_dump創(chuàng)建的備份在內(nèi)部是一致的，也就是說，這個(gè)備份是pg_dump開始運(yùn)行時(shí)刻的數(shù)據(jù)庫(kù)快照，且在pg_dump運(yùn)行過程中發(fā)生的更新將不會(huì)被備份。

pg_dump的全庫(kù)一致性備份指的是實(shí)例中的單個(gè)數(shù)據(jù)庫(kù)的一致性備份，因?yàn)閭浞莶煌臄?shù)據(jù)庫(kù)需要切換連接，無法在不同的數(shù)據(jù)庫(kù)之間共享snapshot，因此只能單庫(kù)一致.這意味著每個(gè)數(shù)據(jù)庫(kù)自身是一致的，但是不同數(shù)據(jù)庫(kù)的快照并不同步。

pg_dump工作的時(shí)候并不阻塞其他的對(duì)數(shù)據(jù)庫(kù)的操作。（但是會(huì)阻塞那些需要排它鎖的操作，比如大部分形式的ALTER TABLE）。

pg_dump相對(duì)于其他備份方法的一個(gè)重要優(yōu)勢(shì)是，pg_dump的輸出可以很容易地在新版本的PostgreSQL中載入，而文件級(jí)備份和連續(xù)歸檔都對(duì)服務(wù)器版本有限定。

pg_dump也是唯一可以將一個(gè)數(shù)據(jù)庫(kù)傳送到一個(gè)不同機(jī)器架構(gòu)上的方法，例如從一個(gè)32位服務(wù)器到一個(gè)64位服務(wù)器。

pg_dump只轉(zhuǎn)儲(chǔ)單個(gè)數(shù)據(jù)庫(kù)。要備份一個(gè)實(shí)例中對(duì)于所有數(shù)據(jù)庫(kù)公共的全局對(duì)象（例如角色和表空間），則需要使用pg_dumpall。

pg_restore是一個(gè)用來從pg_dump創(chuàng)建的非文本格式歸檔恢復(fù)PostgreSQL數(shù)據(jù)庫(kù)的工具。它將數(shù)據(jù)庫(kù)重建成它備份時(shí)的狀態(tài)。這些歸檔文件還允許pg_restore選擇恢復(fù)哪些內(nèi)容或者在恢復(fù)前對(duì)恢復(fù)項(xiàng)重排序。

pg_dump常用的備份選項(xiàng)：

-f,--file 備份腳本輸出文件名；

-F,--format=c|d|t|p  #c自定義格式,d目錄，t 輸出為tar包,p 純文本SQL；

-j,--jobs=num 當(dāng)使用目錄格式時(shí)，可以同時(shí)對(duì)多個(gè)表進(jìn)行dump；

-a,--data-only 只dump表中的數(shù)據(jù)；

-c,--clean 在重建之前，先DROP到重建的對(duì)象；

-C,--create 包含創(chuàng)建database的命令；

-n,--schema；

-N,--exclude-scheam；

-s,--schema-only 只備份表結(jié)構(gòu)；

-t,--table 只備份指定的表；

-T,--exclude-table；

--inserts #使用insert語句替換copy；

--column-inserts #insert語句包含完整的列名。

備份與恢復(fù)實(shí)驗(yàn)

1. 生成測(cè)試數(shù)據(jù)

創(chuàng)建一個(gè)測(cè)試數(shù)據(jù)庫(kù)sncdb。

$ createdb sncdb

$ pgsql sncdb

在這個(gè)測(cè)試數(shù)據(jù)庫(kù)中創(chuàng)建測(cè)試表并生成測(cè)試數(shù)據(jù)。

記錄一下各個(gè)表的hash值，用于恢復(fù)數(shù)據(jù)后校驗(yàn)。

sncdb=# select sum(hashtext(t1.*::text)) from t1;

sum

-------------

-2510573620

(1 row)



sncdb=# select sum(hashtext(t2.*::text)) from t2;

sum

-----------

197834427

(1 row)



sncdb=# select sum(hashtext(t3.*::text)) from t3;

sum

-----------

395668854

(1 row)



sncdb=# select sum(hashtext(t4.*::text)) from t4;

sum

------------

2840903814

(1 row)

2. 使用pg_dump備份數(shù)據(jù)庫(kù)到文本文件并使用psql恢復(fù)數(shù)據(jù)

使用pg_dump將數(shù)據(jù)庫(kù)sncdb備份到一個(gè)sql文本文件db_sncdb.sql。

$ pg_dump sncdb > db_sncdb.sql

備份完成后，刪除這個(gè)數(shù)據(jù)庫(kù)sncdb。

新創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)newdb。

檢查數(shù)據(jù)庫(kù)沒有任何表存在。

使用psql將sql文本db_sncdb.sql恢復(fù)到剛才新創(chuàng)建的數(shù)據(jù)庫(kù)newdb。

psql -d newdb -f db_sncdb.sql

數(shù)據(jù)恢復(fù)完成后，我們可以在newdb查看到這些表了。

檢查表的hash值，與前面記錄的hash值一致，數(shù)據(jù)恢復(fù)成功。

3. 使用pg_dump備份指定表到文本文件并使用psql恢復(fù)數(shù)據(jù)

使用pg_dump將表t4備份到一個(gè)sql文本文件t4.sql。

$ pg_dump -t t4 newdb > t4.sql

新創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)newdb2。

$ createdb newdb2

使用psql將sql文本t4.sql恢復(fù)到剛才新創(chuàng)建的數(shù)據(jù)庫(kù)newdb2。

$ psql -d newdb2 -f t4.sql

數(shù)據(jù)恢復(fù)完成后，我們可以在newdb2查看到表t4了。

4. 備份數(shù)據(jù)庫(kù)到歸檔文件并使用pg_restore恢復(fù)數(shù)據(jù)

創(chuàng)建一個(gè)新的數(shù)據(jù)庫(kù)sncdb2，并把數(shù)據(jù)導(dǎo)入。

使用pg_dump將數(shù)據(jù)庫(kù)sncdb2備份到一個(gè)歸檔文件sncdb2.dump。

$ pg_dump -Fc sncdb2 > sncdb2.dump

刪除數(shù)據(jù)庫(kù)sncdb2并使用pg_restore從歸檔文件sncdb2.dump中恢復(fù)它。

$ dropdb sncdb2
$ pg_restore -C -d postgres sncdb2.dump

數(shù)據(jù)恢復(fù)完成后，我們可以在恢復(fù)出來的數(shù)據(jù)庫(kù)sncdb查看到這些表了。

我們也可以將歸檔文件恢復(fù)到一個(gè)名為sncdb3的新創(chuàng)建的數(shù)據(jù)庫(kù)中。

新創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)sncdb3。

$ createdb -T template0 sncdb3

將歸檔文件sncdb2.dump恢復(fù)到新創(chuàng)建的數(shù)據(jù)庫(kù)sncdb3中。

$ pg_restore -d sncdb3 sncdb2.dump

數(shù)據(jù)恢復(fù)完成后，我們可以在數(shù)據(jù)庫(kù)sncdb_new查看到這些表了。

5. 更多pg_dump的示例

1）pg_dump僅導(dǎo)出數(shù)據(jù)庫(kù)結(jié)構(gòu)：

pg_dump -U TestRole1 -s -f TestDb1.sql TestDb1

2）備份某個(gè)database，備份結(jié)果以自定義壓縮格式輸出：

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -f mydb.backup mydb

3）備份某個(gè)database，備份結(jié)果以SQL文本方式輸出，輸出結(jié)果中需包括CREATE DATABASE語句：

pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb

4）備份某個(gè)database中所有名稱以“pay”開頭的表，備份結(jié)果以自定義壓縮個(gè)數(shù)輸出：

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *.pay* -f pay.backup mydb

5）備份某個(gè)database中hr和payroll這兩個(gè)schema中的所有數(shù)據(jù)，備份結(jié)果以自定義壓縮格式輸出：

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -n hr -n payroll -f hr_payroll.backup mydb

6）備份某個(gè)database中除了public schema中的數(shù)據(jù)以外的所有數(shù)據(jù)，備份結(jié)果以自定義壓縮格式輸出：

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -N public -f all_sch_except_pub.backup mydb

6. pg_dumpall實(shí)例級(jí)別邏輯備份

pg_dumpall常用的備份選項(xiàng)：

-f,--file 備份輸出文件名；

-a,--data-only 只備份數(shù)據(jù)；

-c,--clean在重建之前，先DROP到重建的對(duì)象；

-E,--endcoding 備份文件字符集；

-s,--schema-only 只備份表結(jié)構(gòu)；

建議每天對(duì)角色和表空間定義等全局對(duì)象進(jìn)行備份，但不建議每天使用pg_dumpall來備份全庫(kù)數(shù)據(jù)，因?yàn)閜g_dumpall僅支持導(dǎo)出為SQL文本格式，而使用這種龐大的SQL文本備份來進(jìn)行全庫(kù)級(jí)別的數(shù)據(jù)庫(kù)恢復(fù)時(shí)及其耗時(shí)的，所以一般只建議使用pg_dumpall來備份全局對(duì)象而非全庫(kù)數(shù)據(jù)。