PostgreSQL 误删数据后的恢复方法

灌篮少年  论坛元老 | 2025-4-5 10:17:16 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 1513|帖子 1513|积分 4539

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

您需要 登录 才可以下载或查看，没有账号？立即注册

x
在 PostgreSQL 数据库的利用过程中，误操作可能会导致数据丢失，这些误操作紧张包罗 DDL（数据定义语言）和 DML（数据操作语言）操作。以下是不同误操作环境下的数据恢复方法及相应工具的具体先容。
一、DDL 操作导致的数据丢失及恢复困难

当实行 DDL 操作（如 drop）时，数据恢复会变得极为困难。在 PostgreSQL 数据库中，表是通过 oid 定名规则，并以文件的形式存放在 $PGDATA/base/dbid/relfilenode 中。如果实行了 drop 操作，会将整个文件进行整体删除，这意味着在操作系统层面上，相应的文件已不存在。此时，仅从数据库层面几乎无法恢复数据，只能尝试通过恢复磁盘的方式找回数据，但这种方式成功的概率极低。这再次凸显了数据库定期备份的紧张性，必须做好数据库的定期备份工作，以防数据丢失造成的严重后果。
二、DML 操作导致的数据丢失及恢复方法

（一）利用 pg_resetwal 工具


  • 原理及操作步骤：

    • pg_resetwal 可用于重置 PostgreSQL 数据库的 wal 日记和 pg_control 文件中的一些控制信息，我们可以利用它将数据库回滚到一个一致的状态点。以下是具体的操作步骤：
      创建测试环境：

  1. postgres=# CREATE TABLE test1 (id INT, name VARCHAR(10));
  2. CREATE TABLE
  3. postgres=# INSERT INTO test1 VALUES (1, 'asd');
  4. INSERT 0 1
  5. postgres=# INSERT INTO test1 VALUES (2, 'qwe');
  6. INSERT 0 1
  7. postgres=# INSERT INTO test1 VALUES (3, 'zxc');
  8. INSERT 0 1
复制代码
模拟误删数据：
  1. postgres=# DELETE FROM test1 WHERE id > 1;
  2. DELETE 2
复制代码
检察当前 LSN 及 WAL 文件：
  1. postgres=# SELECT pg_current_wal_lsn(), pg_walfile_name(pg_current_wal_lsn()), pg_walfile_name_offset(pg_current_wal_lsn());
  2. pg_current_wal_lsn |    pg_walfile_name    |        pg_walfile_name_offset
  3. ------------------+----------------------+------------------------------------
  4. 0/17377D8         | 000000010000000000000001 | (000000010000000000000001,7567320)
  5. (1 row)
复制代码
查找误操作的事务号：
利用 pg_waldump -b -p /home/postgres/pg15/data/pg_wal 000000010000000000000001 > wal.log 下令将 WAL 日记信息导出到 wal.log 文件，并在日记中查找误操作对应的事务号。例如，从日记中可能找到类似如下信息：
  1. rmgr: Heap        len (rec/tot):     63/    63, tx:      735, lsn: 0/01737608, prev 0/01737450, desc: INSERT+INIT off 1 flags 0x00
  2.     blkref #0: rel 1663/5/24576 fork main blk 0
  3. 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
  4. rmgr: Heap        len (rec/tot):     63/    63, tx:      736, lsn: 0/01737670, prev 0/01737648, desc: INSERT off 2 flags 0x00
  5.     blkref #0: rel 1663/5/24576 fork main blk 0
  6. 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
  7. 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
  8. 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.0 kB
  9.     blkref #0: rel 1663/5/24576 fork main blk 0
  10. 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
  11. rmgr: Heap        len (rec/tot):     54/    54, tx:      738, lsn: 0/01737740, prev 0/01737718, desc: DELETE off 2 flags 0x00 KEYS_UPDATED
  12.     blkref #0: rel 1663/5/24576 fork main blk 0
  13. rmgr: Heap        len (rec/tot):     54/    54, tx:      738, lsn: 0/01737778, prev 0/01737740, desc: DELETE off 3 flags 0x00 KEYS_UPDATED
  14.     blkref #0: rel 1663/5/24576 fork main blk 0
  15. 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：
  1. pg_ctl stop -D $PGDATA
  2. pg_resetwal -x 738 -D $PGDATA
