PG数据库误删除数据怎么办，还需要跑路吗？

老苏畅谈运维

已于 2024-11-08 15:51:16 修改

阅读量1.2k

点赞数 24

分类专栏： PG 数据恢复文章标签：数据库 postgresql

于 2024-10-09 20:45:21 首次发布

文章来源于老苏畅谈运维，欢迎您转载，转载声明来源老苏畅谈运维

本文链接：https://blog.csdn.net/zhirongsu/article/details/142796129

版权

数据恢复同时被 2 个专栏收录

16 篇文章 1 订阅

订阅专栏

6 篇文章 0 订阅

订阅专栏

对于DBA来说，碰到开发人员误删除数据，或者更新忘记加条件，误更新了数据，如何恢复这些误操作的数据，是DBA必备技能之一了。如果数据有定期备份，你还不慌，大不了从备份去恢复数据。若是没有备份，则该怎么办？还有方法能恢复数据吗，你需要跑路吗？当然是有方法了！

对于oracle数据库可以通过logminer对归档日志的解析，从而分析执行过sql语句及反向生成的回滚sql，从而实现update/delete 误操作数据的找回；

对于MySQL数据库，也可以解析binlog找回数据，如binlog2sql是一个开源的MySQL Binlog解析工具，能够将Binlog解析为原始的SQL，也支持将Binlog解析为回滚的SQL，以便做数据恢复；

当然，对于PostgreSQL数据库，也可以通过解析WAL日志，恢复误操作的数据。那有什么好的工具来恢复？walminer应该算是比较便捷，较高效的恢复方式了。

Walminer简介

WalMiner是从PostgreSQL的WAL(write ahead logs)日志的解析工具，旨在挖掘wal日志所有的有用信息，从而提供PG的数据恢复支持。WalMiner可以从WAL日志中解析出SQL（用户执行的DML语句和DDL语句），并能生成对应的undo SQL语句。

开源的3.0，以插件的方式安装在数据库，支持PostgreSQL10及其以上版本，需要进行编译安装。

4.0在3.0的基础上做了大量增强，并且极大简化了使用步骤，4.0版本摒弃插件模式改为bin模式，脱离对目标数据库的编译依赖和安装依赖。4.0 以后需要 license 了，也不贵，开源作者不易，可以支持一下！

国庆期间，WalMiner作者搞了一个抽奖活动，有幸抽到了一个个人永久使用的license。哈哈，运气实在是可以！！

Walminer使用

环境准备

从官网网址:https://gitee.com/movead/XLogMiner/ 下载软件，目前是更新到了4.8版本。

下载软件后，解压到指定目录

[root@pgdkcs media]# tar -xf walminer_x86_64_v4.8.0.tar.gz -C /usr/local/
[root@pgdkcs local]# cd /usr/local/
[root@pgdkcs local]# ls -ld wal*
drwxrwxr-x 6 szrsu szrsu 65 Oct  1 09:41 walminer_x86_64_v4.8.0
[root@pgdkcs local]# chown -R postgres.postgres /usr/local/walminer_x86_64_v4.8.0/

设置环境变量

[root@pgdkcs local]# vi /etc/profile
export PATH=$PATH:/usr/local/walminer_x86_64_v4.8.0/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/walminer_x86_64_v4.8.0/lib

[root@pgdkcs local]# source /etc/profile

建立walminer运行目录

--手动创建walminer运行目录$HOME/.walminer
$ mkdir -p $HOME/.walminer
$ cd $HOME/.walminer

将walminer.license文件拷贝到$HOME/.walminer

[postgres@pgdkcs media]$ cp walminer.license_suzhirong $HOME/.walminer/walminer.license

查看使用帮助

[root@pgdkcs local]# su - postgres
Last login: Tue Oct  8 16:03:00 CST 2024 on pts/1
[postgres@pgdkcs ~]$ walminer help
walminer [command] [options]
COMMANDS
---------
#wal2sql
  options
    -D dic file for miner
    -C enable DDL miner
    -a out detail info for catalog change
    -w wal file path to miner
    -t dest of miner result(1 stdout, 2 file, 3 db)(stdout default)
    -k boundary kind(1 all, 2 lsn, 3 time, 4 xid)(all default)
    -m miner mode(0 nomal miner, 1 accurate miner)(nomal default) if k=2
    -r the relname for single table miner 
    -b target database name which contain rel pointed by -r
    -s start location if k=2 or k=3, or xid if k = 4 
          if k=2 default the min lsn of input wals   
          if k=3 or k=4 you need input this
    -e end wal location if k=2 or k=3
          if k=2 default the max lsn of input wals   
          if k=3 you need input this
    -f file to store miner result if t = 2
    -d target database name if t=3(default postgres)
    -h target database host if t=3(default localhost)
    -p target database port if t=3(default 5432)
    -u target database user if t=3(default postgres)
    -W target user password if t=3
