数据库教程FGMT55‑PostgreSQL物理备份与恢复实战

数据库教程FGMT55‑PostgreSQL物理备份与恢复实战

前言

本套风哥教程面向DBA、数据库运维工程师、云计算运维人员，聚焦PostgreSQL物理备份整套体系，涵盖WAL预写日志原理、归档配置、pg_basebackup基线备份、PITR时间点恢复、备份验证、故障场景恢复、备份策略规划。风哥教程本文分为理论原理与实战操作两大模块，理论部分讲解物理备份底层机制、WAL归档、PITR实现原理，对比物理备份与逻辑备份差异；实战部分基于主机fgedu‑net‑cn1、fgedu‑net‑cn2，硬件规格统一64G内存，8CPU，数据根目录统一使用/fgedudb，数据库/实例名fgedudb，业务用户名fgedu，包含大量可直接复现Shell与SQL命令，覆盖归档配置、基线备份、备份校验、完整实例恢复、时间点恢复、备份脚本编写、故障排查。

风哥教程本文学习目标：理解PostgreSQL物理备份底层原理，能够完成WAL归档环境部署，熟练使用pg_basebackup完成基线备份，掌握完整实例恢复与PITR时间点恢复，完成备份有效性校验，能够制定生产备份策略，处理备份恢复过程中各类常见故障。

实操提示：备份最重要的环节是定期做恢复演练，只备份不做恢复演练等同于无效备份；所有恢复操作优先在测试环境复现验证。
网上搜索风哥教程可以学习全套数据库教程

目录

1. PostgreSQL物理备份基础理论
   1.1 物理备份基本概念，物理备份与逻辑备份对比
   1.2 WAL预写日志底层原理，pg_wal目录文件说明
   1.3 连续归档与PITR时间点恢复实现原理
   1.4 pg_basebackup工具工作机制
   1.5 归档模式核心参数解析（64G内存8CPU硬件规格）
   1.6 物理备份适用场景与局限性
   1.7 备份恢复相关权限、账号规划
2. 环境准备与WAL归档配置实战（主机fgedu‑net‑cn1）
   2.1 环境确认，目录规划
   2.2 postgresql.conf归档相关参数修改
   2.3 archive_command归档命令配置，归档目录权限设置
   2.4 replication复制账号创建，pg_hba.conf访问配置
   2.5 重启实例，验证归档是否正常工作
3. pg_basebackup基线备份完整实战
   3.1 pg_basebackup常用参数详解
   3.2 plain普通格式基线备份实操
   3.3 tar压缩格式基线备份实操
   3.4 增量基线备份pg_basebackup实操
   3.5 pg_verifybackup备份集有效性校验
4. 物理备份恢复实战
   4.1 完整实例全量恢复（恢复到备份结束时刻）
   4.2 PITR时间点恢复（误删数据场景）
   4.3 restore.signal与恢复参数配置详解
   4.4 恢复后时间线切换现象说明
5. 生产自动化备份脚本编写实战
   5.1 基线备份定时Shell脚本
   5.2 WAL归档文件过期清理脚本
   5.3 crontab定时任务配置
6. 跨主机恢复演练（主机fgedu‑net‑cn2）
   6.1 将fgedu‑net‑cn1备份集恢复到fgedu‑net‑cn2
   6.2 恢复完成业务数据校验
7. 物理备份恢复常见故障排查
   7.1 归档不生效，WAL文件没有生成归档副本
   7.2 pg_basebackup连接失败，复制账号权限问题
   7.3 恢复启动报错，缺少backup_label文件
   7.4 PITR恢复找不到对应WAL归档文件
   7.5 备份集校验pg_verifybackup报异常
8. 生产环境备份策略规划
9. 风哥针对本文总结

1 PostgreSQL物理备份基础理论

风哥 itpux-com

1.1 物理备份基本概念，物理备份与逻辑备份对比

物理备份直接复制数据库实例磁盘上的原始物理文件，包含数据文件、WAL日志、元数据文件、表空间文件，是数据库集群完整二进制副本；逻辑备份pg_dump/pg_dumpall是解析数据库，导出SQL语句或者自定义格式转储文件，属于逻辑层面导出。

