PostgreSQL flashback(闪回)

PostgreSQL flashback(闪回) 功能实现

1. 准备工作
   修改postgresql.conf ($PGDATA/postgresql.conf)
   vacuum_defer_cleanup_age = 1000000 # 延迟100万个事务再回收垃圾，误操作后在100万个事务内
   vacuum_freeze_min_age = 50000000—依据事务号大小定值
   track_commit_timestamp = on # 开启事务结束时间跟踪

2. 配置脏读插件
   2.1 准备一个 pg_dirtyread-master.zip
   2.2 解压,在解压文件夹里执行make make install 或者配置环境变量进行make 和make install作业env PG_CONFIG=/path/to/pg_config make && make install
   2.3 CREATE EXTENSION pg_dirtyread;
   这里可能会报错找不到pg_dirtyread文件路径，这是因为解压档下的重要文件没有出现在路径中,解决方法是将pg_dirtyread-master下的
   -rw-r–r-- 1 root root 130 Oct 12 18:55 pg_dirtyread–1.0–2.sql
   -rw-r–r-- 1 root root 88 Oct 12 18:55 pg_dirtyread–1.0.sql
   -rw-r–r-- 1 root root 96 Oct 12 18:55 pg_dirtyread–2.sql
   -rw-r–r-- 1 root root 5020 Oct 12 18:55 pg_dirtyread.c
   -rw-r–r-- 1 root root 150 Oct 12 18:55 pg_dirtyread.control
   -rw-r–r-- 1 root root 86984 Oct 29 15:34 pg_dirtyread.o
   放入目录PGHOME/ share/postgresql/extension/下 -rwxr-xr-x 1 root root 68413 Oct 29 15:34 pg_dirtyread.so 放入目录PGHOME/lib/postgresql
   再执行CREATE EXTENSION pg_dirtyread;就会显示成功了
   此时你可以测试下是否可用
   postgres=# CREATE TABLE test02 (bar bigint, baz text);
   postgres=# ALTER TABLE test02 SET (
   autovacuum_enabled = false, toast.autovacuum_enabled = false
   );

postgres=# INSERT INTO test02 VALUES (1, ‘Test’), (2, ‘New Test’);

postgres=# select * from test02;
bar | baz
-----±---------
1 | Test
2 | New Test
(2 rows)

postgres=# DELETE FROM test02 WHERE bar = 1 or bar=2 ;
DELETE 2
postgres=# select * from test02;
bar | baz
-----±----
(0 rows)

postgres=# SELECT * FROM pg_dirtyread(‘test02’) as t(bar bigint, baz text);
bar | baz
-----±---------
1 | Test
2 | New Test
(2 rows)

======发现可以找到被误删除的数据