---------

帮助命令比较长，就不一一列举了。walminer支持多种功能如wal2sql、fosync、pgto、waldump，最为核心的功能是wal2sql，主要是以各种方式解析wal日志，并得出产生wal的DML语句、其undo语句、事务信息、lsn信息。

创建测试表

[postgres@pgdkcs walminer_x86_64_v4.8.0]$ psql
psql (14.4)
Type "help" for help.

postgres@[local]:5432=#select now();
             now              
------------------------------
 2024-10-08 16:16:02.67568+08
(1 row)

postgres@[local]:5432=#select pg_current_wal_lsn();
 pg_current_wal_lsn 
--------------------
 0/3A0001C0
(1 row)


postgres@[local]:5432=#create table t_walminer(id int,name varchar(10));
CREATE TABLE
postgres@[local]:5432=#insert into t_walminer select n,'A'||n from generate_series(1,10) as n;
INSERT 0 10
postgres@[local]:5432=#select * from t_walminer;
 id | name 
----+------
  1 | A1
  2 | A2
  3 | A3
  4 | A4
  5 | A5
  6 | A6
  7 | A7
  8 | A8
  9 | A9
 10 | A10
(10 rows)

postgres@[local]:5432=#update t_walminer set name= 'szr' where id < 5;
UPDATE 4

postgres@[local]:5432=#delete from t_walminer where id > 8;
DELETE 2
postgres@[local]:5432=#select * from t_walminer;
 id | name 
----+------
  5 | A5
  6 | A6
  7 | A7
  8 | A8
  1 | szr
  2 | szr
  3 | szr
  4 | szr
(8 rows)

postgres@[local]:5432=#select pg_current_wal_lsn();
 pg_current_wal_lsn 
--------------------
 0/3A0177D8
(1 row)

生成字典

命令参数:

walminer builtdic [options]

-d 目标数据库名(default postgres)

-h 目标数据库的地址(default localhost)

-p 目标数据库的端口(default 5432)

-u 目标数据库的用户名(default postgres)

-W 目标数据库用户的密码

-D 数据字典的生成文件

-f 如果-D指定的文件已经存在那么会重写

注意：如果-h指定的不是localhost，那么需要为-u指定的用户配置流复制权限。

[postgres@pgdkcs .walminer]$ walminer builtdic -D ~/walminer.dic -f -h localhost -p 5432 -u postgres
#################################################
Walminer for PostgreSQL wal
Contact Author by mail 'lchch1990@sina.cn'
Personal License for suzhirong(294770068@qq.com)
#################################################
DIC INFO#
sysid:7129743737488231202 timeline:1 dbversion:140004 walminer:4.8

解析wal操作

命令参数:

-D 指定解析使用的数据字典

-C 开启DDL解析

-w 指定解析的wal日志所在的目录

-t 指定解析结果的输出方式

    1 输出到标准输出(默认)
    2 输出到-f参数指定的文件
    3 保存到一个PG数据库(保存到临时表walminer_contents中)

-k 指定解析类型

    1 解析-w目录中的全部wal日志，无视-s和-e参数(默认)
    2 -s和-e参数指定的为解析的开始和结束lsn
    3 -s和-e参数指定的为解析的开始和结束时间
    4 -s指定的参数为需要解析的事务ID列表

-m 指定解析类型

    0 普通解析(默认)
    1 精确解析

    k为1时，只能指定为普通解析
    精确解析，保证在k不为1时，精确解析出-s和-e指定的范围内的所有数据。

-r 指定当前解析为单表解析，并指定表名

-b 单表解析时，指定表所在的数据库

-s 当k=2时为开始lsn; 当k=3时为开始时间;当k=4时为xid列表

-e 当k=2时为结束lsn; 当k=3时为结束时间;

-f 当t为2时，指定文件名

-d 当t=3时指定目标数据库的数据库名(默认postgres)

-h 当t=3时指定目标数据库的地址(默认localhost)

-p 当t=3时指定目标数据库的端口(默认5432)

-u 当t=3时指定目标数据库的连接用户名(默认postgres)

-W 当t=3时指定目标数据库的连接用户的密码

默认是输出至标准输出

解析指定lsn：