复制代码
  1. - **启动数据库并查看数据**：
复制代码
  1. pg_ctl start -D $PGDATA
  2. postgres=# select * from test1;
  3. id | name
  4. ----+------
  5. 1 | asd
  6. 2 | qwe
  7. 3 | zxc
  8. (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));
  2. CREATE TABLE
  3. postgres=# INSERT INTO test2 VALUES (1, 'asd');
  4. INSERT 0 1
  5. postgres=# INSERT INTO test2 VALUES (2, 'qwe');
  6. INSERT 0 1
  7. postgres=# INSERT INTO test2 VALUES (3, 'zxv');
  8. INSERT 0 1
  9. postgres=# DELETE FROM test2 WHERE id > 1;
  10. DELETE 2
复制代码
下载、安装并编译 pg_recovery 工具：
起首从 https://github.com/radondb/pg_recovery 下载 pg_recovery，上传安装包并解压，然后编译安装：
  1. unzip pg_recovery-master.zip
  2. cd /home/postgres/pg15/soft/contrib/pg_recovery-master
  3. make && make install
复制代码
创建 pg_recovery 扩展并检察误删除数据：
  1. postgres=# CREATE EXTENSION pg_recovery;
  2. CREATE EXTENSION
  3. postgres=# SELECT * FROM pg_recovery ('test2') AS (id int, name varchar(10));
  4. id | name
  5. ----+------
  6. 2 | qwe
  7. 3 | zxv
  8. (2 rows)
复制代码
（三）利用 pg_dirtyread 扩展


  • 原理及操作步骤：

    • pg_dirtyread 是一个第三方 PostgreSQL 扩展，它允许用户读取数据库文件中的“脏”数据，即那些被标记为删除或不再可见的数据，对于数据恢复和调试很有用。操作步骤如下：
    • 创建测试环境：

  1. postgres=# CREATE TABLE test3 (id INT, name VARCHAR(10));
  2. CREATE TABLE
  3. postgres=# INSERT INTO test3 VALUES (1, 'asd');
  4. INSERT 0 1
  5. postgres=# INSERT INTO test3 VALUES (2, 'qwe');
  6. INSERT 0 1
  7. postgres=# INSERT INTO test3 VALUES (3, 'zxv');
  8. INSERT 0 1
  9. postgres=# DELETE FROM test3 WHERE id > 1;
  10. DELETE 2
复制代码
下载、安装并编译 pg_dirtyread 扩展：
从 https://github.com/df7cb/pg_dirtyread 下载 pg_dirtyread，上传安装包并解压，编译安装：
  1. tar xzf pg_dirtyread-2.7.tar.gz
  2. cd /home/postgres/pg15/soft/contrib/pg_dirtyread-2.7
  3. make && make install
复制代码
创建 pg_dirtyread 扩展并检察数据：
  1. postgres=# CREATE EXTENSION pg_dirtyread;
  2. CREATE EXTENSION
  3. postgres=# SELECT * FROM pg_dirtyread('test3') AS (id INT, name VARCHAR(10));
  4. id | name
  5. ----+------
  6. 1 | asd
  7. 2 | qwe
  8. 3 | zxv
  9. (3 rows)
复制代码
（四）利用 pg_filedump 工具


  • 原理及操作步骤：

    • pg_filedump 是一个下令行工具，可在服务端实行，无需毗连数据库，它能分析数据文件中数据的具体信息，内容格式与 pageinspect 类似。可直接读取文件，适用于严重灾难环境，但需知道具体文件位置，适用性有限，且无法找回自定义数据类型的数据，不适用于云数据库的数据找回。操作步骤如下：
    • 创建测试环境：

  1. postgres=# CREATE TABLE test4 (id INT, name VARCHAR(10));
  2. CREATE TABLE
  3. postgres=# INSERT INTO test4 VALUES (1, 'asd');
  4. INSERT 0 1
  5. postgres=# INSERT INTO test4 VALUES (2, 'qwe');
  6. INSERT 0 1
  7. postgres=# INSERT INTO test4 VALUES (3, 'zxv');
  8. INSERT 0 1
  9. postgres=# DELETE FROM test4 WHERE id > 1;
  10. DELETE 2
复制代码
  1. - **下载、安装并编译 pg_filedump 工具**：