生产环境建议物理备份+逻辑备份搭配使用；物理备份用于实例整体灾难恢复，逻辑备份用于误删单表、对象级细粒度恢复。

1.2 WAL预写日志底层原理，pg_wal目录文件说明

WAL即Write‑Ahead‑Logging预写日志，PostgreSQL修改数据遵循WAL规则：修改数据页之前，事务变更先写入WAL日志，再后台刷写数据文件。
pg_wal目录存放WAL段文件，默认单段大小16MB；事务提交首先持久化WAL，数据库崩溃的时候，启动会重放pg_wal内日志完成崩溃恢复。
WAL归档就是把已经写完的WAL段复制到归档存储，这些归档文件就是PITR时间点恢复的基础。

网上搜索风哥教程可以学习全套数据库教程

1.3 连续归档与PITR时间点恢复实现原理

PITR Point‑In‑Time Recovery时间点恢复由两部分组成：

1. 基线基础备份：某一时刻使用pg_basebackup生成的完整物理文件副本，作为恢复起始点。
2. WAL归档集：基线备份生成之后，所有业务产生并且归档保存的WAL段文件。

恢复流程：恢复基线备份，启动数据库实例，读取restore配置，持续重放归档WAL日志，可以选择重放到指定时间点、指定事务ID、直到所有归档日志回放完毕。
通俗理解：基线备份是游戏存档，WAL归档是操作录像；PITR恢复就是读取存档，回放录像，跳转到任意历史时间节点。

1.4 pg_basebackup工具工作机制

pg_basebackup是PostgreSQL原生物理基线备份工具，依托流复制协议连接数据库实例，不需要关闭数据库，实现热备份。
完整工作流程：

1. 通过replication复制账号连接数据库；
2. 数据库执行检查点，生成backup_label备份标签，记录备份起始检查点位置；
3. 流式拷贝全部数据文件、表空间文件；同时流式传输备份期间产生的WAL日志段；
4. 生成backup_manifest备份清单文件，用于pg_verifybackup校验备份完整性；
5. 备份结束，生成备份集，备份集自带备份起始检查点信息。

⚠️pg_basebackup只能备份完整集群，不能单独备份某一个数据库或者某一张表。

1.5 归档模式核心参数解析（64G内存8CPU硬件规格）

针对硬件规格64G内存、8CPU服务器，归档相关postgresql.conf核心参数：

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /fgedudb/pg_archive/%f && cp %p /fgedudb/pg_archive/%f'
archive_timeout = 300
max_wal_senders = 10
wal_buffers = 16MB
max_wal_size = 8GB
min_wal_size = 2GB

参数说明：

1. wal_level=replica：WAL级别，replica满足归档与流复制需求；minimal不支持归档。修改需要重启实例。
2. archive_mode=on：开启WAL归档，主库生效，需要重启实例。
3. archive_command：归档执行shell命令；%p代表源WAL文件路径，%f代表WAL文件名；test判断目标文件不存在再复制，避免重复归档。reload即可生效，无需重启。
4. archive_timeout=300：低业务量数据库，5分钟强制切换WAL段，保证低写入场景日志按时归档。
5. max_wal_senders=10：流复制sender进程最大数量，pg_basebackup备份会占用一个sender。
6. max_wal_size=8GB：WAL循环文件最大总大小，64G内存大业务配置。

风哥教程 113257174

1.6 物理备份适用场景与局限性

适用场景

1. 数据库实例整体灾难恢复，磁盘损坏、文件丢失，快速恢复整个集群。
2. PITR时间点恢复，误操作TRUNCATE/DELETE，恢复到误操作之前时间节点。
3. 搭建流复制备库，pg_basebackup作为备库初始基础备份。
4. TB级别大数据库，备份恢复速度要求高的生产业务。

局限性

1. 只能恢复整个集群，不能单独恢复某一张表，想要单表恢复仍然需要逻辑备份。
2. 物理备份文件版本必须和目标恢复数据库大版本完全一致，跨大版本无法直接物理恢复。
3. 归档磁盘需要预留充足存储空间，业务量大WAL归档文件会持续占用磁盘。

