PostgreSQL教程FG278-PG备份与恢复实战：生产环境PITR落地

本文档风哥主要介绍PostgreSQL数据库时间点恢复（PITR）的实战方案，包括WAL日志归档配置、基础备份创建、恢复过程执行等内容。风哥教程参考PostgreSQL官方文档Backup and Recovery内容编写，适合DBA人员在生产环境中实施PITR，确保数据安全和业务连续性。更多视频教程www.fgedu.net.cn

Part01-基础概念与理论知识

1.1 PostgreSQL PITR的概念

时间点恢复（Point-In-Time Recovery，简称PITR）是PostgreSQL提供的一种高级恢复机制，允许数据库恢复到任意时间点的状态。PITR的核心优势在于：

可以恢复到指定时间点，而不是只能恢复到备份时间
可以恢复被误删除的数据
可以恢复到数据库崩溃前的状态
支持增量备份和恢复，减少恢复时间

PITR的工作原理：

PITR基于WAL（Write-Ahead Logging）日志和基础备份。首先创建一个基础备份，然后在恢复时应用从备份时间到目标时间点的所有WAL日志，从而将数据库恢复到指定时间点的状态。

1.2 WAL日志归档原理

WAL（Write-Ahead Logging）是PostgreSQL的核心特性，用于确保数据的一致性和持久性。WAL日志归档是PITR的基础：

# WAL日志的作用
– 记录所有数据修改操作
– 确保事务的ACID特性
– 支持崩溃恢复
– 支持时间点恢复

# WAL日志归档流程
1. PostgreSQL生成WAL日志文件
2. 当日志文件满时，自动切换到新的日志文件
3. 触发归档命令，将旧的WAL日志文件复制到归档目录
4. 归档的WAL日志用于PITR恢复

# WAL日志文件命名
– 格式：000000010000000000000001
– 前8位：时间线ID
– 中间16位：日志序列号
– 后8位：文件序号

1.3 恢复过程原理

PostgreSQL PITR恢复过程包括以下步骤：

# PITR恢复步骤
1. 准备基础备份：使用pg_basebackup创建基础备份
2. 配置恢复参数：在recovery.conf（PostgreSQL 12+使用postgresql.auto.conf）中配置恢复参数
3. 启动数据库：数据库进入恢复模式
4. 应用WAL日志：自动应用基础备份后的所有WAL日志
5. 到达目标时间点：当应用到指定时间点的WAL日志后，数据库停止恢复
6. 完成恢复：数据库切换到正常运行模式

# 恢复模式
– 完全恢复：恢复到最新状态
– 时间点恢复：恢复到指定时间点
– 基于XID的恢复：恢复到指定事务ID

风哥提示：PITR是PostgreSQL最强大的恢复机制之一，对于保障数据安全和业务连续性至关重要。建议所有生产环境都配置PITR，以应对各种数据丢失场景。

Part02-生产环境规划与建议

2.1 PostgreSQL PITR规划

在实施PITR前，需要进行详细的规划：

# PITR规划要点
1. 恢复目标：确定需要恢复到的时间点粒度
2. 备份策略：确定基础备份的频率和保留期
3. 存储需求：估算WAL日志和备份文件的存储需求
4. 恢复时间：评估恢复所需的时间，确保符合RTO要求
5. 测试计划：定期测试PITR过程，确保其有效性

# 恢复目标设置
– 时间点精度：通常为秒级
– 恢复窗口：根据业务需求确定，如24小时、7天等
– 恢复目标类型：时间戳、事务ID、LSN（日志序列号）

# 监控需求
– WAL归档状态监控
– 备份状态监控
– 存储空间监控

2.2 存储规划与配置

存储规划是PITR实施的关键：

# 存储要求
– 基础备份存储：至少需要与数据库大小相当的空间
– WAL日志存储：根据数据库活动量和备份频率估算
– 存储类型：建议使用高性能、可靠的存储
– 存储位置：建议将备份和WAL日志存储在与数据库不同的物理设备上

# 存储配置建议
– 基础备份：使用NAS、SAN或云存储
– WAL归档：使用独立的存储设备，确保高可靠性
– 存储冗余：配置RAID或其他冗余方案
– 网络带宽：确保备份和恢复时的网络带宽充足

