CODE

PostgreSQL备份与恢复实战：从零掌握pg_dump与pg_restore

2025/11/23 PG 共 3757 字，约 11 分钟

PostgreSQL备份与恢复实战：从零掌握pg_dump与pg_restore

在数据库管理工作中，备份与恢复是最基础也是最重要的技能。无论是因为硬件故障、人为误操作还是系统升级，可靠的数据备份都是最后的救命稻草。PostgreSQL作为一款强大的开源关系型数据库，提供了多种备份恢复方案，其中pg_dump和pg_restore是最常用的逻辑备份工具。

为什么需要逻辑备份？

逻辑备份与物理备份不同，它不是直接复制数据库的文件，而是将数据库中的对象（表、视图、函数等）结构和数据导出为SQL语句或其他格式的文件。这种备份方式的主要优势包括：

  • 跨版本兼容：可以在不同版本的PostgreSQL之间迁移数据
  • 选择性恢复：可以只恢复特定的表或数据
  • 平台无关：备份文件可以在不同操作系统间迁移
  • 可读性强：SQL格式的备份文件可以直接查看和编辑

pg_dump：数据库备份利器

基本语法与常用参数

pg_dump是PostgreSQL自带的备份工具，基本语法如下：

pg_dump [option...] [dbname]

常用的参数包括：

  • -h 或 --host：数据库服务器地址
  • -p 或 --port：数据库端口
  • -U 或 --username：连接用户名
  • -d 或 --dbname：数据库名
  • -F 或 --format：备份格式（c=自定义，d=目录，t=tar，p=纯文本）
  • -f 或 --file：输出文件
  • -v 或 --verbose：详细模式
  • --schema：只备份指定模式
  • --table：只备份指定表

实际备份示例

场景1：备份整个数据库为SQL文件

# 备份mydb数据库到SQL文件
pg_dump -h localhost -p 5432 -U myuser -d mydb -f /backup/mydb_backup.sql

# 或者使用连接字符串
pg_dump postgresql://myuser:mypassword@localhost:5432/mydb -f /backup/mydb_backup.sql

场景2：使用自定义格式备份（推荐）

自定义格式备份文件更小，恢复速度更快，且支持选择性恢复：

# 使用自定义格式备份
pg_dump -h localhost -U myuser -d mydb -F c -f /backup/mydb_backup.dump

# 压缩备份（自定义格式默认压缩）
pg_dump -h localhost -U myuser -d mydb -F c -Z 9 -f /backup/mydb_backup.dump

场景3：只备份特定表

# 只备份users表和orders表
pg_dump -h localhost -U myuser -d mydb -t users -t orders -F c -f /backup/tables_backup.dump

场景4：只备份表结构

# 只备份表结构，不备份数据
pg_dump -h localhost -U myuser -d mydb -s -F c -f /backup/schema_only.dump

pg_restore：数据恢复专家

基本语法与参数

pg_restore用于恢复由pg_dump创建的自定义格式、目录格式或tar格式的备份：

pg_restore [option...] [filename]

常用参数：

  • -d 或 --dbname：要恢复到的数据库名
  • -c 或 --clean：在创建数据库对象前先删除
  • -C 或 --create：恢复前先创建数据库
  • -F 或 --format：备份文件格式
  • -j 或 --jobs：使用并行任务数加速恢复
  • -l 或 --list：列出备份内容
  • -t 或 --table：只恢复指定表
  • --schema：只恢复指定模式

实际恢复示例

场景1：完整恢复到新数据库

# 创建新数据库
createdb -h localhost -U myuser newdb

# 恢复备份到新数据库
pg_restore -h localhost -U myuser -d newdb -v /backup/mydb_backup.dump

场景2：恢复前先清理（适用于覆盖现有数据库）

# 清理现有对象后恢复
pg_restore -h localhost -U myuser -d mydb -c -v /backup/mydb_backup.dump

场景3：并行恢复加速大数据库

# 使用4个并行任务恢复
pg_restore -h localhost -U myuser -d mydb -j 4 -v /backup/mydb_backup.dump

场景4：选择性恢复特定表

