摘要：本文详细介绍 PostgreSQL PITR（Point-In-Time Recovery）时间点恢复的完整流程，包含 WAL 归档配置、基础备份创建、PITR 恢复步骤以及 pg_restore 四种常用恢复方式。文章标注了必选和可选步骤，适合技术小白参考学习。

⚠️ 重要提示

PITR 恢复会替换整个数据库集群，操作前务必备份当前数据目录！

📖 阅读说明

必选 — 必须执行的步骤
可选 — 根据需求选择其中一种

一、PITR 原理简介

PITR（Point-In-Time Recovery）允许将数据库恢复到任意历史时间点。原理如下：

• 基础备份：使用 pg_basebackup 创建物理备份
• WAL 归档：持续归档 WAL 日志文件（类似游戏存档点）
• 重放 WAL：从基础备份开始重放 WAL 日志到指定时间点

💡 通俗理解

基础备份是"游戏存档"，WAL 日志是"游戏录像"，PITR 就是"读档 + 快进到指定时间"。

二、配置 WAL 归档（恢复的前提）必选

这部分是事前准备，需要在数据库正常运行时配置好，不是恢复时才做的！

步骤 1：创建 WAL 归档目录 必选

# 创建归档目录
mkdir -p /var/backups/postgresql/wal

# 设置权限（必须是 postgres 用户）
chown -R postgres:postgres /var/backups/postgresql/wal
chmod 700 /var/backups/postgresql/wal

$ ls -la /var/backups/postgresql/
total 16
drwx------ 4 postgres postgres 4096 Mar 31 10:00 .
drwxr-xr-x 1 root     root     4096 Mar 31 10:00 ..
drwx------ 2 postgres postgres 4096 Mar 31 10:00 wal

步骤 2：修改 postgresql.conf 配置 必选

# 编辑配置文件（路径根据版本可能不同）
# Debian/Ubuntu: /etc/postgresql/15/main/postgresql.conf
# CentOS/RHEL: /var/lib/pgsql/15/data/postgresql.conf

sudo nano /etc/postgresql/15/main/postgresql.conf

# 找到并修改以下配置项（如果不存在就添加）：
wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/backups/postgresql/wal/%f'
archive_timeout = 300

配置说明：

• wal_level = replica：WAL 级别，必须至少为 replica
• archive_mode = on：开启归档模式
• archive_command：归档命令（%p 是文件完整路径，%f 是文件名）
• archive_timeout = 300：5 分钟强制归档一次

步骤 3：重启 PostgreSQL 必选

# 重启 PostgreSQL 服务
sudo systemctl restart postgresql

# 验证配置是否生效
psql -U postgres -c "SHOW archive_mode;"
psql -U postgres -c "SHOW archive_command;"

$ psql -U postgres -c "SHOW archive_mode;"
 archive_mode 
--------------
 on
(1 row)

$ psql -U postgres -c "SHOW archive_command;"
          archive_command           
------------------------------------
 cp %p /var/backups/postgresql/wal/%f
(1 row)

步骤 4：验证 WAL 归档是否正常工作 必选

# 强制切换 WAL 日志（触发归档）
psql -U postgres -c "SELECT pg_switch_wal();"

# 检查归档目录是否有 WAL 文件
ls -lh /var/backups/postgresql/wal/

$ ls -lh /var/backups/postgresql/wal/
total 48M
-rw------- 1 postgres postgres 16M Mar 31 10:00 000000010000000000000001
-rw------- 1 postgres postgres 16M Mar 31 10:05 000000010000000000000002
-rw------- 1 postgres postgres 16M Mar 31 10:10 000000010000000000000003

✅ 成功标志

归档目录中出现 WAL 文件（每个 16MB），说明归档配置成功！

三、创建基础备份 必选

基础备份应该定期创建（如每天一次），有了基础备份 + WAL 归档，才能恢复到任意时间点。

执行 pg_basebackup 必选

# 创建基础备份（带压缩）
pg_basebackup -U postgres -D /var/backups/postgresql/base/20260331_100000 -Ft -z -P -X stream

# 参数说明：
# -D : 备份输出目录
# -Ft : tar 格式输出
# -z : gzip 压缩
# -P : 显示进度
# -X stream : 流式传输 WAL 文件

$ pg_basebackup -U postgres -D /var/backups/postgresql/base/20260331_100000 -Ft -z -P -X stream
23456/23456 kB (100%), 1/1 tablespace

$ ls -lh /var/backups/postgresql/base/20260331_100000/
total 65M
-rw------- 1 postgres postgres 18M Mar 31 10:00 base.tar.gz
-rw------- 1 postgres postgres 45M Mar 31 10:00 pg_wal.tar.gz

四、PITR 时间点恢复（完整步骤）

⚠️ 警告

以下操作会完全替换当前数据库集群！操作前务必备份当前数据目录！

步骤 1：停止 PostgreSQL 服务 必选

# 停止 PostgreSQL
sudo systemctl stop postgresql

# 确认服务已停止
sudo systemctl status postgresql

$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Active: inactive (dead) since Tue 2026-03-31 11:00:00 CST

