急急急！PostgreSQL数据误删怎么办？

呆呆的私房菜 2024-12-23

1092

Whoami：5年+金融、政府、医疗领域工作经验的DBA
Certificate：PGCM、OCP、YCP
Skill：Oracle、Mysql、PostgreSQL、国产数据库
Platform：CSDN、墨天轮、公众号（呆呆的私房菜）

阅读本文可以了解在不小心误删PostgreSQL数据时，能通过哪些方式进行数据恢复。

概述

在数据库层面误操作导致的数据丢失，可能由DDL或DML操作引起的；那么，哪种场景的数据恢复的可能性更大呢？

DDL操作：在PostgreSQL数据库中，表通过oid命名规则，以文件的方式存放于$PGDATA/base/dbid/relfilenode中；如果执行drop操作的话，会将整个文件进行整体删除，此时操作系统上的文件已经不存在了，因此在数据库层面上很难恢复，只能通过恢复磁盘的方式去找回数据，但是这种方式找回数据的概率也很小！再一次敲响给我们敲响警钟：数据库一定一定一定要做好定期备份！！！
DML操作：结合PostgreSQL的MVCC实现，数据库误操作update或delete，我们可以通过针对不同的场景，采用不同的方案来尝试数据恢复。

pg_resetwal

pg_resetwal 用于重置PostgreSQL数据库的wal日志和pg_control文件中的一些控制信息。既如此，那么我们也可以利用pg_resetwal把数据库回滚到一个一致的状态点。

1. 创建测试环境
postgres=# CREATE TABLE test1 (id INT, name VARCHAR(10));
CREATE TABLE
postgres=# INSERT INTO test1 VALUES (1, 'asd');
INSERT 0 1
postgres=# INSERT INTO test1 VALUES (2, 'qwe');
INSERT 0 1
postgres=# INSERT INTO test1 VALUES (3, 'zxc');
INSERT 0 1


2. 模拟误删数据
postgres=# DELETE FROM test1 WHERE id > 1;
DELETE 2


3. 查看当前LSN及WAL文件
postgres=# SELECT pg_current_wal_lsn(), pg_walfile_name(pg_current_wal_lsn()), pg_walfile_name_offset(pg_current_wal_lsn());
 pg_current_wal_lsn |     pg_walfile_name      |       pg_walfile_name_offset
--------------------+--------------------------+------------------------------------
 0/17377D8          | 000000010000000000000001 | (000000010000000000000001,7567320)
(1 row)


4. 查找误操作的事务号
pg_waldump -b -p home/postgres/pg15/data/pg_wal 000000010000000000000001 > wal.log
日志如下：
rmgr: Heap        len (rec/tot):     63/    63, tx:        735, lsn: 0/01737608, prev 0/01737450, desc: INSERT+INIT off 1 flags 0x00
        blkref #0: rel 1663/5/24576 fork main blk 0
rmgr: Transaction len (rec/tot):     34/    34, tx:        735, lsn: 0/01737648, prev 0/01737608, desc: COMMIT 2024-12-20 11:52:10.475243 CST
rmgr: Heap        len (rec/tot):     63/    63, tx:        736, lsn: 0/01737670, prev 0/01737648, desc: INSERT off 2 flags 0x00
        blkref #0: rel 1663/5/24576 fork main blk 0
rmgr: Transaction len (rec/tot):     34/    34, tx:        736, lsn: 0/017376B0, prev 0/01737670, desc: COMMIT 2024-12-20 11:52:10.477603 CST
rmgr: Heap        len (rec/tot):     63/    63, tx:        737, lsn: 0/017376D8, prev 0/017376B0, desc: INSERT off 3 flags 0x00
:2024-12-20 12:04:49.907 CST [1938] LOG:  checkpoint starting: time
2024-12-20 12:04:49.927 CST [1938] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.004 s, total=0.021 s; sync files=2, longest=0.003 s, average=0.002 s; distance=0 kB, estimate=0 kB
        blkref #0: rel 1663/5/24576 fork main blk 0