复制代码
从 https://github.com/df7cb/pg_filedump 下载 pg_filedump，上传安装包并解压，编译安装：
  1. tar xzf pg_filedump-REL_17_1.tar.gz
  2. cd /home/postgres/pg15/soft/contrib/pg_filedump-REL_17_1
  3. make && make install
复制代码
  1. - **查看表文件存放路径及解析文件内容**：
复制代码
  1. postgres=# SELECT pg_relation_filepath('test4');
  2. pg_relation_filepath
  3. ----------------------
  4. base/5/24635
  5. (1 row)
  6. pg_filedump -D int,varchar /home/postgres/pg15/data/base/5/24635 | grep COPY
  7. COPY: 1 asd
  8. COPY: 2 qwe
  9. COPY: 3 zxv
复制代码
（五）利用 pageinspect 扩展


  • 原理及操作步骤：

    • pageinspect 是 PostgreSQL 自带的扩展，可检察数据库页级别的信息，虽不直接用于数据恢复，但能辅助数据恢复过程中的检察和分析工作。以下是操作步骤：
    • 创建测试环境：

  1. postgres=# CREATE TABLE test3 (id INT, name VARCHAR(10));
  2. CREATE TABLE
  3. postgres=# INSERT INTO test3 VALUES (1, 'asd');
  4. INSERT 0 1
  5. postgres=# INSERT INTO test3 VALUES (2, 'qwe');
  6. INSERT 0 1
  7. postgres=# INSERT  INTO test3 VALUES (3, 'zxv');
  8. INSERT 0 1
  9. postgres=# DELETE FROM test3 WHERE id > 1;
  10. DELETE 2
复制代码
编译安装 pageinspect 插件：
  1. cd /home/postgres/pg15/soft/contrib/pageinspect
  2. make && make install
复制代码


  • 创建 pageinspect 扩展，辨认被删除的数据：
  1. postgres=# create extension pageinspect;
  2. CREATE EXTENSION
  3. postgres=# SELECT * FROM heap_page_items(get_raw_page('test3', 0))
  4. postgres-# WHERE t_xmax <> 0;
  5. 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
  6. ----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------------------
  7. 2 |   8128 |        1 |     32 |    747 |    749 |        0 | (0,2)  |       8194 |      1282 |     24 |        |       | \x0200000009717765
  8. 3 |   8096 |        1 |     32 |    748 |    749 |        0 | (0,3)  |       8194 |      1282 |     24 |        |       | \x03000000097a7876
  9. (2 rows)
复制代码
提取字段数据：
  1. postgres=# SELECT tuple_data_split((SELECT oid FROM pg_class WHERE relname = 'test3')::oid, t_data, t_infomask, t_infomask2, t_bits)
  2. postgres-# FROM heap_page_items(get_raw_page('test3', 0))
  3. postgres-# WHERE t_xmax <> 0;
  4.        tuple_data_split
  5. -------------------------------
  6. {"\\x02000000","\\x09717765"}
  7. {"\\x03000000","\\x097a7876"}
  8. (2 rows)
复制代码
紧张留意事项：
以上提及的 pg_resetwal、pg_recovery、pg_dirtyread、pg_filedump 和 pageinspect 这些数据恢复方法，都仅适用于表还没做 vacuum 或者 vacuum full 操作的环境。一旦表已经清算了死元组，这些数据恢复方法可能无法成功恢复数据。因此，在操作数据库时，必要密切关注 vacuum 操作的时间和频率，避免因误操作和 vacuum 操作导致数据无法恢复。同时，对于不同的数据恢复工具和方法，在利用前要充分了解其原理和适用场景，尤其是在生产环境中，务必审慎操作，以免造成更大的数据丧失。
在一样寻常数据库操作中，要时候保持鉴戒，制定完善的数据备份计谋，并定期进行备份，以应对可能出现的误操作和数据丢失风险。在必要恢复数据时，根据不同的误操作场景，仔细评估利用合适的数据恢复工具和方法，尽可能减少数据丢失带来的影响。
接待关注公众号《小周的数据库进阶之路》，更多精彩知识和干货尽在此中。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

灌篮少年

论坛元老
这个人很懒什么都没写!

楼主热帖

标签云

AI 运维 CIO 存储 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表