如何使用base backup与WAL文件恢复PostgreSQL数据库

前置准备 #

• 确认你拥有最新的每日pg_basebackup基础备份，以及所有从pg_xlog目录rsync过来的WAL文件
• 目标服务器的PostgreSQL版本要和备份源保持一致，避免版本兼容问题

步骤1：停止PostgreSQL服务并清空数据目录 #

首先停止运行中的PostgreSQL实例，避免数据冲突：

# 以systemd管理为例，根据你的系统调整命令
systemctl stop postgresql
# 或者用pg_ctl直接操作
pg_ctl stop -D /var/lib/postgresql/14/main -m fast

然后清空目标数据目录（如果需要保留原数据，先手动备份到其他路径）：

rm -rf /var/lib/postgresql/14/main/*

步骤2：恢复基础备份 #

将每日基础备份的所有内容复制到PostgreSQL数据目录：

cp -R /path/to/your/daily_base_backup/* /var/lib/postgresql/14/main/

调整目录权限，确保postgres用户拥有完整权限：

chown -R postgres:postgres /var/lib/postgresql/14/main/

步骤3：配置WAL恢复规则 #

在数据目录下创建recovery.signal文件（PostgreSQL 12+版本用这个，12及以前用recovery.conf），指定WAL文件的恢复命令：

# 切换到postgres用户操作
su - postgres
cd /var/lib/postgresql/14/main/
# 创建并编辑recovery.signal
cat > recovery.signal <<EOF
restore_command = 'cp /path/to/your/rsynced_wal_files/%f "%p"'
# 可选：如果需要恢复到指定时间点，添加以下行
# recovery_target_time = '2024-05-20 15:30:00'
# 可选：如果需要恢复到指定事务ID，添加以下行
# recovery_target_xid = '123456'
EOF

• %f：自动替换为需要恢复的WAL文件名
• %p：自动替换为数据目录中WAL文件的目标路径
• 如果不指定恢复目标，PostgreSQL会自动应用所有可用的WAL文件，恢复到最新状态

步骤4：启动服务并验证恢复结果 #

启动PostgreSQL服务：

systemctl start postgresql
# 或者用pg_ctl
pg_ctl start -D /var/lib/postgresql/14/main

查看日志确认恢复完成：

tail -f /var/log/postgresql/postgresql-14-main.log

当日志中出现database system is ready to accept connections时，说明恢复成功。

最后连接数据库验证数据：

psql -U postgres -d your_database_name
# 执行查询验证数据完整性，比如
SELECT count(*) FROM your_critical_table;

关键注意事项 #

• 确保基础备份的backup_label文件完整，该文件记录了备份的起始/结束WAL位置，恢复会从这个位置开始应用WAL
• 检查rsync同步的WAL文件是否完整，若有缺失会导致恢复失败
• 若使用PostgreSQL 10及以后版本，WAL目录默认是pg_wal而非pg_xlog，注意路径对应

内容的提问来源于stack exchange，提问作者samuto