首页 » PostgreSQL/GaussDB » 如何恢复PostgreSQL误删除的表数据?

如何恢复PostgreSQL误删除的表数据?

  • 2024/10/13
  • PostgreSQL/GaussDB
  • 72 views
  • 如何恢复PostgreSQL误删除的表数据?已关闭评论

今天朋友问如果truncate table如何在postgresql中恢复？另外还有drop , delete删除数据类操作，除了使用常规备份恢复，如果在oracle恢复有flashback query, recyclebin, 或在数据文件中的block补覆盖前抽取如基于rowid抽或DUL类工具扫描datafile , 在PostgreSQL开源软件中似乎只有备份恢复，那有没有其他手段呢？

处于该情况：立即停止数据库pg_ctl stop -m immediate（这immediate很重要，您需要模拟崩溃并防止检查点）然后不要重新启动它. PostgreSQL 中的正常操作DELETE会将行标记为已删除，但实际上并不会立即删除数据，因此如果您立即停止数据库并且不向表中写入任何其他内容，通常可以恢复数据。

TRUNCATE从TRUNCATE文件系统中删除代表数据库表的底层文件，PG会调用操作系统rm，并且会改变disk上的写位置。如果可能的话，恢复数据需要对硬盘进行取证分析。如果数据确实很重要，那么现在就关闭计算机并获取硬盘的磁盘映像。恢复工作可能需要花费几万银子，因为您需要一个既了解 (a) 文件系统内部。结构又了解 (b) PostgreSQL 内部结构的人。pg的page中没有像oracle block中的obj# 拼接文件，大部分是操作系统层的文件恢复。

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

1， 使用事务
养成的好习惯是始终使用事务，并且只在检查一切正常后才提交。

postgres=# create database anbob;
CREATE DATABASE
postgres=# \c anbob
您现在已经连接到数据库 "anbob",用户 "weejar".
anbob=# create table test(id int,name varchar(10));
CREATE TABLE
anbob=# insert into test values(1,'anobb');
INSERT 0 1
anbob=# insert into test values(2,'weejar');
INSERT 0 1
anbob=# begin;
BEGIN
anbob=*# select * from test;
 id |  name
----+--------
  1 | anobb
  2 | weejar
(2 行记录)


anbob=*# truncate table test;
TRUNCATE TABLE
anbob=*# select * from test;
 id | name
----+------
(0 行记录)


anbob=*# rollback;
ROLLBACK
anbob=# select * from test;
 id |  name
----+--------
  1 | anobb
  2 | weejar

2, 利用备份
如果您有有效备份，则可以通过再次从备份中获取所有内容来恢复任何数据处理错误。如pg_basebackup备份，做基于时间戳recovery_target_time执行时间点恢复 (PITR) 。或pg_dump逻辑导出。

pg_dump --file "Your Filename" -h "Your Hostname" -p "Your Port" -U "username" --verbose -F p --data-only --column-inserts  --table "Your table name" "Your DB name"

pg_dump --data-only --table=tablename sourcedb > onetable.pg

-- restore
psql destdb < onetable.pg

如果有full dump备份，也可以只恢复单个表

pg_restore --host "Your Hostname" --port "Your Port" -U "Your Username" --dbname "Your Db Name" --data-only --verbose --schema "Your Schema" --table "Your Table Name" "Your Backup File Name with Path"

pg_restore --data-only --table=tablename fulldump.pg > onetable.pg

3, 使用pg_dirtyread
确实必须从正在运行的 PostgreSQL 实例中恢复数据。好消息是 DELETE 不会真正删除数据，它只是将其标记为对后续事务不可见。这样做是为了让并发事务仍然能够读取数据。只有当 VACUUM（或Autovacuum）清理表时才会真正删除行。
PostgreSQL 中没有内置方法来获取已删除但仍然存在的行，一个 PostgreSQL 扩展可以允许这样做：pg_dirtyread。

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

db=# CREATE EXTENSION pg_dirtyread;
CREATE EXTENSION

该扩展提供了一个像 PostgreSQL 本身一样读取表的函数pg_dirtyread(‘tablename’)，但忽略了任何行删除标记。要求我们在调用它时提供带有数据类型注释的列表

db=# \d addressbook 
           Table "public.addressbook"
 Column | Type | Collation | Nullable | Default 
--------+------+-----------+----------+---------
 name   | text |           |          | 
 city   | text |           |          | 

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)

类似Oracle的闪回查询，然后就可以把数据复制到新表，处理后insert会原表。只要 VACUUM 尚未完成其垃圾收集任务，使用 pg_dirtyread 就可以了。自动清理启动器每分钟都会对更改了至少 20% 的表触发 VACUUM。如果您的错误 DELETE 命中率超过该值（或累积膨胀超过该阈值），您只有不到 60 秒的时间关闭数据库并关闭自动清理，然后才能发生这种情况。（请autovacuum=on在数据库中保留默认设置。虽然关闭它可以更轻松地取消删除行，但如果不定期清除表中的膨胀，就会发生糟糕的事情。）

