PostgreSQL 闪回 - flash back query emulate by trigger
背景
闪回功能虽然听起来有点鸡肋，但是在关键时刻确可以救命。
比如数据被SQL注入误修改，或者被人为的误操作，如何能快速恢复到正常的状态呢？
技术手段较多
利用UNDO，MVCC的OLD VERSION，触发器，甚至REDO都是可以做到的思路。
今天群里又聊到了PostgreSQL的闪回这个东西, 我之前写过一篇关于利用PostgreSQL mvcc特性来模拟闪回, 找回数据的文章, 有兴趣的同学可以参考如下 :
http://blog.163.com/digoal@126/blog/static/163877040201251911813661/
使用以上方法需要担心的一个问题是数据可能被VACUUM掉了, 所以我们可以定制表的autovacuum threshold, 让重点保护的表的autovacuum 阈值较大, 减少VACUUM的间隔, 或者关闭重点保护的表的autovacuum, 改为人为调度VACUUM.
或者改vacuum_defer_cleanup_age参数, 延迟多少个事务之后再回收.
但是使用以上方法也不适合生产, 因为还要停库, 改控制文件, 都是非常危险的操作. (当然你如果为了找回重要数据, 那么拿备库来做也是值得考虑的.)
如果可以在会话层欺骗数据库当前未分配事务号, 最早已提交事务号的话, 其实就不需要修改这么麻烦了. 当然这个就需要改代码了, 因为这部分数据在共享内存区, 直接改的话危险系数太高, 想办法搞成会话层面的吧还好一点.
本文要介绍另一种闪回方法, 触发器.
触发器闪回的步骤如下
1. 首先要记录所有的DML以及truncate. 也就是对于insert, update, delete, truncate操作, 我们可以回退. 通过触发器来记录old value, new value.
2. 需要闪回的表必须有PK, PK列可以被更新. 如果没有PK的话, 不能唯一的定位到一条记录. 因为PG的行号无法定位到一条记录, 一条记录一旦被更新, 是会生成一个新版本的.
3. INSERT的UNDO, delete where pk=NEW.pk
UPDATE的UNDO, UPDATE set cols=OLD.* where pk=NEW.pk
DELETE和TRUNCATE的UNDO, insert into values (OLD.*)
- 表的SCHEMA可能会变, 表名可能会变, 列的类型可能会变, 可能会新增列, 可能会删除列.
这些都必须考虑, 因为DDL(非TRUNCATE)不被跟踪. 所以我们不直接记录UNDO_SQL, 而是在UNDO时根据当前的数据定义来组装SQL. 并且本方法也不支持DDL的闪回.
需要DDL的闪回, 或者完美的闪回, 请使用PITR.
同时, 为了区分需要闪回的表, 我们不能把跟踪记录放在同一个表里面用schema和tablename来区分, 因为schema和tablename可能被DDL改掉, 那么就会造成取不到记录的情况. 例如TIME1, A表,执行了一些DML后, 改名为B表了, 有执行了一些DML, 然后我们要回退到TIME1的时间点, 根据当前表名B, 从统一的跟踪表undo_table里面取记录的话, 需要告诉跟踪表名字为B, XID为?然后取数据拼装UNDO SQL, 这样的话表名为A的记录时取不出来的, 因为过滤条件是tablename=B. 所以跟踪表要每个表各自一个.
tablea, undo_tablea, tableb, undo_tableb…..这样就不管表或者SCHEMA怎么变了.
注意我们不使用hstore来存储被跟踪表的记录, 原因是回退的时候很麻烦, hstore没有直接转换成record的接口. 我们直接使用表的复合类型来存储被跟踪表的记录.
例子
为了增加复杂度, 我们使用大写表名, 列名.
create table public."TBL" (
c1 int,
c2 int,
"C3" text,
c4 text,
c5 text,
c6 text,
c7 int,
crt_time timestamp,
primary key (c1,"C3",c6,c4)
);
创建记录表, 跟踪表的DML和truncate. 可以增加一列txid_snapshot类型存储txid_current_snapshot(), 这样就能回退到一个一致的点了.
CREATE TABLE public.undo_t (
id serial8 primary key,
xid int8,
relid oid,
table_schema text,
table_name text,
when_tg text,
level text,
op text,
encoding name,
old_rec public."TBL",
new_rec public."TBL",
crt_time timestamp without time zone DEFAULT now(),
username text,
client_addr inet,
client_port int
);
创建触发器函数, 将DML, TRUNCATE的数据插入跟踪表
CREATE OR REPLACE FUNCTION public.undo_t_trace()
RETURNS trigger
LANGUAGE plpgsql
AS $BODY$
DECLARE
v_username text := session_user;
v_client_addr inet := inet_client_addr();
v_client_port int := inet_client_port();
v_xid bigint := txid_current(); -- 记录事务号, 回退时以事务号为界限.
v_encoding name := pg_client_encoding();
BEGIN
case TG_OP
when 'DELETE' then
insert into public.undo_t (xid, relid, table_schema, table_name, when_tg, level, op, encoding, old_rec, username, client_addr, client_port)
values (v_xid, tg_relid, tg_table_schema, tg_table_name, tg_when, tg_level, tg_op, v_encoding, OLD, v_username, v_client_addr, v_client_port);
when 'INSERT' then
insert into public.undo_t (xid, relid, table_schema, table_name, when_tg, level, op, encoding, new_rec, username, client_addr, client_port)
values (v_xid, tg_relid, tg_table_schema, tg_table_name, tg_when, tg_level, tg_op, v_encoding, NEW, v_username, v_client_addr, v_client_port);
when 'UPDATE' then
insert into public.undo_t (xid, relid, table_schema, table_name, when_tg, level, op, encoding, old_rec, new_rec, username, client_addr, client_port)
values (v_xid, tg_relid, tg_table_schema, tg_table_name, tg_when, tg_level, tg_op, v_encoding, OLD, NEW, v_username, v_client_addr, v_client_port);
when 'TRUNCATE' then
insert into public.undo_t (xid, relid, table_schema, table_name, when_tg, level, op, encoding, old_rec, username, client_addr, client_port)
select v_xid, tg_relid, tg_table_schema, tg_table_name, tg_when, tg_level, tg_op, v_encoding, t, v_username, v_client_addr, v_client_port from public."TBL" AS t;
else
return null;
end case;
RETURN null;
END;
$BODY$ strict volatile;
添加触发器, 记录表的dml和truncate.
CREATE TRIGGER tg1 AFTER DELETE or INSERT or UPDATE ON public."TBL" FOR EACH ROW EXECUTE PROCEDURE public.undo_t_trace();
CREATE TRIGGER tg2 BEFORE TRUNCATE ON public."TBL" FOR EACH STATEMENT EXECUTE PROCEDURE public.undo_t_trace();
插入测试数据, 为了增加难度, 我们使用了转义字符. 确保前后数据一致.
insert into "TBL" values (1,1,'te\\s\t','c4','c5','c6',1,now());
insert into "TBL" values (2,1,'te\\s\t','c4','c5','c6',1,now());
insert into "TBL" values (3,1,'te\\s\t','c4','c5','c6',1,now());
insert into "TBL" values (4,1,'te\\s\t','c4','c5','c6',1,now());
insert into "TBL" values (5,1,'te\\s\t','c4','c5','c6',1,now());
insert into "TBL" values (6,1,'te\\s\t','c4','c5','c6',1,now());
插入后, 可以看到 INSERT被跟踪了, 并且我们存储了插入数据时的客户端编码. 方便解决编码问题.
postgres=# select * from undo_t;
id | xid | relid | table_schema | table_name | when_tg | level | op | encoding | old_rec | new_
rec | crt_time | username | client_addr | client_port
----+---------+----------+--------------+------------+---------+-------+--------+----------+---------+------------------------------
------------------------------+----------------------------+----------+-------------+-------------
1 | 1301665 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 | | (1,1,"te\\\\s\\t",c4,c5,c6,1,
"2014-08-28 23:06:09.790227") | 2014-08-28 23:06:09.790227 | postgres | |
2 | 1301666 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 | | (2,1,"te\\\\s\\t",c4,c5,c6,1,
"2014-08-28 23:06:09.79597") | 2014-08-28 23:06:09.79597 | postgres | |
3 | 1301667 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 | | (3,1,"te\\\\s\\t",c4,c5,c6,1,
"2014-08-28 23:06:09.80206") | 2014-08-28 23:06:09.80206 | postgres | |
4 | 1301668 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 | | (4,1,"te\\\\s\\t",c4,c5,c6,1,
"2014-08-28 23:06:09.80903") | 2014-08-28 23:06:09.80903 | postgres | |
5 | 1301669 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 | | (5,1,"te\\\\s\\t",c4,c5,c6,1,
"2014-08-28 23:06:09.819092") | 2014-08-28 23:06:09.819092 | postgres | |
6 | 1301670 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 | | (6,1,"te\\\\s\\t",c4,c5,c6,1,
"2014-08-28 23:06:10.228624") | 2014-08-28 23:06:10.228624 | postgres | |
(6 rows)
postgres=# select * from "TBL";
c1 | c2 | C3 | c4 | c5 | c6 | c7 | crt_time
----+----+---------+----+----+----+----+----------------------------
1 | 1 | te\\s\t | c4 | c5 | c6 | 1 | 2014-08-28 23:06:09.790227
2 | 1 | te\\s\t | c4 | c5 | c6 | 1 | 2014-08-28 23:06:09.79597
3 | 1 | te\\s\t | c4 | c5 | c6 | 1 | 2014-08-28 23:06:09.80206
4 | 1 | te\\s\t | c4 | c5 | c6 | 1 | 2014-08-28 23:06:09.80903
5 | 1 | te\\s\t | c4 | c5 | c6 | 1 | 2014-08-28 23:06:09.819092
6 | 1 | te\\s\t | c4 | c5 | c6 | 1 | 2014-08-28 23:06:10.228624
(6 rows)
回退操作我们这里用一个inline plpgsql 代码来处理, 如果你要写成函数也可以, 只需要传入一个XID即可.
回退最后一个事务, 即c1=6的那条记录. 以事务号1301670为界限.
注意变量使用标量, 因为在for 和 cursor fetch到一个变量时, 变量必须是标量.
参考代码
src/pl/plpgsql/src/pl_gram.y
do language plpgsql $$
declare
v_op text;
v_encoding_curr text := pg_client_encoding();
v_encoding_tmp text;
v_old text; -- 本来这里打算用public."TBL"来作为变量类型, 不过for, cursor都不允许存储非标量类型, 所以还是选择了标量text, 使用时转换.
v_new text;
v_xid int8 := 1301670;
begin
for v_op, v_encoding_tmp, v_old, v_new in
select op,encoding,old_rec::text,new_rec::text from undo_t where xid>=v_xid order by xid desc,id desc
LOOP
execute 'set client_encoding='''||v_encoding_tmp||'''';
case v_op
when 'INSERT' then
delete from public."TBL" t where t=v_new::public."TBL";
when 'DELETE' then
insert into public."TBL" values ((v_old::public."TBL").*);
when 'TRUNCATE' then
insert into public."TBL" values ((v_old::public."TBL").*);
when 'UPDATE' then
delete from public."TBL" t where t=v_new::public."TBL";
insert into public."TBL" values ((v_old::public."TBL").*);
else
end case;
end loop;
execute 'set client_encoding='''||v_encoding_curr||'''';
end;
$$;
回退成功
postgres=# select * from "TBL";
c1 | c2 | C3 | c4 | c5 | c6 | c7 | crt_time
----+----+---------+----+----+----+----+----------------------------
1 | 1 | te\\s\t | c4 | c5 | c6 | 1 | 2014-08-28 23:06:09.790227
2 | 1 | te\\s\t | c4 | c5 | c6 | 1 | 2014-08-28 23:06:09.79597
3 | 1 | te\\s\t | c4 | c5 | c6 | 1 | 2014-08-28 23:06:09.80206
4 | 1 | te\\s\t | c4 | c5 | c6 | 1 | 2014-08-28 23:06:09.80903
5 | 1 | te\\s\t | c4 | c5 | c6 | 1 | 2014-08-28 23:06:09.819092
(5 rows)
回退操作同样会产生undo记录.
postgres=# select * from undo_t;
id | xid | relid | table_schema | table_name | when_tg | level | op | encoding | old_rec
| new_rec | crt_time | username | client_ad
dr | client_port
----+---------+----------+--------------+------------+---------+-------+--------+----------+----------------------------------------
--------------------+------------------------------------------------------------+----------------------------+----------+----------
---+-------------
1 | 1301665 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 |
| (1,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-28 23:06:09.790227") | 2014-08-28 23:06:09.790227 | postgres |
|
2 | 1301666 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 |
| (2,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-28 23:06:09.79597") | 2014-08-28 23:06:09.79597 | postgres |
|
3 | 1301667 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 |
| (3,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-28 23:06:09.80206") | 2014-08-28 23:06:09.80206 | postgres |
|
4 | 1301668 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 |
| (4,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-28 23:06:09.80903") | 2014-08-28 23:06:09.80903 | postgres |
|
5 | 1301669 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 |
| (5,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-28 23:06:09.819092") | 2014-08-28 23:06:09.819092 | postgres |
|
6 | 1301670 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 |
| (6,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-28 23:06:10.228624") | 2014-08-28 23:06:10.228624 | postgres |
|
7 | 1301671 | 50534894 | public | TBL | AFTER | ROW | DELETE | UTF8 | (6,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-2
8 23:06:10.228624") | | 2014-08-28 23:07:07.750644 | postgres |
|
(7 rows)
现在执行一个UPDATE, 把所有的记录更新掉.
postgres=# update "TBL" set c7=100;
UPDATE 5
postgres=# select * from "TBL";
c1 | c2 | C3 | c4 | c5 | c6 | c7 | crt_time
----+----+---------+----+----+----+-----+----------------------------
1 | 1 | te\\s\t | c4 | c5 | c6 | 100 | 2014-08-28 23:06:09.790227
2 | 1 | te\\s\t | c4 | c5 | c6 | 100 | 2014-08-28 23:06:09.79597
3 | 1 | te\\s\t | c4 | c5 | c6 | 100 | 2014-08-28 23:06:09.80206
4 | 1 | te\\s\t | c4 | c5 | c6 | 100 | 2014-08-28 23:06:09.80903
5 | 1 | te\\s\t | c4 | c5 | c6 | 100 | 2014-08-28 23:06:09.819092
(5 rows)
postgres=# select * from undo_t;
id | xid | relid | table_schema | table_name | when_tg | level | op | encoding | old_rec
| new_rec | crt_time | username | client_
addr | client_port
----+---------+----------+--------------+------------+---------+-------+--------+----------+----------------------------------------
--------------------+--------------------------------------------------------------+----------------------------+----------+--------
-----+-------------
1 | 1301665 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 |
| (1,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-28 23:06:09.790227") | 2014-08-28 23:06:09.790227 | postgres |
|
2 | 1301666 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 |
| (2,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-28 23:06:09.79597") | 2014-08-28 23:06:09.79597 | postgres |
|
3 | 1301667 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 |
| (3,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-28 23:06:09.80206") | 2014-08-28 23:06:09.80206 | postgres |
|
4 | 1301668 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 |
| (4,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-28 23:06:09.80903") | 2014-08-28 23:06:09.80903 | postgres |
|
5 | 1301669 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 |
| (5,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-28 23:06:09.819092") | 2014-08-28 23:06:09.819092 | postgres |
|
6 | 1301670 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 |
| (6,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-28 23:06:10.228624") | 2014-08-28 23:06:10.228624 | postgres |
|
7 | 1301671 | 50534894 | public | TBL | AFTER | ROW | DELETE | UTF8 | (6,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-2
8 23:06:10.228624") | | 2014-08-28 23:07:07.750644 | postgres |
|
8 | 1301672 | 50534894 | public | TBL | AFTER | ROW | UPDATE | UTF8 | (1,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-2
8 23:06:09.790227") | (1,1,"te\\\\s\\t",c4,c5,c6,100,"2014-08-28 23:06:09.790227") | 2014-08-28 23:08:52.887568 | postgres |
|
9 | 1301672 | 50534894 | public | TBL | AFTER | ROW | UPDATE | UTF8 | (2,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-2
8 23:06:09.79597") | (2,1,"te\\\\s\\t",c4,c5,c6,100,"2014-08-28 23:06:09.79597") | 2014-08-28 23:08:52.887568 | postgres |
|
10 | 1301672 | 50534894 | public | TBL | AFTER | ROW | UPDATE | UTF8 | (3,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-2
8 23:06:09.80206") | (3,1,"te\\\\s\\t",c4,c5,c6,100,"2014-08-28 23:06:09.80206") | 2014-08-28 23:08:52.887568 | postgres |
|
11 | 1301672 | 50534894 | public | TBL | AFTER | ROW | UPDATE | UTF8 | (4,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-2
8 23:06:09.80903") | (4,1,"te\\\\s\\t",c4,c5,c6,100,"2014-08-28 23:06:09.80903") | 2014-08-28 23:08:52.887568 | postgres |
|
12 | 1301672 | 50534894 | public | TBL | AFTER | ROW | UPDATE | UTF8 | (5,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-2
8 23:06:09.819092") | (5,1,"te\\\\s\\t",c4,c5,c6,100,"2014-08-28 23:06:09.819092") | 2014-08-28 23:08:52.887568 | postgres |
|
(12 rows)
回退到更新前, 即1301672 这个XID需要回退掉.
do language plpgsql $$
declare
v_op text;
v_encoding_curr text := pg_client_encoding();
v_encoding_tmp text;
v_old text;
v_new text;
v_xid int8 := 1301672;
begin
for v_op, v_encoding_tmp, v_old, v_new in
select op,encoding,old_rec::text,new_rec::text from undo_t where xid>=v_xid order by xid desc,id desc
LOOP
execute 'set client_encoding='''||v_encoding_tmp||'''';
case v_op
when 'INSERT' then
delete from public."TBL" t where t=v_new::public."TBL";
when 'DELETE' then
insert into public."TBL" values ((v_old::public."TBL").*);
when 'TRUNCATE' then
insert into public."TBL" values ((v_old::public."TBL").*);
when 'UPDATE' then
delete from public."TBL" t where t=v_new::public."TBL";
insert into public."TBL" values ((v_old::public."TBL").*);
else
end case;
end loop;
execute 'set client_encoding='''||v_encoding_curr||'''';
end;
$$;
postgres=# select * from "TBL";
c1 | c2 | C3 | c4 | c5 | c6 | c7 | crt_time
----+----+---------+----+----+----+----+----------------------------
5 | 1 | te\\s\t | c4 | c5 | c6 | 1 | 2014-08-28 23:06:09.819092
4 | 1 | te\\s\t | c4 | c5 | c6 | 1 | 2014-08-28 23:06:09.80903
3 | 1 | te\\s\t | c4 | c5 | c6 | 1 | 2014-08-28 23:06:09.80206
2 | 1 | te\\s\t | c4 | c5 | c6 | 1 | 2014-08-28 23:06:09.79597
1 | 1 | te\\s\t | c4 | c5 | c6 | 1 | 2014-08-28 23:06:09.790227
(5 rows)
现在把所有记录删除掉
postgres=# delete from "TBL";
DELETE 5
postgres=# select * from undo_t;
id | xid | relid | table_schema | table_name | when_tg | level | op | encoding | old_rec
| new_rec | crt_time | username | clien
t_addr | client_port
----+---------+----------+--------------+------------+---------+-------+--------+----------+----------------------------------------
----------------------+--------------------------------------------------------------+----------------------------+----------+------
-------+-------------
1 | 1301665 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 |
| (1,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-28 23:06:09.790227") | 2014-08-28 23:06:09.790227 | postgres |
|
2 | 1301666 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 |
| (2,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-28 23:06:09.79597") | 2014-08-28 23:06:09.79597 | postgres |
|
3 | 1301667 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 |
| (3,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-28 23:06:09.80206") | 2014-08-28 23:06:09.80206 | postgres |
|
4 | 1301668 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 |
| (4,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-28 23:06:09.80903") | 2014-08-28 23:06:09.80903 | postgres |
|
5 | 1301669 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 |
| (5,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-28 23:06:09.819092") | 2014-08-28 23:06:09.819092 | postgres |
|
6 | 1301670 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 |
| (6,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-28 23:06:10.228624") | 2014-08-28 23:06:10.228624 | postgres |
|
7 | 1301671 | 50534894 | public | TBL | AFTER | ROW | DELETE | UTF8 | (6,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-2
8 23:06:10.228624") | | 2014-08-28 23:07:07.750644 | postgres |
|
8 | 1301672 | 50534894 | public | TBL | AFTER | ROW | UPDATE | UTF8 | (1,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-2
8 23:06:09.790227") | (1,1,"te\\\\s\\t",c4,c5,c6,100,"2014-08-28 23:06:09.790227") | 2014-08-28 23:08:52.887568 | postgres |
|
9 | 1301672 | 50534894 | public | TBL | AFTER | ROW | UPDATE | UTF8 | (2,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-2
8 23:06:09.79597") | (2,1,"te\\\\s\\t",c4,c5,c6,100,"2014-08-28 23:06:09.79597") | 2014-08-28 23:08:52.887568 | postgres |
|
10 | 1301672 | 50534894 | public | TBL | AFTER | ROW | UPDATE | UTF8 | (3,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-2
8 23:06:09.80206") | (3,1,"te\\\\s\\t",c4,c5,c6,100,"2014-08-28 23:06:09.80206") | 2014-08-28 23:08:52.887568 | postgres |
|
11 | 1301672 | 50534894 | public | TBL | AFTER | ROW | UPDATE | UTF8 | (4,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-2
8 23:06:09.80903") | (4,1,"te\\\\s\\t",c4,c5,c6,100,"2014-08-28 23:06:09.80903") | 2014-08-28 23:08:52.887568 | postgres |
|
12 | 1301672 | 50534894 | public | TBL | AFTER | ROW | UPDATE | UTF8 | (5,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-2
8 23:06:09.819092") | (5,1,"te\\\\s\\t",c4,c5,c6,100,"2014-08-28 23:06:09.819092") | 2014-08-28 23:08:52.887568 | postgres |
|
13 | 1301673 | 50534894 | public | TBL | AFTER | ROW | DELETE | UTF8 | (5,1,"te\\\\s\\t",c4,c5,c6,100,"2014-08
-28 23:06:09.819092") | | 2014-08-28 23:09:50.590689 | postgres |
|
14 | 1301673 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 |
| (5,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-28 23:06:09.819092") | 2014-08-28 23:09:50.590689 | postgres |
|
15 | 1301673 | 50534894 | public | TBL | AFTER | ROW | DELETE | UTF8 | (4,1,"te\\\\s\\t",c4,c5,c6,100,"2014-08
-28 23:06:09.80903") | | 2014-08-28 23:09:50.590689 | postgres |
|
16 | 1301673 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 |
| (4,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-28 23:06:09.80903") | 2014-08-28 23:09:50.590689 | postgres |
|
17 | 1301673 | 50534894 | public | TBL | AFTER | ROW | DELETE | UTF8 | (3,1,"te\\\\s\\t",c4,c5,c6,100,"2014-08
-28 23:06:09.80206") | | 2014-08-28 23:09:50.590689 | postgres |
|
18 | 1301673 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 |
| (3,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-28 23:06:09.80206") | 2014-08-28 23:09:50.590689 | postgres |
|
19 | 1301673 | 50534894 | public | TBL | AFTER | ROW | DELETE | UTF8 | (2,1,"te\\\\s\\t",c4,c5,c6,100,"2014-08
-28 23:06:09.79597") | | 2014-08-28 23:09:50.590689 | postgres |
|
20 | 1301673 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 |
| (2,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-28 23:06:09.79597") | 2014-08-28 23:09:50.590689 | postgres |
|
21 | 1301673 | 50534894 | public | TBL | AFTER | ROW | DELETE | UTF8 | (1,1,"te\\\\s\\t",c4,c5,c6,100,"2014-08
-28 23:06:09.790227") | | 2014-08-28 23:09:50.590689 | postgres |
|
22 | 1301673 | 50534894 | public | TBL | AFTER | ROW | INSERT | UTF8 |
| (1,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-28 23:06:09.790227") | 2014-08-28 23:09:50.590689 | postgres |
|
23 | 1301674 | 50534894 | public | TBL | AFTER | ROW | DELETE | UTF8 | (5,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-2
8 23:06:09.819092") | | 2014-08-28 23:10:17.32766 | postgres |
|
24 | 1301674 | 50534894 | public | TBL | AFTER | ROW | DELETE | UTF8 | (4,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-2
8 23:06:09.80903") | | 2014-08-28 23:10:17.32766 | postgres |
|
25 | 1301674 | 50534894 | public | TBL | AFTER | ROW | DELETE | UTF8 | (3,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-2
8 23:06:09.80206") | | 2014-08-28 23:10:17.32766 | postgres |
|
26 | 1301674 | 50534894 | public | TBL | AFTER | ROW | DELETE | UTF8 | (2,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-2
8 23:06:09.79597") | | 2014-08-28 23:10:17.32766 | postgres |
|
27 | 1301674 | 50534894 | public | TBL | AFTER | ROW | DELETE | UTF8 | (1,1,"te\\\\s\\t",c4,c5,c6,1,"2014-08-2
8 23:06:09.790227") | | 2014-08-28 23:10:17.32766 | postgres |
|
(27 rows)
回退到删除前, 即1301674回退掉.
do language plpgsql $$
declare
v_op text;
v_encoding_curr text := pg_client_encoding();
v_encoding_tmp text;
v_old text;
v_new text;
v_xid int8 := 1301674;
begin
for v_op, v_encoding_tmp, v_old, v_new in
select op,encoding,old_rec::text,new_rec::text from undo_t where xid>=v_xid order by xid desc,id desc
LOOP
execute 'set client_encoding='''||v_encoding_tmp||'''';
case v_op
when 'INSERT' then
delete from public."TBL" t where t=v_new::public."TBL";
when 'DELETE' then
insert into public."TBL" values ((v_old::public."TBL").*);
when 'TRUNCATE' then
insert into public."TBL" values ((v_old::public."TBL").*);
when 'UPDATE' then
delete from public."TBL" t where t=v_new::public."TBL";
insert into public."TBL" values ((v_old::public."TBL").*);
else
end case;
end loop;
execute 'set client_encoding='''||v_encoding_curr||'''';
end;
$$;
postgres=# select * from "TBL";
c1 | c2 | C3 | c4 | c5 | c6 | c7 | crt_time
----+----+---------+----+----+----+----+----------------------------
1 | 1 | te\\s\t | c4 | c5 | c6 | 1 | 2014-08-28 23:06:09.790227
2 | 1 | te\\s\t | c4 | c5 | c6 | 1 | 2014-08-28 23:06:09.79597
3 | 1 | te\\s\t | c4 | c5 | c6 | 1 | 2014-08-28 23:06:09.80206
4 | 1 | te\\s\t | c4 | c5 | c6 | 1 | 2014-08-28 23:06:09.80903
5 | 1 | te\\s\t | c4 | c5 | c6 | 1 | 2014-08-28 23:06:09.819092
(5 rows)
现在回退到只有一条记录的时候. 即1301666
postgres=# do language plpgsql $$
declare
v_op text;
v_encoding_curr text := pg_client_encoding();
v_encoding_tmp text;
v_old text;
v_new text;
v_xid int8 := 1301666;
begin
for v_op, v_encoding_tmp, v_old, v_new in
select op,encoding,old_rec::text,new_rec::text from undo_t where xid>=v_xid order by xid desc,id desc
LOOP
execute 'set client_encoding='''||v_encoding_tmp||'''';
case v_op
when 'INSERT' then
delete from public."TBL" t where t=v_new::public."TBL";
when 'DELETE' then
insert into public."TBL" values ((v_old::public."TBL").*);
when 'TRUNCATE' then
insert into public."TBL" values ((v_old::public."TBL").*);
when 'UPDATE' then
delete from public."TBL" t where t=v_new::public."TBL";
insert into public."TBL" values ((v_old::public."TBL").*);
else
end case;
end loop;
execute 'set client_encoding='''||v_encoding_curr||'''';
end;
$$;
DO
postgres=# select * from "TBL";
c1 | c2 | C3 | c4 | c5 | c6 | c7 | crt_time
----+----+---------+----+----+----+----+----------------------------
1 | 1 | te\\s\t | c4 | c5 | c6 | 1 | 2014-08-28 23:06:09.790227
(1 row)
接下来测试一下添加字段后的回退.
postgres=# alter table "TBL" add column c8 text;
ALTER TABLE
postgres=# insert into "TBL" values (2,1,'test','c4','c5','c6',1,now(),'c8');
INSERT 0 1
postgres=# insert into "TBL" values (3,1,'test','c4','c5','c6',1,now(),'c8');
INSERT 0 1
postgres=# select * from "TBL";
c1 | c2 | C3 | c4 | c5 | c6 | c7 | crt_time | c8
----+----+---------+----+----+----+----+----------------------------+----
1 | 1 | te\\s\t | c4 | c5 | c6 | 1 | 2014-08-28 23:06:09.790227 |
2 | 1 | test | c4 | c5 | c6 | 1 | 2014-08-28 23:14:00.235677 | c8
3 | 1 | test | c4 | c5 | c6 | 1 | 2014-08-28 23:14:35.012675 | c8
回退到添加字段前1301666.
postgres=# do language plpgsql $$
declare
v_op text;
v_encoding_curr text := pg_client_encoding();
v_encoding_tmp text;
v_old text;
v_new text;
v_xid int8 := 1301666;
begin
for v_op, v_encoding_tmp, v_old, v_new in
select op,encoding,old_rec::text,new_rec::text from undo_t where xid>=v_xid order by xid desc,id desc
LOOP
execute 'set client_encoding='''||v_encoding_tmp||'''';
case v_op
when 'INSERT' then
delete from public."TBL" t where t=v_new::public."TBL";
when 'DELETE' then
insert into public."TBL" values ((v_old::public."TBL").*);
when 'TRUNCATE' then
insert into public."TBL" values ((v_old::public."TBL").*);
when 'UPDATE' then
delete from public."TBL" t where t=v_new::public."TBL";
insert into public."TBL" values ((v_old::public."TBL").*);
else
end case;
end loop;
execute 'set client_encoding='''||v_encoding_curr||'''';
end;
$$;
DO
postgres=# select * from "TBL";
c1 | c2 | C3 | c4 | c5 | c6 | c7 | crt_time | c8
----+----+---------+----+----+----+----+----------------------------+----
1 | 1 | te\\s\t | c4 | c5 | c6 | 1 | 2014-08-28 23:06:09.790227 |
(1 row)
接下来删除字段测试
postgres=# alter table "TBL" drop column c5;
ALTER TABLE
postgres=# select * from "TBL";
c1 | c2 | C3 | c4 | c6 | c7 | crt_time | c8
----+----+---------+----+----+----+----------------------------+----
1 | 1 | te\\s\t | c4 | c6 | 1 | 2014-08-28 23:06:09.790227 |
(1 row)
postgres=# insert into "TBL" values (3,1,'test','c4','c6',1,now(),'c8');
INSERT 0 1
postgres=# select * from "TBL";
c1 | c2 | C3 | c4 | c6 | c7 | crt_time | c8
----+----+---------+----+----+----+----------------------------+----
1 | 1 | te\\s\t | c4 | c6 | 1 | 2014-08-28 23:06:09.790227 |
3 | 1 | test | c4 | c6 | 1 | 2014-08-28 23:17:24.722663 | c8
(2 rows)
回退到1301666
postgres=# do language plpgsql $$
declare
v_op text;
v_encoding_curr text := pg_client_encoding();
v_encoding_tmp text;
v_old text;
v_new text;
v_xid int8 := 1301666;
begin
for v_op, v_encoding_tmp, v_old, v_new in
select op,encoding,old_rec::text,new_rec::text from undo_t where xid>=v_xid order by xid desc,id desc
LOOP
execute 'set client_encoding='''||v_encoding_tmp||'''';
case v_op
when 'INSERT' then
delete from public."TBL" t where t=v_new::public."TBL";
when 'DELETE' then
insert into public."TBL" values ((v_old::public."TBL").*);
when 'TRUNCATE' then
insert into public."TBL" values ((v_old::public."TBL").*);
when 'UPDATE' then
delete from public."TBL" t where t=v_new::public."TBL";
insert into public."TBL" values ((v_old::public."TBL").*);
else
end case;
end loop;
execute 'set client_encoding='''||v_encoding_curr||'''';
end;
$$;
DO
postgres=# select * from "TBL";
c1 | c2 | C3 | c4 | c6 | c7 | crt_time | c8
----+----+---------+----+----+----+----------------------------+----
1 | 1 | te\\s\t | c4 | c6 | 1 | 2014-08-28 23:06:09.790227 |
(1 row)
测试完全没有问题, 数据类型正常的转换, 字段和值的顺序匹配没有出现错乱.
其他
1. 使用标记为来标记delete在程序设计中用得比较多, 主要是防止程序的使用者误点删除操作, 可以把标记位改回来.
但是这种方法仅仅适用于不是直接执行SQL来删除的场景, 如果是直接使用delete from table 来删除的话, 有没有标记位都于事无补, 因为DELETE掉了.
注意
1. 如果事务中包含多个表的变更, 为了达到一致性的闪回, 那么多个表都要记录他们的UNDO, 所以需要在多个表上创建对应的触发器.
2. 我们记录的是事务号分配的顺序, 而不是提交顺序, 所以闪回到一个事务号时, 并不是闪回到这个事务提交的点, 而是这个事务分配的点上, 这与通过XLOG来还原是不一样的, 必须注意. 如果要达到提交点, 可以在跟踪表添加一列存储txid_current_snapshot(), 在恢复时跳过当时未提交的事务即可.
3. 还需要注意编码和逃逸的问题.
插入数据时的client_encoding和闪回数据时的client_encoding如果不一致可能会有问题. 所以我们在闪回时, 每次都指定跟踪时记录到的当时的client_encoding. 闪回操作结束后改回来.
触发器记录的是逃逸前的字符串, 在闪回时需要注意逃逸. 可以使用quote_nullable来解决, 使用record时不会有问题.
4. 注意表名, 列名的大小写问题, 使用quote_ident 来解决.
参考
1. http://blog.163.com/digoal@126/blog/static/16387704020128772037884/
2. http://blog.163.com/digoal@126/blog/static/163877040201252575529358/
3. http://www.postgresql.org/docs/9.4/static/hstore.html
4. http://blog.163.com/digoal@126/blog/static/163877040201321125220134/
5. http://blog.163.com/digoal@126/blog/static/1638770402012731944439/
6. http://blog.163.com/digoal@126/blog/static/1638770402012731203716/
7. http://blog.163.com/digoal@126/blog/static/1638770402013283547959/
8. http://blog.163.com/digoal@126/blog/static/1638770402013211102130526/
9. src/pl/plpgsql/src/pl_gram.y
case T_DATUM:
check_assignable(yylval.wdatum.datum, yylloc);
if (yylval.wdatum.datum->dtype == PLPGSQL_DTYPE_ROW ||
yylval.wdatum.datum->dtype == PLPGSQL_DTYPE_REC)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("\"%s\" is not a scalar variable",
NameOfDatum(&(yylval.wdatum))),
parser_errposition(yylloc)));
fieldnames[nfields] = NameOfDatum(&(yylval.wdatum));
varnos[nfields++] = yylval.wdatum.datum->dno;
break;