進階數(shù)據(jù)庫系列（二十）：PostgreSQL 數(shù)據(jù)庫備份與恢復(fù)

點擊下方名片，設(shè)為星標(biāo)！

回復(fù)“1024”獲取2TB學(xué)習(xí)資源！

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

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

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

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

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

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

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

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

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

在恢復(fù)過程中，會拷貝歸檔文件，進行數(shù)據(jù)恢復(fù)。

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

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

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

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

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

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

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

插入實驗數(shù)據(jù)

查看是否正確插入數(shù)據(jù):

開始備份

建立備份文件存放路徑

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

查看備份的文件：

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

停止數(shù)據(jù)庫：

刪除原庫的數(shù)據(jù)文件：

解壓備份文件到對應(yīng)路徑：要注意是解壓到哪里

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

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

可以按時間線恢復(fù)到最新

按時間點恢復(fù)

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

啟動數(shù)據(jù)庫

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

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

最終確認是否恢復(fù)正常：

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

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

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

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

pg_dump 熱備單表和該表的數(shù)據(jù)熱恢復(fù)

備份前數(shù)據(jù)庫test的表內(nèi)容：

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

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

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

如果希望恢復(fù)的時候不需要切換數(shù)據(jù)庫，那么，應(yīng)該使用參數(shù)大C，命令如下：

生成的備份文件內(nèi)容如下

pg_dump的恢復(fù)命令

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

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

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

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

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

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

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

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

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

pg_dump 熱備單庫熱恢復(fù)（熱恢復(fù)指的是不需要停止數(shù)據(jù)庫服務(wù)）

備份的文件內(nèi)容

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

使用命令行恢復(fù)

自定義歸檔模式備份單庫

利用歸檔文件恢復(fù)

需要提前建立test這個數(shù)據(jù)庫

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

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

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

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

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

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

帶schema的備份：

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

更多關(guān)于 PostgreSQL 系列的學(xué)習(xí)文章，請參閱：PostgreSQL 數(shù)據(jù)庫，本系列持續(xù)更新中。

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

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

非常的方便，簡單就可以把數(shù)據(jù)庫內(nèi)的所有用戶信息備份了。

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

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

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

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

修改postgresql.conf配置

修改pg_hba.conf配置

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

啟動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)建復(fù)制槽

barman check

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

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

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

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

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

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

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

查看備份列表

查看postgresql server

遠程執(zhí)行switch-wal

檢查backupid

查看文件

–target參數(shù)包括：

備份恢復(fù)

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

更多關(guān)于 PostgreSQL 系列的學(xué)習(xí)文章，請參閱：PostgreSQL 數(shù)據(jù)庫，本系列持續(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讀者專屬技術(shù)群

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

掃碼加我好友，拉你進群

推薦閱讀 點擊標(biāo)題可跳轉(zhuǎn)

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

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

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

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

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

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

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

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