作者：吳守陽
PostgreSQL 的點時間恢復（PITR，Point-In-Time Recovery）是一種強大的恢復機制，能夠讓用戶將數據庫恢復到特定的時間點，從而有效應對數據丟失或損壞的情況。
作者 | 吳守陽
審校 | 重樓
目錄
1、概述
2、主要模式
3、回復步驟介紹
4、前置條件
5、基礎備份
6、操作示例
基于recovery_target_xid恢復
基于recovery_target_name恢復
基于recovery_target_lsn恢復
基于recovery_target_time恢復
7、注意事項
8、總結
概述
PostgreSQL 的點時間恢復（PITR，Point-In-Time Recovery）是一種強大的恢復機制，能夠讓用戶將數據庫恢復到特定的時間點，從而有效應對數據丟失或損壞的情況。這種機制在處理誤操作（如數據刪除、意外更改）時尤其重要，用戶可以通過將數據庫回滾到誤操作發生之前的狀態，確保數據的完整性和一致性。PITR 通過結合定期備份和持續的事務日志（WAL）歸檔，使得用戶能夠在發生故障時精確地選擇恢復的時間點，從而最大限度地減少數據損失，并為數據庫管理提供靈活性和可靠性。
主要模式
1. 恢復到特定時間點(recovery_target_time)
這種模式允許用戶指定一個時間點，數據庫將恢復到那個時刻的狀態。
2.恢復到特定 LSN(recovery_target_lsn)
LSN 是 PostgreSQL 中的另一種標識方式，表示 WAL 日志中的一個特定位置。
3. 恢復到某個標簽(recovery_target_name)
PostgreSQL 允許用戶使用標簽（標記）來執行恢復.
4.恢復到特定事務 ID(recovery_target_xid)
用戶還可以根據特定的事務 ID（XID）進行恢復。
恢復步驟介紹
作為數據庫DBA要定期巡查數據備份情況，當意識到數據或表被誤刪后，方便利用備份數據讀取運行庫的歸檔數據進行重放。
步驟1：基礎備份
步驟2：模擬誤操作，確定要重放到的節點
步驟3：切換wal日志
步驟4：修改端口，配置參數
步驟5：touch recovery.signal --啟動重放模式
步驟6：啟動數據庫，常看表數據
步驟7：數據遷移至主庫
步驟8：刪除備份庫
前置條件
##源庫已開啟歸檔
vim postgresql.conf
archive_mode = on
archive_command = 'test ! -f /jesong/pgdata/archive/%f && cp %p /jesong/pgdata/archive/%f'基礎備份
##基礎備份
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
##備份驗證
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恢復
#模擬誤操作
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日志落盤并歸檔
pg_switch_wal
---------------
0/A6002520
(1 row)修改備份庫配置文件：
data_directory = '/jesong/zz' --指定備份文件目錄
port = 5433 --指定新的端口
recovery_target_xid = '1061' --指定要回放txid
restore_command = 'cp /jesong/pgdata/archive/%f %p' --在備份指定其回放時，從歸檔路徑中尋找歷史wal啟動備份庫：
touch recovery.signal --啟動回放模式
chown -R postgres:postgres /jesong/zz/ --授權
su - postgres -c '/usr/pgsql-16/bin/pg_ctl start -D /jesong/zz/' --啟動驗證：
[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)當前數據庫為只讀狀態，通過下面命令開啟讀寫
select pg_wal_replay_resume();2、基于recovery_target_name恢復
#模擬誤操作
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'); ---命名恢復點
create_restore_point
-------------------------
0/AC0000F8
(1 row)
postgres=# drop table pg_text;
postgres=# select pg_switch_wal();
pg_switch_wal
---------------
0/AC000D48
(1 row)修改配置參數：
vim postgresql.conf
data_directory = '/jesong/zz' --指定備份文件目錄
port = 5433 --指定新的端口
recovery_target_name = 'restore_point' ---恢復點名稱
restore_command = 'cp /jesong/zz/archive/%f %p' --從備份庫歸檔路徑中尋找歷史wal啟動備份庫：
touch recovery.signal --啟動回放模式
rm -fr /jesong/zz/archive && cp /jesong/pgdata/archive/* /jesong/zz/archive/* --- 將原庫WAL歸檔日志，拷貝到備份庫的歸檔目錄中
chown -R postgres:postgres /jesong/zz/ --授權
su - postgres -c '/usr/pgsql-16/bin/pg_ctl start -D /jesong/zz/' --啟動3、基于recovery_target_lsn恢復
#模擬誤操作
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)
#使用這個函數來繼續重放
postgres=# SELECT pg_wal_replay_resume();修改配置文件：
vim postgresql.conf
data_directory = '/jesong/zz' --指定備份文件目錄
port = 5433 --指定新的端口
recovery_target_lsn = '0/B001B430' ---恢復點名稱
restore_command = 'cp /jesong/zz/archive/%f %p' --從備份庫歸檔路徑中尋找歷史wal啟動備份庫：
touch recovery.signal --啟動回放模式
rm -fr /jesong/zz/archive && cp /jesong/pgdata/archive/* /jesong/zz/archive/* --- 將原庫WAL歸檔日志，拷貝到備份庫的歸檔目錄中
chown -R postgres:postgres /jesong/zz/ --授權
su - postgres -c '/usr/pgsql-16/bin/pg_ctl start -D /jesong/zz/' --啟動
#使用這個函數來繼續重放
postgres=# SELECT pg_wal_replay_resume();4、基于recovery_target_time恢復
#模擬誤操作
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' ---恢復時間點
restore_command = 'cp /jesong/zz/archive/%f %p' --從備份庫歸檔路徑中尋找歷史wal啟動備份庫：
touch recovery.signal --啟動回放模式
rm -fr /jesong/zz/archive && cp /jesong/pgdata/archive/* /jesong/zz/archive/* --- 將原庫WAL歸檔日志，拷貝到備份庫的歸檔目錄中
chown -R postgres:postgres /jesong/zz/ --授權
su - postgres -c '/usr/pgsql-16/bin/pg_ctl start -D /jesong/zz/' --啟動
#使用這個函數來繼續重放
postgres=# SELECT pg_wal_replay_resume();注意事項
- 備份與歸檔：要實現有效的 PITR，必須在使用之前定期進行完整備份，同時確保 WAL 日志歸檔（可用 archive_mode 和 archive_command 配置進行設置）。
- 測試恢復流程：定期對恢復流程進行測試，以確保在出現數據丟失或損壞時能夠快速恢復。
總結
PostgreSQL 的 PITR 提供了一種靈活的方式來恢復數據庫，能夠根據用戶的需求選擇特定的恢復模式。通過使用時間點、事務 ID 或 LSN，用戶可以有效地管理和恢復數據，減少因誤操作或故障造成的損失。
作者介紹
吳守陽，51CTO社區編輯，擁有8年DBA工作經驗，熟練管理MySQL、Redis、MongoDB等開源數據庫。精通性能優化、備份恢復和高可用性架構設計。善于故障排除和自動化運維，保障系統穩定可靠。具備良好的團隊合作和溝通能力，致力于為企業提供高效可靠的數據庫解決方案。
責任編輯：華軒
來源：
51CTO
