PostgreSQL 误删数据后的恢复方法

最新推荐文章于 2025-03-08 22:37:56 发布
最新推荐文章于 2025-03-08 22:37:56 发布
阅读量1.9k 收藏 20
点赞数 28
分类专栏： postgresql 文章标签： 服务器 postgresql linux 运维 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/qq_36936192/article/details/145190133
版权

在 PostgreSQL 数据库的使用过程中，误操作可能会导致数据丢失，这些误操作主要包括 DDL（数据定义语言）和 DML（数据操作语言）操作。以下是不同误操作情况下的数据恢复方法及相应工具的详细介绍。

一、DDL 操作导致的数据丢失及恢复困难

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

二、DML 操作导致的数据丢失及恢复方法

（一）使用 pg_resetwal 工具

  1. 原理及操作步骤：
    • 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 [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.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)
  1. 注意事项：
    pg_resetwal 会对整个实例进行回滚到指定的事务 ID，且 WAL 日志整体会被清理，因此在生产环境中务必慎用。建议先将数据拷贝到测试环境进行恢复操作，然后再考虑将恢复后的数据还原到生产环境中，以避免对生产环境造成不可逆的损害。

（二）使用 pg_recovery 工具

  1. 原理及操作步骤：
    • 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 扩展

  1. 原理及操作步骤：
    • 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 工具

  1. 原理及操作步骤：
    • 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 扩展

  1. 原理及操作步骤：
    • 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=# INSERT  INTO 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 操作导致数据无法恢复。同时，对于不同的数据恢复工具和方法，在使用前要充分了解其原理和适用场景，尤其是在生产环境中，务必谨慎操作，以免造成更大的数据损失。

在日常数据库操作中，要时刻保持警惕，制定完善的数据备份策略，并定期进行备份，以应对可能出现的误操作和数据丢失风险。在需要恢复数据时，根据不同的误操作场景，仔细评估使用合适的数据恢复工具和方法，尽可能减少数据丢失带来的影响。

欢迎关注公众号《小周的数据库进阶之路》，更多精彩知识和干货尽在其中。

