Debian系统中PostgreSQL数据恢复方法
一、恢复前的准备工作
在进行任何恢复操作前，务必备份当前数据库，防止恢复过程中发生二次数据丢失。可使用以下命令备份所有数据库：
sudo pg_dumpall -U postgres > /path/to/backup/all-databases.sql
此外，停止PostgreSQL服务以确保数据一致性：
sudo systemctl stop postgresql
二、基于逻辑备份的恢复（适用于常规备份）
逻辑备份是通过pg_dump生成的.dump或.sql文件，恢复时需根据备份格式选择对应命令：
1. SQL格式备份恢复（.sql文件）
若备份为纯文本SQL格式（如pg_dump -Fp生成），可使用psql命令直接恢复：
sudo -u postgres psql -d target_database < /path/to/backup/file.sql
2. 自定义格式备份恢复（.dump文件）
若备份为自定义格式（如pg_dump -Fc生成），需使用pg_restore命令（支持并行恢复，提升速度）：
sudo -u postgres pg_restore -U postgres -d target_database /path/to/backup/file.dump
3. tar格式备份恢复
若备份为tar格式（如pg_dump -Ft生成），同样使用pg_restore命令：
sudo -u postgres pg_restore -d target_database /path/to/backup/file.tar
恢复完成后，启动PostgreSQL服务：
sudo systemctl start postgresql
三、基于物理备份的恢复（适用于大规模数据或灾难恢复）
物理备份是通过pg_basebackup复制数据库物理文件（如数据目录），恢复时需覆盖原数据目录：
1. 执行物理备份（若未备份）
sudo -u postgres pg_basebackup -Ft -Pv -Xf -z -Z5 -p 5432 -D /backup/full_backup
2. 恢复物理备份
停止服务后，清空原数据目录（如/var/lib/postgresql/<版本号>/main/），将备份文件复制到数据目录：
sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/<版本号>/main/*
sudo cp -r /backup/full_backup/* /var/lib/postgresql/<版本号>/main/
3. 设置权限并启动服务
sudo chown -R postgres:postgres /var/lib/postgresql/<版本号>/main/
sudo systemctl start postgresql
四、基于时间点恢复（PITR，恢复到特定时刻）
若需要恢复到误操作前的某一时间点（如删除数据前的10分钟），需提前开启WAL归档并配置PITR参数：
1. 开启WAL归档（修改postgresql.conf）
编辑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
2. 执行基础备份（PITR的前提）
使用pg_basebackup创建基础备份（覆盖之前的备份目录）：
sudo -u postgres pg_basebackup -Ft -Pv -Xf -z -Z5 -p 5432 -D /backup/full_backup
3. 配置PITR恢复参数
停止服务，清空原数据目录，复制基础备份到数据目录，创建postgresql.auto.conf文件（用于覆盖恢复参数）：
sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/<版本号>/main/*
sudo cp -r /backup/full_backup/* /var/lib/postgresql/<版本号>/main/
sudo vi /var/lib/postgresql/<版本号>/main/postgresql.auto.conf
在postgresql.auto.conf中添加以下内容（替换为目标时间和归档路径）：
restore_command = 'cp /usr/local/pgsql/arch/%f %p'
recovery_target_time = '2025-11-01 10:00:00+08' # 替换为需恢复的时间点
recovery_target_timeline = 'latest'
4. 启动恢复并验证
启动PostgreSQL服务，系统会自动执行恢复（恢复完成后会自动重启）：
sudo systemctl start postgresql
恢复完成后，登录数据库验证数据：
sudo -u postgres psql -d target_database -c "SELECT * FROM table_name;"
五、WAL日志损坏的修复（高级场景）
若WAL日志（Write-Ahead Logging）损坏导致数据库无法启动，可使用pg_resetwal工具重置WAL日志（此操作会丢失损坏日志后的数据，需谨慎使用）：
1. 停止服务并备份数据
sudo systemctl stop postgresql
sudo cp -r /var/lib/postgresql/<版本号>/main /var/lib/postgresql/<版本号>/main_backup
2. 运行pg_resetwal重置WAL
sudo pg_resetwal -D /var/lib/postgresql/<版本号>/main -f
3. 启动服务并验证
sudo systemctl start postgresql
sudo -u postgres psql -c "\l" # 检查数据库是否可用
注意事项
- 恢复优先级：优先尝试逻辑备份或物理备份恢复，PITR适用于无备份或备份过期的情况。
- 权限问题：所有操作需以
postgres用户或root用户执行，确保数据目录权限正确（postgres:postgres）。 - 测试恢复：定期测试备份文件的恢复流程，确保备份的有效性。
- 专业支持：若数据极其重要且无法自行恢复，建议联系专业数据库恢复服务商。