rmgr: Transaction len (rec/tot):     34/    34, tx:        737, lsn: 0/01737718, prev 0/017376D8, desc: COMMIT 2024-12-20 11:52:10.749071 CST
rmgr: Heap        len (rec/tot):     54/    54, tx:        738, lsn: 0/01737740, prev 0/01737718, desc: DELETE off 2 flags 0x00 KEYS_UPDATED
        blkref #0: rel 1663/5/24576 fork main blk 0
rmgr: Heap        len (rec/tot):     54/    54, tx:        738, lsn: 0/01737778, prev 0/01737740, desc: DELETE off 3 flags 0x00 KEYS_UPDATED
        blkref #0: rel 1663/5/24576 fork main blk 0
rmgr: Transaction len (rec/tot):     34/    34, tx:        738, lsn: 0/017377B0, prev 0/01737778, desc: COMMIT 2024-12-20 11:52:14.104171 CST


5. 关闭数据库，根据wal日志回滚到目标事务ID
pg_ctl stop -D $PGDATA
pg_resetwal -x 738 -D $PGDATA


6. 启动数据库并查看数据
pg_ctl start -D $PGDATA
postgres=# select * from test1;
 id | name
----+------
  1 | asd
  2 | qwe
  3 | zxc
(3 rows)

注意：生产环境慎用！pg_resetwal会对整个实例进行回滚到指定的事务ID，且wal日志整体被清理！可以考虑将数据拷贝到测试环境进行恢复后在还原到生产中。

pg_recovery

pg_recovery是一款基于 PostgreSQL 的数据恢复工具，主要针对在执行了 update、delete、rollback、drop column 等操作后的数据恢复。

1. 创建测试环境
postgres=# CREATE TABLE test2 (id INT, name VARCHAR(10));
CREATE TABLE
postgres=# INSERT INTO test2 VALUES (1, 'asd');
INSERT 0 1
postgres=# INSERT INTO test2 VALUES (2, 'qwe');
INSERT 0 1
postgres=# INSERT INTO test2 VALUES (3, 'zxv');
INSERT 0 1
postgres=# DELETE FROM test2 WHERE id > 1;
DELETE 2


2. 下载pg_recovery，上传安装包并解压，编译安装
https://github.com/radondb/pg_recovery
unzip pg_recovery-master.zip
cd /home/postgres/pg15/soft/contrib/pg_recovery-master
make && make install


3. 创建pg_recovery扩展
postgres=# CREATE EXTENSION pg_recovery;
CREATE EXTENSION


4. 查看误删除数据
postgres=# SELECT * FROM pg_recovery ('test2') AS (id int, name varchar(10));
 id | name
----+------
  2 | qwe
  3 | zxv
(2 rows)

pg_dirtyread

pg_dirtyread是一个第三方PostgreSQL扩展，它允许用户读取数据库文件中的“脏”数据，即那些被标记为删除或不再可见的数据。这个扩展对于数据恢复和调试非常有用，尤其是在需要恢复被删除或更新前的数据时。

1. 创建测试环境
postgres=# CREATE TABLE test3 (id INT, name VARCHAR(10));
CREATE TABLE
postgres=# INSERT INTO test3 VALUES (1, 'asd');
INSERT 0 1
postgres=# INSERT INTO test3 VALUES (2, 'qwe');
INSERT 0 1
postgres=# INSERT INTO test3 VALUES (3, 'zxv');
INSERT 0 1
postgres=# DELETE FROM test3 WHERE id > 1;
DELETE 2


2. 下载pg_recovery，上传安装包并解压，编译安装
https://github.com/df7cb/pg_dirtyread
tar xzf pg_dirtyread-2.7.tar.gz
cd /home/postgres/pg15/soft/contrib/pg_dirtyread-2.7
make && make install


3. 创建pg_recovery扩展
postgres=# CREATE EXTENSION pg_dirtyread;
CREATE EXTENSION


4. 查看表中所有数据行，包括已经被标记为删除或不再可见的行
postgres=# SELECT * FROM pg_dirtyread('test3') AS (id INT, name VARCHAR(10));
 id | name