1.7 备份恢复相关权限、账号规划

pg_basebackup连接数据库的账号，必须具备REPLICATION复制权限，超级用户也可以；pg_hba.conf必须允许该账号的replication协议连接。
操作系统层面，所有备份目录、归档目录，必须是postgres操作系统用户读写权限，权限700，禁止开放everyone公共权限。

上51CTO搜索风哥可以学习全套数据库教程

2 环境准备与WAL归档配置实战（主机fgedu‑net‑cn1）

实验主机fgedu‑net‑cn1；实例数据目录/fgedudb/fgedudb_data；业务库fgedudb，业务账号fgedu；硬件规格64G内存8CPU。

2.1 环境确认，目录规划

Linux终端使用postgres操作系统用户执行，创建归档目录、备份根目录。

# 创建WAL归档存储目录
mkdir -p /fgedudb/pg_archive
# 创建基线备份存储目录
mkdir -p /fgedudb/pg_basebackup
# 创建备份日志目录
mkdir -p /fgedudb/pg_backup_log

# 修改目录属主权限
chown -R postgres:postgres /fgedudb
chmod 700 /fgedudb/pg_archive
chmod 700 /fgedudb/pg_basebackup

登录psql确认实例运行状态：

psql -U postgres -h 127.0.0.1 -d postgres -p 5432

SELECT version();
SHOW wal_level;
SHOW archive_mode;

2.2 postgresql.conf归档相关参数修改

编辑/fgedudb/fgedudb_data/postgresql.conf，修改归档相关参数：

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /fgedudb/pg_archive/%f && cp %p /fgedudb/pg_archive/%f'
archive_timeout = 300
max_wal_senders = 10
max_wal_size = 8GB
min_wal_size = 2GB

2.3 replication复制账号创建，pg_hba.conf访问配置

登录psql，创建复制专用账号pg_repl：

CREATE USER pg_repl WITH REPLICATION PASSWORD 'Repl@Fg123456';

编辑/fgedudb/fgedudb_data/pg_hba.conf，增加复制连接规则，允许本地内网复制协议访问：

host    replication     pg_repl        127.0.0.1/32            scram‑sha‑256
host    replication     pg_repl        192.168.1.0/24          scram‑sha‑256

2.4 重启实例，验证归档是否正常工作

修改wal_level、archive_mode参数需要重启数据库实例。

#重启实例
pg_ctl -D /fgedudb/fgedudb_data restart

归档验证，执行多次数据库写操作，触发WAL段切换：

-- 生成测试数据
CREATE TABLE test_arch(id int primary key,info text);
INSERT INTO test_arch SELECT generate_series(1,10000),'test archive wal';
-- 手动切换wal段，强制触发归档
SELECT pg_switch_wal();

shell查看归档目录是否生成WAL归档文件：

ls -l /fgedudb/pg_archive

目录出现wal段文件，代表归档配置成功。

风哥数据库教程 itpux-com

3 pg_basebackup基线备份完整实战

3.1 pg_basebackup常用参数详解

3.2 plain普通格式基线备份实操

plain格式备份，备份目录为完整数据目录结构，恢复的时候直接作为PGDATA数据目录。

pg_basebackup -h 127.0.0.1 -p 5432 -U pg_repl \
-D /fgedudb/pg_basebackup/base_$(date +%Y%m%d) \
-Fp -Xs -P -v --checkpoint=fast

执行完成，/fgedudb/pg_basebackup/base_20260916生成完整基线备份集。

3.3 tar压缩格式基线备份实操

tar格式，全部文件打包tar，节省磁盘存储空间，适合长期保存备份集。

pg_basebackup -h 127.0.0.1 -p 5432 -U pg_repl \
-D /fgedudb/pg_basebackup/base_tar_$(date +%Y%m%d) \
-Ft -Xs -P -v --compress=zstd

3.4 增量基线备份pg_basebackup实操

