PostgreSQL 备份与恢复详解 —— pg_dump / pg_restore 全方位指南

✅ PostgreSQL 备份与恢复详解 —— pg_dump / pg_restore 全方位指南

数据安全是数据库管理的核心。本篇将带你深入掌握 PostgreSQL 最核心的备份恢复工具 —— pg_dump 和 pg_restore，涵盖逻辑备份、物理备份、增量备份、自动化脚本等企业级实践。

---

🧭 一、备份类型总览

类型	工具/方法	特点	适用场景
逻辑备份	pg_dump / pg_dumpall	导出 SQL 语句或自定义格式，跨版本兼容，灵活恢复	日常备份、迁移、开发环境
物理备份	pg_basebackup / 文件系统快照	复制数据文件，速度快，需相同版本，支持 PITR（时间点恢复）	生产环境全量备份 + WAL 归档
增量备份	WAL 归档 + pg_receivewal	基于 WAL 日志，实现秒级恢复	高可用、金融级数据安全

💡 本篇重点：逻辑备份（pg_dump / pg_restore） —— 最常用、最灵活！

---

一、pg_dump —— 逻辑备份工具

---

✅ 1. 基本语法

pg_dump [选项] [数据库名] > 备份文件

常用选项：

选项	说明
-U username	指定用户名
-h host	指定主机
-p port	指定端口
-F format	输出格式：p(plain), c(custom), d(dir), t(tar)
-f file	指定输出文件
-v	详细模式
-j n	并行备份（仅 -Fd 格式）
-t table	只备份指定表
-n schema	只备份指定模式
--data-only	只备份数据（无结构）
--schema-only	只备份结构（无数据）
--inserts	使用 INSERT 语句（默认是 COPY）
--column-inserts	INSERT 包含列名（便于部分恢复）
--exclude-table	排除指定表

---

✅ 2. 四大备份格式详解

▶ 1. 纯文本格式（-Fp，默认）

pg_dump -U postgres mydb > mydb.sql

• 优点：可读性强，可用 psql 恢复，支持跨版本
• 缺点：恢复慢，不支持选择性恢复

▶ 2. 自定义格式（-Fc）

pg_dump -U postgres -Fc mydb > mydb.dump

• 优点：压缩率高，支持并行恢复，支持选择性恢复（pg_restore）
• 缺点：只能用 pg_restore 恢复

▶ 3. 目录格式（-Fd）

pg_dump -U postgres -Fd -f mydb_backup_dir mydb

• 优点：支持并行备份/恢复，每个表独立文件，便于管理
• 缺点：占用目录空间

▶ 4. tar 格式（-Ft）

pg_dump -U postgres -Ft mydb > mydb.tar

• 优点：兼容 tar 工具
• 缺点：不支持压缩，不支持并行

💡 生产环境推荐：-Fc 或 -Fd（支持并行和选择性恢复）

---

✅ 3. 实用备份示例

示例1：完整备份（推荐格式）

# 自定义格式（压缩+并行恢复）
pg_dump -U postgres -h localhost -p 5432 -Fc -f /backup/mydb_$(date +%Y%m%d).dump mydb

# 目录格式（并行备份）
pg_dump -U postgres -Fd -j 4 -f /backup/mydb_dir mydb

示例2：仅备份结构

pg_dump -U postgres --schema-only -f mydb_schema.sql mydb

示例3：仅备份数据

pg_dump -U postgres --data-only -f mydb_data.sql mydb

示例4：备份特定表

pg_dump -U postgres -t users -t orders -f mydb_tables.dump mydb

示例5：排除某些表

pg_dump -U postgres --exclude-table=log_* -f mydb_no_logs.dump mydb

示例6：使用 INSERT 语句（便于部分编辑）

pg_dump -U postgres --inserts --column-inserts -f mydb_inserts.sql mydb

---

✅ 4. pg_dumpall —— 备份整个集群

# 备份所有数据库 + 全局对象（角色、表空间等）
pg_dumpall -U postgres -f cluster_backup.sql

# 只备份全局对象
pg_dumpall -U postgres --globals-only -f globals.sql

# 只备份某个数据库（等同 pg_dump）
pg_dumpall -U postgres -l mydb -f mydb.sql

⚠️ pg_dumpall 只能输出纯文本格式（-Fp）

---

二、pg_restore —— 逻辑恢复工具

---

✅ 1. 基本语法

pg_restore [选项] [备份文件]

常用选项：

选项	说明
-U username	指定用户名
-h host	指定主机
-p port	指定端口
-d dbname	指定目标数据库
-C	创建数据库（需连接到 postgres 数据库）
-c	恢复前清理（DROP 对象）
-j n	并行恢复
-t table	只恢复指定表
-n schema	只恢复指定模式
-l	列出备份内容（生成列表文件）
-L list-file	按列表文件选择性恢复
--data-only	只恢复数据
--schema-only	只恢复结构
--verbose	详细输出

---

✅ 2. 恢复实战示例

示例1：完整恢复（自定义格式）

# 方法1：直接恢复到现有数据库
pg_restore -U postgres -d mydb -j 4 mydb.dump

# 方法2：创建新数据库并恢复
pg_restore -U postgres -C -d postgres -j 4 mydb.dump
# -C 会创建数据库，-d postgres 表示连接到 postgres 数据库执行创建

