PostgreSQL数据库如何进行定时备份?pg_dump与恢复验证教程

PostgreSQL 用起来很舒服,但在"备份"这件事上,很多人的习惯还停留在"手动 pg_dump 一下,丢到 /tmp 就完事"。真出事的时候才发现:备份文件是空的(因为权限不足或库名写错)、备份存在数据库同一块盘上(盘挂了一起没)、或者备份能生成但根本恢复不回去。备份的价值不在于"生成了文件",而在于"能恢复出正确的数据"。 这句话在 PostgreSQL 上尤其成立,因为 pg_dump 有很多容易忽略的坑。

这篇文章给一套可直接落地的 PostgreSQL 定时备份方案:先讲清 pg_dump、pg_dumpall、pg_basebackup 的适用场景,再给出真实可用的备份脚本和 crontab 定时任务,然后重点讲恢复验证——这是整套方案里最容易被跳过、却最重要的一环。文中的命令、版本、路径都按真实环境给出,并区分 RHEL/CentOS/Rocky(dnf/yum)与 Debian/Ubuntu(apt)。

需要提前说明:pg_dump 是逻辑备份,适合中小型库和需要"迁移、单表恢复"的场景;超大库(几百 GB 以上)更适合物理备份(pg_basebackup + WAL 归档,PITR)。文中给出的容量阈值都是经验值,请结合自己的库大小和恢复时间要求选择。

一、先选对工具:三种备份方式

PostgreSQL 官方工具里,和备份相关的核心有三个,用途完全不同:

工具 类型 备份对象 恢复方式 适用场景
pg_dump 逻辑备份 单个数据库 psql / pg_restore 中小库、迁移、单表恢复
pg_dumpall 逻辑备份 整个实例(含全局对象) psql 需要连角色、表空间一起备份
pg_basebackup 物理备份 整个数据目录 起实例 + WAL 回放 大库、需要 PITR 时间点恢复

pg_dump 只备份一个数据库,不含角色(role)、表空间(tablespace)等实例级对象。所以如果你的库依赖特定的角色、扩展(extension)或表空间,只恢复 pg_dump 出来的库是不够的,必须另外记录这些全局对象,或者用 pg_dumpall。

pg_basebackup 是物理备份,直接复制数据文件,配合连续归档(WAL archiving)能实现"恢复到任意时间点",是大库生产环境的标准做法。但它的配置更复杂(要开 archive_mode、配 archive_command),本文重点放在更通用、更易上手的 pg_dump 定时备份。

版本兼容性提醒: pg_dump 的版本建议不低于数据库服务器版本(低版本客户端连高版本服务器可能报错)。备份时最好用与服务端同版本的客户端工具。

二、安装客户端工具

如果备份是单独在一台机器上跑(推荐!避免和数据库同机同盘),需要安装对应版本的客户端。

# RHEL/CentOS/Rocky 系(以 PostgreSQL 15 为例,PGDG 官方仓库)
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf install -y postgresql15

# 老版本 CentOS 7 用 yum
# sudo yum install -y postgresql15

# Debian/Ubuntu 系
sudo apt update
sudo apt install -y postgresql-client-15
# 通用(版本随发行版):sudo apt install -y postgresql-client

验证:

pg_dump --version
psql --version

三、pg_dump 核心用法与关键参数

一个健壮的备份命令,参数不能少。先看常用参数的用途:

参数 作用 是否建议
-h / -p / -U / -d 主机/端口/用户/库名 必备
-F c 输出自定义格式(二进制、压缩、可并行恢复) 强烈建议
-F p 纯 SQL 文本格式 便于人工查看,但大
-f 输出到文件 必备
-j N 并行导出(仅 -F d 目录格式支持) 大库提速
-Z 6 压缩级别(对 -F c 生效) 省空间
--no-owner 恢复时不设 owner,便于跨环境 跨实例恢复建议
--no-privileges 不导出权限 视情况
-t / -T 只导出某些表 / 排除某些表 单表场景
-n / -N 只导出某模式 / 排除 多 schema 场景

一个推荐的、生产可用的备份命令:

# 自定义格式 + 压缩(最通用的选择)
PGPASSWORD='your_password' pg_dump \
  -h 127.0.0.1 -p 5432 -U backup_user -d your_db \
  -F c -Z 6 --no-owner \
  -f /backup/pg/your_db_$(date +%F_%H%M).dump

不用密码交互,也可以用 .pgpass 文件(更安全,避免密码出现在命令行和进程列表里):

# 在备份账号家目录创建 ~/.pgpass,权限必须是 600
echo "127.0.0.1:5432:your_db:backup_user:your_password" >> ~/.pgpass
chmod 600 ~/.pgpass
# 之后 pg_dump 就不用 PGPASSWORD 了

