内容摘要：作者 | 吴守阳审校 | 重楼目录1、概述2、主要模式3、回复步骤介绍4、前置条件5、基础备份6、操作示例基于recovery_target_xid恢复基于recovery_target_name恢复

作者 | 吴守阳

审校 | 重楼

目录

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.

验证：

复制[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)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.

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

复制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.

启动备份库：

复制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();1.2.3.4.5.6. 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.

启动备份库：

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

总结

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

作者介绍

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