社区博客 | 从PostgreSQL表中恢复已删除数据

KunlunBase 昆仑数据库 2024-03-29

1724

点击上方蓝字关注我们

导读
对应于文中描述的PostgreSQL的数据误操作的恢复能力，Klustron具备等价的功能：Klustron的逻辑备份恢复功能，辅以实时流式增量备份的binlog日志，可以达到等价的恢复效果。

关键词：PostgreSQL、备份恢复、全页写入

PostgreSQL 非常擅长保护用户的数据，避免数据无故丢失。不幸的是，反之亦然——如果数据被删除了，它就无法恢复。

在这篇文章中，我们将探讨从PostgreSQL表中恢复已删除数据的选项。

选项1：谨慎的人使用事务

如果您在数据操作过程中永远不犯错，理论上是不需要备用恢复方案的。但考虑到我们每个人都可能犯错，以下情况便有可能发生：

-- 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

有了事务，意外的行数就不那么让人压力山大了。

选项2：细心的人有备份

如果您有有效的备份，那么可以通过再次从备份中获取所有东西来从任何数据处理错误中恢复。

-- 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

您现在可以执行时间点恢复（PITR），恢复到不希望的DELETE命令之前的时间戳。

选项3：迅速的人使用pg_dirtyread

假设选项1和2都失败了，我们真的需要从正在运行的PostgreSQL实例中恢复数据。好消息是DELETE实际上并不删除数据，它只是将其标记为对后续事务不可见。这样做是为了允许并发事务仍然能读取数据。实际删除行只在VACUUM（或自动清理）清理表时发生。（对于那些对该机制更感兴趣的人，请参阅PostgreSQL文档中的MVCC章节。）

PostgreSQL没有内置的方式来访问已删除但仍存在的行，但有一个我正在维护的PostgreSQL扩展允许这样做：pg_dirtyread。

-- 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 秒的时间关闭数据库并关闭自动清理。（请默认在您的数据库中开启自动清理。虽然关闭它使得恢复行更容易，但如果表不定期清理膨胀，会发生不好的事情。）

选项 4：绝望的人使用全页写入

如果 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 行 DELETE 的行，第一个 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
$ ls -l fpw
-rw-r--r-- 1 postgres postgres 8192  5. Mär 17:18 00000001-00000007-373798E0.1663.5.125616.0_main
-rw-r--r-- 1 postgres postgres 8192  5. Mär 17:18 00000001-00000007-37379E00.1663.5.125616.0_main
$ rm fpw/00000001-00000007-37379E00.1663.5.125616.0_main

实际上提取了两个 FPW，但查看 LSN，我们只对第一个感兴趣，所以我删除了第二个。

让我们将这个反馈给 PostgreSQL，通过创建一个新表，并将 FPW 文件串联起来形成表内容：

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

这种方法是脆弱的，最好在有问题的 DELETE 命令触及表格之后，除了最后的 CHECKPOINT 之外，没有其他命令接触到表格。如果在最后一次 VACUUM 之后，其他行被删除了，它们也可能重新出现。如果您的 PostgreSQL 版本早于 16，那么 --save-fullpage 开关功能需要被迁移。

翻译工具：ChatGPT 4.0

原文作者：Christoph Berg

原文链接：

https://www.cybertec-postgresql.com/en/recovering-deleted-data-from-postgresql-tables/

END

为促进团队内外的沟通联系，我们Klustron团队的bbs论坛开始上线，欢迎各位同学使用！（链接：https://forum.klustron.com/，或者点击文末“阅读原文”，即可跳转）

论坛目前是测试版，可能还存在不稳定的现象，欢迎各位老师、朋友共享信息，如果遇到问题还请谅解。

欢迎大家下载和安装Klustron数据库集群，并免费使用（无需注册码）。

Klustron 完整软件包下载：

http://downloads.klustron.com/

如需购买请邮箱联系sales_vip@klustron.com，有相关问题欢迎添加下方小助手微信联系🌹

产品文档

Klustron 快速入门：

https://doc.klustron.com/zh/Klustron_Instruction_Manual.html

Klustron 快速体验指南：

https://doc.klustron.com/zh/Klustron_Quickly_Guide.html

Klustron 功能体验范例：

https://doc.klustron.com/zh/Klustron-function-experience-example.html

Klustron 产品使用和测评指南：

https://doc.klustron.com/zh/product-usage-and-evaluation-guidelines.html

点击👆上方，关注获取源代码及技术信息~

文章转载自KunlunBase 昆仑数据库，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

社区博客 | 从PostgreSQL表中恢复已删除数据

点击上方蓝字关注我们

导读对应于文中描述的PostgreSQL的数据误操作的恢复能力，Klustron具备等价的功能：Klustron的逻辑备份恢复功能，辅以实时流式增量备份的binlog日志，可以达到等价的恢复效果。关键词：PostgreSQL、备份恢复、全页写入

评论

导读
对应于文中描述的PostgreSQL的数据误操作的恢复能力，Klustron具备等价的功能：Klustron的逻辑备份恢复功能，辅以实时流式增量备份的binlog日志，可以达到等价的恢复效果。

关键词：PostgreSQL、备份恢复、全页写入