進階數據庫系列（二十）：PostgreSQL 數據庫備份與恢復

點擊下方名片，設為星標！

回復“1024”獲取2TB學習資源！

前面介紹了 PostgreSQL 事務與并發(fā)控制、主從復制、高可用方案、基于 Patroni 高可用架構部署及故障切換、 基于 repmgr 高可用架構實踐、基于 pgpool 實現讀寫分離實踐等相關的知識點，今天我將詳細的為大家介紹 PostgreSQL 數據庫備份與恢復相關知識，希望大家能夠從中收獲多多！如有幫助，請點在看、轉發(fā)支持一波！！！

今天將就比較常用的postgresql數據庫備份做一個比較詳盡的總結。冷備冷恢復(第一種物理備份方式)

此種方式是直接備份數據庫物理文件，在pg數據庫里，這通常指的是PGDATA變量定義的文件夾，例如：

我的這個示例數據庫是安裝在了/usr/local/pgsql目錄下，那么，備份如下，文件備份到了/media目錄下了：

但需要注意的是，此種方式由于比較直接，不管是否數據庫有無IO情況，因此，備份的時候需要先停止數據庫，恢復的時候要刪除原數據庫文件，重新覆蓋回去后，才可以在啟動數據庫，如果在數據庫啟動的時候備份，那么，可能會造成數據備份不全，也可以理解為冷備方式。物理備份的恢復

那么，此種方式比較霸道，也可以說是野蠻，因此，優(yōu)點是簡單，直接，高效，缺點是需要停機維護，不能實時備份，算是冷備范疇。另一個缺點是，一般生產環(huán)境的數據庫由于經年累月的使用，數據庫一般會是非常大的，幾百G 上T也是經常的事情，而cp命令的效率是和文件大小是有關聯的，受限于此，這個時候的備份和恢復是比較困難的。更多關于 PostgreSQL 系列的學習文章，請參閱：PostgreSQL 數據庫，本系列持續(xù)更新中。熱備冷恢復(第二種物理備份方式)pg_basebackup 基本原理和步驟

pg_basebackup 也算是物理方式，是可以使用流協議。并且是熱備范疇，備份的時候不需要停數據庫，但，恢復的時候需要停數據庫（多說一句，某些項目數據庫是24小時不能停的，如果停了，可能接受不了，因此，備份前最好搞清楚自己是否能夠接受冷備）。

備份：使用pg_basebackup命令來進行備份，這個命令可以將postgresql的數據文件備份為兩個壓縮文件：base.tar和 pg_wal.tar。本別是數據文件和歸檔文件，恢復的時候，需要設置按照歸檔文件來恢復。那么，此種方式的備份可以備份自定義表空間。

恢復：需要先把備份的壓縮文件替換當前的數據文件，然后修改postgresql.conf，因為這個配置文件在data文件夾中，所以只能是在把base.tar解壓到數據庫當前數據位置，也就是我們默認初始化指定的數據保存位置data文件夾中，才能修改配置，在配置好歸檔設置以后，可以啟動pgsql服務，進行啟動恢復。

在恢復過程中，會拷貝歸檔文件，進行數據恢復。

恢復成功，也就是數據庫服務啟動成功。這個時候我們訪問數據庫，它是作為歸檔狀態(tài)存在的，所以只能讀，不能寫操作。

為了恢復數據庫寫操作，我們需要在命令行下執(zhí)行切換數據庫狀態(tài)的指令。切換成功之后，才可以進行讀寫操作。實操示例

環(huán)境介紹：數據庫主節(jié)點，IP：192.168.123.60，數據庫端口：5432，數據庫安裝路徑為/usr/local/pgsql ，管理用戶為pg1。

創(chuàng)建一個自定義表空間，創(chuàng)建表空間存放路徑。

登陸命令行，創(chuàng)建表空間

此時的$PGDATA目錄下的pg_tblspc目錄下有一個軟鏈接：

創(chuàng)建一個新庫和新表在此表空間內：

插入實驗數據

查看是否正確插入數據:

