PostgreSQL 备份：pg_dump 逻辑备份与物理备份怎么选

我是小够。前面讲过 MySQL 的备份脚本，但不少应用（比如很多开源项目、数据分析场景）用的是 PostgreSQL。PG 的备份工具体系和 MySQL 不太一样，这篇一次讲透：三种方案，分别什么时候用。

方案一：pg_dump 逻辑备份（最常用）

把数据库导出成 SQL 文本，和 mysqldump 一个思路：

# 备份单个数据库
pg_dump -U postgres -F c -f pg-backup-2025-08-19.dump mydb
# -F c 是自定义压缩格式，恢复快、体积小

恢复：

pg_restore -U postgres -d mydb pg-backup-2025-08-19.dump

优点：简单，跨版本恢复方便，单表也能导。缺点：大数据量慢，备份期间是某一时刻的快照。

定时任务里这样写：

pg_dump -U postgres -F c -f /backup/pg-$(date +%F).dump mydb

记得 .pgpass 文件配好密码，别把密码写在脚本里明文躺着。

方案二：pg_dumpall（全实例）

pg_dumpall -U postgres | gzip > pg-all-2025-08-19.sql.gz

连用户、权限一起导出来。适合实例级别的完整备份，恢复时重建整个实例。小够建议每周做一次全实例，平时做单库。

方案三：pg_basebackup 物理备份 + WAL

这是 PG 的杀手锏：在线热备，备份期间数据库正常读写：

pg_basebackup -U postgres -D /backup/pg-base -F tar -z -P

配合 WAL 归档（archive_mode = on），可以恢复到任意时间点（PITR）。这是金融级的要求，个人博客用不上，但业务数据库值得配。

配置 WAL 归档（postgresql.conf）：

archive_mode = on
archive_command = 'cp %p /backup/wal/%f'

三种方案怎么选

恢复演练：一定要做

# 建个测试库，把备份恢复进去验证
createdb -U postgres restore_test
pg_restore -U postgres -d restore_test pg-backup-2025-08-19.dump
# 连上去看表和数据对不对
psql -U postgres -d restore_test -c "SELECT count(*) FROM your_table;"

备份没恢复过，等于没备份。这句话小够每篇都要说一遍，因为真的有人栽过。

小够的建议

• 备份文件同样要加密+异地，前面讲的 gpg 和 rclone 那套直接套用
• 大数据库的 pg_dump 会慢，错峰到凌晨跑
• 版本升级前（比如 PG 大版本升级）一定要先做一次完整逻辑备份，物理备份跨大版本恢复不了
• 保留策略照 GFS 那套来，数据库备份多留几份日备

数据库是应用的心脏，备份是心脏的保险。pg_dump 五分钟能配好，别拖到丢数据的那天才后悔。

WAL 归档完整配置

想做到"任意时间点恢复"，WAL 归档是必须的。完整步骤：

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /backup/wal/%f && cp %p /backup/wal/%f'
max_wal_senders = 3

# 先做一个基础备份
pg_basebackup -U postgres -D /backup/pg-base -F tar -z -P

之后 WAL 日志会持续归档到 /backup/wal/。恢复时：解压基础备份 → 重放 WAL → 指定恢复到某个时间点。

# recovery.conf / postgresql.auto.conf
restore_command = 'cp /backup/wal/%f %p'
recovery_target_time = '2025-08-19 14:00:00'

这就是 PITR（Point-In-Time Recovery），金融级的数据保护。配置一次，之后全自动。

主从复制简介

WAL 的另一个用途是主从复制：备库实时重放主库的 WAL，数据几乎实时同步。

# 备库的 postgresql.conf
primary_conninfo = 'host=主库IP port=5432 user=replicator'

主从不只是备份，更是容灾：主库挂了，备库顶上（配合前面的主备容灾篇）。小业务用不上，但要知道有这条路。

大库备份策略

数据库上百 GB 时，pg_dump 会跑很久：

1. 并行导出：pg_dump -j 4 开 4 个并行（目录格式 -F d）
2. 物理备份为主：pg_basebackup 比逻辑备份快得多
3. 分开备份：核心业务库每天全备，日志表这类可以低频
4. 备库上备份：有主从的话，在备库上做备份，不影响主库业务

备份验证自动化

#!/bin/bash
# 每天备份后，自动恢复到测试库验证
createdb -U postgres verify_$(date +%F)
pg_restore -U postgres -d verify_$(date +%F) /backup/pg-$(date +%F).dump
psql -U postgres -d verify_$(date +%F) -c "SELECT count(*) FROM orders;" || echo "VERIFY FAILED" | mail -s "备份验证失败" [email protected]
# 验证完删掉测试库
dropdb -U postgres verify_$(date +%F)

常见问题

Q：pg_dump 备份时锁表吗？ A：默认不锁全表，但长事务可能被阻塞。大库用 --single-transaction，或者直接上 pg_basebackup 物理备份。

