PostgreSQL基于PITR的数据恢复实践-51CTO.COM

作者 | 吴守阳

审校 | 重楼

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等开源数据库。精通性能优化、备份恢复和高可用性架构设计。善于故障排除和自动化运维，保障系统稳定可靠。具备良好的团队合作和沟通能力，致力于为企业提供高效可靠的数据库解决方案。