# 存储容量计算
– 基础备份大小 = 数据库大小 × 备份保留份数
– WAL日志大小 = 每日生成量 × 保留天数
– 建议预留30%的缓冲空间

2.3 备份策略设计

设计合理的备份策略是PITR成功的关键：

风哥教程针对风哥教程针对风哥教程针对生产环境建议：

基础备份频率：根据数据库大小和变化率，建议每天或每周一次
WAL日志保留：至少保留到下一次基础备份完成
备份验证：定期验证备份的有效性
备份测试：每月至少进行一次完整的PITR测试
备份自动化：使用脚本自动执行备份和清理

Part03-生产环境项目实施方案

3.1 WAL归档配置

3.1.1 配置postgresql.conf

# 编辑postgresql.conf文件
$ vi /postgresql/fgdata/postgresql.conf

# 启用归档模式
archive_mode = on

# 归档命令
archive_command = ‘cp %p /archive/%f’

# 归档超时（可选）
archive_timeout = 300

# WAL级别（建议设置为logical以支持逻辑复制）
wal_level = replica

# 最大WAL文件数
max_wal_senders = 10

# 保存点设置
checkpoint_timeout = 30min
max_wal_size = 1GB
min_wal_size = 80MB

# 重启PostgreSQL使配置生效
$ pg_ctl -D /postgresql/fgdata restart

# 验证归档模式是否启用
$ psql -U pgsql -c “SHOW archive_mode;”
archive_mode
————–
on
(1 row)

# 检查归档命令是否正确
$ psql -U pgsql -c “SHOW archive_command;”
archive_command
————————
cp %p /archive/%f
(1 row)

3.1.2 准备归档目录

# 创建归档目录
$ mkdir -p /archive

# 设置权限
$ chown pgsql: pgsql /archive
$ chmod 700 /archive

# 验证目录权限
$ ls -la /archive/
total 0
drwx—— 2 pgsql pgsql 6 Apr 2 10:00 .
drwxr-xr-x 3 root root 20 Apr 2 10:00 ..

# 测试归档命令
$ su – pgsql $ touch testfile
$ cp testfile /archive/testfile
$ ls -la /archive/
total 0
drwx—— 2 pgsql pgsql 20 Apr 2 10:01 .
drwxr-xr-x 3 root root 20 Apr 2 10:00 ..
-rw-r–r– 1 pgsql pgsql 0 Apr 2 10:01 testfile
$ rm /archive/testfile testfile

3.2 基础备份创建

3.2.1 使用pg_basebackup创建基础备份

# 创建基础备份目录
$ mkdir -p /backup/base

# 设置权限
$ chown pgsql: pgsql /backup/base

# 使用pg_basebackup创建备份
$ su – pgsql $ pg_basebackup -D /backup/base/$(date +%Y%m%d) -F p -X stream -v

# 输出示例
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/1234567
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot “pg_basebackup_12345”
pg_basebackup: write-ahead log end point: 0/1234567
pg_basebackup: waiting for background process to finish streaming…
pg_basebackup: base backup completed

