首页
登录 | 注册

PostgreSQL 在没有备份的情况下_如何恢复误删的数据

PostgreSQL 在没有备份的情况下_如何恢复误删的数据



删除的例子

误删,这里是指DML(delete/update/insert/select)语句,主要是指delete和update。DDL不适用。
例子。


create table test(id int, info text);
insert into test(id, info) values(1, 'abc');
insert into test(id, info) values(2, 'efg');
postgres=# select * from test;
 id | info 
----+------
  1 | abc
  2 | efg
(2 rows) 

误删


postgres=# delete from test where id = 2;
DELETE 1
 id | info 
----+------
  1 | abc
(1 row) 


恢复


恢复的原理简介

由于Postgresql的vacuum机制,删除的数据,并不会立刻删掉。只是做了相关的标志。如果vacuum一旦清理了这些数据,那么是无法恢复。vacuum的机制请参考官方文档或者其他相关的资料。


恢复步骤
  1. 查看当前的事务id。用于后面复原当前数据库的状态


postgres=# select txid_current();
 txid_current 
--------------
          590 
  1. 加载pageinspect。需要使用这个插件来窥察表的情况。linux上面需要先安装pg_contrib,windows已经安装好了。加载

    create extension pageinspect。 
  2. 分析表的情况

    postgres=# select * from heap_page_items(get_raw_page('test', 'main', 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       
    ----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------------------
      1 |   8160 |        1 |     32 |    587 |      0 |        0 | (0,1)  |           2 |       2306 |     24 |        |       | \x0100000009616263
      2 |   8128 |        1 |     32 |    588 |    589 |        0 | (0,2)  |        8194 |       1282 |     24 |        |       | \x0200000009656667
    (2 rows) 

    从这里可以看到,删除的数据的事务号是:589。如果数据很多,可以灵活的看到那些是被删除的数据,可以根据xmax不为0,然后再筛选(再根据xmax倒排等)。

  3. 关闭postgresql服务

    windows 直接关闭服务
    centos7 systemctl stop postgresql-11 
  4. 配置关闭auto_vacuumm, 保护好原来的数据,防止被vacuum, postgresql.conf

    autovacuum = off 
  5. 把数据库的事务id切回到589。即是刚才删除数据的事务id。

    # pg_resetwal -D $PGDATA -x 589
    windows设置pg的安装环境,运行
    pg_resetwwal -D D:/pg_data -x 589
    centos7
    /usr/pgsql-11/bin/pg_resetwal /var/lib/pgsql/11/data/ -x 589 
  6. 启动postgresql服务

  7. 创建表,写入被删除的数据

    create table test_had_delete as select * from test where xmax = 589; 
  8. 关闭服务,并重新设置autovacuum。postgresql.conf

    autovacuum = on 
  9. 重新设置事务id
    /usr/pgsql-11/bin/pg_resetwal /var/lib/pgsql/11/data/ -x 590

  10. 启动postgresql服务

  11. 查询结果

    postgres=# select * from test;
     id | info 
    ----+------
      1 | abc
    (1 row)
    
    postgres=# select * from test_had_delete ;
     id | info 
    ----+------
      2 | efg
    (1 row) 

    被删除的数据回来了


相关文章

  • 在实际工作中,一个MySQL实例中可能有多个database.而我们备份时,通常采用完全备份,将所有database都备份到一个文件中. 但是,偶尔会遇到只恢复一个database或者一个表的情况.怎么解决呢? 一.利用全备恢复一个库(da ...
  • Redis本质上是一个Key-Value类型的内存数据库,很像memcached,整个数据库统统加载在内存当中进行操作,定期通过异步操作把数据库数据flush到硬盘上进行保存.因为是纯内存操作,Redis的性能非常出色,每秒可以处理超过 1 ...
  • 3大模型,搞定Kubernetes网络
    Kubernetes为Pods的网络通信设定了一些条件和要求: 2.节点是运行Kubernetes集群的机器. 这些可以是虚拟机或物理机,也可以是能够运行Kubernetes的任何其他机器. 这些节点还能够与所有Pod通信,而无需NAT. ...
  • 一一讲解网关、IP地址、DNS以及子网掩码等网络配置都各自代表着什么。
    搞过网络的同学一定对默认网关.IP地址.DNS以及子网掩码等配置信息非常熟悉,而初学者或初入行业的小伙伴们接触这些名词可能会是下面这个表情,为了避免尴尬,我们逐条了解一下它们的含义吧! 一.什么是网关 网关(Gateway)又称网间连接器. ...
  • 量子计算与现有的安全体系
    什么是量子计算? 这里的"快得多"是按数量级来说的"快得多".在某些情况下,一个计算任务如果由普通计算机来执行,可能要耗费几年或者几十年才能完成,但如果由量子计算机来执行,就只需要几秒钟.这样的速度甚 ...

2020 unjeep.com webmaster#unjeep.com
12 q. 0.012 s.
京ICP备10005923号