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)
上一篇:几个PostgreSQL数据库操作总结


下一篇:SQLServer之创建链接服务器