開始備份

建立備份文件存放路徑

可以看到有兩個tablespace被備份了，-z -Z 5是壓縮等級，范圍是0-9， -Ft是tar包格式備份。

查看備份的文件：

備份完成后，刪除test數據庫，看看一會能否恢復。更多關于 PostgreSQL 系列的學習文章，請參閱：PostgreSQL 數據庫，本系列持續(xù)更新中。恢復

停止數據庫：

刪除原庫的數據文件：

解壓備份文件到對應路徑：要注意是解壓到哪里

創(chuàng)建wal文件存放路徑：賦予數據庫管理用戶權限，最后一個命令不能忘

修改postgresql.conf文件：任選一種恢復方式立刻恢復

可以按時間線恢復到最新

按時間點恢復

如果不想進入備份模式，直接數據庫啟動就可以用，那么就使用promote。

啟動數據庫

查看數據庫狀態(tài)：此時的數據庫是備份狀態(tài)，因此即可。

或者是進入命令行，執(zhí)行以下函數即可：

最終確認是否恢復正常：

OK，利用 pg_basebackup 命令歸檔備份完全成功了。更多關于 PostgreSQL 系列的學習文章，請參閱：PostgreSQL 數據庫，本系列持續(xù)更新中。熱備熱恢復

首選當然是pg_dump啦，這個備份工具是和pg_restore配套的，也可以看成是一個組合。

該備份工具的特點是穩(wěn)定，高效，冷熱備份恢復都可以，可以選擇數據庫部分表備份，只備份表結構，因此，該工具的使用比較復雜，這點是相對物理備份來說的。

怎么說呢，物理備份有一種暴力的美學感覺，簡單的方法有時候更為高效。邏輯備份比較枯燥，復雜。pg_dump常用參數

pg_dump 熱備單表和該表的數據熱恢復

備份前數據庫test的表內容：

可以看到有三個表，分別是emp，bonus，salgrade，庫名是test。

現在計劃備份emp表，該表備份到pg1用戶的家目錄下（如果是其它目錄，可能會沒有權限），使用工具為pg_dump。備份命令

如果有遷移到其它數據庫，比如oracle的計劃，那么，最好還是添加參數--inserts，上面的命令修改為如下：

如果希望恢復的時候不需要切換數據庫，那么，應該使用參數大C，命令如下：

生成的備份文件內容如下

pg_dump的恢復命令

進入pg命令行直接執(zhí)行備份的SQL文件即可（執(zhí)行SQL語句前需要切換數據庫到test）：

重進一次pg 命令行，執(zhí)行查詢恢復即可，可查到數據表明恢復成功：

以上備份和恢復都是使用的純文本模式（也有人稱之為轉儲），因此，在應對存有大量數據的數據庫備份時，很可能會力不從心，可能需要歸檔備份模式，壓縮備份模式。更多關于 PostgreSQL 系列的學習文章，請參閱：PostgreSQL 數據庫，本系列持續(xù)更新中。歸檔文件分類

歸檔格式的備份文件又分為兩種，最靈活的輸出文件格式是“custom”自定義格式（使用命令項參數“-Fc”來指定），它允許對歸檔元素進行選取和重新排列，并且默認是壓縮的；另一種是tar格式（使用命令項參數“-Ft”來指定），這種格式的文件不是壓縮的，并且加載時不能重新排序，但是它也很靈活，可以用標準UNIX下的tar工具進行處理。custom自定義格式比較常用。

不帶-F參數的時候，默認是純文本模式（純文本模式備份的文件可以使用記事本打開，里面都是SQL語句）

歸檔格式的備份文件必須與pg_restore一起使用來重建數據庫，這種格式允許pg_restore選擇恢復哪些數據，甚至可以在恢復之前對需要恢復的條目重新排序。

pg_dump可以將整個數據庫備份到一個歸檔格式的備份文件中，而pg_restore則可以從這個歸檔格式的備份文件中選擇性地恢復部分表或數據庫對象，而不必恢復所有的數據。

