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）。
测试恢复：定期测试备份文件的恢复流程，确保备份的有效性。
专业支持：若数据极其重要且无法自行恢复，建议联系专业数据库恢复服务商。

Debian中PostgreSQL数据恢复方法