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

6210阅读 0评论2019-04-09 轨迹16
分类:Mysql/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) 

    被删除的数据回来了

上一篇:PostgreSQL基础备份_增量备份与任意点恢复
下一篇:Postgresql使用left join 优化 not in