PostgreSQL恢复误删数据
weixin_33716154的博客
04-28 5704
　　在Oracle中；删除表或者误删表记录；有个闪回特性，不需要停机操作，可以完美找回记录。当然也有一些其他的恢复工具；例如odu工具，gdul工具。都可以找回数据。而PostgreSQL目前没有闪回特性。如何在不停机情况下恢复误删数据。还好是有完整的热备份。 　　本文描述的方法是：利用热备份在另一台服务器进行数据恢复；再导入正式环境；这样不影响数据库操作。这方法也适用在Oracle恢复。必须满...
PostgreSQL之数据库恢复
数据源的港湾
04-03 2682
前面我们了解到PostgreSQL的故障恢复与checkpoint、WAL密切有关。当数据库异常宕机，通过顺序的重放WAL段文件中从重做点（REDO point）开始的XLOG记录来进行数据库的恢复操作。
误删除数据，怎么恢复或回滚RDS数据库？
01-06
数据恢复的方式跟备份的方式有关。比如物理备份还是逻辑备份，是否开启了日志备份。所谓日志备份其实就是日志，如果开启了日志备份，那可以恢复到备份范围内的的任意时间点。 下面按数据库类型分别列举数据恢复的方法。 mysql 恢复某个备份或者恢复到某时间点 恢复单个库或表，或者恢复多个库或表 恢复到其它地域，这个主要是用于快速在另一个地域建立数据库 sql server 恢复mssql备份或者恢复到时间点 恢复到一个临时数据库，可作为临时验证使用 登录临时数据库 pg 恢复备份或者恢复到时间点 把本地备份恢复到云上 ppas或oracle 恢复备份或者恢复到时间点 mariadb 恢复mariadb备
pg误删除数据恢复（PostgreSQL delete数据恢复）---惜分飞
最新发布
03-08 109
应用维护人员通过应用程序误删除了一些数据,希望对其进行恢复.通过咨询确认是PostgreSQL数据,wal和arch文件都在,取客户删除数据时间点相关wal文件。如果此类的数据库(oracle,mysql,sql server,PostgreSQL)等恢复请求,需要专业恢复技术支持。通过工具解析wal日志,比较顺利的获取到了undo sql语句。然后把这些sql语句反向插入到生产库,完成这些误操作数据的恢复。然后让客户提供具体涉及误删除的相关的库和表信息。
【PGCCC】从 PostgreSQL 表恢复已删除的数据 | 翻译
PGCCC的博客
09-30 1409
PostgreSQL 非常擅长保护您的数据安全，因此它不会自行消失。不幸的是，反之亦然——如果数据已被删除，它将无法恢复。在本文中，我们将探讨从 PostgreSQL 表中恢复已删除数据的选项。
PostgreSQL误删数据库怎么办
weixin_44594317的博客
11-26 1129
在 PostgreSQL 中，如果误删了数据库，能否还原取决于是否有提前做备份以及数据库配置的日志策略。
PostgreSQL12通过pg_resetwal进行误删恢复
weixin_42583514的博客
02-10 1万+
文章目录1、pg_resetwal1.1、pg_resetwal 语法解析1.2、pg_resetwal 恢复数据1.2.1、创建需要的表和数据1.2.2、delete误删和恢复1.2.2.1、恢复数据1.2.2.1.1、先确认当前的日志的以及LSN号1.2.2.1.2、查找误操作的事务号1.2.2.1.3、确认事务号之后，进行恢复1.2.3、truncate 恢复 1、pg_resetwal pg_resetwal 主要的工作的目的是为了在系统数据损坏的时候，通过pg_resetwal来重新标志日志的位置
PostgreSQL数据delete删除恢复
weixin_42329915的博客
12-06 568
本文档适用数据表数据被delete类型的删除语句情况下恢复，需要满足数据库或数据表未被vacuum或者vacuum full。
PostgreSQL 怎么通过命令来恢复删除的数据
热门推荐
sql server的专栏
09-24 1万+
最近一段工作较忙，更新的速度可能会减慢，敬请见谅，后期采用隔天更新的方式误删除数据的情况，一般都是通过备份，或日志来进行恢复，当然ORACLE FLASHBACK的模式实际上也是对一定期限...
PostgreSQL误删数据的救命稻草
Wmh的博客
11-08 3964
PostgreSQL误删数据恢复
postgresql数据库备份和恢复
11-07
postgresql数据库备份和恢复文档资料，详细介绍了PostgreSQL数据库的备份与恢复 方法，值得学习下
PostgreSQL12中pg-resetwal命令用于误删数据恢复的技术指南
12-04
内容概要：本文详细阐述了在PostgreSQL12环境中，利用pg_resetwal工具进行数据误删后的恢复过程。包括对pg_resetwal的基本使用方法、相关参数说明及其注意事项，重点解释了如何确定删除操作的LSN编号和对应的事务ID...
PostgreSQL误删恢复数据
09-13
要在PostgreSQL中恢复误删的数据，可以使用完整的热备份进行恢复。以下是步骤： 1. 首先，确认你有可用...请注意，PostgreSQL当前没有内置的闪回特性，因此在误删数据后恢复数据时，最常用的方法是使用备份进行恢复。
PostgreSQL如何恢复误操作的数据，walminer关键时刻救你一命！
老苏畅谈运维
10-29 910
，由于使用了Walminer 4.8版本，这个需要购买license，才能使用。有读者私信，有没有开源的工具可以使用呢？当然了，Walminer有完全开源的3.0版，但它需要以插件的方式安装在数据库，支持PostgreSQL10及其以上版本，功能上也相比Walminer 4.x版本弱。如果你的需求不高，应该也能满足使用，下面就来说说walminer 3.0是怎么使用的。
PostgreSQL误删pg_filenode.map怎么办
Focus on PostgreSQL
01-18 1423
今天在网上看到有人问误删pg_filenode.map该如何恢复或者重建，解决这个问题前我们先来了解下pg_filenode.map文件。 对于PostgreSQL中的每张表在磁盘上都有与之相关的文件，而这些文件的名字便是relfilenode，我们可以通过pg_class的relfilenode字段去查询。 但是有一部分特殊的表我们会发现其对应的该字段为0，官方文档的解释为：0表示这是一个“映射”关系，其磁盘文件名取决于低层状态。那么哪些表的relfilenode字段会是0呢？这些relfilenode为
pgsql delete 语句恢复数据
波吉桑
04-11 3533
确定删除数据的时间点 通过 PostgreSQL 中的 WAL 日志和 archived WAL 日志，找到删除数据时的时间点，也就是最后一个有效的备份后到删除数据前的时间段。从 WAL 日志和 archived WAL 日志中恢复数据 将 WAL 日志和 archived WAL 日志中的数据恢复到新的 PostgreSQL 实例中。需要注意的是，这只是 PITR 恢复已删除数据的一种方法，并且恢复数据是一个非常复杂和危险的操作，应该在测试环境中进行，并且必须非常小心地操作。
9.2 PostgreSQL数据恢复方法与实践
Python老吕的博客
09-23 1192
数据恢复是信息系统管理中的一个重要组成部分，它涉及到在数据丢失、损坏或不可访问时，将数据恢复到正常状态的过程。一系列活动：包括识别、定位、提取、修复和恢复丢失或损坏的数据。一种过程：旨在将数据恢复到其原始、完整和可用的状态。一种策略：涉及数据备份、存储和恢复计划的制定和实施。
postgresql数据库中误删数据，闪回方法
weixin_73350116的博客
08-23 2063
postgresql数据库中误删、误更新数据，进行数据闪回，使用插件pg_dirtyread
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值