示例2：选择性恢复（单表）

pg_restore -U postgres -d mydb -t users mydb.dump

示例3：仅恢复数据（跳过结构）

pg_restore -U postgres -d mydb --data-only mydb.dump

示例4：清理后恢复（先 DROP 再 CREATE）

pg_restore -U postgres -d mydb -c mydb.dump

示例5：列出备份内容并选择性恢复

# 生成内容列表
pg_restore -l mydb.dump > mydb.list

# 编辑 mydb.list，删除不需要的行（如注释掉某些表）
# 只恢复 users 表：
# ;2345; 0 12345 TABLE users postgres

# 按列表恢复
pg_restore -L mydb.list -d mydb mydb.dump

示例6：并行恢复（大幅提升速度）

pg_restore -U postgres -d mydb -j 8 -Fd mydb_backup_dir

💡 并行恢复要求：

• 备份格式为 -Fc 或 -Fd
• PostgreSQL 9.3+

---

三、备份恢复最佳实践

---

✅ 1. 自动化备份脚本（Linux 示例）

#!/bin/bash
# backup_postgres.sh

BACKUP_DIR="/backup/postgres"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="mydb"
USER="postgres"

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行备份
pg_dump -U $USER -Fc -f $BACKUP_DIR/${DB_NAME}_${DATE}.dump $DB_NAME

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

# 记录日志
echo "[$(date)] 备份完成: ${DB_NAME}_${DATE}.dump" >> /var/log/pg_backup.log

添加到 crontab（每天凌晨2点）：

0 2 * * * /path/to/backup_postgres.sh

---

✅ 2. 增量备份策略（WAL + 基础备份）

# 1. 启用 WAL 归档（postgresql.conf）
wal_level = replica
archive_mode = on
archive_command = 'cp %p /wal_archive/%f'

# 2. 定期基础备份
pg_basebackup -D /backup/base -Ft -z -P

# 3. 恢复时先恢复基础备份，再应用 WAL 日志

本篇重点逻辑备份，物理备份另文详解。

---

✅ 3. 备份验证与监控

验证备份完整性：

# 检查自定义格式备份
pg_restore -l mydb.dump > /dev/null && echo "备份有效"

# 恢复到测试数据库验证
createdb test_restore
pg_restore -d test_restore mydb.dump
psql -d test_restore -c "SELECT COUNT(*) FROM users;"
dropdb test_restore

监控备份：

# 检查备份文件大小
ls -lh /backup/*.dump

# 检查备份日志
tail -f /var/log/pg_backup.log

---

✅ 4. 常见问题与解决

❓ 1. 权限不足

# 确保用户有 CONNECT 和 SELECT 权限
GRANT CONNECT ON DATABASE mydb TO backup_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;

❓ 2. 恢复时对象已存在

# 使用 -c 选项先清理
pg_restore -c -d mydb mydb.dump

# 或手动 DROP DATABASE
dropdb mydb
createdb mydb
pg_restore -d mydb mydb.dump

❓ 3. 编码问题

# 指定客户端编码
pg_dump -U postgres --encoding=UTF8 -f backup.sql mydb

❓ 4. 大表备份中断

# 使用目录格式 + 并行，支持断点续备
pg_dump -Fd -j 4 -f backup_dir mydb

---

四、高级技巧

---

✅ 1. 压缩备份

# pg_dump + gzip
pg_dump -U postgres mydb | gzip > mydb.sql.gz

# 恢复
gunzip -c mydb.sql.gz | psql -U postgres mydb

# 或使用 -Fc 格式（内置压缩）
pg_dump -Fc -f mydb.dump mydb  # 压缩率通常 70-90%

---

✅ 2. 远程备份

# 从远程服务器备份
pg_dump -h remote_host -U postgres -Fc mydb > mydb.dump

# 通过 SSH 备份
ssh user@remote "pg_dump -U postgres mydb" > mydb.sql

---

✅ 3. 备份到云存储（AWS S3 示例）

# 备份并上传到 S3
pg_dump -U postgres -Fc mydb | aws s3 cp - s3://mybucket/mydb.dump

# 从 S3 恢复
aws s3 cp s3://mybucket/mydb.dump - | pg_restore -U postgres -d mydb

---

✅ 4. 使用 .pgpass 文件免密

创建 ~/.pgpass 文件：

hostname:port:database:username:password
localhost:5432:mydb:postgres:mypassword

设置权限：

chmod 600 ~/.pgpass

---

🎯 五、实践任务

请完成以下操作：

1. 使用 pg_dump 备份你的数据库（自定义格式）
2. 创建一个新数据库 mydb_restore
3. 使用 pg_restore 恢复备份到新数据库
4. 尝试只恢复某一张表
5. 编写一个自动化备份脚本，每天执行并保留最近7天备份
6. 验证备份文件的完整性

---

📊 备份策略推荐表

场景	推荐工具	格式	频率	保留策略
开发/测试环境	pg_dump	-Fc	每日	7天
生产环境（中小型）	pg_dump	-Fd -j4	每日	30天
生产环境（大型）	pg_basebackup + WAL	物理	每周全量 + 每日增量	60天
数据迁移	pg_dump	-Fp	一次性	-
灾难恢复	物理备份 + WAL 归档	-	实时	180天