Debian下PostgreSQL数据恢复方法

一、恢复前的准备工作

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

逻辑备份通过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"验证表结构及数据。

物理备份通过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;）。

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。

若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"。