Debian下PostgreSQL数据恢复方法

一、恢复前的准备工作

1. 备份当前数据：无论采用哪种恢复方式，都应先备份现有数据库集群，防止恢复过程中数据覆盖或丢失。
   执行命令：sudo pg_dumpall -U postgres > /path/to/backup/all-databases.sql
   该命令会生成包含所有数据库结构和数据的SQL备份文件。

2. 停止PostgreSQL服务：恢复操作前需停止服务，确保数据一致性。
   执行命令：sudo systemctl stop postgresql

二、常见恢复方法

1. 逻辑备份与恢复（适用于常规场景）

逻辑备份通过pg_dump生成可读的SQL或自定义格式文件，恢复时通过psql或pg_restore还原数据。

• 备份数据库：
  使用pg_dump生成自定义格式（支持压缩和并行恢复）：
  pg_dump -Fc laozheng > laozheng.dump
  或生成SQL格式（兼容性强，但恢复速度较慢）：
  pg_dump -Fp laozheng > laozheng.sql
• 恢复数据库：
  • 自定义格式（推荐）：sudo -u postgres pg_restore -d laozheng /path/to/laozheng.dump
  • SQL格式：sudo -u postgres psql -d laozheng < /path/to/laozheng.sql
  • tar格式（需指定目录）：sudo -u postgres pg_restore -d laozheng -C /path/to/laozheng.tar
    恢复后可通过psql -U postgres -d laozheng -c "\dt"验证表结构及数据。

2. 物理备份与恢复（适用于大型数据库或物理文件损坏）

物理备份通过pg_basebackup复制数据库集群的物理文件（包括数据目录、WAL日志），恢复速度快，但需停机。

• 创建物理备份：
  执行命令：pg_basebackup -Ft -Pv -Xf -z -Z5 -p 5432 -D /backup/full_backup
  参数说明：-Ft表示tar格式，-Pv显示进度，-Xf包含WAL日志，-z压缩，-Z5压缩级别5。
• 恢复物理备份：
  1. 停止PostgreSQL服务：sudo systemctl stop postgresql
  2. 清空原数据目录（如/var/lib/postgresql/15/main/）：sudo rm -rf /var/lib/postgresql/15/main/*
  3. 解压备份文件到数据目录：sudo tar -xzf /backup/full_backup/base.tar.gz -C /var/lib/postgresql/15/main/
  4. 修改文件权限：sudo chown -R postgres:postgres /var/lib/postgresql/15/main/
  5. 启动PostgreSQL服务：sudo systemctl start postgresql
     恢复后需验证数据完整性（如SELECT * FROM key_table LIMIT 10;）。

3. 基于时间点恢复（PITR，适用于误操作或数据损坏）

PITR通过WAL（Write-Ahead Logging）日志将数据库恢复到指定时间点（如某时刻、某事务ID），需提前开启WAL归档。

• 开启WAL归档：
  编辑PostgreSQL配置文件（/etc/postgresql/<版本>/main/postgresql.conf），修改以下参数：

  wal_level = replica
  archive_mode = on
  archive_command = 'cp %p /usr/local/pgsql/arch/%f'
  max_wal_senders = 2
  wal_keep_segments = 100
  

  重启服务使配置生效：sudo systemctl restart postgresql。
• 创建基础备份：
  使用pg_basebackup创建全量备份（如备份到/backup/pitr_base）：
  pg_basebackup -D /backup/pitr_base -Ft -Pv -Xf -z -Z5 -p 5432。
• 执行PITR：
  1. 停止PostgreSQL服务：sudo systemctl stop postgresql
  2. 清空原数据目录：sudo rm -rf /var/lib/postgresql/15/main/*
  3. 复制基础备份到数据目录：sudo cp -r /backup/pitr_base/* /var/lib/postgresql/15/main/
  4. 创建postgresql.auto.conf文件（用于恢复配置）：
     echo "restore_command = 'cp /usr/local/pgsql/arch/%f %p'" >> /var/lib/postgresql/15/main/postgresql.auto.conf
echo "recovery_target_time = '2025-10-15 14:30:00+08'" >> /var/lib/postgresql/15/main/postgresql.auto.conf
     （recovery_target_time可根据需求改为事务ID，如recovery_target_xid = '12345'）
  5. 启动PostgreSQL服务：sudo systemctl start postgresql
     服务启动后会自动应用WAL日志至指定时间点，完成后会自动重启为正常模式。可通过日志确认恢复状态：journalctl -u postgresql -f。

4. 使用pg_resetwal修复WAL日志损坏（高级场景）

若WAL日志损坏导致数据库无法启动，可使用pg_resetwal工具重置WAL日志（需谨慎操作，可能导致数据丢失）。

• 操作步骤：
  1. 停止PostgreSQL服务：sudo systemctl stop postgresql
  2. 备份原数据目录：sudo cp -r /var/lib/postgresql/15/main /var/lib/postgresql/15/main_backup
  3. 运行pg_resetwal：sudo pg_resetwal -D /var/lib/postgresql/15/main -f
     （-f参数强制重置，无备份时不建议使用）
  4. 启动PostgreSQL服务：sudo systemctl start postgresql
  5. 登录数据库验证数据：sudo -u postgres psql -c "\l"。

三、注意事项

1. 恢复前验证备份：恢复前需检查备份文件的完整性和可恢复性（如尝试用pg_restore -l列出备份内容）。
2. 选择合适恢复方法：逻辑备份适合常规恢复，物理备份适合大型数据库，PITR适合时间点恢复，pg_resetwal仅用于WAL日志损坏。
3. 测试恢复流程：建议在测试环境演练恢复步骤，避免生产环境误操作。
4. 监控恢复过程：恢复时可通过journalctl -u postgresql -f实时查看日志，及时处理错误。