增量备份只备份和上一份基线备份发生变更的数据块，减少备份IO与备份存储占用，增量备份不能直接恢复，需要pg_combinebackup合并为完整备份集。

#基于上一份base_20260916做增量备份
pg_basebackup -h 127.0.0.1 -p 5432 -U pg_repl \
-D /fgedudb/pg_basebackup/incr_$(date +%Y%m%d) \
‑‑incremental=/fgedudb/pg_basebackup/base_20260916 \
-Fp -Xs -P -v
#合并增量备份生成完整可恢复备份集
pg_combinebackup /fgedudb/pg_basebackup/base_20260916 \
‑‑input /fgedudb/pg_basebackup/incr_$(date +%Y%m%d) \
‑‑output /fgedudb/pg_basebackup/combine_full

3.5 pg_verifybackup备份集有效性校验

pg_verifybackup读取backup_manifest清单，校验备份集文件完整性，检测磁盘损坏、文件缺失，备份完成后必须执行校验，确认备份集可用。

pg_verifybackup /fgedudb/pg_basebackup/base_$(date +%Y%m%d)

输出backup is valid代表备份集完整有效；如果报错代表备份文件损坏，备份集不可用。

风哥教程 113257174

4 物理备份恢复实战

⚠️恢复操作会完全覆盖目标数据目录，恢复前确认目标目录数据已经不需要保留。

4.1 完整实例全量恢复（恢复到备份结束时刻）

把plain格式基线备份直接恢复，重放备份自带WAL，恢复到备份结束时间点。

1. 停止原有数据库实例

pg_ctl -D /fgedudb/fgedudb_data stop -m immediate

2. 清空原有损坏数据目录

rm -rf /fgedudb/fgedudb_data
#复制基线备份集作为新数据目录
cp -r /fgedudb/pg_basebackup/base_20260916 /fgedudb/fgedudb_data
chown -R postgres:postgres /fgedudb/fgedudb_data
chmod 700 /fgedudb/fgedudb_data

3. 创建recovery.signal恢复标记文件，实例识别进入恢复模式

touch /fgedudb/fgedudb_data/recovery.signal

4. 启动实例，自动完成恢复，回放内置WAL，恢复完成自动进入读写状态

pg_ctl -D /fgedudb/fgedudb_data start

5. psql登录验证业务数据。

4.2 PITR时间点恢复（误删数据场景）

业务发生误操作，例如执行TRUNCATE TABLE test_arch，需要恢复到误操作之前时间点，使用外部WAL归档集做PITR时间点恢复。

恢复步骤：

1. 停止实例，拷贝基线备份集到目标数据目录

pg_ctl -D /fgedudb/fgedudb_data stop -m immediate
rm -rf /fgedudb/fgedudb_data
cp -r /fgedudb/pg_basebackup/base_20260916 /fgedudb/fgedudb_data
chown -R postgres:postgres /fgedudb/fgedudb_data

2. 创建recovery.signal，修改postgresql.conf增加恢复相关参数
   编辑/fgedudb/fgedudb_data/postgresql.conf

restore_command = 'cp /fgedudb/pg_archive/%f %p'
recovery_target_time = '2026‑09‑16 14:20:00'
#recovery_target_xid = '' #也可以指定事务ID恢复
#recovery_target_inclusive = off

restore_command告诉数据库去哪里读取归档WAL文件；recovery_target_time指定需要恢复到的历史时间。
3. 创建恢复标记文件

touch /fgedudb/fgedudb_data/recovery.signal

4. 启动实例，数据库回放基线备份之后所有归档WAL，直到到达目标时间，恢复结束自动promote升级为正常实例。

pg_ctl -D /fgedudb/fgedudb_data start

网上搜索风哥教程可以学习全套数据库教程

4.3 restore.signal与恢复参数配置详解

• recovery.signal：文件存在，实例启动进入介质恢复模式；恢复完成该文件会被数据库自动删除。
• restore_command：恢复阶段读取归档WAL的shell命令；PITR时间点恢复必须配置。
• recovery_target_time：时间点恢复，指定时间戳。
• recovery_target_xid：指定事务ID恢复，恢复到某一个事务执行之前。
• recovery_target_inclusive：目标点是否包含该时间/事务。
• recovery_target_timeline：指定恢复的时间线，用于多次PITR之后多时间线场景。