安全提示:把密码写在命令行里,会出现在 ps aux 的进程列表中,别的用户可能看到;也会留在 shell 历史里。生产环境请用 .pgpass 或环境变量,并限制文件权限。

如果要把整个实例连角色一起备份:

# pg_dumpall:备份所有数据库 + 全局对象(角色、表空间定义等)
pg_dumpall -h 127.0.0.1 -U postgres -f /backup/pg/all_$(date +%F).sql

四、定时备份脚本

下面是一个可以直接用的备份脚本,包含:设置严格模式、失败即退出、按日期命名、压缩、保留最近 N 天、并记录日志。

#!/usr/bin/env bash
# /opt/scripts/pg_backup.sh
set -euo pipefail

# ---- 配置区 ----
PGHOST="127.0.0.1"
PGPORT="5432"
PGUSER="backup_user"
DBNAME="your_db"
BACKUP_DIR="/backup/pg"
RETENTION_DAYS=14          # 经验值:保留最近 14 天
LOG_FILE="/var/log/pg_backup.log"

STAMP="$(date +%F_%H%M%S)"
OUT="${BACKUP_DIR}/${DBNAME}_${STAMP}.dump"

mkdir -p "${BACKUP_DIR}"

echo "[$(date '+%F %T')] 开始备份 ${DBNAME}" >> "${LOG_FILE}"

# 自定义格式备份;--no-owner 便于跨环境恢复
pg_dump -h "${PGHOST}" -p "${PGPORT}" -U "${PGUSER}" -d "${DBNAME}" \
        -F c -Z 6 --no-owner -f "${OUT}" >> "${LOG_FILE}" 2>&1

# 校验文件非空(pg_dump 失败时可能生成 0 字节文件)
if [ ! -s "${OUT}" ]; then
  echo "[$(date '+%F %T')] 错误:备份文件为空 ${OUT}" >> "${LOG_FILE}"
  exit 1
fi

# 校验备份内容可被 pg_restore 读取(只列表,不真正恢复)
if ! pg_restore --list "${OUT}" > /dev/null 2>>"${LOG_FILE}"; then
  echo "[$(date '+%F %T')] 错误:备份文件损坏 ${OUT}" >> "${LOG_FILE}"
  exit 1
fi

echo "[$(date '+%F %T')] 备份完成:${OUT} ($(du -h "${OUT}" | cut -f1))" >> "${LOG_FILE}"

# 清理过期备份
find "${BACKUP_DIR}" -name "${DBNAME}_*.dump" -mtime +${RETENTION_DAYS} -delete
echo "[$(date '+%F %T')] 已清理 ${RETENTION_DAYS} 天前的备份" >> "${LOG_FILE}"

赋权并测试:

chmod +x /opt/scripts/pg_backup.sh
sudo -u postgres /opt/scripts/pg_backup.sh    # 用数据库账号身份跑一遍
cat /var/log/pg_backup.log

配置 crontab 定时执行(比如每天凌晨 2 点):

# 编辑 root 或 postgres 用户的 crontab
sudo crontab -e

# 每天 02:00 执行备份;把输出重定向,避免 cron 发邮件
0 2 * * * /opt/scripts/pg_backup.sh >/dev/null 2>&1

# 可选:每周日凌晨 3 点做一次全实例备份
0 3 * * 0 pg_dumpall -h 127.0.0.1 -U postgres -f /backup/pg/all_$(date +\%F).sql

Cron 坑提示:crontab 里 % 是特殊字符,日期格式化 date +\%F 要转义。另外 cron 的环境变量很干净,脚本里最好用绝对路径、显式设置 PGHOST 等,别依赖登录 shell 的配置。

五、恢复验证:这一步不能省

很多团队备份做得好,但从没验证过恢复,直到真出事才第一次尝试——这是最危险的。恢复验证要定期做,最好是自动化的。

恢复单个库(自定义格式)到测试实例:

# 1) 建一个空的测试库
createdb -h 127.0.0.1 -U postgres restore_test

# 2) 用 pg_restore 恢复;-j 并行,--exit-on-error 遇到错误就停(便于发现)
pg_restore -h 127.0.0.1 -U postgres -d restore_test \
  -j 4 --no-owner --exit-on-error \
  /backup/pg/your_db_2027-01-01_020000.dump

# 3) 如果只想看备份里有什么,不恢复:
pg_restore --list /backup/pg/your_db_2027-01-01_020000.dump | head -50

从纯 SQL 文本恢复:

psql -h 127.0.0.1 -U postgres -d restore_test -f /backup/pg/your_db.sql

