PostgreSQL基于PITR的數(shù)據(jù)恢復(fù)實(shí)踐

原創(chuàng) 精選

作者：吳守陽(yáng) 2024-11-15 08:00:00

數(shù)據(jù)庫(kù) PostgreSQL

作者 | 吳守陽(yáng)

審校 | 重樓

1、概述

2、主要模式

3、回復(fù)步驟介紹

4、前置條件

5、基礎(chǔ)備份

6、操作示例

基于recovery_target_xid恢復(fù)

基于recovery_target_name恢復(fù)

基于recovery_target_lsn恢復(fù)

基于recovery_target_time恢復(fù)

7、注意事項(xiàng)

8、總結(jié)

概述

PostgreSQL 的點(diǎn)時(shí)間恢復(fù)（PITR，Point-In-Time Recovery）是一種強(qiáng)大的恢復(fù)機(jī)制，能夠讓用戶將數(shù)據(jù)庫(kù)恢復(fù)到特定的時(shí)間點(diǎn)，從而有效應(yīng)對(duì)數(shù)據(jù)丟失或損壞的情況。這種機(jī)制在處理誤操作（如數(shù)據(jù)刪除、意外更改）時(shí)尤其重要，用戶可以通過(guò)將數(shù)據(jù)庫(kù)回滾到誤操作發(fā)生之前的狀態(tài)，確保數(shù)據(jù)的完整性和一致性。PITR 通過(guò)結(jié)合定期備份和持續(xù)的事務(wù)日志（WAL）歸檔，使得用戶能夠在發(fā)生故障時(shí)精確地選擇恢復(fù)的時(shí)間點(diǎn)，從而最大限度地減少數(shù)據(jù)損失，并為數(shù)據(jù)庫(kù)管理提供靈活性和可靠性。

主要模式

1. 恢復(fù)到特定時(shí)間點(diǎn)(recovery_target_time)

這種模式允許用戶指定一個(gè)時(shí)間點(diǎn)，數(shù)據(jù)庫(kù)將恢復(fù)到那個(gè)時(shí)刻的狀態(tài)。

2.恢復(fù)到特定 LSN(recovery_target_lsn)

LSN 是 PostgreSQL 中的另一種標(biāo)識(shí)方式，表示 WAL 日志中的一個(gè)特定位置。

3. 恢復(fù)到某個(gè)標(biāo)簽(recovery_target_name)

PostgreSQL 允許用戶使用標(biāo)簽（標(biāo)記）來(lái)執(zhí)行恢復(fù).

4.恢復(fù)到特定事務(wù) ID(recovery_target_xid)

用戶還可以根據(jù)特定的事務(wù) ID（XID）進(jìn)行恢復(fù)。

恢復(fù)步驟介紹

作為數(shù)據(jù)庫(kù)DBA要定期巡查數(shù)據(jù)備份情況，當(dāng)意識(shí)到數(shù)據(jù)或表被誤刪后，方便利用備份數(shù)據(jù)讀取運(yùn)行庫(kù)的歸檔數(shù)據(jù)進(jìn)行重放。

步驟1：基礎(chǔ)備份

步驟2：模擬誤操作，確定要重放到的節(jié)點(diǎn)

步驟3：切換wal日志

步驟4：修改端口，配置參數(shù)

步驟5：touch recovery.signal --啟動(dòng)重放模式

步驟6：?jiǎn)?dòng)數(shù)據(jù)庫(kù)，常看表數(shù)據(jù)

步驟7：數(shù)據(jù)遷移至主庫(kù)

步驟8：刪除備份庫(kù)

前置條件

##源庫(kù)已開(kāi)啟歸檔
vim postgresql.conf
archive_mode = on 
archive_command = 'test ! -f /jesong/pgdata/archive/%f && cp %p /jesong/pgdata/archive/%f'

基礎(chǔ)備份

##基礎(chǔ)備份
shell > pg_basebackup -h 127.0.0.1 -U postgres -p 5432 -Xs -v -Fp -P -r 100M -R -D /jesong/zz/
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/A3000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_3357921"
2686999/2686999 kB (100%), 1/1 tablespace 
pg_basebackup: write-ahead log end point: 0/A3000138
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
##備份驗(yàn)證
shell > /usr/pgsql-16/bin/pg_verifybackup /jesong/zz/
backup successfully verified
##查看備份文件信息
shell > more /jesong/zz/backup_label 
START WAL LOCATION: 0/A3000028 (file 0000000100000000000000A3)
CHECKPOINT LOCATION: 0/A3000060
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2024-11-01 15:18:13 CST
LABEL: pg_basebackup base backup
START TIMELINE: 1

