【PGCCC】PostgreSQL 删除的数据真的消失了吗？教你如何找回“丢失的宝藏”！

你有没有遇到过这样的情形：一不小心删除了PostgreSQL中的重要数据，然后陷入了“我是不是再也找不回来了”的恐慌中？别急，PostgreSQL 虽然不像一些数据库一样直接提供“后悔药”，但通过一些巧妙的操作，我们依然可以恢复已删除的数据！今天，我们就来聊聊如何在 PostgreSQL 中恢复误删的数据，还会给你展示一个实际案例，帮你理解这个“救命神器”是如何在生产环境中大显神威的！

恢复已删除数据的背后逻辑

在 PostgreSQL 中，数据并不会因为一次简单的 DELETE 操作就立刻从硬盘上消失。实际上，PostgreSQL 是使用**多版本并发控制（MVCC）**来管理数据的，这意味着当你删除一行记录时，数据并不会立刻被清除，而是被标记为“已删除”，直到触发 VACUUM 进行清理。

换句话说，只要在 VACUUM 执行前，你还是有机会将“误删”的数据找回来的！

1. 使用pg_logical工具：捕获历史数据

首先，我们可以通过PostgreSQL的 pg_logical 来进行数据恢复。pg_logical 是一种逻辑复制工具，允许我们捕获并重现历史数据的变化。具体步骤如下：

安装pg_logical扩展

CREATE EXTENSION IF NOT EXISTS pg_logical;

订阅数据流

SELECT pg_logical.create_replication_set('data_recovery_set');

通过设置数据流的订阅，我们可以捕获并重放表中发生的变更操作。只要你在执行 DELETE 后立刻行动，pg_logical 可以帮你抓住这些“溜走的记录”。

2. 使用WAL日志：数据库的时间机器

PostgreSQL 的 Write-Ahead Logging (WAL) 是一项强大的功能，记录了每个事务的所有更改。通过 WAL日志，我们可以“时光倒流”，将数据库恢复到某个特定的时间点，以便找回误删的数据。

步骤1：确认WAL日志的存储路径
首先，我们需要找到数据库中WAL日志的存储路径。可以通过以下命令查询：

SHOW data_directory;

WAL日志通常存储在 pg_wal/ 或 pg_xlog/ 目录下。

步骤2：启动时间点恢复

接下来，我们可以通过 PITR（Point In Time Recovery），将数据库恢复到误删操作前的时间点。

• 先停止数据库：

pg_ctl stop -D /var/lib/postgresql/data

• 然后编辑 recovery.conf 文件，指定需要恢复的时间点：

restore_command = 'cp /path_to_wal_archive/%f %p'
recovery_target_time = '2024-09-25 12:00:00' -- 这里填入你删除数据前的时间点

• 启动数据库恢复：

pg_ctl start -D /var/lib/postgresql/data

3. 借助pgAdmin恢复数据

对于不想碰命令行的朋友，可以借助 pgAdmin 工具轻松恢复误删数据。通过操作界面，我们可以查看表的变更历史，恢复到特定的时间点。

1. 打开 pgAdmin 并连接到你的数据库。
2. 选择你误删数据的表，右键点击，选择 “Restore”。
3. 根据你需要恢复的时间点，配置恢复选项，并确认。

实际案例演示

现在，我们来看一个真实的案例：某电商公司在其生产环境中误删了用户订单表中的部分数据，导致了一定的业务中断。幸运的是，运维团队及时使用了WAL日志的PITR技术，成功将数据恢复，避免了巨大的损失。

问题场景：

公司运营人员在订单系统中执行了错误的 DELETE 操作：

DELETE FROM orders WHERE order_date < '2024-09-01';

结果导致2024年9月1日之前的所有订单数据丢失。幸运的是，VACUUM尚未执行。

解决方案：

通过以下步骤恢复数据：

1. 确认最近一次备份的时间点。
2. 查找删除操作发生的确切时间点。
3. 使用WAL日志的PITR功能，将数据库恢复到删除操作前。

通过执行以下恢复命令，成功找回了删除的订单数据：

pg_ctl stop -D /var/lib/postgresql/data
vi /var/lib/postgresql/data/recovery.conf

在 recovery.conf 文件中设置恢复目标时间为操作前的时间点：

recovery_target_time = '2024-09-01 10:00:00'

启动数据库后，数据顺利恢复，订单系统恢复正常。

小结

恢复 PostgreSQL 中已删除的数据并不如想象中复杂，但关键在于行动要及时！无论是使用 pg_logical 还是 WAL日志，PostgreSQL 都为我们提供了灵活的工具来应对这种数据灾难。希望今天的分享能帮到你，下次误删数据时别再慌张，你已经掌握了这把“数据复活”的钥匙！

扩展阅读参考

• PostgreSQL 官方文档 - MVCC
• WAL日志详细解读
• pg_logical扩展的应用

#PG证书#PG考试#postgresql初级#postgresql中级#postgresql高级