验证数据是否完整: 恢复后要做数据校验,不能只看"恢复过程没报错"。

-- 对比关键表的行数(和备份前记录的值比对)
SELECT relname, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC
LIMIT 20;

-- 精确计数(重要表)
SELECT COUNT(*) FROM public.orders;
SELECT COUNT(*) FROM public.users;

-- 检查最新数据的边界(确认备份的时间点正确)
SELECT MAX(created_at) FROM public.orders;

一个实用的做法是:备份脚本每天把关键表的行数快照记录到一个文件里,恢复验证时比对。如果差异超出预期,说明备份有问题。

pg_dumpall 的恢复:

# 恢复到实例(会重建角色、数据库)
psql -h 127.0.0.1 -U postgres -f /backup/pg/all_2027-01-01.sql

风险提示:恢复操作会覆盖目标库的数据。恢复验证一定要在独立的测试实例或测试库上做,绝不要在生产库上直接恢复备份,否则就是拿备份把线上数据覆盖掉。恢复前确认目标库名、端口、实例,别连错。

六、把备份做扎实:进阶建议

备份要异地、异盘。 备份文件和数据库放同一台机器的同一块盘,等于没备份。规划里应该把备份同步到另一台机器或对象存储(用 rsync、scp 或对象存储客户端)。主流云厂商的对象存储配合生命周期策略是常见做法。

定期做恢复演练。 建议至少每月一次,把备份恢复到测试环境,跑通完整流程,记录 RTO(恢复所需时间)。只有演练过,才知道备份到底靠不靠谱、恢复要多久。

大库考虑物理备份 + PITR。 当库达到几百 GB,逻辑备份的耗时和恢复时间会变得不可接受。这时应上 pg_basebackup 加 WAL 归档,实现增量和时间点恢复:

# postgresql.conf 中的归档相关配置
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /backup/pg_wal/%f && cp %p /backup/pg_wal/%f'
# 全量物理备份
pg_basebackup -h 127.0.0.1 -U replicator -D /backup/pg_base -Ft -z -P

监控备份是否成功。 备份脚本失败要能告警:可以检查日志最后一行、备份文件的最新修改时间。定期(如每天早上)确认"昨天的备份文件存在且非空",比事后才发现备份一直是失败的强得多。

注意备份期间的资源。 pg_dump 会占用数据库连接和 CPU/IO,大库备份最好放在业务低峰期,并考虑限速(--compress 配合外部压缩,或用 ionice/nice 降低优先级)。

常见问题

Q1:pg_dump 备份时数据库会锁表吗?
默认不会锁表影响读写。pg_dump 使用一致性快照(依赖 MVCC),能保证导出的数据是一致的。但对某些操作(如 ALTER TABLE)可能需求 ACCESS EXCLUSIVE 锁,备份期间避免做 DDL。加 --serializable-deferrable 可进一步减少冲突(视版本和场景)。

Q2:为什么 pg_dump 备份出来是 0 字节?
常见原因是:权限不足、库名写错、磁盘写满、或 pg_hba.conf 认证失败。脚本里一定要检查文件非空([ -s file ]),否则会安静地生成一个没用的空文件。

Q3:-F c 和 -F p 到底选哪个?
优先 -F c(自定义格式):体积小、可压缩、可并行恢复、可选择性恢复单表。-F p(纯 SQL)适合需要人工查看或管道处理的场景,但不支持并行恢复,文件也大。

Q4:备份怎么保证能恢复?
唯一的办法是实际做一次恢复验证。定期把备份恢复到测试库,核对行数和关键数据。pg_restore --list 只能证明文件格式没坏,证明不了数据正确。

Q5:crontab 里脚本跑失败,怎么排查?
先手动用相同用户执行脚本看报错;检查 PGHOST、.pgpass 权限(600)、路径是否为绝对路径;看备份日志 /var/log/pg_backup.log;注意 cron 的 PATH 与登录环境不同。把命令输出重定向到日志文件是关键。

总结

  • 选对工具:单库逻辑备份用 pg_dump,整实例(含角色)用 pg_dumpall,大库/需要 PITR 用 pg_basebackup + WAL 归档。
  • 备份命令用 -F c -Z 6 --no-owner,密码走 .pgpass(权限 600),别写在命令行里。
  • 用脚本 + crontab 定时备份,脚本里必须做非空校验和 pg_restore --list 校验,失败即告警,保留最近 N 天。
  • 备份要有异地/异盘副本,并定期做恢复演练——能恢复的备份才算备份。
  • 恢复操作会覆盖数据,验证一律在测试实例进行;大库到几百 GB 应转向物理备份方案。