PostgreSQL 误删数据后的恢复方法
在 PostgreSQL 数据库的利用过程中，误操作可能会导致数据丢失，这些误操作紧张包罗 DDL（数据定义语言）和 DML（数据操作语言）操作。以下是不同误操作环境下的数据恢复方法及相应工具的具体先容。一、DDL 操作导致的数据丢失及恢复困难
当实行 DDL 操作（如 drop）时，数据恢复会变得极为困难。在 PostgreSQL 数据库中，表是通过 oid 定名规则，并以文件的形式存放在 $PGDATA/base/dbid/relfilenode 中。如果实行了 drop 操作，会将整个文件进行整体删除，这意味着在操作系统层面上，相应的文件已不存在。此时，仅从数据库层面几乎无法恢复数据，只能尝试通过恢复磁盘的方式找回数据，但这种方式成功的概率极低。这再次凸显了数据库定期备份的紧张性，必须做好数据库的定期备份工作，以防数据丢失造成的严重后果。
二、DML 操作导致的数据丢失及恢复方法
（一）利用 pg_resetwal 工具
[*]原理及操作步骤：
[*]pg_resetwal 可用于重置 PostgreSQL 数据库的 wal 日记和 pg_control 文件中的一些控制信息，我们可以利用它将数据库回滚到一个一致的状态点。以下是具体的操作步骤：
创建测试环境：
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
模拟误删数据：
postgres=# DELETE FROM test1 WHERE id > 1;
DELETE 2
检察当前 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)
查找误操作的事务号：
利用 pg_waldump -b -p /home/postgres/pg15/data/pg_wal 000000010000000000000001 > wal.log 下令将 WAL 日记信息导出到 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 LOG:checkpoint starting: time
2024-12-20 12:04:49.927 CST 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.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
关闭数据库，根据 WAL 日记回滚到目的事务 ID：
pg_ctl stop -D $PGDATA
pg_resetwal -x 738 -D $PGDATA
- **启动数据库并查看数据**：
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 等操作后的数据恢复。以下是利用该工具的步骤：
[*]创建测试环境：
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
下载、安装并编译 pg_recovery 工具：
起首从 https://github.com/radondb/pg_recovery 下载 pg_recovery，上传安装包并解压，然后编译安装：
unzip pg_recovery-master.zip
cd /home/postgres/pg15/soft/contrib/pg_recovery-master
make && make install
创建 pg_recovery 扩展并检察误删除数据：
postgres=# CREATE EXTENSION pg_recovery;
CREATE EXTENSION
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 扩展，它允许用户读取数据库文件中的“脏”数据，即那些被标记为删除或不再可见的数据，对于数据恢复和调试很有用。操作步骤如下：
[*]创建测试环境：
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
下载、安装并编译 pg_dirtyread 扩展：
从 https://github.com/df7cb/pg_dirtyread 下载 pg_dirtyread，上传安装包并解压，编译安装：
tar xzf pg_dirtyread-2.7.tar.gz
cd /home/postgres/pg15/soft/contrib/pg_dirtyread-2.7
make && make install
创建 pg_dirtyread 扩展并检察数据：
postgres=# CREATE EXTENSION pg_dirtyread;
CREATE EXTENSION
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 类似。可直接读取文件，适用于严重灾难环境，但需知道具体文件位置，适用性有限，且无法找回自定义数据类型的数据，不适用于云数据库的数据找回。操作步骤如下：
[*]创建测试环境：
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
- **下载、安装并编译 pg_filedump 工具**：
从 https://github.com/df7cb/pg_filedump 下载 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
- **查看表文件存放路径及解析文件内容**：
postgres=# SELECT pg_relation_filepath('test4');
pg_relation_filepath
----------------------
base/5/24635
(1 row)
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 自带的扩展，可检察数据库页级别的信息，虽不直接用于数据恢复，但能辅助数据恢复过程中的检察和分析工作。以下是操作步骤：
[*]创建测试环境：
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=# INSERTINTO test3 VALUES (3, 'zxv');
INSERT 0 1
postgres=# DELETE FROM test3 WHERE id > 1;
DELETE 2
编译安装 pageinspect 插件：
cd /home/postgres/pg15/soft/contrib/pageinspect
make && make install
[*]创建 pageinspect 扩展，辨认被删除的数据：
postgres=# create extension pageinspect;
CREATE EXTENSION
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)
提取字段数据：
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)
紧张留意事项：
以上提及的 pg_resetwal、pg_recovery、pg_dirtyread、pg_filedump 和 pageinspect 这些数据恢复方法，都仅适用于表还没做 vacuum 或者 vacuum full 操作的环境。一旦表已经清算了死元组，这些数据恢复方法可能无法成功恢复数据。因此，在操作数据库时，必要密切关注 vacuum 操作的时间和频率，避免因误操作和 vacuum 操作导致数据无法恢复。同时，对于不同的数据恢复工具和方法，在利用前要充分了解其原理和适用场景，尤其是在生产环境中，务必审慎操作，以免造成更大的数据丧失。
在一样寻常数据库操作中，要时候保持鉴戒，制定完善的数据备份计谋，并定期进行备份，以应对可能出现的误操作和数据丢失风险。在必要恢复数据时，根据不同的误操作场景，仔细评估利用合适的数据恢复工具和方法，尽可能减少数据丢失带来的影响。
接待关注公众号《小周的数据库进阶之路》，更多精彩知识和干货尽在此中。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]