歸檔模式的時候，必須使用pg_restore工具來進行恢復

以下仍然是單表的備份和恢復，備份文件名做了一個時間格式化，恢復的時候是使用pg_restore命令，使用psql將會報錯。

pg_dump 熱備單庫熱恢復（熱恢復指的是不需要停止數據庫服務）

備份的文件內容

可以看到有建庫的SQL語句，因為使用了-C參數，一般情況下是不使用--insecres參數，如果數據庫比較大的話，會非常的費時間，同樣的，恢復的時候也非常費時間。

使用命令行恢復

自定義歸檔模式備份單庫

利用歸檔文件恢復

需要提前建立test這個數據庫

這里說的備份文件格式是由pg_dump -Fc參數生成的文件，此類文件是二進制格式的，不可當做文本文件查看，恢復的時候必須使用pg_restore命令，例如上面的test-database.dump文件就是此類文件。查看該文件的具體屬性可以看到如下

此文件是可以利用pg_restore命令查看的，參數為-l ：

OK，將查看到的內容重定向到一個文件，那么，此重定向文件就是TOC文件了：

當然了，還可以這樣生成TOC文件，和上面的命令是等價的（TOC.txt和TOC-1.txt兩個文件是一模一樣的）：

這個時候的TOC文件是可編輯的文本文件，不需要執(zhí)行的部分刪除或者;;注釋掉就可以了。使用TOC文件：

當然，不使用TOC文件也是可以的，這也就是意味著全部還原，去掉-L參數即可：

帶schema的備份：

帶schema的恢復（-n后面接的是schema的名稱，此schema需要提前創(chuàng)建）：

更多關于 PostgreSQL 系列的學習文章，請參閱：PostgreSQL 數據庫，本系列持續(xù)更新中。

此工具是全庫備份，但，一般是不使用這個的，因為，數據庫有可能會很大，進而備份的時候出現問題，此工具可以備份用戶信息，例如下面這個命令（全局對象里包括用戶，因此，如果是僅備份用戶信息，也可以使用參數r即可）：

OK，刪除zsk這個用戶，看看能否通過命令行恢復：

非常的方便，簡單就可以把數據庫內的所有用戶信息備份了。

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

Barman (Backup and Recovery Manager-備份恢復管理器) 。

是一個用Python語言實現的PostgreSQL災難恢復管理工具，它由第二象限公司（2ndQuadrant）開源并維護。它允許我們在關鍵業(yè)務環(huán)境中執(zhí)行遠程備份，為數據庫管理員在恢復階段提供有效的數據保證。Barman最優(yōu)秀的功能包括備份元數據、增量備份、保留策略、遠程回復、WAL文件歸檔壓縮和備份。

barman 建議以一個 dedicated server 存在。盡量不要部署到 postgresql server 上，但不是不能部署到 postgresql server 主機上。barman優(yōu)點可以與postgresql的master和slaver分離部署，但是barman服務器需要安裝postgresql的一些工具，如pg_basebackup、pg_receivewal通過流復制可以同步拉取wal，如果postgresql當前的wal還沒close，barman會產生一個.partial文件實時同步，若在恢復時使用該文件需要將擴展后綴去掉barman可以一臺服務器管理多個postgresql server，提供本地及遠程恢復（遠程恢復需要配置ssh免密）提供備份相關文件的檢測及詳細查詢barman特點零數據丟失備份。保證用戶在只有一臺備份服務器的情況下達到零數據丟失。與備份服務器合作。允許備份服務器在與主服務器的流式復制不可用時，從barman獲取wal文件。可靠的監(jiān)控集成。用于監(jiān)控和創(chuàng)建有關Barman配置狀態(tài)的報告。可并行的備份和恢復。以更快的速度執(zhí)行拷貝，加快了整個備份和恢復過程。備份目錄。允許用戶在同一界面中列出、保留、刪除、存檔和恢復多個完整備份。支持多個服務器。允許您從同一位置管理多個服務器的備份和恢復。存儲備份數據。存儲來自多個PostgreSQL實例的備份數據，支持不同的版本。完全熱備份。從其他服務器執(zhí)行PostgreSQL服務器的完全熱備份，專門為備份和災難恢復而設計。增量備份和恢復。針對新的和修改過的數據生成不同的備份，從而加快處理速度。保留策略。配置自定義備份保留策略以滿足組織的需要。遠程恢復。執(zhí)行PostgreSQL服務器備份的遠程恢復。Wal文件壓縮。自定義歸檔和壓縮WAL文件和備份。備份信息。生成所有備份的常規(guī)和磁盤使用信息。地理冗余。給DBA和系統管理員為PostgreSQL數據庫的災難恢復解決方案增加另一層保障。barman安裝192.168.2.2 pg （postgresql server服務器）192.168.2.3 barman （barman備份服務器）postgresql版本11.2barman版本2.9.1yum安裝postgresql