操作示例

1、基于recovery_target_xid恢復(fù)

#模擬誤操作
postgres=# create table pg_text(id int,remark varchar);
postgres=# insert into pg_text(id ,remark) values (1,'111');
postgres=# insert into pg_text(id ,remark) values (2,'111');
postgres=# select txid_current(); --記錄回訪txid
 txid_current 
--------------
 1061

postgres=# drop table pg_text; --模擬誤操作
postgres=# select pg_switch_wal(); --wal日志落盤(pán)并歸檔
 pg_switch_wal 
---------------
 0/A6002520
(1 row)

修改備份庫(kù)配置文件：

data_directory = '/jesong/zz' --指定備份文件目錄 
port = 5433 --指定新的端口
recovery_target_xid = '1061' --指定要回放txid
restore_command = 'cp /jesong/pgdata/archive/%f %p' --在備份指定其回放時(shí)，從歸檔路徑中尋找歷史wal

啟動(dòng)備份庫(kù)：

touch recovery.signal --啟動(dòng)回放模式
chown -R postgres:postgres /jesong/zz/ --授權(quán)
su - postgres -c '/usr/pgsql-16/bin/pg_ctl start -D /jesong/zz/' --啟動(dòng)

驗(yàn)證：

[root@test19-server07 jesong]# psql -U postgres -h 127.0.0.1 -p 5433 -W 
postgres=# \d 
 List of relations
 Schema | Name | Type | Owner 
--------+---------+-------+----------
 public | bak | table | postgres
 public | foo | table | postgres
 public | lxs | table | postgres
 public | pg_text | table | postgres

postgres=# select * from pg_text;
 id | remark 
----+--------
 1 | 111
 2 | 111
(2 rows)

當(dāng)前數(shù)據(jù)庫(kù)為只讀狀態(tài)，通過(guò)下面命令開(kāi)啟讀寫(xiě)

select pg_wal_replay_resume();

2、基于recovery_target_name恢復(fù)

#模擬誤操作
postgres=# create table pg_1(id int,remark varchar);
postgres=# insert into pg_1(id ,remark) values (1,'111');
postgres=# insert into pg_text(id ,remark) values (2,'111');
postgres=# select pg_create_restore_point('restore_point'); ---命名恢復(fù)點(diǎn)
create_restore_point 
-------------------------
 0/AC0000F8
(1 row)
postgres=# drop table pg_text;
postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/AC000D48
(1 row)

修改配置參數(shù)：

vim postgresql.conf
data_directory = '/jesong/zz' --指定備份文件目錄 
port = 5433 --指定新的端口
recovery_target_name = 'restore_point' ---恢復(fù)點(diǎn)名稱(chēng)
restore_command = 'cp /jesong/zz/archive/%f %p' --從備份庫(kù)歸檔路徑中尋找歷史wal

啟動(dòng)備份庫(kù)：

