PostgresQL数据库备份与还原全攻略（逻辑+物理+混合策略）

本文系统梳理 PostgresQL 数据库的 备份与还原全流程，涵盖 逻辑备份、物理备份 和 混合策略，并通过实际操作示例演示从基础到高级的备份方案。适合运维人员、开发者快速掌握生产级数据保护策略。

一、逻辑备份与还原

1. 使用 pg_dump 的逻辑备份

1.1 备份步骤

# 基础备份（生成 SQL 文件）
pg_dump -U <用户名> -h <主机IP> -p <端口> -d <数据库名> -F p -f /path/to/backup.sql

# 压缩备份（节省存储空间）
pg_dump -U <用户名> -d <数据库名> | gzip > /path/to/backup.sql.gz

1.2 还原步骤

# 创建目标数据库
createdb -U <用户名> <目标数据库名>

# 恢复 SQL 文件
psql -U <用户名> -h <主机IP> -d <目标数据库名> -f /path/to/backup.sql

# 恢复压缩文件
gunzip < /path/to/backup.sql.gz | psql -U <用户名> -d <目标数据库名>

1.3 高级用法

• 仅备份表结构：

  pg_dump -U <用户名> -d <数据库名> --schema-only -f /path/to/schema.sql

• 仅备份数据：

  pg_dump -U <用户名> -d <数据库名> --data-only -f /path/to/data.sql

---

2. 使用 pg_dumpall 的全局备份

2.1 备份步骤

# 备份所有数据库及全局对象
pg_dumpall -U <用户名> -f /path/to/all_dbs_backup.sql

# 仅备份全局对象（角色、表空间）
pg_dumpall -U <用户名> -g -f /path/to/global_objects.sql

2.2 还原步骤

# 恢复所有数据库
psql -U <用户名> -f /path/to/all_dbs_backup.sql

# 恢复全局对象
psql -U <用户名> -f /path/to/global_objects.sql

---

二、物理备份与还原

1. 使用 pg_basebackup 的物理备份

1.1 备份步骤

# 创建基础备份（目录格式）
pg_basebackup -D /path/to/backup_dir -F d -Pv -U <备份用户> -h <主机IP> -p <端口> -R

# 创建压缩备份（tar 格式）
pg_basebackup -D /path/to/backup.tar -F t -Pv -U <备份用户> -h <主机IP> -p <端口> -R -z

1.2 还原步骤

1. 模拟数据丢失：

   systemctl stop PostgresQL
   rm -rf /var/lib/pgsql/data/*

2. 恢复备份文件：

   # 解压 base.tar 到数据目录
   tar -xvf /path/to/base.tar -C /var/lib/pgsql/data
   
   # 解压 WAL 日志到指定目录（需在 PostgresQL.auto.conf 中配置）
   tar -xvf /path/to/pg_wal.tar -C /archive

3. 启动数据库：

   systemctl start PostgresQL
   # 取消只读模式
   psql -c "SELECT pg_wal_replay_resume();"

---

三、混合策略：物理备份 + WAL 归档（实现 PITR）

1. 配置 WAL 归档

# 修改 PostgresQL.conf 启用归档
archive_mode = on
archive_command = 'cp %p /path/to/archive/%f'

# 确保归档目录存在且可写
mkdir -p /path/to/archive
chown -R postgres:postgres /path/to/archive

2. 时间点恢复（PITR）

2.1 还原步骤

1. 准备恢复环境：

   systemctl stop PostgresQL
   rm -rf /var/lib/pgsql/data/*
   tar -xvf /path/to/base.tar -C /var/lib/pgsql/data

2. 配置恢复目标：
   创建 recovery.conf 文件（PostgresQL 12+ 需要 standby.signal）：

   restore_command = 'cp /path/to/archive/%f "%p"'
   recovery_target_time = '2025-09-03 12:00:00'

3. 启动恢复：

   systemctl start PostgresQL

---

四、自动化与第三方工具

1. 定时任务备份脚本

# 每天凌晨 2 点执行备份
0 2 * * * pg_dump -U <用户名> -d <数据库名> -F c | gzip > /backup/$(date +\%Y\%m\%d).dump.gz

2. 使用 Barman（备份管理器）

# 安装 Barman
apt install barman

# 配置 Barman
echo "barman:
  servers:
    mydb:
      name: mydb
      host: <主机IP>
      port: 5432
      user: barman
      backup_directory: /var/backups/mydb
      archive_mode: on
      archive_command: 'rsync -a %p <备份服务器>:/path/to/archive'" > /etc/barman.conf

---

五、注意事项与最佳实践

1. 权限与安全

• 备份用户需具备 SELECT 权限（逻辑备份）或 REPLICATION 权限（物理备份）。
• 敏感数据建议加密备份文件（使用 gpg 或工具自带加密功能）。

2. 存储与验证

• 定期测试备份恢复流程，确保备份有效性。
• 生产环境建议采用 3-2-1 法则：3 份备份，2 种介质，1 份异地存储。

3. 策略选择

场景	推荐方式
小型数据库	pg_dump（SQL 文件）
大型数据库	pg_basebackup（物理备份）
灾难恢复	物理备份 + WAL 归档（PITR）
自动化管理	Barman / pgBackRest

---

六、总结

通过组合 逻辑备份（灵活、可读性强）、物理备份（高效、完整） 和 WAL 归档（时间点恢复），可以构建多层次的数据保护体系。根据业务需求选择合适的工具与策略，并定期验证备份流程，是保障数据库安全的关键。