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) 

    被删除的数据回来了