PostgreSQL基于PITR的数据恢复实践

wangpeibao

2025-10-12

数据库

作者 | 吴守阳

审校 | 重楼

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/%f1.2.3.4.

基础备份

复制##基础备份 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: 11.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.

操作示例

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)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.

修改备份库配置文件：

复制data_directory = /jesong/zz --指定备份文件目录 port = 5433 --指定新的端口 recovery_target_xid = 1061 --指定要回放txid restore_command = cp /jesong/pgdata/archive/%f %p --在备份指定其回放时，从归档路径中寻找历史wal1.2.3.4.

启动备份库：

复制touch recovery.signal --启动回放模式 chown -R postgres:postgres /jesong/zz/ --授权 su - postgres -c /usr/pgsql-16/bin/pg_ctl start -D /jesong/zz/ --启动1.2.3.

验证：

当前数据库为只读状态，通过下面命令开启读写

复制select pg_wal_replay_resume();1.

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)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.

修改配置参数：

复制vim postgresql.conf data_directory = /jesong/zz --指定备份文件目录 port = 5433 --指定新的端口 recovery_target_name = restore_point ---恢复点名称 restore_command = cp /jesong/zz/archive/%f %p --从备份库归档路径中寻找历史wal1.2.3.4.5.

启动备份库：

复制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/ --启动1.2.3.4.

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();1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.

修改配置文件：

复制vim postgresql.conf data_directory = /jesong/zz --指定备份文件目录 port = 5433 --指定新的端口 recovery_target_lsn = 0/B001B430 ---恢复点名称 restore_command = cp /jesong/zz/archive/%f %p --从备份库归档路径中寻找历史wal1.2.3.4.5.

启动备份库：

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();1.2.3.4.5.6.7.8.9.10.11.12.

修改配置文件：

复制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 --从备份库归档路径中寻找历史wal1.2.3.4.5.

启动备份库：

注意事项备份与归档：要实现有效的 PITR，必须在使用之前定期进行完整备份，同时确保 WAL 日志归档（可用 archive_mode 和 archive_command 配置进行设置）。测试恢复流程：定期对恢复流程进行测试，以确保在出现数据丢失或损坏时能够快速恢复。

总结

PostgreSQL 的 PITR 提供了一种灵活的方式来恢复数据库，能够根据用户的需求选择特定的恢复模式。通过使用时间点、事务 ID 或 LSN，用户可以有效地管理和恢复数据，减少因误操作或故障造成的损失。

作者介绍

吴守阳，51CTO社区编辑，拥有8年DBA工作经验，熟练管理MySQL、Redis、MongoDB等开源数据库。精通性能优化、备份恢复和高可用性架构设计。善于故障排除和自动化运维，保障系统稳定可靠。具备良好的团队合作和沟通能力，致力于为企业提供高效可靠的数据库解决方案。

THE END

比MySQL快801倍！ClickHouse这么牛吗？是的，简直开挂！

<<上一篇

2025年，通信行业有哪些方向值得关注？

下一篇>>