步骤 2：备份当前数据目录 必选

# 找到当前数据目录（不同系统路径可能不同）
# Debian/Ubuntu: /var/lib/postgresql/15/main
# CentOS/RHEL: /var/lib/pgsql/15/data

# 备份当前数据目录（重要！防止恢复失败可以回退）
sudo mv /var/lib/postgresql/15/main /var/lib/postgresql/15/main.backup.$(date +%Y%m%d_%H%M%S)

# 创建新的空数据目录
sudo mkdir -p /var/lib/postgresql/15/main
sudo chown -R postgres:postgres /var/lib/postgresql/15/main
sudo chmod 700 /var/lib/postgresql/15/main

$ ls -la /var/lib/postgresql/15/
total 16
drwxr-xr-x 1 postgres postgres 4096 Mar 31 11:00 .
drwxr-xr-x 1 postgres postgres 4096 Mar 31 10:00 ..
drwx------ 1 postgres postgres 4096 Mar 31 11:00 main.backup.20260331_110000
drwx------ 2 postgres postgres 4096 Mar 31 11:00 main

步骤 3：解压基础备份到数据目录 必选

# 解压 base.tar.gz
sudo -u postgres tar -xzf /var/backups/postgresql/base/20260331_100000/base.tar.gz -C /var/lib/postgresql/15/main

# 解压 pg_wal.tar.gz（如果有）
sudo -u postgres tar -xzf /var/backups/postgresql/base/20260331_100000/pg_wal.tar.gz -C /var/lib/postgresql/15/main/pg_wal

# 验证解压结果
ls -la /var/lib/postgresql/15/main/

$ ls -la /var/lib/postgresql/15/main/
total 180
drwx------ 19 postgres postgres  4096 Mar 31 10:00 .
drwxr-xr-x  1 postgres postgres  4096 Mar 31 11:00 ..
-rw-------  1 postgres postgres     3 Mar 31 10:00 PG_VERSION
drwx------  6 postgres postgres  4096 Mar 31 10:00 base
drwx------  2 postgres postgres  4096 Mar 31 10:00 global
drwx------  2 postgres postgres  4096 Mar 31 10:00 pg_wal
...

步骤 4：创建恢复信号文件 必选

# PostgreSQL 12+ 使用 recovery.signal 文件
sudo -u postgres touch /var/lib/postgresql/15/main/recovery.signal

# 验证文件已创建
ls -la /var/lib/postgresql/15/main/recovery.signal

$ ls -la /var/lib/postgresql/15/main/recovery.signal
-rw-r--r-- 1 postgres postgres 0 Mar 31 11:05 recovery.signal

步骤 5：配置恢复目标 可选（4 选 1）

⚠️ 重要

以下 4 种恢复目标只需选择其中一种，不要全部配置！

# 编辑 postgresql.auto.conf
sudo -u postgres nano /var/lib/postgresql/15/main/postgresql.auto.conf

# 在文件末尾添加以下配置（4 选 1）：

方案 A：恢复到指定时间点 最常用

适合场景：误删除数据后恢复到删除前的时间点

restore_command = 'cp /var/backups/postgresql/wal/%f %p'
recovery_target_time = '2026-03-31 10:30:00'
recovery_target_action = 'promote'

💡 提示

时间格式必须是 'YYYY-MM-DD HH:MM:SS'，用单引号包裹。

方案 B：恢复到指定事务 ID 高级用法

适合场景：知道具体事务 ID 的场景（需要事先记录）

restore_command = 'cp /var/backups/postgresql/wal/%f %p'
recovery_target_xid = '1234567'
recovery_target_action = 'promote'

方案 C：恢复到指定命名还原点 推荐

适合场景：重大操作前创建还原点（如数据库迁移前）

restore_command = 'cp /var/backups/postgresql/wal/%f %p'
recovery_target_name = 'before_migration'
recovery_target_action = 'promote'

💡 如何创建还原点

在重大操作前执行：psql -U postgres -c "SELECT pg_create_restore_point('before_migration');"

方案 D：恢复到最新可用状态 尽可能恢复

适合场景：WAL 文件不完整时，能恢复多少算多少

restore_command = 'cp /var/backups/postgresql/wal/%f %p'
recovery_target_timeline = 'latest'

步骤 6：启动 PostgreSQL 并开始恢复 必选

# 启动 PostgreSQL
sudo systemctl start postgresql

# 查看恢复日志（实时监控恢复进度）
sudo tail -f /var/log/postgresql/postgresql-15-main.log

$ sudo tail -f /var/log/postgresql/postgresql-15-main.log
Mar 31 11:05:01 server postgres[12345]: database system was interrupted
Mar 31 11:05:02 server postgres[12345]: starting archive recovery
Mar 31 11:05:02 server postgres[12345]: redo starts at 0/5000028
Mar 31 11:05:03 server postgres[12345]: restored log file "000000010000000000000005"
Mar 31 11:05:04 server postgres[12345]: restored log file "000000010000000000000006"
Mar 31 11:05:05 server postgres[12345]: recovery stopping before commit
Mar 31 11:05:06 server postgres[12345]: redo done at 0/7000123
Mar 31 11:05:07 server postgres[12345]: database system is ready to accept connections