4.4 恢复后时间线切换现象说明

每次PITR时间点恢复完成，数据库会生成新的时间线timeline，新产生WAL段文件属于新timeline；旧归档WAL属于旧时间线，不会被新实例复用。
多次PITR恢复场景，需要保存全部历史timeline归档文件。

上51CTO搜索风哥可以学习全套数据库教程

5 生产自动化备份脚本编写实战

5.1 基线备份定时Shell脚本

保存为pg_base_backup.sh

#!/bin/bash
BACKUP_DATE=$(date +%Y%m%d)
BASE_BACKUP_DIR="/fgedudb/pg_basebackup"
LOG_FILE="/fgedudb/pg_backup_log/base_${BACKUP_DATE}.log"

#清理7天以前基线备份
find ${BASE_BACKUP_DIR} -maxdepth 1 -type d -name "base_*" -mtime +7 -exec rm -rf {} \;

#执行pg_basebackup
pg_basebackup -h 127.0.0.1 -p 5432 -U pg_repl \
-D ${BASE_BACKUP_DIR}/base_${BACKUP_DATE} \
-Fp -Xs -P -v --checkpoint=fast >> ${LOG_FILE} 2>&1

#备份集校验
pg_verifybackup ${BASE_BACKUP_DIR}/base_${BACKUP_DATE} >> ${LOG_FILE} 2>&1
echo "backup finish at $(date)" >> ${LOG_FILE}

赋予执行权限

chmod +x pg_base_backup.sh
chown postgres:postgres pg_base_backup.sh

5.2 WAL归档文件过期清理脚本

WAL归档不能无限制保存，保留30天归档，超过自动清理，保存脚本pg_archive_clean.sh

#!/bin/bash
ARCHIVE_DIR="/fgedudb/pg_archive"
find ${ARCHIVE_DIR} -maxdepth 1 -type f -mtime +30 -delete

chmod +x pg_archive_clean.sh
chown postgres:postgres pg_archive_clean.sh

5.3 crontab定时任务配置

postgres用户配置crontab定时，每天凌晨2点执行基线备份，每日凌晨4点清理过期归档。

crontab -u postgres -e

写入定时内容：

0 2 * * * /fgedudb/pg_base_backup.sh
0 4 * * * /fgedudb/pg_archive_clean.sh

6 跨主机恢复演练（主机fgedu‑net‑cn2）

主机fgedu‑net‑cn2，硬件规格64G内存8CPU，目标是把fgedu‑net‑cn1生成的备份集恢复到fgedu‑net‑cn2，演练灾难跨主机恢复。

1. 将fgedu‑net‑cn1的基线备份目录、pg_archive归档目录拷贝到fgedu‑net‑cn2主机/fgedudb目录。
2. fgedu‑net‑cn2主机停止本地原有实例，清空目标数据目录。
3. 拷贝基线备份集作为PGDATA数据目录，修正目录属主postgres。
4. 配置postgresql.conf里面restore_command指向本机/fgedudb/pg_archive归档目录。
5. 生成recovery.signal标记文件，启动实例执行PITR恢复。
6. psql登录，校验业务库fgedudb，核对表、数据记录数量，确认恢复数据完整。

风哥数据库教程 itpux-com

7 物理备份恢复常见故障排查

7.1 归档不生效，WAL文件没有生成归档副本

排查步骤：

1. 确认wal_level=replica，archive_mode=on，修改参数后必须重启实例。
2. 查看数据库日志，看archive_command执行报错；常见问题目录权限不足。
3. 手动执行SELECT pg_switch_wal();强制切换WAL段，触发归档。
4. 检查archive_command命令语法，%p %f参数是否书写正确。

7.2 pg_basebackup连接失败，复制账号权限问题

报错现象：pg_basebackup连接被拒绝。