# 验证备份
$ ls -la /backup/base/20260402/
total 16
drwx—— 19 pgsql pgsql 4096 Apr 2 10:10 .
drwxr-xr-x 3 pgsql pgsql 4096 Apr 2 10:09 ..
drwx—— 6 pgsql pgsql 4096 Apr 2 10:10 base
drwx—— 2 pgsql pgsql 4096 Apr 2 10:10 global
drwx—— 2 pgsql pgsql 4096 Apr 2 10:10 pg_commit_ts
drwx—— 2 pgsql pgsql 4096 Apr 2 10:10 pg_dynshmem
drwx—— 4 pgsql pgsql 4096 Apr 2 10:10 pg_logical
drwx—— 4 pgsql pgsql 4096 Apr 2 10:10 pg_multixact
drwx—— 2 pgsql pgsql 4096 Apr 2 10:10 pg_notify
drwx—— 2 pgsql pgsql 4096 Apr 2 10:10 pg_replslot
drwx—— 2 pgsql pgsql 4096 Apr 2 10:10 pg_serial
drwx—— 2 pgsql pgsql 4096 Apr 2 10:10 pg_snapshots
drwx—— 2 pgsql pgsql 4096 Apr 2 10:10 pg_stat
-drwx—— 2 pgsql pgsql 4096 Apr 2 10:10 pg_stat_tmp
-drwx—— 2 pgsql pgsql 4096 Apr 2 10:10 pg_subtrans
-drwx—— 2 pgsql pgsql 4096 Apr 2 10:10 pg_tblspc
-drwx—— 2 pgsql pgsql 4096 Apr 2 10:10 pg_twophase
-rw——- 1 pgsql pgsql 3 Apr 2 10:10 PG_VERSION
-rw——- 1 pgsql pgsql 238 Apr 2 10:10 postgresql.auto.conf
-rw——- 1 pgsql pgsql 2650 Apr 2 10:10 postgresql.conf
-rw——- 1 pgsql pgsql 0 Apr 2 10:10 standby.signal

3.2.2 自动备份脚本

# 创建自动备份脚本
$ cat > /postgresql/scripts/backup_base.sh << 'EOF' #!/bin/bash # backup_base.sh # from:www.itpux.com.qq113257174.wx:itpux-com # from:www.itpux.com.qq113257174.wx:itpux-com # from:www.itpux.com.qq113257174.wx:itpux-com # from:www.itpux.com.qq113257174.wx:itpux-com # web: `http://www.fgedu.net.cn` # web: `http://www.fgedu.net.cn` # web: `http://www.fgedu.net.cn` # web: http://www.fgedu.net.cn # 备份目录 BACKUP_DIR="/backup/base" # 保留天数 RETENTION_DAYS=7 # 创建备份目录 BACKUP_DATE=$(date +%Y%m%d) BACKUP_PATH="$BACKUP_DIR/$BACKUP_DATE" mkdir -p "$BACKUP_PATH" # 执行备份 echo "Starting base backup at $(date)" su - pgsql -c "pg_basebackup -D $BACKUP_PATH -F p -X stream -v" # 检查备份是否成功 if [ $? -eq 0 ]; then echo "Base backup completed successfully at $(date)" # 清理过期备份 echo "Cleaning up backups older than $RETENTION_DAYS days" find "$BACKUP_DIR" -type d -mtime +$RETENTION_DAYS -exec rm -rf {} \; else echo "Base backup failed at $(date)" exit 1 fi EOF # 设置脚本权限 $ chmod +x /postgresql/scripts/backup_base.sh # 添加到crontab $ crontab -e # 每天凌晨2点执行备份 0 2 * * * /postgresql/scripts/backup_base.sh >> /postgresql/logs/backup.log 2>&1

3.3 恢复配置与执行

3.3.1 准备恢复环境

# 停止PostgreSQL服务
$ pg_ctl -D /postgresql/fgdata stop

# 备份当前数据目录（可选）
$ mv /postgresql/fgdata /postgresql/fgdata_old

# 准备恢复目录
$ mkdir -p /postgresql/fgdata