✅ 成功标志

日志中出现 database system is ready to accept connections 表示恢复完成！

步骤 7：验证恢复结果 必选

# 连接数据库验证
psql -U postgres -c "SELECT current_timestamp;"
psql -U postgres -c "SELECT COUNT(*) FROM your_table;"

# 检查数据库是否已退出恢复模式
psql -U postgres -c "SELECT pg_is_in_recovery();"

$ psql -U postgres -c "SELECT current_timestamp;"
       current_timestamp       
-------------------------------
 2026-03-31 10:30:00.123456+08
(1 row)

$ psql -U postgres -c "SELECT pg_is_in_recovery();"
 pg_is_in_recovery 
-------------------
 f
(1 row)

pg_is_in_recovery() = f 表示恢复完成，数据库已提升为主库（f = false = 不在恢复中）。

五、pg_restore 四种常用恢复方式

注意

pg_restore 用于从 pg_dump 创建的备份中恢复逻辑数据，与 PITR 物理恢复 不同。

方式 1：恢复整个数据库 最常用

# 从自定义格式备份恢复整个数据库
pg_restore -U postgres -d mydb -c --if-exists /var/backups/postgresql/daily/mydb_20260331.dump

# 参数说明：
# -c : 恢复前先清理（DROP）现有对象
# --if-exists : 只在对象存在时删除，避免报错

$ pg_restore -U postgres -d mydb -c --if-exists /var/backups/postgresql/daily/mydb_20260331.dump
pg_restore: creating TABLE public.users
pg_restore: creating TABLE public.orders
pg_restore: processing data for table "public.users"
$ echo $?
0

方式 2：恢复到新数据库 不影响原库

# 先创建新数据库
createdb -U postgres mydb_restored

# 恢复到新数据库
pg_restore -U postgres -d mydb_restored /var/backups/postgresql/daily/mydb_20260331.dump

# 验证恢复结果
psql -U postgres -d mydb_restored -c "\dt"

$ psql -U postgres -d mydb_restored -c "\dt"
         List of relations
 Schema |  Name  | Type  |  Owner   
--------+--------+-------+----------
 public | orders | table | postgres
 public | users  | table | postgres
(2 rows)

方式 3：只恢复特定表 选择性恢复

# 先查看备份中包含哪些表
pg_restore -l /var/backups/postgresql/daily/mydb_20260331.dump | grep TABLE

# 只恢复特定表
pg_restore -U postgres -d mydb -t users -t orders /var/backups/postgresql/daily/mydb_20260331.dump

$ pg_restore -l /var/backups/postgresql/daily/mydb_20260331.dump
3; 1259 16384 TABLE public users postgres
4; 1259 16385 TABLE public orders postgres

$ pg_restore -U postgres -d mydb -t users -t orders /var/backups/postgresql/daily/mydb_20260331.dump
pg_restore: creating TABLE public.users
pg_restore: creating TABLE public.orders

方式 4：并行恢复 加速大数据库

# 使用 -j 参数并行恢复（仅适用于目录格式）
# 首先用目录格式备份：
pg_dump -U postgres -d mydb -Fd -j 4 -f /var/backups/postgresql/daily/mydb_dir

# 然后并行恢复
pg_restore -U postgres -d mydb -j 4 /var/backups/postgresql/daily/mydb_dir

$ time pg_restore -U postgres -d mydb -j 4 /var/backups/postgresql/daily/mydb_dir
real    0m15.234s

$ time pg_restore -U postgres -d mydb /var/backups/postgresql/daily/mydb_dir
real    0m45.678s

# 并行恢复速度提升约 3 倍

六、常见问题排查

问题 1：WAL 文件缺失

LOG:  could not open archive file "000000010000000000000010": No such file or directory

原因： 归档目录缺少必要的 WAL 文件

解决： 只能恢复到最后一个可用的 WAL 文件，使用方案 D（恢复到最新可用状态）。

问题 2：权限错误

could not open file "recovery.signal": Permission denied

解决：

sudo chown -R postgres:postgres /var/lib/postgresql/15/main
sudo chmod 700 /var/lib/postgresql/15/main

问题 3：恢复后无法连接

FATAL:  the database system is starting up

原因： 恢复尚未完成，数据库仍在重放 WAL

解决： 等待恢复完成，查看日志确认进度。

📋 PITR 恢复流程速查

1. 停止 PostgreSQL：sudo systemctl stop postgresql
2. 备份当前数据目录：mv /var/lib/pgsql/15/data /var/lib/pgsql/15/data.backup
3. 解压基础备份：tar -xzf base.tar.gz -C /var/lib/pgsql/15/data
4. 创建恢复信号：touch /var/lib/pgsql/15/data/recovery.signal
5. 配置恢复目标：4 选 1（推荐方案 A 或 C）
6. 启动 PostgreSQL：sudo systemctl start postgresql
7. 验证恢复结果：psql -c "SELECT pg_is_in_recovery();"