Q：WAL 日志会把磁盘撑爆吗？ A：会，如果归档命令失败，WAL 会一直堆积。监控 /backup/wal/ 的大小，归档失败要告警。这是 WAL 方案唯一的运维负担。

Q：从 MySQL 迁到 PostgreSQL，备份要注意什么？ A：完全不同的两套工具，别混用。迁移时先用逻辑备份（SQL 文本）过渡，稳定后再配 WAL 归档。

备份策略模板：小业务照抄

#!/bin/bash
# pg-backup.sh
DATE=$(date +%F)
BACKUP_DIR=/backup/postgres

# 每天：单库逻辑备份
pg_dump -U postgres -F c -f $BACKUP_DIR/daily/mydb-$DATE.dump mydb

# 每周日：全实例备份
if [ $(date +%u) -eq 7 ]; then
    pg_dumpall -U postgres | gzip > $BACKUP_DIR/weekly/all-$DATE.sql.gz
fi

# 保留策略
find $BACKUP_DIR/daily -mtime +7 -delete
find $BACKUP_DIR/weekly -mtime +28 -delete

# 加密 + 异地（前面讲过的套路）
gpg --encrypt --recipient "[email protected]" --trust-model always \
    --batch --yes $BACKUP_DIR/daily/mydb-$DATE.dump
rclone copy $BACKUP_DIR/ mybackup:pg-backup/

一套脚本，备份、保留、加密、异地全齐了。

升级前的备份

PG 大版本升级（比如 15→16）是高危操作，流程：

1. 升级前：完整逻辑备份（pg_dumpall），这是底线
2. 读官方升级说明，看有没有不兼容变更
3. 先在测试环境演练一遍
4. 升级后验证业务，跑核心 SQL 看结果对不对

物理备份不能跨大版本恢复，这点和 MySQL 不一样，记死了：大版本升级前一定要有逻辑备份。

监控备份

# 检查今天的备份文件存在且非空
f=$(ls -t /backup/postgres/daily/*.dump | head -1)
[ -s "$f" ] || echo "PG备份异常" | mail -s "告警" [email protected]

备份脚本 exit 0 不代表备份成功了，文件存在、非空、能恢复，三层检查才保险。

常见问题

Q：pg_dump 很慢，业务卡了？ A：pg_dump 默认不锁表，但大库导出时 IO 压力大。用 --jobs 并行（目录格式），或者错峰到凌晨，再或者去备库上做备份。

Q：WAL 归档占了好多空间？ A：归档的 WAL 只在"基础备份之后"的才有用，做了新的基础备份，旧的 WAL 就可以删。写个清理脚本，或者用 pg_archivecleanup。

Q：测试库恢复验证要多久？ A：看库大小。小库几分钟，大库可能半小时。验证可以抽样：每天全量验证太重，改成每天验证文件完整性、每周做一次完整恢复验证。

pgBackRest：进阶备份工具

数据量大了，pgBackRest 是更专业的选择：

• 并行备份/恢复，比 pg_basebackup 快
• 内置压缩、加密
• 支持 S3 直接备份，不用经本地中转
• 保留策略、过期清理一条龙

# 备份到 S3
pgbackrest --stanza=mydb --type=full backup

小库用 pg_dump 就够了，库上百 GB 了再上 pgBackRest。工具是为规模服务的，别提前优化。

备份清单：每次对照打勾

# /backup/checklist.txt
[ ] pg_dump 每天凌晨 2 点，文件存在且非空
[ ] pg_dumpall 每周日，文件存在
[ ] WAL 归档在跑（/backup/wal/ 有新文件）
[ ] 备份已加密
[ ] 备份已同步到异地
[ ] 本周做过一次恢复验证

贴在备忘录里，每周看一眼。备份最怕的不是没做，是以为做了其实没做。

从 MySQL 迁移过来的注意

• pg_dump 对应 mysqldump，但参数完全不同，别凭记忆敲
• PG 的用户权限在 pg_dumpall 里，不在单库备份里
• 字符集：PG 默认 UTF8，一般不用操心，比 MySQL 省心
• 自增 ID、布尔类型这些语法差异，迁移时用 pgloader 工具转

小够的数据库备份观

数据库备份就三句话：每天自动备、异地加密存、定期恢复验。做到这三条，99% 的数据库灾难都扛得住。剩下的 1% 是"备份服务器和业务机同时着火"这种，认命吧，真遇到了也不是备份能解决的。

备份失败的常见原因

1. 磁盘满了：备份写到一半失败，df -h 先看
2. 权限不对：postgres 用户写不了备份目录，chown 修一下
3. 密码过期：.pgpass 里的密码和实际不一致，测试连接先
4. WAL 归档失败：归档命令报错，WAL 堆积，pg_ls_dir('pg_wal') 看数量

备份脚本加上 set -e，出错就停，别静默失败。失败了要告警，这是底线。