1. 账号缺少REPLICATION权限；执行ALTER USER pg_repl WITH REPLICATION;。
2. pg_hba.conf没有配置replication协议访问规则，reload配置。
3. max_wal_senders参数过小，sender进程耗尽，调大max_wal_senders。

7.3 恢复启动报错，缺少backup_label文件

现象：恢复启动数据库报错，找不到backup_label。
根因：不是pg_basebackup生成的有效备份集，普通文件系统冷备份没有backup_label标签文件；恢复必须使用pg_basebackup生成的备份集。

7.4 PITR恢复找不到对应WAL归档文件

1. restore_command路径配置错误，归档目录路径写错。
2. 需要的时间点对应的WAL归档文件已经被提前清理删除，无法继续PITR恢复。
3. 多timeline时间线场景，缺少旧timeline的归档文件。

7.5 备份集校验pg_verifybackup报异常

1. 备份集文件磁盘损坏，存储介质故障。
2. 备份过程中途中断，备份集不完整；需要重新执行pg_basebackup生成新备份。

风哥教程 113257174

8 生产环境备份策略规划

1. 备份组合策略：物理备份 + 逻辑备份
   物理pg_basebackup做集群灾难恢复；搭配pg_dumpall逻辑备份，用于单表误删细粒度恢复。
2. 基线备份周期：根据业务量，建议7天一次完整基线备份；TB级大库可拉长周期，但是归档WAL存储空间要充足。
3. WAL归档保存周期：至少大于两份基线备份间隔，保证PITR时间点恢复可以覆盖业务故障窗口。归档目录存放到独立磁盘，和数据盘分离。
4. 备份异地保存：定期将基线备份集+WAL归档拷贝到异地存储，防止主机磁盘整体故障全部丢失。
5. 强制定期恢复演练：每季度至少做一次完整恢复演练，验证备份集可用；只备份不演练等于没有备份。
6. 监控告警：监控pg_basebackup脚本执行返回码，监控归档目录文件生成，备份失败、归档停滞配置告警通知DBA。

9 风哥针对本文总结

风哥教程本文完整讲解PostgreSQL物理备份整套知识，硬件规格64G内存8CPU，实验主机fgedu‑net‑cn1、fgedu‑net‑cn2，统一目录/fgedudb，数据库fgedudb，业务账号fgedu。

核心要点梳理：

1. PostgreSQL物理备份直接复制实例物理文件，依靠WAL预写日志+连续归档实现PITR任意时间点恢复；物理备份适合实例整体灾难恢复，无法单独恢复单张表，生产建议物理备份搭配逻辑备份pg_dump/pg_dumpall一起使用。
2. WAL归档部署关键点：wal_level=replica、archive_mode=on这两个参数修改需要重启实例；archive_command定义WAL段归档复制命令；创建具备REPLICATION权限的复制账号，pg_hba.conf放行replication协议访问。归档目录操作系统权限必须为postgres用户读写。
3. pg_basebackup是原生热基线备份工具，支持plain普通格式、tar压缩格式、增量基线备份；备份完成务必使用pg_verifybackup校验备份集完整性。增量备份不能直接恢复，需要pg_combinebackup合并生成完整备份集。
4. 恢复分为两种模式：①恢复到备份结束时刻；②PITR时间点恢复到历史指定时间；恢复需要recovery.signal标记文件，restore_command指定归档WAL读取路径；PITR恢复完成会切换数据库时间线timeline。
5. 实战包含定时备份Shell脚本、归档清理脚本、crontab定时任务配置，同时完成跨主机灾难恢复演练，模拟真实生产主机故障场景。
6. 高频故障点：归档不生效需要检查参数是否重启、目录权限；pg_basebackup失败优先排查REPLICATION账号与pg_hba.conf；PITR恢复失败重点确认归档WAL文件是否完整存在。
7. 生产备份策略不能只做本地备份，需要异地存放备份；定期做恢复演练是备份体系最重要一环，备份脚本执行状态、归档生成需要接入监控告警。

物理备份是PostgreSQL生产灾难恢复的基石，但备份只是手段，真正的目标是故障发生时可以成功恢复业务数据。