點擊上方“IT那活兒”，關注后了解更多內容，不管IT什么活兒，干就完了！！！

邏輯備份和恢復介紹

pg_dump是一個用來對PostgreSQL數據庫進行邏輯備份的工具。即使數據庫正在被并發使用，它也能創建一致的備份。

由pg_dump創建的備份在內部是一致的，也就是說，這個備份是pg_dump開始運行時刻的數據庫快照，且在pg_dump運行過程中發生的更新將不會被備份。

pg_dump的全庫一致性備份指的是實例中的單個數據庫的一致性備份，因為備份不同的數據庫需要切換連接，無法在不同的數據庫之間共享snapshot，因此只能單庫一致.這意味著每個數據庫自身是一致的，但是不同數據庫的快照并不同步。

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

pg_dump相對于其他備份方法的一個重要優勢是，pg_dump的輸出可以很容易地在新版本的PostgreSQL中載入，而文件級備份和連續歸檔都對服務器版本有限定。

pg_dump也是唯一可以將一個數據庫傳送到一個不同機器架構上的方法，例如從一個32位服務器到一個64位服務器。

pg_dump只轉儲單個數據庫。要備份一個實例中對于所有數據庫公共的全局對象（例如角色和表空間），則需要使用pg_dumpall。

pg_restore是一個用來從pg_dump創建的非文本格式歸檔恢復PostgreSQL數據庫的工具。它將數據庫重建成它備份時的狀態。這些歸檔文件還允許pg_restore選擇恢復哪些內容或者在恢復前對恢復項重排序。

pg_dump常用的備份選項：

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

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

-j,--jobs=num 當使用目錄格式時，可以同時對多個表進行dump；

-a,--data-only 只dump表中的數據；

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

-C,--create 包含創建database的命令；

-n,--schema；

-N,--exclude-scheam；

-s,--schema-only 只備份表結構；

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

-T,--exclude-table；

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

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

備份與恢復實驗

1. 生成測試數據

創建一個測試數據庫sncdb。

$ createdb sncdb

$ pgsql sncdb

在這個測試數據庫中創建測試表并生成測試數據。

記錄一下各個表的hash值，用于恢復數據后校驗。

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備份數據庫到文本文件并使用psql恢復數據

使用pg_dump將數據庫sncdb備份到一個sql文本文件db_sncdb.sql。

$ pg_dump sncdb > db_sncdb.sql

備份完成后，刪除這個數據庫sncdb。

新創建一個數據庫newdb。

檢查數據庫沒有任何表存在。

使用psql將sql文本db_sncdb.sql恢復到剛才新創建的數據庫newdb。

psql -d newdb -f db_sncdb.sql

數據恢復完成后，我們可以在newdb查看到這些表了。

檢查表的hash值，與前面記錄的hash值一致，數據恢復成功。

3. 使用pg_dump備份指定表到文本文件并使用psql恢復數據

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

$ pg_dump -t t4 newdb > t4.sql

新創建一個數據庫newdb2。

$ createdb newdb2

使用psql將sql文本t4.sql恢復到剛才新創建的數據庫newdb2。

$ psql -d newdb2 -f t4.sql

數據恢復完成后，我們可以在newdb2查看到表t4了。

4. 備份數據庫到歸檔文件并使用pg_restore恢復數據

創建一個新的數據庫sncdb2，并把數據導入。

使用pg_dump將數據庫sncdb2備份到一個歸檔文件sncdb2.dump。

$ pg_dump -Fc sncdb2 > sncdb2.dump

刪除數據庫sncdb2并使用pg_restore從歸檔文件sncdb2.dump中恢復它。

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

數據恢復完成后，我們可以在恢復出來的數據庫sncdb查看到這些表了。

我們也可以將歸檔文件恢復到一個名為sncdb3的新創建的數據庫中。

新創建一個數據庫sncdb3。

$ createdb -T template0 sncdb3

將歸檔文件sncdb2.dump恢復到新創建的數據庫sncdb3中。

$ pg_restore -d sncdb3 sncdb2.dump

數據恢復完成后，我們可以在數據庫sncdb_new查看到這些表了。

5. 更多pg_dump的示例

1）pg_dump僅導出數據庫結構：

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

2）備份某個database，備份結果以自定義壓縮格式輸出：

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

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

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

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

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

5）備份某個database中hr和payroll這兩個schema中的所有數據，備份結果以自定義壓縮格式輸出：

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

6）備份某個database中除了public schema中的數據以外的所有數據，備份結果以自定義壓縮格式輸出：

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

6. pg_dumpall實例級別邏輯備份

pg_dumpall常用的備份選項：

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

-a,--data-only 只備份數據；

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

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

-s,--schema-only 只備份表結構；

建議每天對角色和表空間定義等全局對象進行備份，但不建議每天使用pg_dumpall來備份全庫數據，因為pg_dumpall僅支持導出為SQL文本格式，而使用這種龐大的SQL文本備份來進行全庫級別的數據庫恢復時及其耗時的，所以一般只建議使用pg_dumpall來備份全局對象而非全庫數據。