PostgreSQL 备份与恢复实战指南

数据库备份不是“有空再做”的事情。真正出问题时，能不能恢复、恢复到什么时间点、恢复需要多久，都会直接决定业务损失。

本文聚焦 PostgreSQL 常见的逻辑备份和恢复流程，适合中小型项目、单机服务、日常运维和迁移场景。

一、备份方式选择

PostgreSQL 常见备份方式：

1
2
3

pg_dump      逻辑备份单个数据库
pg_dumpall   备份所有数据库和全局对象
pg_basebackup 物理备份整个集群

日常最常用的是 pg_dump。它生成的是 SQL 或自定义格式文件，适合迁移、回滚、定时备份。

如果数据库很大，或者需要时间点恢复，才需要进一步考虑 WAL 归档和物理备份。

二、使用 pg_dump 备份

备份为自定义格式：

1	pg_dump -h 127.0.0.1 -U myapp -d myapp -F c -f myapp.dump

参数说明：

-h：数据库地址
-U：用户名
-d：数据库名
-F c：自定义格式，适合用 pg_restore 恢复
-f：输出文件

自定义格式比纯 SQL 更灵活，恢复时可以选择并行、只恢复某些对象等。

三、备份为 SQL 文件

1	pg_dump -h 127.0.0.1 -U myapp -d myapp -f myapp.sql

SQL 文件可读性强，适合小库、排查和简单迁移。但大库恢复时通常不如自定义格式灵活。

压缩备份：

1	pg_dump -h 127.0.0.1 -U myapp -d myapp \| gzip > myapp.sql.gz

四、恢复自定义格式备份

先创建目标数据库：

1	createdb -h 127.0.0.1 -U postgres myapp_restore

恢复：

1	pg_restore -h 127.0.0.1 -U postgres -d myapp_restore myapp.dump

如果希望清理已有对象后恢复：

1	pg_restore -h 127.0.0.1 -U postgres -d myapp_restore --clean --if-exists myapp.dump

生产环境执行 --clean 要谨慎，它会删除目标库中已有对象。

五、恢复 SQL 文件

1	psql -h 127.0.0.1 -U postgres -d myapp_restore -f myapp.sql

恢复压缩 SQL：

1	gunzip -c myapp.sql.gz \| psql -h 127.0.0.1 -U postgres -d myapp_restore

恢复前建议使用空数据库，避免对象重复、约束冲突或脏数据混入。

六、定时备份脚本

示例脚本 /opt/scripts/backup-postgres.sh：

#!/usr/bin/env bash
set -euo pipefail

BACKUP_DIR="/data/backups/postgres"
DB_HOST="127.0.0.1"
DB_USER="myapp"
DB_NAME="myapp"
DATE="$(date +%Y%m%d-%H%M%S)"

mkdir -p "$BACKUP_DIR"

pg_dump -h "$DB_HOST" -U "$DB_USER" -d "$DB_NAME" -F c -f "$BACKUP_DIR/$DB_NAME-$DATE.dump"

find "$BACKUP_DIR" -type f -name "*.dump" -mtime +14 -delete

设置执行权限：

1	chmod +x /opt/scripts/backup-postgres.sh

配置 cron：

1	0 3 * * * /opt/scripts/backup-postgres.sh >> /var/log/postgres-backup.log 2>&1

这表示每天凌晨 3 点备份，并保留最近 14 天。

七、备份校验

只生成备份文件不够，还要定期验证能否恢复。

基本校验：

1	ls -lh /data/backups/postgres

更可靠的方式是定期恢复到测试库：

1 2	createdb myapp_restore_test pg_restore -d myapp_restore_test /data/backups/postgres/myapp-20260604-030000.dump

然后检查核心表数量：

1 2	select count() from users; select count() from orders;

没有经过恢复验证的备份，只能算“看起来像备份”。

八、权限和安全

备份文件可能包含用户数据、Token、订单信息等敏感内容。建议：

备份目录只允许运维用户读取
传输到对象存储前进行加密
不要把备份文件提交到 Git
备份日志不要输出数据库密码
离线备份和异地备份至少保留一份

九、恢复演练清单

真正事故发生前，至少演练一次：

找到最近一次可用备份
在新数据库恢复
校验核心表数据
修改应用连接串指向恢复库
验证核心业务接口
记录恢复耗时