4, 使用FPW（full page write）
PostgreSQL 会跟踪预写日志 (WAL) 中的所有更改。虽然这些更改记录仅包含更改后的数据，但第一次接触每个页面（PostgreSQL 在磁盘上处理数据的 8kB 单位）时，整个页面的图像都会写入 WAL。可以收集这些整页写入 (FPW) 以提取已删除的行。

查找位置

anbob=# select * from test;
 id |  name
----+--------
  1 | anobb
  2 | weejar

anbob=# delete from test where id=2;
DELETE 1

anbob=# select * from pg_tablespace;
 oid  |  spcname   | spcowner | spcacl | spcoptions
------+------------+----------+--------+------------
 1663 | pg_default |       10 |        |
 1664 | pg_global  |       10 |        |

anbob=# select oid from pg_database where datname = current_database();
  oid
-------
 16438
anbob=# select oid,relfilenode from pg_class where relname = 'test';
  oid  | relfilenode
-------+-------------
 16439 |       16439

anbob=# select pg_walfile_name(pg_current_wal_lsn());
     pg_walfile_name
--------------------------
 000000010000000000000001

可以使用 pg_waldump 来解码 WAL
–relation=tblspc/db/rel

$ pg_waldump --relation=1663/16438/16439 --path=D:\postgresql\pgsql\data17\pg_wal -s 0/1C220E8
pg_waldump: first record is after 0/1C220E8, at 0/1C223F0, skipping over 776 bytes
rmgr: Heap        len (rec/tot):     65/    65, tx:        762, lsn: 0/01C229C0, prev 0/01C22808, desc: INSERT+INIT off: 1, flags: 0x00, blkref #0: rel 1663/16438/16439 blk 0
rmgr: Heap        len (rec/tot):     66/    66, tx:        763, lsn: 0/01C22A68, prev 0/01C22A30, desc: INSERT off: 2, flags: 0x00, blkref #0: rel 1663/16438/16439 blk 0
rmgr: Heap        len (rec/tot):     59/   171, tx:        765, lsn: 0/01C22ED0, prev 0/01C22E98, desc: DELETE xmax: 765, off: 2, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16438/16439 blk 0 FPW

可以看到已删除的 1 行，并且第一个DELETE WAL 记录被标记为包含 FPW。

使用 PG16 的 pg_waldump，我们可以将 FPW 提取到一个文件中：

$ pg_waldump --relation=1663/16438/16439 --path=D:\postgresql\pgsql\data17\pg_wal -s 0/1C220E8 --save-fullpage=fpw
pg_waldump: first record is after 0/1C220E8, at 0/1C223F0, skipping over 776 bytes
rmgr: Heap        len (rec/tot):     65/    65, tx:        762, lsn: 0/01C229C0, prev 0/01C22808, desc: INSERT+INIT off: 1, flags: 0x00, blkref #0: rel 1663/16438/16439 blk 0
rmgr: Heap        len (rec/tot):     66/    66, tx:        763, lsn: 0/01C22A68, prev 0/01C22A30, desc: INSERT off: 2, flags: 0x00, blkref #0: rel 1663/16438/16439 blk 0
rmgr: Heap        len (rec/tot):     59/   171, tx:        765, lsn: 0/01C22ED0, prev 0/01C22E98, desc: DELETE xmax: 765, off: 2, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/16438/16439 blk 0 FPW

D:\postgresql\pgsql\bin>dir fpw

2024/10/13 20:30 <DIR> .
2024/10/13 20:30 <DIR> ..
2024/10/13 20:30 8,192 00000001-00000000-01C22ED0.1663.16438.16439.0_main
 1 个文件 8,192 字节
 2 个目录 235,497,811,968 可用字节

 

–another case on Linux demo

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

通过创建新表并连接 FPW 文件来形成表内容
在postgresql中可以直接替换table对应的file，重启Pg就可以被pg识别到。

db=# create table test_fpw (like test);
CREATE TABLE

db=# select relfilenode from pg_class where relname = 'addressbook';
 relfilenode
-------------
      125628
-- stop pg
# systemctl stop postgresql

-- merge file
$ cat fpw/* > base/5/125628

-- start pg
$ sudo systemctl start postgresql

至此创建的新表，就包含了我们合成的page,可以SQL查询，由于 FPW 中的某些行已被标记为已删除，我们仍然必须使用 pg_dirtyread：

db=# SELECT * FROM pg_dirtyread('test_fpw') test(id text, name text);
 id |  name
----+--------
  1 | anobb
  2 | weejar   --- deleted

剩下的就可以继续往原表insert了。这种方法很脆弱，当自上次 CHECKPOINT 以来没有其他命令（有问题的 DELETE 除外）触碰表时，这种方法效果最好。如果在上次 VACUUM 之后删除了其他行，它们也可能会重新出现。如果您的 PostgreSQL 版本早于 16，则–save-fullpage需要找替代方法。

5, 购买技术支持维保
这里列出的大多数选项都需要对 PostgreSQL 内部结构有相当多的了解。如果你想要安全起见，可以考虑找专业的公司购买技术支持。

References

Recovering Deleted Data From PostgreSQL Tables

打赏

, ,

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：