【PGCCC】从 PostgreSQL 表恢复已删除的数据 | 翻译

PostgreSQL 非常擅长保护您的数据安全，因此它不会自行消失。不幸的是，反之亦然——如果数据已被删除，它将无法恢复。
在本文中，我们将探讨从 PostgreSQL 表中恢复已删除数据的选项。

01 谨慎的人使用事务

如果在处理数据时从不犯错误，就不会需要紧急程序。但由于我们都是人，像这样的事情偶尔会发生：

-- remove an entry from our contact list
db=> DELETE FROM addressbook WHERE name = 'Heinz Schmidt';
DELETE 3
-- PANIC:  WHY WERE THERE 3 ROWS AND NOT JUST ONE

养成的一个好习惯是始终使用事务，在确认一切正常后才提交它们。

-- remove an entry from our contact list
db=> BEGIN;
BEGIN
db=*> DELETE FROM addressbook WHERE name = 'Heinz Schmidt';
DELETE 3
-- NOTICE:  huh?
db=*> ROLLBACK;
ROLLBACK

使用事务后，未预期的行数变化让人感到轻松了许多。

02 谨慎的人有备份

如果您有可用的备份，您可以通过从备份中获取一些所需内容用于恢复数据处理的错误。

-- remove an entry from our contact list
db=> DELETE FROM addressbook WHERE name = 'Heinz Schmidt';
DELETE 3
-- WARNING:  oh no, not again
db=> SELECT now();
             now
-----------------------------
 2024-03-11 16:22:25.1679+01

现在，您可以指定一个Delete命令之前的时间戳来执行“时间点还原”。

03 迅速的人使用pg_dirtyread

假设选项1和2失败了，我们确实需要从正在运行的PostgreSQL实例中恢复数据。好消息是，DELETE 实际上并不删除数据，它只是将其标记为对后续事务不可见。这样做是为了允许并发事务仍然读取数据。只有当VACUUM（或autovacuum）清理表时，才会实际删除行。（对于对此机制感兴趣的人，可以参考PostgreSQL文档中的MVCC章节。）
在PostgreSQL中没有内置的方法来获取已删除但仍然存在的行，但我维护着一个名为pg_dirtyread 的PostgreSQL扩展允许这样做。

-- remove an entry from our contact list
db=> DELETE FROM addressbook WHERE name = 'Heinz Schmidt';
DELETE 3
-- NOTICE:  sigh
db=>