PostgreSQL物理備份與恢復(fù)
點(diǎn)擊上方“IT那活兒”，關(guān)注后了解更多內(nèi)容，不管IT什么活兒，干就完了?。?！
本文主要介紹PostgreSQL的基礎(chǔ)備份和基于時(shí)間點(diǎn)PITR(Point-in-Time Recovery)的恢復(fù)方法。如果數(shù)據(jù)庫(kù)在運(yùn)行過(guò)程中發(fā)生故障，需要恢復(fù)到故障發(fā)生前的某個(gè)時(shí)間點(diǎn)，或者在使用過(guò)程中由于誤操作導(dǎo)致刪除了重要數(shù)據(jù)，需要將數(shù)據(jù)還原到誤操作前的某個(gè)時(shí)間點(diǎn)的狀態(tài),則可以借助基礎(chǔ)備份來(lái)實(shí)施恢復(fù)。
postgres=# select name,setting,unit from pg_settings where category=Preset Options;
postgres=# select version();
- minimal --不能通過(guò)基礎(chǔ)備份和wal日志恢復(fù)數(shù)據(jù)庫(kù)。
- replica = 9.6版本以前的archive和hot_standby --該級(jí)別支持wal歸檔和復(fù)制。
- logical --在replica級(jí)別的基礎(chǔ)上添加了支持邏輯解碼所需的信息。
查看當(dāng)前數(shù)據(jù)庫(kù)的wal level。$ pg_controldata -D /pgsql |grep wal_level
或者
cat postgresql.conf |grep wal_level
vi postgresql.conf
archive_mode = on
如果原來(lái)是off,調(diào)整為on, 需要重啟數(shù)據(jù)庫(kù)生效。cat postgresql.conf |grep archive_mode
首先要?jiǎng)?chuàng)建用于存放wal歸檔文件目錄, 數(shù)據(jù)庫(kù)啟動(dòng)用戶需要寫權(quán)限。vi postgresql.conf
archive_command = cp %p /pgsql/data/archive/%f
pg_ctl -D /pgsql/data/ reload
4）建議設(shè)置work process,sender進(jìn)程數(shù),這樣才可以通過(guò)pg_basebackup使用流復(fù)制協(xié)議進(jìn)行備份。必須設(shè)置大于0,每個(gè)流復(fù)制連接需要一個(gè)wal sender進(jìn)程。max_worker_processes = 16
max_wal_senders = 8
5）建議設(shè)置wal_keep_segments,確保大型的數(shù)據(jù)庫(kù)在比較長(zhǎng)的備份時(shí)間周期內(nèi)wal日志不被覆蓋。注意配置之后，pg_wal至少會(huì)占用wal_keep_segments * wal文件大小 的空間。需要預(yù)留好空間。cd /pgsql/data
mv postgresql.conf postgresql.conf.bak20220227
vi postgresql.conf
listen_addresses = *
port = 5432
superuser_reserved_connections = 20
wal_level = archive
archive_mode = on
archive_command = cp %p /pgsql/data/archive/%f
unix_socket_directories = /var/lib/postgres/
unix_socket_permissions = 0700
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 10
vacuum_cost_delay = 10
bgwriter_delay = 10ms
synchronous_commit = off
wal_writer_delay = 10ms
log_destination = csvlog
logging_collector = on
log_directory = pg_log
log_filename =postgresql-%Y-%m-%d_%H%M%S.log
log_file_mode = 0600
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
log_timezone = PRC
datestyle = iso, mdy
timezone = PRC
default_text_search_config = pg_catalog.english
max_worker_processes = 16
max_wal_senders = 8
wal_keep_segments = 1024
PostgreSQL提供pg_switch_wal()函數(shù)可以手工切換WAL日志，執(zhí)行pg_switch_wal()后，WAL會(huì)切換到新的日志，這時(shí)會(huì)將WAL日志歸檔到配置的歸檔路徑。
完成上述準(zhǔn)備工作后, 就可以開始進(jìn)行基礎(chǔ)備份了,我們使用pg_basebackup工具來(lái)完成備份。pg_basebackup備份操作不會(huì)影響連接到該數(shù)據(jù)庫(kù)的其他客戶端，并且可以被用于基于時(shí)間點(diǎn)的恢復(fù)。pg_basebackup創(chuàng)建的是整個(gè)數(shù)據(jù)庫(kù)集群文件的一份二進(jìn)制副本，不能備份單個(gè)數(shù)據(jù)庫(kù)或數(shù)據(jù)庫(kù)對(duì)象。pg_basebackup的主要參數(shù)選項(xiàng)如下：-D directory 保存?zhèn)浞輸?shù)據(jù)的本地目錄。 --format=format plain|tar-r rate 從該服務(wù)器傳輸數(shù)據(jù)的最大傳輸速率。-R 在輸出目錄中寫入一個(gè)最小的recovery.conf恢復(fù)配置文件。-S slotanme 該選項(xiàng)需要和-X stream一起使用。它導(dǎo)致WAL流使用指定復(fù)制槽。-X stream 如果為none,則備份不會(huì)包含日志，如果fetch，在備份末尾收集日志文件，如果為stream,在備份的同時(shí)備份日志。 --wal-method = none | stream | fetch使用pg_basebackup備份數(shù)據(jù)庫(kù)必須由超級(jí)用戶或者具有REPLICATION權(quán)限的用戶發(fā)起。$ psql
psql.bin (10.12)
Type "help" for help.
pg_basebackup使用replication協(xié)議連接到數(shù)據(jù)庫(kù)實(shí)例上，所以pg_hba.conf必須允許replication連接。$ cp pg_hba.conf pg_hba.conf.bak20200920
$ vi pg_hba.conf
注意：能通過(guò)pg_ctl reload方式生效的參數(shù)，不需要重啟數(shù)據(jù)庫(kù)；需要通過(guò)重啟來(lái)生效的，則無(wú)法通過(guò)pg_ctl reload方式生效。記錄當(dāng)前xid，在數(shù)據(jù)庫(kù)中以超級(jí)用戶執(zhí)行如下命令：創(chuàng)建一個(gè)備份標(biāo)簽：我們可以使用pg_start_backup()函數(shù)在$PGDATA目錄中創(chuàng)建一個(gè)關(guān)于備份信息的備份標(biāo)簽文件，也被稱為backup_label，其中包括了開始時(shí)間和標(biāo)簽字符串。該函數(shù)也會(huì)在$PGDATA目錄中創(chuàng)建一個(gè) 名為tablespace_map的表空間映射文件，如果在pg_tblspc/中有一個(gè)或者多個(gè)表空間符號(hào)鏈接存在，該文件會(huì)包含它們的信息。如果你需要從備份中恢復(fù)，這兩個(gè)文件對(duì)于備份的 完整性都至關(guān)重要。使用一個(gè)具有運(yùn)行 pg_start_backup 權(quán)利的用戶（超級(jí)用戶，或者被授予在該 函數(shù)上 EXECUTE 的用戶）連接到服務(wù)器（不在乎是哪個(gè)數(shù)據(jù)庫(kù)）并且發(fā)出命令：默認(rèn)情況下，pg_start_backup會(huì)花費(fèi)很長(zhǎng)時(shí)間來(lái)完成。這是因?yàn)樗鼤?huì)執(zhí)行一個(gè)檢查點(diǎn)，而檢查點(diǎn)所需要的I/O在相當(dāng)一段時(shí)間內(nèi)將會(huì)被傳播，默認(rèn)情況下這段時(shí)間是內(nèi)部檢查點(diǎn)間隔的一半（參見(jiàn)配置參數(shù)checkpoint_completion_target）。這通常是你所希望的，因?yàn)樗軐?duì)查詢處理的影響最小化。SELECT pg_start_backup(label, true);
這會(huì)使檢查點(diǎn)盡可能快地被完成。注意，如果服務(wù)器在備份期間崩潰，必須從$PGDATA 目錄手動(dòng)刪除backup_label文件，才能重新啟動(dòng)。創(chuàng)建數(shù)據(jù)庫(kù)服務(wù)器的一個(gè)基礎(chǔ)備份并將它存儲(chǔ)在本地目錄/pgsql/data/backup。pg_basebackup -h ***.***.223.128 -D /pgsql/data/backup
要使用備份來(lái)恢復(fù)數(shù)據(jù)，你需要保留所有在備份期間以及之后生成的WAL段文件。為了更方便地進(jìn)行這些工作，基礎(chǔ)備份過(guò)程中會(huì)創(chuàng)建一個(gè)備份歷史文件，它將被立刻存儲(chǔ)到WAL歸檔目錄。該文件以備份中你需要的第一個(gè)WAL段文件命名。如果開始的WAL文件是000000010000000000000059，則備份歷史文件將被命名為000000010000000000000059. 00000060.backup.（文件名的第二部分表明WAL文件中的一個(gè)準(zhǔn)確位置，一般可以被忽略）。一旦你已經(jīng)安全地歸檔了備份和在備份過(guò)程中被使用的WAL段文件（如備份歷史文件中所指定的） ，所有名字在數(shù)字上低于備份歷史文件中記錄值的已歸檔WAL段對(duì)于恢復(fù)文件系統(tǒng)備份就不再需要了，可以將它們刪除。備份歷史文件是一個(gè)很小的文本文件。它包含你指定給pg_basebackup的標(biāo)簽字符串，以及備份的起止時(shí)間以及起止WAL段。如果你使用該標(biāo)簽來(lái)標(biāo)識(shí)相關(guān)備份文件，則已歸檔的歷史文件可以說(shuō)明需要哪個(gè)備份文件進(jìn)行恢復(fù)。備份完成后,在pg_wal目錄下生成了一個(gè)000000010000000000000059.00000060.backup文件。
接下來(lái)，我們將演示如何實(shí)現(xiàn)以及如何設(shè)計(jì)一個(gè)恢復(fù)方案。要恢復(fù)到任意時(shí)間點(diǎn)，我們就必須告訴恢復(fù)進(jìn)程一個(gè)點(diǎn)，這個(gè)點(diǎn)可以是時(shí)間，字符串，或者xid。- 時(shí)間點(diǎn)，恢復(fù)到這個(gè)時(shí)間點(diǎn)之后的WAL中的第一個(gè)事務(wù)結(jié)束位置。
- 字符串,這個(gè)是需要通過(guò)pg_create_restore_point函數(shù)來(lái)創(chuàng)建的一個(gè)還原點(diǎn)，需要超級(jí)用戶調(diào)用這個(gè)函數(shù)。
- xid，就是恢復(fù)到指定事務(wù)的結(jié)束位置。
既然我們已經(jīng)知道了數(shù)據(jù)庫(kù)可以恢復(fù)到指定的位置，接下來(lái)我們模擬的就是當(dāng)數(shù)據(jù)被誤刪除后，借助更早時(shí)候的基礎(chǔ)備份和WAL歸檔日志來(lái)將數(shù)據(jù)恢復(fù)到誤操作前的某個(gè)時(shí)間點(diǎn)。1. 創(chuàng)建測(cè)試數(shù)據(jù)創(chuàng)建測(cè)試表ttt并生成測(cè)試數(shù)據(jù)。這個(gè)文件可能還沒(méi)有歸檔,我們可以主動(dòng)觸發(fā)歸檔切換wal日志：記錄當(dāng)前哈希值,用于恢復(fù)后的比對(duì)。刪除表ttt中的部分?jǐn)?shù)據(jù)，PostgreSQL默認(rèn)是自動(dòng)提交，因此DML語(yǔ)句不需要commit。記錄第一次刪除數(shù)據(jù)后的時(shí)間T2：這個(gè)文件可能還沒(méi)有歸檔,我們可以主動(dòng)觸發(fā)歸檔：將基礎(chǔ)備份和歸檔wal日志拷貝到***.***.***.129這臺(tái)服務(wù)器。基礎(chǔ)備份選擇將要恢復(fù)的時(shí)間點(diǎn)之前的一個(gè)備份，歸檔則選擇在此之后的所有歸檔文件。配置recovery.conf，指定將數(shù)據(jù)恢復(fù)到xid：562。啟動(dòng)數(shù)據(jù)庫(kù)。$ pg_ctl -D /pgsql/data start
這時(shí)候數(shù)據(jù)庫(kù)還是read only模式，recovery還沒(méi)有結(jié)束，如果不執(zhí)行select pg_wal_replay_resume();開啟新事務(wù)將會(huì)報(bào)錯(cuò)：postgres=# select txid_current();
ERROR: cannot execute txid_current() during recovery
postgres=#
postgres=# select pg_wal_replay_resume();
pg_wal_replay_resume
----------------------
(1 row)
檢查數(shù)據(jù)已經(jīng)恢復(fù)到 delete from ttt where relacl is null 之后的狀態(tài)。postgres=# select count(*) from ttt;
recovery.conf已被自動(dòng)重命名為recovery.done。如果你還要執(zhí)行其他后續(xù)的恢復(fù)操作，recovery.conf必須被重置。另外可以根據(jù)時(shí)間點(diǎn)來(lái)進(jìn)行恢復(fù)，在recovery.conf文件里面配置recovery_target_time即可。
本文來(lái)源：IT那活兒（上海新炬王翦團(tuán)隊(duì)）
文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請(qǐng)注明本文地址：http://systransis.cn/yun/129592.html