touch recovery.signal --啟動(dòng)回放模式
rm -fr /jesong/zz/archive && cp /jesong/pgdata/archive/* /jesong/zz/archive/* --- 將原庫(kù)WAL歸檔日志，拷貝到備份庫(kù)的歸檔目錄中
chown -R postgres:postgres /jesong/zz/ --授權(quán)
su - postgres -c '/usr/pgsql-16/bin/pg_ctl start -D /jesong/zz/' --啟動(dòng)

3、基于recovery_target_lsn恢復(fù)

#模擬誤操作
postgres=# create table pg_1(id int,remark varchar);
postgres=# insert into pg_1(id ,remark) values (1,'111');
postgres=# insert into pg_1(id ,remark) values (2,'111');
postgres=# SELECT pg_current_wal_lsn();
 pg_current_wal_lsn 
--------------------
 0/B001B430

postgres=# drop table pg_1 ;
postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/B001BFC8
(1 row)
#使用這個(gè)函數(shù)來(lái)繼續(xù)重放
postgres=# SELECT pg_wal_replay_resume();

修改配置文件：

vim postgresql.conf
data_directory = '/jesong/zz' --指定備份文件目錄 
port = 5433 --指定新的端口
recovery_target_lsn = '0/B001B430' ---恢復(fù)點(diǎn)名稱(chēng)
restore_command = 'cp /jesong/zz/archive/%f %p' --從備份庫(kù)歸檔路徑中尋找歷史wal

啟動(dòng)備份庫(kù)：

touch recovery.signal --啟動(dòng)回放模式
rm -fr /jesong/zz/archive && cp /jesong/pgdata/archive/* /jesong/zz/archive/* --- 將原庫(kù)WAL歸檔日志，拷貝到備份庫(kù)的歸檔目錄中
chown -R postgres:postgres /jesong/zz/ --授權(quán)
su - postgres -c '/usr/pgsql-16/bin/pg_ctl start -D /jesong/zz/' --啟動(dòng)
#使用這個(gè)函數(shù)來(lái)繼續(xù)重放
postgres=# SELECT pg_wal_replay_resume();

4、基于recovery_target_time恢復(fù)

#模擬誤操作
postgres=# create table pg_2(id int,remark varchar);
postgres=# insert into pg_2(id ,remark) values (1,'111');
postgres=# insert into pg_2(id ,remark) values (2,'111');
postgres=# SELECT current_timestamp;
 current_timestamp 
-------------------------------
 2024-11-04 14:57:34.657866+08
(1 row)

postgres=# drop table pg_2;
postgres=# select pg_switch_wal();

修改配置文件：

vim postgresql.conf
data_directory = '/jesong/zz' --指定備份文件目錄 
port = 5433 --指定新的端口
recovery_target_time= '2024-11-04 14:57:34.657866+08' ---恢復(fù)時(shí)間點(diǎn)
restore_command = 'cp /jesong/zz/archive/%f %p' --從備份庫(kù)歸檔路徑中尋找歷史wal

啟動(dòng)備份庫(kù)：

touch recovery.signal --啟動(dòng)回放模式
rm -fr /jesong/zz/archive && cp /jesong/pgdata/archive/* /jesong/zz/archive/* --- 將原庫(kù)WAL歸檔日志，拷貝到備份庫(kù)的歸檔目錄中
chown -R postgres:postgres /jesong/zz/ --授權(quán)
su - postgres -c '/usr/pgsql-16/bin/pg_ctl start -D /jesong/zz/' --啟動(dòng)
#使用這個(gè)函數(shù)來(lái)繼續(xù)重放
postgres=# SELECT pg_wal_replay_resume();

注意事項(xiàng)

備份與歸檔：要實(shí)現(xiàn)有效的 PITR，必須在使用之前定期進(jìn)行完整備份，同時(shí)確保 WAL 日志歸檔（可用 archive_mode 和 archive_command 配置進(jìn)行設(shè)置）。
測(cè)試恢復(fù)流程：定期對(duì)恢復(fù)流程進(jìn)行測(cè)試，以確保在出現(xiàn)數(shù)據(jù)丟失或損壞時(shí)能夠快速恢復(fù)。

總結(jié)

PostgreSQL 的 PITR 提供了一種靈活的方式來(lái)恢復(fù)數(shù)據(jù)庫(kù)，能夠根據(jù)用戶的需求選擇特定的恢復(fù)模式。通過(guò)使用時(shí)間點(diǎn)、事務(wù) ID 或 LSN，用戶可以有效地管理和恢復(fù)數(shù)據(jù)，減少因誤操作或故障造成的損失。

作者介紹

吳守陽(yáng)，51CTO社區(qū)編輯，擁有8年DBA工作經(jīng)驗(yàn)，熟練管理MySQL、Redis、MongoDB等開(kāi)源數(shù)據(jù)庫(kù)。精通性能優(yōu)化、備份恢復(fù)和高可用性架構(gòu)設(shè)計(jì)。善于故障排除和自動(dòng)化運(yùn)維，保障系統(tǒng)穩(wěn)定可靠。具備良好的團(tuán)隊(duì)合作和溝通能力，致力于為企業(yè)提供高效可靠的數(shù)據(jù)庫(kù)解決方案。

責(zé)任編輯：華軒來(lái)源： 51CTO