[postgres@pgdkcs ~]$ walminer wal2sql -D /home/postgres/walminer.dic -w /usr/local/pg144/data/pg_wal -d postgres -k 2 -s  0/3A0001C0 -e 0/3A0177D8 
#################################################
Walminer for PostgreSQL wal
Contact Author by mail 'lchch1990@sina.cn'
Personal License for suzhirong(294770068@qq.com)
#################################################
Switch wal to 00000001000000000000003A on time 2024-10-08 17:16:48.927297+08
[XID]=864, [TOPXID]=0
[SQLNO]=1
[SQL]=INSERT INTO public.t_walminer(id ,name) VALUES(1 ,'A1')
[UNDO]=DELETE FROM public.t_walminer WHERE id=1 AND name='A1'
[database]=postgres
[COMPLETE]=true
[LSN]=0/3a0172a8
[COMMITLSN]=0/3a017528
[COMMITTIME]=2024-10-08 16:20:49.073911+08
------------------------------------------------------
[XID]=864, [TOPXID]=0
[SQLNO]=2
[SQL]=INSERT INTO public.t_walminer(id ,name) VALUES(2 ,'A2')
[UNDO]=DELETE FROM public.t_walminer WHERE id=2 AND name='A2'
[database]=postgres
[COMPLETE]=true
[LSN]=0/3a0172e8
[COMMITLSN]=0/3a017528
[COMMITTIME]=2024-10-08 16:20:49.073911+08
------------------------------------------------------
[XID]=864, [TOPXID]=0
[SQLNO]=3
[SQL]=INSERT INTO public.t_walminer(id ,name) VALUES(3 ,'A3')
[UNDO]=DELETE FROM public.t_walminer WHERE id=3 AND name='A3'
[database]=postgres
[COMPLETE]=true
[LSN]=0/3a017328
[COMMITLSN]=0/3a017528
[COMMITTIME]=2024-10-08 16:20:49.073911+08
------------------------------------------------------
[XID]=864, [TOPXID]=0
[SQLNO]=4
[SQL]=INSERT INTO public.t_walminer(id ,name) VALUES(4 ,'A4')
[UNDO]=DELETE FROM public.t_walminer WHERE id=4 AND name='A4'
[database]=postgres
[COMPLETE]=true
[LSN]=0/3a017368
[COMMITLSN]=0/3a017528
[COMMITTIME]=2024-10-08 16:20:49.073911+08
------------------------------------------------------
[XID]=864, [TOPXID]=0
[SQLNO]=5
[SQL]=INSERT INTO public.t_walminer(id ,name) VALUES(5 ,'A5')
[UNDO]=DELETE FROM public.t_walminer WHERE id=5 AND name='A5'
[database]=postgres
[COMPLETE]=true
[LSN]=0/3a0173a8
[COMMITLSN]=0/3a017528
[COMMITTIME]=2024-10-08 16:20:49.073911+08
------------------------------------------------------
[XID]=864, [TOPXID]=0
[SQLNO]=6
[SQL]=INSERT INTO public.t_walminer(id ,name) VALUES(6 ,'A6')
[UNDO]=DELETE FROM public.t_walminer WHERE id=6 AND name='A6'
[database]=postgres
[COMPLETE]=true
[LSN]=0/3a0173e8
[COMMITLSN]=0/3a017528
[COMMITTIME]=2024-10-08 16:20:49.073911+08
------------------------------------------------------
[XID]=864, [TOPXID]=0
[SQLNO]=7
[SQL]=INSERT INTO public.t_walminer(id ,name) VALUES(7 ,'A7')
[UNDO]=DELETE FROM public.t_walminer WHERE id=7 AND name='A7'
[database]=postgres
[COMPLETE]=true
[LSN]=0/3a017428
[COMMITLSN]=0/3a017528
[COMMITTIME]=2024-10-08 16:20:49.073911+08
------------------------------------------------------
[XID]=864, [TOPXID]=0
[SQLNO]=8
[SQL]=INSERT INTO public.t_walminer(id ,name) VALUES(8 ,'A8')
[UNDO]=DELETE FROM public.t_walminer WHERE id=8 AND name='A8'
[database]=postgres
[COMPLETE]=true
[LSN]=0/3a017468
[COMMITLSN]=0/3a017528
[COMMITTIME]=2024-10-08 16:20:49.073911+08
------------------------------------------------------
[XID]=864, [TOPXID]=0
[SQLNO]=9
[SQL]=INSERT INTO public.t_walminer(id ,name) VALUES(9 ,'A9')
[UNDO]=DELETE FROM public.t_walminer WHERE id=9 AND name='A9'
[database]=postgres
[COMPLETE]=true
[LSN]=0/3a0174a8
[COMMITLSN]=0/3a017528
[COMMITTIME]=2024-10-08 16:20:49.073911+08
------------------------------------------------------
[XID]=864, [TOPXID]=0
[SQLNO]=10
[SQL]=INSERT INTO public.t_walminer(id ,name) VALUES(10 ,'A10')
[UNDO]=DELETE FROM public.t_walminer WHERE id=10 AND name='A10'
[database]=postgres
[COMPLETE]=true
[LSN]=0/3a0174e8
[COMMITLSN]=0/3a017528
[COMMITTIME]=2024-10-08 16:20:49.073911+08
------------------------------------------------------
[XID]=865, [TOPXID]=0
[SQLNO]=1
[SQL]=UPDATE public.t_walminer SET name='szr' WHERE id=1 AND name='A1'
[UNDO]=UPDATE public.t_walminer SET name='A1' WHERE id=1 AND name='szr'
[database]=postgres
[COMPLETE]=true
[LSN]=0/3a017588
[COMMITLSN]=0/3a0176a8
[COMMITTIME]=2024-10-08 16:21:44.497847+08
------------------------------------------------------
[XID]=865, [TOPXID]=0
[SQLNO]=2
[SQL]=UPDATE public.t_walminer SET name='szr' WHERE id=2 AND name='A2'
[UNDO]=UPDATE public.t_walminer SET name='A2' WHERE id=2 AND name='szr'
[database]=postgres
[COMPLETE]=true
[LSN]=0/3a0175d0
[COMMITLSN]=0/3a0176a8
[COMMITTIME]=2024-10-08 16:21:44.497847+08
------------------------------------------------------
[XID]=865, [TOPXID]=0
[SQLNO]=3
[SQL]=UPDATE public.t_walminer SET name='szr' WHERE id=3 AND name='A3'
[UNDO]=UPDATE public.t_walminer SET name='A3' WHERE id=3 AND name='szr'
[database]=postgres
[COMPLETE]=true
[LSN]=0/3a017618
[COMMITLSN]=0/3a0176a8
[COMMITTIME]=2024-10-08 16:21:44.497847+08
------------------------------------------------------
[XID]=865, [TOPXID]=0
[SQLNO]=4
[SQL]=UPDATE public.t_walminer SET name='szr' WHERE id=4 AND name='A4'
[UNDO]=UPDATE public.t_walminer SET name='A4' WHERE id=4 AND name='szr'
[database]=postgres
[COMPLETE]=true
[LSN]=0/3a017660
[COMMITLSN]=0/3a0176a8
[COMMITTIME]=2024-10-08 16:21:44.497847+08
------------------------------------------------------
[XID]=866, [TOPXID]=0
[SQLNO]=1
[SQL]=DELETE FROM public.t_walminer WHERE id=9 AND name='A9'
[UNDO]=INSERT INTO public.t_walminer(id ,name) VALUES(9 ,'A9')
[database]=postgres
[COMPLETE]=true
[LSN]=0/3a017708
[COMMITLSN]=0/3a017778
[COMMITTIME]=2024-10-08 16:22:15.796674+08
------------------------------------------------------
[XID]=866, [TOPXID]=0
[SQLNO]=2
[SQL]=DELETE FROM public.t_walminer WHERE id=10 AND name='A10'
[UNDO]=INSERT INTO public.t_walminer(id ,name) VALUES(10 ,'A10')
[database]=postgres
[COMPLETE]=true
[LSN]=0/3a017740
[COMMITLSN]=0/3a017778
[COMMITTIME]=2024-10-08 16:22:15.796674+08
------------------------------------------------------

可以看到解析出了10条插入数据，4条更新的数据和2条删除的数据，与上面的测试操作步骤吻合。

解析的方式还有其他的方式，如

–指定时间进行解析

walminer wal2sql -D /home/postgres/walminer.dic -w /usr/local/pg144/data/pg_wal -d postgres -k 3 -s '2024-10-08 16:16:00' -e '2024-10-08 16:23'

–指定事务ID解析

walminer wal2sql -D /home/postgres/walminer.dic -w /usr/local/pg144/data/pg_wal -d postgres -k 4 -s 864,865,866

当然，还有其他的功能，有兴趣的，可以去官网仔细看看。

结语

通过上述测试，walminer是非常好的解析pg的wal日志工具，其功能现在已经足够处理常规的误操作，达到闪回的效果，此工具还在不断开发完善中，让我们一起期待一个更强大更完善的救火工具。