PostgreSQL误删操作怎么处理

方式一：

　　PostgreSQL中没有Oracle的闪回机制，只有更加复杂的PITR恢复机制，这要求数据库有全量备份和增量备份，否则无法进行回滚。

方式二：

　　虽然PostgreSQL有延迟复制的技术，在备库设置recovery_min_apply_delay时间，但是数据库control信息和wal日志都已经接收到备库了，备库到了间隔时间就会进行回放。这样的情景一定情况下可以解决一些问题：我在误删除的时候，立即到备库上，将备库中原来数据备份出来解决问题。

方式三：

　　那么还有一种方式，使用pg_resetxlog(10之后是pg_resetwal)来重置事务ID来访问被修改的数据。

　　例如删除数据的xid为100，那么我们回退到99，那么删除到操作还不可见，因此就能看到被删除的数据，但是删除是已经发生的，当我们提升xid到100时，删除就生效，你将无法访问到删除的数据。

　　被重置的xid之后的操作还是存在，无法抹除。当在xid为99时，我们再插入一条数据，那么这个时候访问表，我们将得到原来删除了表，在插入一条记录的情况。删除和插入将在一个xid下。

　　因此，使用重置xid的方式，我们也必须在重置之后，将现在的表备份出来，简单方法是create test_old  as select * from test;的方式来做。因为随着xid的增长，误操作也会被重现。

　　

下面是方式三的演示，通过pg_xlogdump找到误删的事务号（xid），停止数据库，然后重置xlog，启动数据库，数据就是重置的xid位置可见。

apple=# create table test (id int);
CREATE TABLE
apple=# insert into test select generate_series(1, 10);
INSERT 0 10
apple=# select pg_current_xlog_location();
 pg_current_xlog_location
--------------------------
 0/9A0171F0
(1 row)

apple=# delete from test where id > 5;
DELETE 5
apple=# select pg_current_xlog_location();
 pg_current_xlog_location
--------------------------
 0/9A017330
(1 row)

apple=# q
end main
appledeMacBook-Pro-2:pg_xlog apple$ pg_xlogdump -s 0/9A0171F0
rmgr: Heap        len (rec/tot):      8/    54, tx:     718255, lsn: 0/9A0171F0, prev 0/9A0171C8, desc: DELETE off 6 KEYS_UPDATED , blkref #0: rel 1663/16384/53529 blk 0
rmgr: Heap        len (rec/tot):      8/    54, tx:     718255, lsn: 0/9A017228, prev 0/9A0171F0, desc: DELETE off 7 KEYS_UPDATED , blkref #0: rel 1663/16384/53529 blk 0
rmgr: Heap        len (rec/tot):      8/    54, tx:     718255, lsn: 0/9A017260, prev 0/9A017228, desc: DELETE off 8 KEYS_UPDATED , blkref #0: rel 1663/16384/53529 blk 0
rmgr: Heap        len (rec/tot):      8/    54, tx:     718255, lsn: 0/9A017298, prev 0/9A017260, desc: DELETE off 9 KEYS_UPDATED , blkref #0: rel 1663/16384/53529 blk 0
rmgr: Heap        len (rec/tot):      8/    54, tx:     718255, lsn: 0/9A0172D0, prev 0/9A017298, desc: DELETE off 10 KEYS_UPDATED , blkref #0: rel 1663/16384/53529 blk 0
rmgr: Transaction len (rec/tot):      8/    34, tx:     718255, lsn: 0/9A017308, prev 0/9A0172D0, desc: COMMIT 2019-03-28 14:36:02.475298 CST
pg_xlogdump: FATAL:  error in WAL record at 0/9A017308: invalid record length at 0/9A017330

appledeMacBook-Pro-2:pg_xlog apple$ pg_ctl stop
waiting for server to shut down.... done
server stopped
appledeMacBook-Pro-2:pg_xlog apple$ pg_resetxlog -x 718255 -D ../
Transaction log reset
appledeMacBook-Pro-2:pg_xlog apple$ pg_ctl start
server starting
appledeMacBook-Pro-2:pg_xlog apple$ LOG:  00000: redirecting log output to logging collector process
HINT:  Future log output will appear in directory "pg_log".
LOCATION:  SysLogger_Start, syslogger.c:622

appledeMacBook-Pro-2:pg_xlog apple$ psql
argv 0 is: psql
psql will in act_filepsql (9.5.3)
Type "help" for help.

apple=# select * from test;
 id
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)