# 首先查看备份文件内容
pg_restore -l /backup/mydb_backup.dump > /backup/list.txt

# 编辑list.txt，注释掉不需要恢复的对象
# 然后根据编辑后的列表恢复
pg_restore -h localhost -U myuser -d mydb -L /backup/list.txt /backup/mydb_backup.dump

日常应急备份恢复流程

完整备份策略示例

对于生产环境，建议采用以下备份策略：

#!/bin/bash
# backup_script.sh

# 设置变量
BACKUP_DIR="/backup/postgres"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="mydb"
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.dump"

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

# 执行备份
echo "开始备份数据库 ${DB_NAME}..."
pg_dump -h localhost -U postgres -d $DB_NAME -F c -Z 9 -f $BACKUP_FILE

# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "备份成功: ${BACKUP_FILE}"
    
    # 删除7天前的备份
    find $BACKUP_DIR -name "*.dump" -mtime +7 -delete
    echo "已清理7天前的备份文件"
else
    echo "备份失败!"
    exit 1
fi

设置定时任务，每天凌晨执行备份：

# 编辑crontab
crontab -e

# 添加以下行，每天凌晨2点执行备份
0 2 * * * /path/to/backup_script.sh

应急恢复场景演练

场景：开发人员误删重要数据表

  1. 确认问题：发现users表被误删除
  2. 停止相关服务：防止新数据写入造成数据不一致
  3. 从备份中恢复特定表：
# 创建临时数据库用于恢复特定表
createdb -h localhost -U postgres temp_restore

# 恢复users表到临时数据库
pg_restore -h localhost -U postgres -d temp_restore -t users /backup/mydb_backup.dump

# 从临时数据库导出users表数据
pg_dump -h localhost -U postgres -d temp_restore -t users -a --inserts > users_data.sql

# 将数据导入生产数据库
psql -h localhost -U postgres -d mydb -f users_data.sql

# 清理临时数据库
dropdb -h localhost -U postgres temp_restore
  1. 验证数据完整性
  2. 恢复服务

使用SQL格式备份的恢复

对于SQL格式的备份文件，可以使用psql工具直接恢复：

# 恢复整个数据库
psql -h localhost -U myuser -d mydb -f /backup/mydb_backup.sql

# 或者先创建数据库再恢复
createdb -h localhost -U myuser newdb
psql -h localhost -U myuser -d newdb -f /backup/mydb_backup.sql

最佳实践与注意事项

备份最佳实践

  1. 定期测试恢复流程：备份只有在能够成功恢复时才有价值
  2. 异地存储备份：将备份文件存储在与数据库服务器不同的位置
  3. 监控备份任务：设置告警监控备份任务是否成功执行
  4. 版本兼容性：注意PostgreSQL主版本间的兼容性问题
  5. 备份文件加密：对于敏感数据，考虑对备份文件进行加密

常见问题排查

问题1：备份时连接被拒绝

# 检查pg_hba.conf配置
# 确保允许相应的连接方式

# 检查PostgreSQL服务状态
systemctl status postgresql

# 检查端口监听
netstat -ln | grep 5432

问题2：恢复时权限错误

# 确保恢复用户有足够的权限
# 或者使用超级用户执行恢复

# 检查表空间权限
psql -h localhost -U postgres -d mydb -c "\dn+"

问题3：备份文件损坏

# 验证备份文件完整性
pg_restore -l /backup/mydb_backup.dump

# 如果验证失败，检查备份日志，重新执行备份

总结

PostgreSQL的pg_dump和pg_restore工具提供了强大而灵活的备份恢复方案。通过掌握这些工具的使用方法，并建立规范的备份恢复流程，可以有效地保护数据安全，应对各种意外情况。

记住，备份不是目的，能够成功恢复才是关键。定期测试恢复流程，确保在真正需要时能够快速、准确地恢复数据，这是每个DBA和开发者的重要职责。

行动建议：立即为你的重要数据库设置自动备份，并至少每季度进行一次恢复演练，确保在紧急情况下能够从容应对。

文档信息

Search

    Table of Contents