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=> SELECT * FROM addressbook;
name | city
---------------------+-------------
Christoph Berg | Krefeld
Hans-Jürgen Schönig | Wöllersdorf
(2 rows)
-- WARNING: put on safety goggles now, we'll need superuser privileges
我们需要从一个包中安装 pg_dirtyread（或从源代码编译），然后在发生事故的数据库中创建这个扩展:
$ sudo apt install postgresql-16-dirtyread
db=# CREATE EXTENSION pg_dirtyread;
CREATE EXTENSION
该扩展提供了一个函数pg_dirtyread(‘tablename’) ，它像 PostgreSQL本身一样读取表，但忽略了任何行删除的标记。SQL要求我们在调用它时提供一个带有数据类型注释的列表，因此我们首先检查表的定义：
db=# \d addressbook
Table "public.addressbook"
Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
name | text | | |
city | text | | |
由此我们可以编译 pg_dirtyread 调用：
db=# SELECT * from pg_dirtyread('addressbook') addressbook(name text, city text);
name | city
---------------------+-------------
Christoph Berg | Krefeld
Heinz Schmidt | Berlin
Heinz Schmidt | Wien
Heinz Schmidt | Basel
Hans-Jürgen Schönig | Wöllersdorf
(5 rows)
-- NOTICE: phew
我们的数据就在那里！我们可以将缺失的行复制到新表中，然后将其注入原始表中：
db=# CREATE TABLE addressbook_recover AS
SELECT * from pg_dirtyread('addressbook') addressbook(name text, city text)
WHERE name = 'Heinz Schmidt';
SELECT 3
db=# SELECT * from addressbook_recover ;
name | city
---------------+--------
Heinz Schmidt | Berlin
Heinz Schmidt | Wien
Heinz Schmidt | Basel
(3 rows)
db=# INSERT INTO addressbook SELECT * FROM addressbook_recover;
INSERT 3
只要VACUUM没有执行垃圾回收工作pg_dirtyread就可以正常工作。VACUUM由自动清理程序启动，针对那些至少变化了20%的表，每分钟执行一次。如果您的错误DELETE影响了更多的行（或者引起了累积膨胀超过该阈值），在这种情况下，在发生之前您只有不到60秒的时间关闭数据库并关闭自动清理。（请务必将 autovacuum=on 保留为数据库的默认设置。虽然关闭它会使恢复删除的行更容易，但如果表不定期清理膨胀，会发生不好的事情。）
04 绝望的人使用整页书写
如果pg_dirtyread太晚到达，因为行已经被垃圾收集了，仍然有希望。PostgreSQL在预写式日志（WAL）中跟踪所有更改。虽然这些更改记录只包含更改后的数据，但在每次操作每个页面（PostgreSQL在磁盘上处理数据的8KB单元）时，都会将整个页面的镜像写入WAL。这些完整页写入（FPW）可以被收集以提取已删除的行。
首先，我们需要一些关于在哪里查找的低级信息：
db=# select oid from pg_database where datname = current_database();
oid
-----
5
db=# select relfilenode from pg_class where relname = 'addressbook';
relfilenode
-------------
125616
db=# select pg_walfile_name(pg_current_wal_lsn());
pg_walfile_name
--------------------------
000000010000000700000037
我们可以使用 pg_waldump 来解码 WAL：
$ /usr/lib/postgresql/16/bin/pg_waldump --relation=1663/5/125616 16/main/pg_wal/000000010000000700000037 | grep DELETE
rmgr: Heap len (rec/tot): 59/ 359, tx: 1894, lsn: 7/373798E0, prev 7/373798A8, desc: DELETE xmax: 1894, off: 2, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/5/125616 blk 0 FPW
rmgr: Heap len (rec/tot): 54/ 54, tx: 1894, lsn: 7/37379A48, prev 7/373798E0, desc: DELETE xmax: 1894, off: 3, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/5/125616 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 1894, lsn: 7/37379A80, prev 7/37379A48, desc: DELETE xmax: 1894, off: 4, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/5/125616 blk 0
我们可以看到我们的3个已删除行，第一个WAL记录被标记为包含FPW。使用PG16的pg_waldump，我们可以将FPW提取到一个文件中：
$ /usr/lib/postgresql/16/bin/pg_waldump --save-fullpage=fpw --relation=1663/5/125616 16/main/pg_wal/000000010000000700000037 | grep DELETE
rmgr: Heap len (rec/tot): 59/ 359, tx: 1894, lsn: 7/373798E0, prev 7/373798A8, desc: DELETE xmax: 1894, off: 2, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/5/125616 blk 0 FPW
rmgr: Heap len (rec/tot): 54/ 54, tx: 1894, lsn: 7/37379A48, prev 7/373798E0, desc: DELETE xmax: 1894, off: 3, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/5/125616 blk 0
rmgr: Heap len (rec/tot): 54/ 54, tx: 1894, lsn: 7/37379A80, prev 7/37379A48, desc: DELETE xmax: 1894, off: 4, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/5/125616 blk 0
它实际上提取了两个 FPW，但是查看 LSN，我们只对第一个感兴趣，所以我删除了第二个。
让我们通过创建新表并连接 FPW 文件来形成表内容，将其反馈给 PostgreSQL：
db=# create table addressbook_fpw (like addressbook);
CREATE TABLE
db=# select relfilenode from pg_class where relname = 'addressbook';
relfilenode
-------------
125628
$ sudo systemctl stop postgresql
$ cat fpw/* > base/5/125628
$ sudo systemctl start postgresql
由于 FPW 中的某些行已被标记为已删除，我们仍然必须使用 pg_dirtyread：
db=# SELECT * FROM pg_dirtyread('addressbook_fpw') addressbook(name text, city text);
name | city
---------------------+-------------
Christoph Berg | Krefeld
Heinz Schmidt | Berlin
Heinz Schmidt | Wien
Heinz Schmidt | Basel
Hans-Jürgen Schönig | Wöllersdorf
(5 Zeilen)
db=# INSERT into addressbook
SELECT * FROM pg_dirtyread('addressbook_fpw') addressbook(name text, city text)
WHERE name = 'Heinz Schmidt';
INSERT 3
-- NOTICE: hopefully for the last time
这种方法很脆弱，最好在最后一次检查点之后没有其他命令影响表时运行。如果在上次清理后删除了其他行，它们也可能重新出现。如果您的PostgreSQL 版本早于16，需要将–save-fullpage开关功能进行回溯移植。
翻译工具：ChatGPT 4.0
原文作者：Christoph Berg
原文链接：
https://www.cybertec-postgresql.com/en/recovering-deleted-data-from-postgresql-tables/
#PG证书#PG考试#postgresql初级#postgresql中级#postgresql高级