PostgreSQL12中實現增量備份與任意時間點恢復
在postgres中,能夠使用簡單的邏輯備份工具pg_dump做某個資料庫的完整備份
問題
如果資料庫在執行過程中發生故障, 使用邏輯備份只能將資料庫還原到備份時刻, 無法恢復到故障發生前的那個時刻;
由於誤操作修改或刪除了重要資料, 需要還原到誤操作前的那個時刻
解決方案
增量備份和任意時間點恢復
實現增量備份的思路是
1. 記錄資料庫系統的操作記錄(WAL歸檔)
2. 在某一時刻進行一次完整的資料庫備份
3. 需要恢復時,還原上一次完整的資料庫備份,根據操作記錄恢復資料庫至指定的某個時刻(即可以實現PITR 時間點恢復)
操作
1、配置資料庫、vim postgresql.conf
wal_level = replica archive_mode = on archive_command = 'cp %p /home/test/archive/%f && echo %f >> /home/test/archive/archive.list'
wal_keep_segments=1024
2、重啟資料庫
pg_ctl -D /home/test/data/ -l /home/test/log restart
3、此後資料庫的操作都會記錄wal日誌檔案到/home/test/archive目錄下
[test@localhost bin]$ ls /home/test/archive/ 000000010000000000000001 000000010000000000000002 000000010000000000000003 000000010000000000000004 archive.list
4、建立基礎備份,可以使用pg_basebackup
pg_basebackup -D /home/test/back -Fp -v -P -Upostgres -R
5、測試資料準備
postgres=# create table t2(a int); CREATE TABLE postgres=# insert into t2 values (generate_series(1,1000000)); INSERT 0 1000000
記錄時間:2020年 05月 09日 星期六 21:46:57 CST
postgres=# create table t3(a int); CREATE TABLE postgres=# insert into t3 values (generate_series(1,1000000)); INSERT 0 1000000
6、假定資料庫故障,停止資料庫,備份故障data並將基礎備份的back替換為新data
mv data data_back mv back data
7、修改data目錄下postgresql.auto.conf
restore_command = 'cp /home/test/archive/%f %p > /home/test/archive/recovery.log 2>&1 ' recovery_target_time = '2020-05-09 21:46:57'
recovery_target_time表示要恢復到的時間點
8、啟動資料庫
/usr/pgsql-12/bin/./pg_ctl -D data/ start
9、檢視資料,可知t3未被恢復
[test@localhost ~]$ psql -Upostgres psql (12.2) 輸入 "help" 來獲取幫助資訊. postgres=# \dt 關聯列表 架構模式 | 名稱 | 型別 | 擁有者 ----------+------+--------+---------- public | t1 | 資料表 | postgres public | t2 | 資料表 | postgres (2 行記錄)
日誌資訊:
2020-05-09 21:40:15.638 CST [13422] 日誌: 資料庫上次關閉時間為 2020-05-08 23:24:35 CST 2020-05-09 21:40:15.686 CST [13420] 日誌: 資料庫系統準備接受連線 2020-05-09 22:05:18.986 CST [23055] 日誌: 資料庫系統中斷;上一次的啟動時間是在2020-05-09 21:53:25 CST 2020-05-09 22:05:19.003 CST [23055] 日誌: 正在進入備用模式 2020-05-09 22:05:19.019 CST [23055] 日誌: 從歸檔中恢復日誌檔案 "000000010000000000000009" 2020-05-09 22:05:19.040 CST [23055] 日誌: redo 在 0/9000060 開始 2020-05-09 22:05:19.041 CST [23055] 日誌: 在0/9000138上已到達一致性恢復狀態 2020-05-09 22:05:19.042 CST [23053] 日誌: 資料庫系統準備接受只讀請求的連線 2020-05-09 22:05:19.055 CST [23055] 日誌: 從歸檔中恢復日誌檔案 "00000001000000000000000A" 2020-05-09 22:05:19.384 CST [23055] 日誌: 從歸檔中恢復日誌檔案 "00000001000000000000000B" 2020-05-09 22:05:19.837 CST [23055] 日誌: 從歸檔中恢復日誌檔案 "00000001000000000000000C" 2020-05-09 22:05:20.343 CST [23055] 日誌: 從歸檔中恢復日誌檔案 "00000001000000000000000D" 2020-05-09 22:05:20.986 CST [23055] 日誌: 恢復停止在事物 494 提交之前, 時間 2020-05-09 21:47:16.174754+08 2020-05-09 22:05:20.986 CST [23055] 日誌: 恢復操作已暫停 2020-05-09 22:05:20.986 CST [23055] 提示: 執行 pg_wal_replay_resume() 以繼續.
the end;