PostgreSQL 数据备份方案技术选型

PostgreSQL 主流备份分为逻辑备份（pg_dump /pg_dumpall）、物理备份（文件级、pg_basebackup），还有第三方工具（pgBackRest、Barman）。

逻辑备份：导出 SQL / 自定义格式，可跨版本、跨平台，适合小库、迁移；恢复慢。

物理备份：拷贝数据库原始数据文件，速度快，适合大库，版本必须完全一致。

一、逻辑备份（pg_dump）

1. 备份单个数据库

① 导出 SQL 文本格式（可读，可直接执行）

pg_dump -h 127.0.0.1 -p 5432 -U postgres -d mydb -f mydb_backup.sql

• -h 主机，-p端口，-U用户名，-d数据库名，-f输出文件

② 自定义二进制格式（推荐，支持压缩、选择性恢复）

pg_dump -h 127.0.0.1 -p 5432 -U postgres -d mydb -F c -f mydb_backup.dump

• -F c：custom 自定义格式，压缩，只能用 pg_restore 恢复

③ 目录格式（并行备份大库）

pg_dump -h 127.0.0.1 -p 5432 -U postgres -d mydb -F d -j 4 -f ./mydb_dir_backup

• -F d 目录格式，-j 4 4 个并发线程，适合大库

2. 备份所有库 + 全局对象（用户、角色、表空间）pg_dumpall

pg_dump 只备份单个库，角色、权限不会导出，需要 pg_dumpall 导出全局：

# 导出全部数据库+角色到sql文件
pg_dumpall -h 127.0.0.1 -p 5432 -U postgres -f all_db_backup.sql

逻辑备份恢复

恢复 sql 格式

psql -h 127.0.0.1 -p 5432 -U postgres -d mydb -f mydb_backup.sql

恢复 custom / 目录格式，使用 pg_restore

# 恢复整个库
pg_restore -h 127.0.0.1 -p 5432 -U postgres -d mydb mydb_backup.dump

# 目录格式恢复
pg_restore -h 127.0.0.1 -p 5432 -U postgres -d mydb ./mydb_dir_backup

# 只恢复某一张表
pg_restore -h 127.0.0.1 -p 5432 -U postgres -d mydb -t public.t_user mydb_backup.dump

⚠️注意：恢复前目标库最好提前建好；pg_dump 不包含用户角色，恢复前先执行 pg_dumpall 导出的角色部分。

二、物理备份 pg_basebackup（适合生产大库）

物理备份拷贝整个 PG 数据目录，支持流复制备份，备份期间数据库正常读写。

前提

postgresql.conf 需要开启：

wal_level = replica
max_wal_senders = 10

pg_hba.conf 允许备份用户连接。

执行备份

pg_basebackup -h 127.0.0.1 -p 5432 -U postgres -D /data/pg_backup/base_backup -F p -P -X stream

• -D：备份输出目录

• -F p plain 原样文件；-F t打包 tar

• -P显示进度

• -X stream：同时备份 WAL 日志，保证备份一致性

物理备份恢复步骤

1. 停止 postgresql 服务

2. 将原 data 目录改名备份，把/data/pg_backup/base_backup全部拷贝为新 data 目录

3. 修改 postgresql.conf、pg_hba.conf，权限属主改为 postgres 用户

4. 启动数据库，会自动回放 WAL 完成恢复

物理备份只能恢复到完全相同大版本，例如 16 的备份不能给 15 使用。

三、WAL 归档 + PITR 时间点恢复（生产必备）

开启 WAL 归档，可以做到时间点恢复 PITR，可以恢复到故障前任意时间，而不是只能恢复备份时刻。

1. 修改 postgresql.conf

wal_level = replica
archive_mode = on
archive_command = 'cp %p /data/wal_archive/%f'

2. 重载配置，WAL 文件自动归档到 /data/wal_archive

3. 定期执行 pg_basebackup 做基础备份；当故障发生，使用基础备份 + 归档 WAL，可以恢复到任意时间点。

四、备份脚本示例（定时 pg_dump 逻辑备份，Linux crontab）

#!/bin/bash
BACKUP_DIR="/data/pg_dump_backup"
DATE=$(date +%Y%m%d_%H%M%S)
PGPASSWORD="你的密码"
export PGPASSWORD

pg_dump -h 127.0.0.1 -p 5432 -U postgres -d mydb -F c -f ${BACKUP_DIR}/mydb_${DATE}.dump

# 删除7天前旧备份
find ${BACKUP_DIR} -name "*.dump" -mtime +7 -delete

配置 crontab 定时执行：

0 2 * * * /opt/pg_backup.sh >> /var/log/pg_backup.log 2>&1

注意：PGPASSWORD 环境变量不建议生产明文，推荐使用.pgpass文件。

~/.pgpass

127.0.0.1:5432:*:postgres:密码

设置权限：chmod 600 ~/.pgpass，就不需要密码参数。

五、工具选型对比

生产最佳实践

1. 中小库：每日 pg_dump custom 格式备份；同时导出 pg_dumpall 角色权限。

2. 大型库：pg_basebackup 基础备份 + WAL 归档实现 PITR。

3. 定期做恢复演练，备份不验证等于没有备份。

4. 备份文件异地存储，不要和数据库放在同一台机器。

常见坑

1. pg_dump 不会导出角色，恢复后报权限错误：要导入 pg_dumpall 的角色部分。

2. 自定义 dump 文件不能直接 psql 执行，必须用 pg_restore。

3. 物理备份版本不一致直接启动会数据库无法启动。

4. crontab 执行 pg_dump 注意环境变量，很多时候 crontab 找不到 pg_dump 命令，写绝对路径如/usr/pgsql-16/bin/pg_dump。

本文原创作者：易君召，详见：https://www.yijunzhao.cn/authors/yijunzhao，转载请注明出处。

原文链接 https://www.yijunzhao.cn/archives/postgresql-data-backup-solutions-comparison

欢迎访问 小易撩挨踢