----+------
  1 | asd
  2 | qwe
  3 | zxv
(3 rows)

pg_filedump

pg_filedump是一个命令行工具，可以在服务端执行，不需要连接数据库。这个工具可以分析出数据文件中数据的详细数据，内容格式与pageinspect类似。它可以直接读取文件，适用于严重灾难的情况，但是需要知道具体的文件位置，适用性不强。
pg_filedump可以直接通过SQL将数据一键找回，需要编译找回数据方法。不过，它无法找回自定义数据类型的数据，并且由于只能在服务端执行，不适用于云数据库的数据找回。

1. 创建测试环境
postgres=# CREATE TABLE test4 (id INT, name VARCHAR(10));
CREATE TABLE
postgres=# INSERT INTO test4 VALUES (1, 'asd');
INSERT 0 1
postgres=# INSERT INTO test4 VALUES (2, 'qwe');
INSERT 0 1
postgres=# INSERT INTO test4 VALUES (3, 'zxv');
INSERT 0 1
postgres=# DELETE FROM test4 WHERE id > 1;
DELETE 2


2. 下载pg_recovery，上传安装包并解压，编译安装
https://github.com/df7cb/pg_filedump
tar xzf pg_filedump-REL_17_1.tar.gz
cd /home/postgres/pg15/soft/contrib/pg_filedump-REL_17_1
make && make install


3. 查看test4表文件存放路径
postgres=# SELECT pg_relation_filepath('test4');
 pg_relation_filepath
----------------------
 base/5/24635
(1 row)


4. 解析文件内容
pg_filedump -D int,varchar home/postgres/pg15/data/base/5/24635 | grep COPY
COPY: 1 asd
COPY: 2 qwe
COPY: 3 zxv

pageinspect

pageinspect是 PostgreSQL 自带的一个扩展，它提供了查看数据库页级别的信息的功能。虽然它不直接用于数据恢复，但可以辅助我们在数据恢复过程中查看和分析数据。

1. 创建测试环境
postgres=# CREATE TABLE test3 (id INT, name VARCHAR(10));
CREATE TABLE
postgres=# INSERT INTO test3 VALUES (1, 'asd');
INSERT 0 1
postgres=# INSERT INTO test3 VALUES (2, 'qwe');
INSERT 0 1
postgres=# INSERT INTO test3 VALUES (3, 'zxv');
INSERT 0 1
postgres=# DELETE FROM test3 WHERE id > 1;
DELETE 2


3. 编译安装pageinspect插件
cd /home/postgres/pg15/soft/contrib/pageinspect
make && make install 


4. 创建pageinspect扩展
postgres=# create extension pageinspect;
CREATE EXTENSION


5. 识别被删除的数据
postgres=# SELECT * FROM heap_page_items(get_raw_page('test3', 0))
postgres-# WHERE t_xmax <> 0;
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |       t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------------------
  2 |   8128 |        1 |     32 |    747 |    749 |        0 | (0,2)  |        8194 |       1282 |     24 |        |       | \x0200000009717765
  3 |   8096 |        1 |     32 |    748 |    749 |        0 | (0,3)  |        8194 |       1282 |     24 |        |       | \x03000000097a7876
(2 rows)


6. 提取出字段数据
postgres=# SELECT tuple_data_split((SELECT oid FROM pg_class WHERE relname = 'test3')::oid, t_data, t_infomask, t_infomask2, t_bits)
postgres-# FROM heap_page_items(get_raw_page('test3', 0))
postgres-# WHERE t_xmax <> 0;
       tuple_data_split
-------------------------------
 {"\\x02000000","\\x09717765"}
 {"\\x03000000","\\x097a7876"}
(2 rows)


7. 转换成可视数据......

注意：以上几种方式都仅适用于表还没做vacuum或者vacuum full操作。如果表已经清理了死元组，则数据无法恢复。

本文内容就到这啦，阅读完本篇，相信你已经掌握误删数据时PostgreSQL恢复数据的方法了吧！我们下篇再见！

点击上方公众号，关注我吧！

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

急急急！PostgreSQL数据误删怎么办？

评论