VastbaseE100

闪回功能

• 闪回查询

  1) 开启”跟踪事务提交时间戳”参数，将值设为on，修改完成后重启数据库

  2) 闪回查询

  使用asql客户端工具执行以下命令：

  CREATE TABLE t_person (id BIGINT,name VARCHAR(64));
  SELECT pg_sleep(10);
  INSERT INTO t_person VALUES (1,'bob');
  INSERT INTO t_person VALUES (2,'tim');
  SELECT * FROM t_person;
  SELECT pg_sleep(10);
  SELECT pg_sleep(10);
  UPDATE t_person SET NAME = 'cat' WHERE ID = 2;
  SELECT * FROM t_person;
  

  3) 闪回查询15秒以前的表数据

  SELECT * FROM flashback_query('public.t_person',CURRENT_TIMESTAMP - INTERVAL '15 second') AS t(id BIGINT,name VARCHAR(64));
  SELECT pg_sleep(10);
  SELECT pg_sleep(10);
  DELETE FROM t_person WHERE ID = 2;
  SELECT * FROM t_person;
  SELECT * FROM flashback_query('public.t_person',CURRENT_TIMESTAMP - INTERVAL '15 second') AS t(id BIGINT,name VARCHAR(64));
  

• 闪回表

  1) 闪回查询

  使用asql客户端工具执行以下命令：

  TRUNCATE TABLE t_person;
  INSERT INTO t_person VALUES (1,'bob');
  INSERT INTO t_person VALUES (2,'tim');
  INSERT INTO t_person VALUES (3,'kiki');
  SELECT * FROM t_person;
  

  2) 对数据表的操作进行回退

  SELECT pg_sleep(10);
  SELECT pg_sleep(10);
  INSERT INTO t_person VALUES (4,'yuki');
  UPDATE t_person SET name = 'mini' WHERE id = 2;
  DELETE FROM t_person WHERE id = 3;
  SELECT * FROM t_person;
  SELECT flashback('public.t_person',CURRENT_TIMESTAMP - INTERVAL '15 second');
  SELECT * FROM t_person;