# 复制基础备份到数据目录
$ cp -r /backup/base/20260402/* /postgresql/fgdata/

# 设置权限
$ chown -R pgsql: pgsql /postgresql/fgdata

3.3.2 配置恢复参数

# PostgreSQL 12+ 使用postgresql.auto.conf
$ cat > /postgresql/fgdata/postgresql.auto.conf << 'EOF' # 恢复配置 restore_command = 'cp /archive/%f %p' recovery_target = '2026-04-02 10:30:00+08' recovery_target_inclusive = true recovery_target_timeline = 'latest' EOF # 或者使用recovery.conf（PostgreSQL 11及以下） # $ cat > /postgresql/fgdata/recovery.conf << 'EOF' # restore_command = 'cp /archive/%f %p' # recovery_target = '2026-04-02 10:30:00+08' # recovery_target_inclusive = true # recovery_target_timeline = 'latest' # EOF # 创建standby.signal文件（PostgreSQL 12+） $ touch /postgresql/fgdata/standby.signal # 设置权限 $ chown pgsql: pgsql /postgresql/fgdata/postgresql.auto.conf $ chown pgsql: pgsql /postgresql/fgdata/standby.signal

3.3.3 执行恢复

# 启动PostgreSQL服务
$ pg_ctl -D /postgresql/fgdata start

# 查看恢复状态
$ tail -f /postgresql/fgdata/log/postgresql-2026-04-02.log

# 输出示例
2026-04-02 10:40:00.000 CST [12345] LOG: starting PostgreSQL 18.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.2.0, 64-bit
2026-04-02 10:40:00.000 CST [12345] LOG: listening on IPv4 address “0.0.0.0”, port 5432
2026-04-02 10:40:00.000 CST [12345] LOG: listening on IPv6 address “::”, port 5432
2026-04-02 10:40:00.000 CST [12345] LOG: listening on Unix socket “/tmp/.s.PGSQL.5432”
2026-04-02 10:40:00.000 CST [12346] LOG: fgedudb system was interrupted; last known up at 2026-04-02 10:00:00 CST
2026-04-02 10:40:00.000 CST [12346] LOG: starting point-in-time recovery to 2026-04-02 10:30:00+08
2026-04-02 10:40:01.000 CST [12346] LOG: restored log file “000000010000000000000001” from archive
2026-04-02 10:40:02.000 CST [12346] LOG: restored log file “000000010000000000000002” from archive
2026-04-02 10:40:03.000 CST [12346] LOG: recovery stopping before commit of transaction 12345, time 2026-04-02 10:30:01+08
2026-04-02 10:40:03.000 CST [12346] LOG: recovery has paused
2026-04-02 10:40:03.000 CST [12345] LOG: fgedudb system is ready to accept read-only connections

# 完成恢复
$ psql -U pgsql -c “SELECT pg_wal_replay_resume();”

# 验证数据库状态
$ psql -U pgsql -c “SELECT pg_is_in_recovery();”
pg_is_in_recovery
——————-
f
(1 row)

# 验证数据是否恢复到指定时间点
$ psql -U pgsql -d fgedudb -c “SELECT * FROM fgedu_table WHERE created_at < '2026-04-02 10:30:00';"

风哥提示：PITR恢复过程中，数据库会先进入只读模式，直到应用完所有必要的WAL日志后才会切换到可写模式。在恢复完成前，不要尝试写入数据。学习交流加群风哥微信: itpux-com

Part04-生产案例与实战讲解

4.1 PITR实战演练

4.1.1 误删除数据恢复

# 场景：误删除了重要表数据，需要恢复到删除前的时间点

# 1. 确定删除时间
# 通过日志或应用记录确定删除操作的时间
# 假设删除时间为2026-04-02 15:30:00

# 2. 准备恢复
# 停止PostgreSQL服务
$ pg_ctl -D /postgresql/fgdata stop

# 复制基础备份
$ cp -r /backup/base/20260402/* /postgresql/fgdata/

# 配置恢复参数
$ cat > /postgresql/fgdata/postgresql.auto.conf << 'EOF' restore_command = 'cp /archive/%f %p' recovery_target = '2026-04-02 15:29:00+08' recovery_target_inclusive = true EOF # 创建standby.signal $ touch /postgresql/fgdata/standby.signal # 3. 执行恢复 $ pg_ctl -D /postgresql/fgdata start # 4. 验证数据 # 连接数据库，检查被删除的数据是否恢复 $ psql -U pgsql -d fgedudb -c "SELECT * FROM fgedu_table;" # 5. 完成恢复 $ psql -U pgsql -c "SELECT pg_wal_replay_resume();" # 6. 备份恢复后的数据（可选） $ pg_dump -U pgsql -d fgedudb -f /backup/fgedudb_recovered.sql

4.1.2 数据库崩溃恢复

# 场景：数据库服务器崩溃，需要恢复到崩溃前的状态

# 1. 准备恢复环境
# 确保硬件故障已修复
# 重新安装操作系统（如果需要）
# 安装PostgreSQL

# 2. 复制基础备份
$ cp -r /backup/base/20260402/* /postgresql/fgdata/

# 3. 配置恢复参数（恢复到最新状态）
$ cat > /postgresql/fgdata/postgresql.auto.conf << 'EOF' restore_command = 'cp /archive/%f %p' # 不指定recovery_target，默认恢复到最新状态 EOF # 创建standby.signal $ touch /postgresql/fgdata/standby.signal # 4. 执行恢复 $ pg_ctl -D /postgresql/fgdata start # 5. 验证恢复 # 检查数据库状态 $ psql -U pgsql -c "SELECT pg_is_in_recovery();" # 检查数据完整性 $ psql -U pgsql -d fgedudb -c "SELECT count(*) FROM fgedu_table;" # 检查最近的事务 $ psql -U pgsql -d fgedudb -c "SELECT * FROM fgedu_table ORDER BY created_at DESC LIMIT 10;"

4.2 常见恢复场景处理

4.2.1 基于LSN的恢复

# 场景：需要恢复到指定的LSN位置

# 1. 确定目标LSN
# 可以从日志中获取LSN信息
# 例如：0/12345678

# 2. 配置恢复参数
$ cat > /postgresql/fgdata/postgresql.auto.conf << 'EOF' restore_command = 'cp /archive/%f %p' recovery_target_lsn = '0/12345678' EOF # 3. 执行恢复 $ pg_ctl -D /postgresql/fgdata start # 4. 验证恢复 $ psql -U pgsql -c "SELECT pg_current_wal_lsn();"

4.2.2 基于事务ID的恢复

# 场景：需要恢复到指定的事务ID

# 1. 确定目标事务ID
# 例如：12345

# 2. 配置恢复参数
$ cat > /postgresql/fgdata/postgresql.auto.conf << 'EOF' restore_command = 'cp /archive/%f %p' recovery_target_xid = '12345' EOF # 3. 执行恢复 $ pg_ctl -D /postgresql/fgdata start # 4. 验证恢复 $ psql -U pgsql -c "SELECT txid_current();"

4.3 恢复故障排查

4.3.1 常见恢复错误及解决方法

# 1. WAL日志丢失
# 错误信息：could not restore file “000000010000000000000001”: No such file or directory
# 解决方法：
– 确保归档目录中存在所需的WAL日志
– 检查archive_command是否正确
– 从备份中恢复缺失的WAL日志

# 2. 恢复目标时间点不可达
# 错误信息：recovery could not find a consistent point to start from
# 解决方法：
– 检查基础备份是否完整
– 确保WAL日志覆盖了从备份到目标时间点的所有操作
– 调整恢复目标时间点

# 3. 权限问题
# 错误信息：could not open file “base/12345/67890”: Permission denied
# 解决方法：
– 确保数据目录权限正确（pgsql: postgres）
– 检查文件系统权限

# 4. 存储空间不足
# 错误信息：could not write to file “base/12345/67890”: No space left on device
# 解决方法：
– 确保目标磁盘有足够的空间
– 清理不必要的文件

# 5. 配置错误
# 错误信息：invalid recovery parameter “recovery_target”
# 解决方法：
– 检查恢复配置文件格式
– 确保使用正确的参数名称
– 验证参数值格式是否正确

4.3.2 恢复性能优化

# 恢复性能优化建议

# 1. 并行恢复
– 设置max_worker_processes参数
– 配置parallel_fgapply参数（PostgreSQL 9.6+）

# 2. 存储优化
– 使用高性能存储设备
– 确保足够的I/O带宽
– 避免在恢复期间进行其他I/O密集型操作

# 3. 内存优化
– 增加shared_buffers参数
– 设置适当的work_mem参数

# 4. 配置优化
– 禁用autovacuum during recovery
– 调整checkpoint参数
– 配置适当的maintenance_work_mem

# 5. 网络优化（如果WAL日志在远程存储）
– 确保网络带宽充足
– 使用压缩传输
– 考虑使用本地缓存

风哥教程针对风哥教程针对风哥教程针对生产环境建议：在进行PITR恢复前，建议先在测试环境进行演练，熟悉恢复流程并评估恢复时间。同时，确保有足够的存储空间和网络带宽，以确保恢复过程顺利完成。更多学习教程公众号风哥教程itpux_com

Part05-风哥经验总结与分享

5.1 PITR最佳实践

PostgreSQL PITR最佳实践：

定期测试：每月至少进行一次PITR测试，确保备份和恢复流程正常
备份验证：定期验证基础备份的完整性和可用性
WAL归档监控：实时监控WAL归档状态，确保WAL日志正常归档
存储管理：合理规划存储，确保备份和WAL日志有足够的空间
文档化：详细记录PITR流程和配置，确保团队成员都熟悉恢复过程
自动化：使用脚本自动执行备份和清理，减少人工操作错误
多副本：将备份存储在多个位置，提高数据安全性

5.2 PITR实施检查清单

# PostgreSQL PITR实施检查清单

## 配置检查
– [ ] 启用归档模式（archive_mode = on）
– [ ] 配置归档命令（archive_command）
– [ ] 设置适当的WAL级别（wal_level = replica）
– [ ] 配置足够的max_wal_senders
– [ ] 验证归档目录权限和空间

## 备份检查
– [ ] 定期执行基础备份（pg_basebackup）
– [ ] 验证备份的完整性
– [ ] 清理过期备份
– [ ] 存储备份到多个位置

## 监控检查
– [ ] 监控WAL归档状态
– [ ] 监控备份执行状态
– [ ] 监控存储空间使用情况
– [ ] 监控数据库活动和WAL生成速率

## 恢复准备
– [ ] 编写详细的恢复流程文档
– [ ] 定期测试PITR恢复
– [ ] 准备恢复所需的工具和资源
– [ ] 培训团队成员掌握恢复流程

## 应急响应
– [ ] 建立PITR恢复的应急响应流程
– [ ] 明确恢复责任人
– [ ] 制定恢复时间目标（RTO）
– [ ] 建立恢复后的验证流程

5.3 备份恢复工具推荐

PostgreSQL备份恢复工具推荐：

pg_basebackup：PostgreSQL内置的基础备份工具
pg_dump/pg_restore：逻辑备份和恢复工具
Barman：高级备份管理工具，支持远程备份和恢复
pgBackRest：高性能备份恢复工具，支持增量备份
WAL-G：基于WAL的备份工具，支持云存储
OmniPITR：WAL归档和PITR管理工具
Patroni：高可用解决方案，包含自动故障转移和备份管理

风哥提示：选择合适的备份恢复工具取决于具体的业务需求和环境。对于企业级生产环境，建议使用专业的备份管理工具，如Barman或pgBackRest，以获得更好的性能和管理能力。from PostgreSQL视频:www.itpux.com

持续改进：PITR是一个复杂的过程，需要不断学习和优化。建议定期回顾备份恢复策略，根据业务需求和技术发展调整配置，确保数据安全和业务连续性。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

PostgreSQL教程FG278-PG备份与恢复实战：生产环境PITR落地

Part01-基础概念与理论知识

1.1 PostgreSQL PITR的概念

1.2 WAL日志归档原理

1.3 恢复过程原理

Part02-生产环境规划与建议

2.1 PostgreSQL PITR规划

2.2 存储规划与配置

2.3 备份策略设计

Part03-生产环境项目实施方案

3.1 WAL归档配置

3.1.1 配置postgresql.conf

3.1.2 准备归档目录

3.2 基础备份创建

3.2.1 使用pg_basebackup创建基础备份

3.2.2 自动备份脚本

3.3 恢复配置与执行

3.3.1 准备恢复环境

3.3.2 配置恢复参数

3.3.3 执行恢复

Part04-生产案例与实战讲解

4.1 PITR实战演练

4.1.1 误删除数据恢复

4.1.2 数据库崩溃恢复

4.2 常见恢复场景处理

4.2.1 基于LSN的恢复

4.2.2 基于事务ID的恢复

4.3 恢复故障排查

4.3.1 常见恢复错误及解决方法

4.3.2 恢复性能优化

Part05-风哥经验总结与分享

5.1 PITR最佳实践

5.2 PITR实施检查清单

5.3 备份恢复工具推荐

相关推荐

联系我们