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