外观
PostgreSQL 误删除恢复
误删除是数据库运维中常见的风险，包括误删除表、误删除数据行、误删除数据库等。PostgreSQL提供了多种机制来应对不同类型的误删除场景。本文将详细介绍各种误删除情况的恢复方法。
误删除表的恢复
从备份恢复表
如果有近期备份，可以使用单表恢复的方式恢复被删除的表。
使用pg_restore恢复表
如果备份是自定义格式（-F c）或目录格式（-F d），可以直接恢复单个表：
bash
# 查看备份中的表
pg_restore -l backup.dump | grep -i "table" | grep -i your_deleted_table
# 恢复表结构和数据
pg_restore -d your_database -t your_deleted_table backup.dump从SQL备份恢复表
如果备份是SQL格式，可以提取表结构和数据：
bash
# 提取表结构
grep -A 200 "CREATE TABLE your_deleted_table" backup.sql > table_schema.sql
# 提取表数据
grep -A 10000 "COPY your_deleted_table" backup.sql > table_data.sql
# 恢复表
psql -d your_database -f table_schema.sql
psql -d your_database -f table_data.sql使用时间点恢复（PITR）
如果启用了WAL归档和定期基础备份，可以将数据库恢复到删除表之前的时间点：
- 停止数据库服务
- 恢复基础备份
- 配置恢复参数，指定删除前的时间点
- 启动数据库，自动恢复到指定时间点
- 从恢复的数据库中导出被删除的表
- 将表导入到生产数据库 详细步骤参考PostgreSQL 时间点恢复（PITR）。
使用pg_repack或pg_squeeze
如果表被误删除但WAL日志还在，可以使用第三方工具如pg_repack或pg_squeeze来恢复，但这些工具主要用于表空间重建，不是专门的误删除恢复工具。
误删除数据行的恢复
从备份恢复
如果有备份，可以：
- 恢复备份到临时数据库
- 从临时数据库中导出误删除的数据
- 将数据导入到生产数据库
bash
# 创建临时数据库
createdb temp_db
# 恢复备份到临时数据库
pg_restore -d temp_db backup.dump
# 导出误删除的数据
pg_dump -d temp_db -t your_table --data-only -F c -f deleted_data.dump
# 或使用SQL查询导出
export PGPASSWORD=your_password
psql -h localhost -U postgres -d temp_db -c "COPY (SELECT * FROM your_table WHERE condition) TO '/tmp/deleted_data.csv' CSV HEADER;"
# 将数据导入生产数据库
pg_restore -d your_database --data-only deleted_data.dump
# 或使用CSV导入
psql -d your_database -c "COPY your_table FROM '/tmp/deleted_data.csv' CSV HEADER;"使用时间点恢复
如果启用了WAL归档，可以将整个数据库恢复到数据被删除之前的时间点，然后导出误删除的数据，再导入到生产数据库。
使用PostgreSQL日志挖掘
如果WAL日志可用且未被覆盖，可以使用pg_waldump工具分析WAL日志，找到删除操作，然后反向生成插入语句。
bash
# 查看WAL日志内容
pg_waldump /path/to/pg_wal/00000001000000000000000A | grep -i delete
# 或分析特定WAL文件
pg_waldump -p /path/to/data_dir/pg_wal/00000001000000000000000A | grep -A 10 -B 10 DELETE预防措施：使用软删除
为了避免误删除数据行，可以考虑使用软删除模式：
sql
-- 添加删除标记列
ALTER TABLE your_table ADD COLUMN is_deleted BOOLEAN DEFAULT false;
-- 删除数据时标记为删除，而不是真正删除
UPDATE your_table SET is_deleted = true WHERE id = 123;
-- 查询时过滤已删除数据
SELECT * FROM your_table WHERE is_deleted = false;误删除数据库的恢复
从全量备份恢复
如果有数据库的全量备份，可以直接恢复：
bash
# 恢复整个数据库
pg_restore -d postgres backup.dump
# 或使用SQL备份
psql -d postgres -f backup.sql从集群备份恢复
如果误删除了整个数据库，可以从集群级备份恢复：
bash
# 停止数据库服务
systemctl stop postgresql-14
# 清空数据目录
rm -rf /var/lib/postgresql/14/main/*
# 恢复基础备份
pg_basebackup -D /var/lib/postgresql/14/main -F t -r /path/to/base_backup.tar -X none
# 启动数据库服务
systemctl start postgresql-14误删除索引的恢复
从备份恢复
可以从备份中提取索引创建语句：
bash
# 从自定义格式备份提取索引创建语句
pg_restore -s -d your_database backup.dump | grep -A 10 "CREATE INDEX"
# 或从SQL备份提取
grep -A 5 "CREATE INDEX" backup.sql重新创建索引
根据表结构重新创建索引：
sql
-- 查看表结构
d\d your_table
-- 重新创建索引
CREATE INDEX idx_your_table_column ON your_table(column_name);误删除Schema的恢复
从备份恢复
bash
# 从自定义备份恢复特定schema
pg_restore -d your_database -n your_schema backup.dump
# 从SQL备份提取schema
grep -A 500 "CREATE SCHEMA your_schema" backup.sql > schema_restore.sql
psql -d your_database -f schema_restore.sql从时间点恢复
如果启用了PITR，可以恢复到schema被删除之前的时间点。
最佳实践
定期备份
- 制定合理的备份策略，包括全量备份和增量备份
- 启用WAL日志归档，支持时间点恢复
- 定期测试备份的可恢复性
权限控制
- 限制
DROP和DELETE权限，使用最小权限原则 - 对关键表使用软删除模式
- 启用审计日志，记录所有DDL和DML操作
监控和告警
- 监控数据库对象变化
- 设置关键表的行数监控
- 定期检查数据库对象完整性
文档化恢复流程
- 详细记录各种误删除场景的恢复步骤
- 包括所需工具、命令和注意事项
- 定期更新恢复文档
演练恢复流程
- 定期模拟误删除场景
- 测试恢复流程的有效性
- 记录恢复时间，确保符合RTO要求
常见问题与解决方案
备份不可用
问题：没有可用的备份或备份过时 解决方案：
- 立即启用WAL归档和定期备份
- 考虑使用第三方备份工具提高备份可靠性
- 制定备份验证策略
WAL日志已被覆盖
问题：WAL日志已被自动清理，无法进行PITR 解决方案：
- 调整
wal_keep_size参数，保留更多WAL日志 - 启用WAL归档到安全位置
- 定期测试WAL归档的完整性
恢复时间过长
问题：恢复过程耗时过长，影响业务 解决方案：
- 优化备份策略，使用增量备份减少恢复时间
- 考虑使用并行恢复（PostgreSQL 12+）
- 优化存储性能
恢复后数据不一致
问题：恢复的数据与预期不符 解决方案：
- 验证备份的完整性
- 确保恢复的是正确的备份版本
- 恢复后运行数据一致性检查