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

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

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

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

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

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

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

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

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

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

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

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

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

恢復(fù)成功，也就是數(shù)據(jù)庫(kù)服務(wù)啟動(dòng)成功。這個(gè)時(shí)候我們?cè)L問(wèn)數(shù)據(jù)庫(kù)，它是作為歸檔狀態(tài)存在的，所以只能讀，不能寫(xiě)操作。

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

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

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

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

此時(shí)的$PGDATA目錄下的pg_tblspc目錄下有一個(gè)軟鏈接：

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

插入實(shí)驗(yàn)數(shù)據(jù)

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

開(kāi)始備份

建立備份文件存放路徑

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

查看備份的文件：

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

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

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

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

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

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

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

按時(shí)間點(diǎn)恢復(fù)

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

啟動(dòng)數(shù)據(jù)庫(kù)

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

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

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

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

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

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

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

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

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

可以看到有三個(gè)表，分別是emp，bonus，salgrade，庫(kù)名是test。

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

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

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

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

pg_dump的恢復(fù)命令

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

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

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

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

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

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

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

歸檔模式的時(shí)候，必須使用pg_restore工具來(lái)進(jìn)行恢復(fù)

以下仍然是單表的備份和恢復(fù)，備份文件名做了一個(gè)時(shí)間格式化，恢復(fù)的時(shí)候是使用pg_restore命令，使用psql將會(huì)報(bào)錯(cuò)。

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

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

可以看到有建庫(kù)的SQL語(yǔ)句，因?yàn)槭褂昧?C參數(shù)，一般情況下是不使用--insecres參數(shù)，如果數(shù)據(jù)庫(kù)比較大的話，會(huì)非常的費(fèi)時(shí)間，同樣的，恢復(fù)的時(shí)候也非常費(fèi)時(shí)間。

使用命令行恢復(fù)

自定義歸檔模式備份單庫(kù)

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

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

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

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

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

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

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

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

帶schema的備份：

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

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

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

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

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

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

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

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

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

啟動(dòng)postgresql

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

yum安裝barman

由于barman是python寫(xiě)的，且有些功能需要使用postgresql的工具，安裝時(shí)會(huì)有些依賴

修改barman的公共配置

修改barman對(duì)postgresql server的私有配置

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

創(chuàng)建復(fù)制槽

barman check

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

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

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

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

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

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

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

查看備份列表

查看postgresql server

遠(yuǎn)程執(zhí)行switch-wal

檢查backupid

查看文件

–target參數(shù)包括：

備份恢復(fù)

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

更多關(guān)于 PostgreSQL 系列的學(xué)習(xí)文章，請(qǐng)參閱：PostgreSQL 數(shù)據(jù)庫(kù)，本系列持續(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ù)交流社群，歡迎從事后端開(kāi)發(fā)、運(yùn)維技術(shù)進(jìn)群（備注崗位，已在技術(shù)交流群的請(qǐng)勿重復(fù)添加）。主要以技術(shù)交流、內(nèi)推、行業(yè)探討為主，請(qǐng)文明發(fā)言。廣告人士勿入，切勿輕信私聊，防止被騙。

掃碼加我好友，拉你進(jìn)群

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

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

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

阿里再次大改革，江湖再無(wú) P8 了。。。

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

良心推薦！這 5 款免費(fèi)開(kāi)源軟件一年為你節(jié)省上萬(wàn)元

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

面試官：如何快速修改 Docker 鏡像默認(rèn)存儲(chǔ)位置？

PS：因?yàn)楣娞?hào)平臺(tái)更改了推送規(guī)則，如果不想錯(cuò)過(guò)內(nèi)容，記得讀完點(diǎn)一下“在看”，加個(gè)“星標(biāo)”，這樣每次新文章推送才會(huì)第一時(shí)間出現(xiàn)在你的訂閱列表里。點(diǎn)“在看”支持我們吧!

南京兆柏?cái)?shù)據(jù)恢復(fù)中心