修改postgresql.conf配置

修改pg_hba.conf配置

創(chuàng)建pg_archive，并設置用戶組。

啟動postgresql

創(chuàng)建barman和barman_streaming用戶

yum安裝barman

由于barman是python寫的，且有些功能需要使用postgresql的工具，安裝時會有些依賴

修改barman的公共配置

修改barman對postgresql server的私有配置

將192.168.2.2 pg添加到/etc/hosts中barman使用

創(chuàng)建復制槽

barman check

其中minimum redundancy requirements: FAILED可忽略，首次配置后還沒有全備，執(zhí)行全備會變?yōu)镺K。

首次配置完會產生minimum redundancy requirements: FAILED、receive-wal running:FAILED，執(zhí)行barman cron即可變?yōu)镺K，barman會執(zhí)行如下過程。

所以barman的全備是利用pg_basebackup實現，增量是利用pg_receivewal實現。barman優(yōu)點

1.可以與postgresql的master和slaver分離部署，但是barman服務器需要安裝postgresql的一些工具，如、

2.通過流復制可以同步拉取wal，如果postgresql當前的wal還沒close，barman會產生一個.partial文件實時同步，若在恢復時使用該文件需要將擴展后綴去掉

3.barman可以一臺服務器管理多個postgresql server，提供本地及遠程恢復（遠程恢復需要配置ssh免密）

4.提供備份相關文件的檢測及詳細查詢barman部分功能查看當前復制狀態(tài)

查看備份列表

查看postgresql server

遠程執(zhí)行switch-wal

檢查backupid

查看文件

–target參數包括：

備份恢復

還提供：、、、查看備份詳情

更多關于 PostgreSQL 系列的學習文章，請參閱：PostgreSQL 數據庫，本系列持續(xù)更新中。參考文章：https://blog.csdn.net/alwaysbefine/article/details/129422083 https://blog.csdn.net/weixin_39992480/article/details/100022373 https://zskjohn.blog.csdn.net/article/details/129648750讀者專屬技術群

構建高質量的技術交流社群，歡迎從事后端開發(fā)、運維技術進群（備注崗位，已在技術交流群的請勿重復添加）。主要以技術交流、內推、行業(yè)探討為主，請文明發(fā)言。廣告人士勿入，切勿輕信私聊，防止被騙。

掃碼加我好友，拉你進群

推薦閱讀 點擊標題可跳轉

突然，很多人被裁！知名公司遭曝光。。。

不雅信息發(fā)到工作群！回應：病毒入侵，已報警！

阿里再次大改革，江湖再無 P8 了。。。

這款輕量級文件傳輸工具真心強大！支持網頁版

良心推薦！這 5 款免費開源軟件一年為你節(jié)省上萬元

最受歡迎Web服務器！來看看它比Nginx牛逼在哪？

面試官：如何快速修改 Docker 鏡像默認存儲位置？

PS：因為公眾號平臺更改了推送規(guī)則，如果不想錯過內容，記得讀完點一下“在看”，加個“星標”，這樣每次新文章推送才會第一時間出現在你的訂閱列表里。點“在看”支持我們吧!

南京兆柏數據恢復中心