外观
PostgreSQL pg_dump/pg_restore使用与最佳实践
核心概念
pg_dump和pg_restore是PostgreSQL内置的备份恢复工具，用于创建和恢复数据库备份。它们支持多种备份格式和恢复选项，是PostgreSQL数据库运维中最常用的备份恢复工具。
- pg_dump：用于创建数据库的逻辑备份，可以备份整个数据库、特定schema、表或查询结果
- pg_restore：用于从pg_dump创建的备份文件中恢复数据库，可以选择性恢复对象或数据
- 逻辑备份：备份数据库的逻辑结构和数据，与物理备份相比，恢复时更灵活但速度较慢
- 备份格式：支持自定义格式、tar格式和plain文本格式
pg_dump使用方法
1. 基础用法
bash
# 备份整个数据库到SQL文件
pg_dump -h localhost -U postgres -d mydb -f mydb_backup.sql
# 备份特定数据库到自定义格式文件（推荐）
pglib_dump -h localhost -U postgres -d mydb -F c -f mydb_backup.dump
# 备份特定schema
pg_dump -h localhost -U postgres -d mydb -n myschema -F c -f myschema_backup.dump
# 备份特定表
pg_dump -h localhost -U postgres -d mydb -t mytable -F c -f mytable_backup.dump2. 常用参数
| 参数 | 说明 | 示例 |
|---|---|---|
-h, --host | 数据库主机地址 | -h localhost |
-p, --port | 数据库端口 | -p 5432 |
-U, --username | 连接用户名 | -U postgres |
-d, --dbname | 要备份的数据库名 | -d mydb |
-f, --file | 输出文件名 | -f backup.dump |
-F, --format | 输出格式（c:自定义, d:目录, t:tar, p:plain） | -F c |
-c, --clean | 备份前先清理对象 | -c |
-C, --create | 包含创建数据库语句 | -C |
-n, --schema | 只备份指定schema | -n myschema |
-t, --table | 只备份指定表 | -t mytable |
-T, --exclude-table | 排除指定表 | -T log_table |
-j, --jobs | 并行备份的任务数 | -j 4 |
-v, --verbose | 详细输出 | -v |
--schema-only | 只备份schema，不备份数据 | --schema-only |
--data-only | 只备份数据，不备份schema | --data-only |
--no-owner | 不备份对象所有者信息 | --no-owner |
--no-privileges | 不备份权限信息 | --no-privileges |
3. 高级用法
bash
# 使用并行备份加速大数据库备份
pg_dump -h localhost -U postgres -d mydb -F d -j 4 -f mydb_parallel_backup
# 备份多个表
pg_dump -h localhost -U postgres -d mydb -t table1 -t table2 -F c -f tables_backup.dump
# 备份除特定表之外的所有表
pg_dump -h localhost -U postgres -d mydb -T table1 -T table2 -F c -f no_tables_backup.dump
# 压缩备份输出
pg_dump -h localhost -U postgres -d mydb -F c | gzip > mydb_backup.dump.gz
# 备份到远程服务器
pg_dump -h localhost -U postgres -d mydb -F c | ssh user@remotehost "cat > /backups/mydb_backup.dump"pg_restore使用方法
1. 基础用法
bash
# 从自定义格式备份恢复整个数据库
pg_restore -h localhost -U postgres -d mydb mydb_backup.dump
# 从tar格式备份恢复
pg_restore -h localhost -U postgres -d mydb -F t mydb_backup.tar
# 从目录格式备份恢复
pg_restore -h localhost -U postgres -d mydb -F d mydb_backup_dir
# 查看备份文件内容
pg_restore -l mydb_backup.dump2. 常用参数
| 参数 | 说明 | 示例 |
|---|---|---|
-h, --host | 数据库主机地址 | -h localhost |
-p, --port | 数据库端口 | -p 5432 |
-U, --username | 连接用户名 | -U postgres |
-d, --dbname | 恢复到的数据库名 | -d mydb |
-F, --format | 备份文件格式 | -F c |
-c, --clean | 恢复前先清理对象 | -c |
-C, --create | 创建数据库（如果不存在） | -C |
-j, --jobs | 并行恢复的任务数 | -j 4 |
-v, --verbose | 详细输出 | -v |
-l, --list | 列出备份文件中的对象 | -l |
-L, --use-list | 使用指定的对象列表文件恢复 | -L restore_list.txt |
-n, --schema | 只恢复到指定schema | -n myschema |
-t, --table | 只恢复指定表 | -t mytable |
--schema-only | 只恢复schema，不恢复数据 | --schema-only |
--data-only | 只恢复数据，不恢复schema | --data-only |
--no-owner | 不恢复对象所有者信息 | --no-owner |
--no-privileges | 不恢复权限信息 | --no-privileges |
--single-transaction | 单个事务中恢复所有对象 | --single-transaction |
--if-exists | 只在对象存在时执行删除操作 | --if-exists |
3. 高级用法
bash
# 并行恢复加速大数据库恢复
pg_restore -h localhost -U postgres -d mydb -j 4 mydb_backup.dump
# 选择性恢复对象
# 1. 首先列出备份文件中的对象
pg_restore -l mydb_backup.dump > restore_list.txt
# 2. 编辑restore_list.txt，注释掉不需要恢复的对象
# 3. 使用编辑后的列表恢复
pg_restore -h localhost -U postgres -d mydb -L restore_list.txt mydb_backup.dump
# 从压缩备份文件恢复
gunzip -c mydb_backup.dump.gz | pg_restore -h localhost -U postgres -d mydb
# 恢复到远程数据库
pg_restore mydb_backup.dump | psql -h remotehost -U postgres -d mydb
# 查看备份文件的大小统计
pg_restore -s mydb_backup.dump最佳实践
1. 备份策略设计
- 全量备份：每周执行一次全量备份
- 增量备份：结合WAL归档，实现增量备份
- 备份验证：定期验证备份文件的完整性
- 异地存储：将备份文件复制到异地存储
2. 性能优化
bash
# 使用并行备份提高备份速度（适用于大数据库）
pglib_dump -h localhost -U postgres -d mydb -F d -j 8 -f mydb_backup_dir
# 使用压缩减少备份文件大小
glib_dump -h localhost -U postgres -d mydb -F c | gzip -9 > mydb_backup.dump.gz
# 备份时禁用触发器和约束（提高备份速度）
pg_dump -h localhost -U postgres -d mydb --disable-triggers --no-tablespaces -F c -f mydb_backup.dump3. 安全最佳实践
- 最小权限原则：使用具有最小备份权限的用户执行备份
- 密码安全：避免在命令行中直接输入密码，使用
.pgpass文件或环境变量 - 备份加密：对备份文件进行加密存储
- 访问控制：限制备份文件的访问权限，使用
chmod 600保护备份文件
4. 恢复最佳实践
bash
# 恢复前先创建数据库
createdb -h localhost -U postgres mydb
# 使用单个事务恢复，确保原子性
pg_restore -h localhost -U postgres -d mydb --single-transaction mydb_backup.dump
# 恢复后验证数据完整性
psql -h localhost -U postgres -d mydb -c "SELECT COUNT(*) FROM mytable;"
# 恢复后更新统计信息
psql -h localhost -U postgres -d mydb -c "ANALYZE;"常见问题（FAQ）
Q1：pg_dump和pg_basebackup有什么区别？
A1：
- pg_dump：创建逻辑备份，备份数据库的逻辑结构和数据，支持选择性备份和恢复
- pg_basebackup：创建物理备份，备份数据库的物理文件，恢复速度快但不支持选择性恢复
- 适用场景：小数据库或需要选择性恢复时使用pg_dump，大数据库或需要快速恢复时使用pg_basebackup
Q2：如何备份PostgreSQL集群中的所有数据库？
A2：可以使用以下方法：
bash
# 方法1：使用pg_dumpall备份所有数据库
pg_dumpall -h localhost -U postgres -f all_dbs_backup.sql
# 方法2：遍历所有数据库，逐个备份
for db in $(psql -h localhost -U postgres -t -c "SELECT datname FROM pg_database WHERE datname NOT IN ('template0', 'template1');"); do
pg_dump -h localhost -U postgres -d $db -F c -f ${db}_backup.dump
doneQ3：如何解决pg_dump备份时出现的权限错误？
A3：
- 确保备份用户具有适当的权限：
pg_dump需要SELECT权限和pg_stat_database的SELECT权限 - 对于schema备份，需要
USAGE权限 - 对于表备份，需要
SELECT权限 - 可以使用超级用户或专门创建的备份用户
Q4：如何优化大数据库的pg_dump备份速度？
A4：
- 使用并行备份：
-j参数指定并行任务数 - 使用自定义格式：
-F c比plain文本格式更快 - 禁用触发器和约束：
--disable-triggers - 优化WAL写入：在备份期间临时调整
wal_buffers和checkpoint_segments参数 - 考虑使用物理备份：对于超大数据库，pg_basebackup可能更高效
Q5：如何从备份中恢复单个表？
A5：
bash
# 方法1：使用pg_restore的-t参数
pg_restore -h localhost -U postgres -d mydb -t mytable mydb_backup.dump
# 方法2：使用对象列表文件
pg_restore -l mydb_backup.dump > restore_list.txt
# 编辑restore_list.txt，只保留mytable相关的条目
echo ";;只恢复mytable表" > mytable_list.txt
grep -i mytable restore_list.txt >> mytable_list.txt
pg_restore -h localhost -U postgres -d mydb -L mytable_list.txt mydb_backup.dumpQ6：如何验证备份文件的完整性？
A6：
bash
# 方法1：使用pg_restore的-C参数检查备份文件
pg_restore -C -v mydb_backup.dump > /dev/null
# 方法2：尝试恢复到测试数据库
createdb -h localhost -U postgres testdb
pg_restore -h localhost -U postgres -d testdb mydb_backup.dump
# 验证恢复结果
psql -h localhost -U postgres -d testdb -c "SELECT COUNT(*) FROM pg_tables;"
dropdb -h localhost -U postgres testdb