跳转至

单机闪回

单机闪回

  • 支持闪回表的DML、DDL操作。
  • 支持库级闪回。
  • 支持查询闪回。

配置参数

  • track_commit_timestamp
    设置为on，开启事务提交时间跟踪，确保可以从事务号得到事务结束的时间。

  • shared_preload_libraries 设置shared_preload_libraries = 'sdtrashcan'，加载此功能需要用的库。

  • flashback_mode
    设置flashback_mode = 'mod'，来保证不涉及删除表文件的事务可以被闪回，不必重启服务，但需要重新加载配置参数。 设置flashback_mode = 'all'，来保证所有事务可以被闪回，不必重启服务，但需要重新加载配置参数。

  • autovacuum
    控制是否使用autovacuum功能，设置autovacuum = 'off'，关闭该功能。

说明

前两个参数需要重启服务才能生效。

闪回单表

能够在短时间内恢复错误操作等造成的数据错误问题，恢复错误的 DML、DDL操作，为数据库的数据恢复提供更加便捷的途径。

功能描述:

允许将表中数据恢复到做DML或DDL操作前的某一指定状态。

工作原理：

在 SeaboxSQL中，根据mvcc原理，删除或者更新元组并没有立即删除旧数据，而只是标记为已删除，在vacuum清理之前，这些数据还存在page页中，根据元组可见性，通过闪回查询过去某个时间点或某个事务可见的数据，然后恢复到表中。

指定时间点闪回

功能描述：

使用FLASHBACK语句将表闪回至某个过去时间点的状态。

闪回语法： 

FLASHBACK TABLE table_name TIMESTAMP expression
- TIMESTAMP 用于指定要闪回到的时间点。时间为timestamp 类型，时间格式为“YYYY-MM-DD HH24:MI:SS”。

示例：

  • DML操作
seaboxsql=# create table test(id int,tx text,ts timestamp);
CREATE TABLE
seaboxsql=# insert into test values (1, 'beijing', now());
INSERT 0 1  
seaboxsql=# select * from test ;
 id |   tx    |             ts              
----+---------+----------------------------
  1 | beijing | 2023-02-20 15:42:56.256888
(1 row)

seaboxsql=# update test set tx = 'tianjin', ts = now() where id = 1;
UPDATE 1
seaboxsql=# select * from test ;
 id |   tx    |             ts              
----+---------+----------------------------
  1 | tianjin | 2023-02-20 15:43:12.663977
(1 row)

seaboxsql=# update test set tx = 'hebei', ts = now() where id = 1;
UPDATE 1
seaboxsql=# select * from test ;
 id |  tx   |             ts              
----+-------+----------------------------
  1 | hebei | 2023-02-20 15:43:37.055783
(1 row)
  • 闪回DML
seaboxsql=# flashback table test timestamp '2023-02-20 15:43:13';
FLASHBACK TABLE
seaboxsql=# select * from test;
 id |   tx    |             ts             
----+---------+----------------------------
  1 | tianjin | 2023-02-20 15:43:12.663977
(1 row)

seaboxsql=# flashback table test timestamp '2023-02-20 15:42:57';
FLASHBACK TABLE
seaboxsql=# select * from test;            
 id |   tx    |             ts             
----+---------+----------------------------
  1 | beijing | 2023-02-20 15:42:56.256888
(1 row)
  • DDL操作
seaboxsql=# create table test(id int,tx text,ts timestamp);
CREATE TABLE
seaboxsql=# insert into test values (1, 'beijing', now());
INSERT 0 1
seaboxsql=# select * from test;
 id |   tx    |             ts             
----+---------+----------------------------
  1 | beijing | 2023-06-06 11:15:08.210109
(1 row)

seaboxsql=# select * from now();
              now              
-------------------------------
 2023-06-06 11:15:08.212382+08
(1 row)

seaboxsql=# drop table test;
DROP TABLE
  • 闪回DDL
seaboxsql=# flashback table test timestamp '2023-06-06 11:15:08.212382';
FLASHBACK TABLE
seaboxsql=# \d
                List of relations
 Schema | Name | Type  |     Owner     | Storage 
--------+------+-------+---------------+---------
 public | dual | view  | zhanghailiang | 
 public | test | table | zhanghailiang | heap
(2 rows)

seaboxsql=# select * from test;
 id |   tx    |             ts             
----+---------+----------------------------
  1 | beijing | 2023-06-06 11:15:08.210109
(1 row)
指定事务号闪回

功能描述：

使用 FLASHBACK 语句将表闪回至某个过去事务号的状态。

闪回语法： 

FLASHBACK TABLE table_name XID expression
其中， XID 用于指定库级闪回时查询的事务号。

示例：

  • DML操作
seaboxsql=# create table test(id int,tx text,ts timestamp);
CREATE TABLE
seaboxsql=# insert into test values (1, 'beijing', now());
INSERT 0 1
seaboxsql=# select xmin,xmax,* from test;
 xmin | xmax | id |   tx    |             ts             
------+------+----+---------+----------------------------
 1344 |    0 |  1 | beijing | 2023-02-20 16:01:14.504023
(1 row)

seaboxsql=# update test set tx = 'tianjin', ts = now() where id = 1;
UPDATE 1
seaboxsql=# select xmin,xmax,* from test;
 xmin | xmax | id |   tx    |             ts             
------+------+----+---------+----------------------------
 1345 |    0 |  1 | tianjin | 2023-02-20 16:05:19.336343
(1 row)

seaboxsql=# update test set tx = 'hebei', ts = now() where id = 1;
UPDATE 1
seaboxsql=# select xmin,xmax,* from test;
 xmin | xmax | id |  tx   |             ts             
------+------+----+-------+----------------------------
 1346 |    0 |  1 | hebei | 2023-02-20 16:05:32.188188
(1 row)
  • 闪回DML
seaboxsql=# flashback table test xid 1345;
FLASHBACK TABLE
seaboxsql=# select xmin,xmax,* from test;
 xmin | xmax | id |   tx    |             ts             
------+------+----+---------+----------------------------
 1347 |    0 |  1 | tianjin | 2023-02-20 16:05:19.336343
(1 row)

seaboxsql=# flashback table test xid 1344;
FLASHBACK TABLE
seaboxsql=# 
seaboxsql=# select xmin,xmax,* from test;
 xmin | xmax | id |   tx    |             ts             
------+------+----+---------+----------------------------
 1348 |    0 |  1 | beijing | 2023-02-20 16:01:14.504023
(1 row)
  • DDL操作
seaboxsql=# create table test(id int,tx text,ts timestamp);
CREATE TABLE
seaboxsql=# insert into test values (1, 'beijing', now());
INSERT 0 1
seaboxsql=# select xmin, xmax, * from test;
 xmin | xmax | id |   tx    |             ts             
------+------+----+---------+----------------------------
  741 |    0 |  1 | beijing | 2023-06-06 11:19:06.028686
(1 row)

seaboxsql=# drop table test;
DROP TABLE
seaboxsql=# \d
               List of relations
 Schema | Name | Type |     Owner     | Storage 
--------+------+------+---------------+---------
 public | dual | view | zhanghailiang | 
(1 row)
  • 闪回DDL
seaboxsql=# flashback table test xid 741;
FLASHBACK TABLE
seaboxsql=# \d
                List of relations
 Schema | Name | Type  |     Owner     | Storage 
--------+------+-------+---------------+---------
 public | dual | view  | zhanghailiang | 
 public | test | table | zhanghailiang | heap
(2 rows)

seaboxsql=# select * from test;
 id |   tx    |             ts             
----+---------+----------------------------
  1 | beijing | 2023-06-06 11:19:06.028686
(1 row)

库级闪回

能够在短时间内恢复当前数据库内错误操作等造成的数据错误问题，恢复错误的 DML、DDL操作，为数据库的数据恢复提供更加便捷的途径。

功能描述:

允许将数据库中数据恢复到做DML或DDL操作前的某一指定状态。

工作原理：

在 SeaboxSQL中，根据mvcc原理，删除或者更新元组并没有立即删除旧数据，而只是标记为已删除，在vacuum清理之前，这些数据还存在page页中，根据元组可见性，通过闪回查询过去某个时间点或某个事务可见的数据，然后恢复到表中。

指定时间点闪回

功能描述：

使用db_flashback_xid函数将数据库闪回至某个过去时间点的状态。

闪回语法： 

SELECT db_flashback_xid(XID)
其中， XID 用于指定库级闪回时查询的事务号。

示例：

seaboxsql=# create extension if not exists sdtrashcan;
CREATE EXTENSION
seaboxsql=# create table t1(a int);
CREATE TABLE
seaboxsql=# insert into t1 values(1),(2);
INSERT 0 2
seaboxsql=# create table t2(a int);
CREATE TABLE
seaboxsql=# insert into t2 values(1),(2);
INSERT 0 2
seaboxsql=# select txid_current();
 txid_current 
--------------
          745
(1 row)

seaboxsql=# drop table t1;
DROP TABLE
seaboxsql=# drop table t2;
DROP TABLE
seaboxsql=# select db_flashback_xid(745);
           db_flashback_xid            
---------------------------------------
 (745,"2023-08-21 17:29:20.550879+08")
(1 row)

seaboxsql=# select * from t1;
 a 
---
 1
 2
(2 rows)

seaboxsql=# select * from t2;
 a 
---
 1
 2
(2 rows)

闪回查询

闪回查询允许查询过去时间点数据，或过去时间段数据变化。能够在短时间内恢复错误操作等造成的数据错误问题，恢复错误的DML操作，为数据库的数据恢复提供更加便捷的途径。

闪回查询

功能描述:

闪回查询允许查询过去时间点数据，或过去时间段数据变化。能够在短时间内恢复错误操作等造成的数据错误问题，恢复错误的DML操作，为数据库的数据恢复提供更加便捷的途径。

工作原理：

在SeaboxSQL中，根据mvcc原理，删除或者更新元组并没有立即删除旧数据，而只是标记为已删除，在vacuum清理之前，这些数据还存在page页中，根据元组可见性，闪回查询过去某个时间点或某个事务可见的数据。

指定时间点闪回查询

功能描述：

在SELECT中使用FLASHBACK子句指定过去时间点查询。

闪回语法： 

SELECT column_name[, …]
FROM table_name
[FLASHBACK TIMESTAMP expression]
[WHERE condition]

其中， FLASHBACK用于指定闪回查询时查询的时间点。时间为timestamp类型，时间格式为“YYYY-MM-DD HH24:MI:SS”。

示例：

  • 事务操作
=> create table test(id int,tx text,ts timestamp);
CREATE TABLE
=> insert into test values (1, 'beijing', now());
INSERT 0 1
=> select xmin,xmax,* from test ;
 xmin | xmax | id |   tx    |             ts             
------+------+----+---------+----------------------------
  550 |    0 |  1 | beijing | 2020-06-15 16:01:35.947974
(1 row)

=> update test set tx = 'tianjin', ts = now() where id = 1;
UPDATE 1
=> select xmin,xmax,* from test ;
 xmin | xmax | id |   tx    |             ts             
------+------+----+---------+----------------------------
  551 |    0 |  1 | tianjin | 2020-06-15 16:01:47.453507
(1 row)

=> update test set tx = 'hebei', ts = now() where id = 1;
UPDATE 1
=> select xmin,xmax,* from test ;
 xmin | xmax | id |  tx   |             ts             
------+------+----+-------+----------------------------
  552 |    0 |  1 | hebei | 2020-06-15 16:01:59.399336
(1 row)
  • 闪回查询
=> select * from test flashback timestamp '2020-06-15 16:01:47';
 id |   tx    |             ts             
----+---------+----------------------------
  1 | beijing | 2020-06-15 16:01:35.947974
(1 row)

=> select * from test flashback timestamp '2020-06-15 16:01:59';
 id |   tx    |             ts             
----+---------+----------------------------
  1 | tianjin | 2020-06-15 16:01:47.453507
(1 row)
指定事务号闪回查询

功能描述：

在SELECT中使用FLASHBACK子句指定事务号查询。

闪回语法： 

SELECT column_name[, …]
FROM table_name
[FLASHBACK XID expression]
[WHERE condition]
其中， FLASHBACK 用于指定闪回查询时查询的事务号。

示例：

=> select * from test flashback xid 550;
 id |   tx    |             ts             
----+---------+----------------------------
  1 | beijing | 2020-06-15 16:01:35.947974
(1 row)

=> select * from test flashback xid 551;
 id |   tx    |             ts             
----+---------+----------------------------
  1 | tianjin | 2020-06-15 16:01:47.453507
(1 row)
闪回版本查询

功能描述：

闪回版本查询返回在指定时间间隔或事务号间隔内的所有版本。

工作原理：

与闪回查询同理，返回一个时间段内所有的版本。

指定时间点闪回版本查询

功能描述：

在 SELECT 中使用 FLASHBACK BETWEEN 子句指定时间点查询。

闪回语法： 

SELECT column_name[, …]
FROM table_name
[FLASHBACK BETWEEN
TIMESTAMP expression AND expression]
WHERE condition
FLASHBACK BETWEEN 用于指定闪回版本查询时查询的时间段。

=> select * from test flashback between timestamp '2020-06-15 16:01:35' and '2020-06-15 16:02:00' where id = 1;
 id |   tx    |             ts             
----+---------+----------------------------
  1 | beijing | 2020-06-15 16:01:35.947974
  1 | tianjin | 2020-06-15 16:01:47.453507
  1 | hebei   | 2020-06-15 16:01:59.399336
(3 rows)

=> select * from test flashback between timestamp '2020-06-15 16:01:35' and '2020-06-15 16:01:59' where id = 1;
 id |   tx    |             ts             
----+---------+----------------------------
  1 | beijing | 2020-06-15 16:01:35.947974
  1 | tianjin | 2020-06-15 16:01:47.453507
(2 rows)
指定事务号闪回版本查询

功能描述：

在SELECT中使用FLASHBACK BETWEEN子句指定事务号查询。

闪回语法： 

SELECT column_name[, …]
FROM table_name
[FLASHBACK BETWEEN
XID expression AND expression]
WHERE condition

  • FLASHBACK BETWEEN 用于指定闪回版本查询时查询的事务号段。

  • 示例 1

=> select * from test flashback between xid 550 and 552 where id = 1;
 id |   tx    |             ts             
----+---------+----------------------------
  1 | beijing | 2020-06-15 16:01:35.947974
  1 | tianjin | 2020-06-15 16:01:47.453507
  1 | hebei   | 2020-06-15 16:01:59.399336
(3 rows)

=> select * from test flashback between xid 550 and 551 where id = 1;
 id |   tx    |             ts             
----+---------+----------------------------
  1 | beijing | 2020-06-15 16:01:35.947974
  1 | tianjin | 2020-06-15 16:01:47.453507
(2 rows)
* 示例 2

在同一个事务中对一行更新多次，只显示最后一次提交的版本。

=> create table test(id int,tx text,ts timestamp);
CREATE TABLE
=> insert into test values (1, 'beijing', now());
INSERT 0 1
=> select xmin,xmax,* from test ;
 xmin | xmax | id |   tx    |             ts             
------+------+----+---------+----------------------------
  555 |    0 |  1 | beijing | 2020-06-15 16:38:42.170305
(1 row)

=> begin;
BEGIN
=> update test set tx = 'tianjin' where id = 1;
UPDATE 1
=> update test set tx = 'hebei' where id = 1;
UPDATE 1
=> commit;
COMMIT
=> select xmin,xmax,* from test ;
 xmin | xmax | id |  tx   |             ts             
------+------+----+-------+----------------------------
  556 |    0 |  1 | hebei | 2020-06-15 16:38:42.170305
(1 row)

=> select xmin,xmax,* from test flashback between timestamp '2020-06-15 16:38:42' and '2020-06-15 16:40:42';
 xmin | xmax | id |   tx    |             ts             
------+------+----+---------+----------------------------
  555 |  556 |  1 | beijing | 2020-06-15 16:38:42.170305
  556 |    0 |  1 | hebei   | 2020-06-15 16:38:42.170305
(2 rows)
通过闪回查询进行数据恢复

闪回查询可以支持数据恢复，将数据恢复到过去某个时刻的状态。

示例 1

在更新时，未指定where条件，导致全表更新，闪回查询过去时间点的数据放到临时表，从临时表恢复原数据或者重命名表。

=> select xmin,xmax,* from test ;
 xmin | xmax | id |   tx    |             ts             
------+------+----+---------+----------------------------
  559 |    0 |  1 | beijing | 2020-06-15 17:19:35.540812
  560 |    0 |  2 | tianjin | 2020-06-15 17:19:35.542743
  561 |    0 |  3 | hebei   | 2020-06-15 17:19:35.544101
(3 rows)

=> update test set tx = 'shanghai';
UPDATE 3
=> select * from test;
 id |    tx    |             ts             
----+----------+----------------------------
  1 | shanghai | 2020-06-15 17:19:35.540812
  2 | shanghai | 2020-06-15 17:19:35.542743
  3 | shanghai | 2020-06-15 17:19:35.544101
(3 rows)

=> select * from test flashback timestamp '2020-06-15 17:19:36';
 id |   tx    |             ts             
----+---------+----------------------------
  1 | beijing | 2020-06-15 17:19:35.540812
  2 | tianjin | 2020-06-15 17:19:35.542743
  3 | hebei   | 2020-06-15 17:19:35.544101
(3 rows)

=> create table test_tmp as select * from test flashback timestamp '2020-06-15 17:19:36';
SELECT 3
=> select * from test_tmp;
 id |   tx    |             ts             
----+---------+----------------------------
  1 | beijing | 2020-06-15 17:19:35.540812
  2 | tianjin | 2020-06-15 17:19:35.542743
  3 | hebei   | 2020-06-15 17:19:35.544101
(3 rows)
=> update test as a set tx = (select b.tx from test_tmp as b where a.id = b.id);
UPDATE 3
=> select * from test;
 id |   tx    |             ts             
----+---------+----------------------------
  1 | beijing | 2020-06-15 17:19:35.540812
  2 | tianjin | 2020-06-15 17:19:35.542743
  3 | hebei   | 2020-06-15 17:19:35.544101
(3 rows)

# 重命名表名
=> alter table test_tmp rename to test;
ALTER TABLE
=> select * from test;
 id |   tx    |             ts             
----+---------+----------------------------
  1 | beijing | 2020-06-15 17:19:35.540812
  2 | tianjin | 2020-06-15 17:19:35.542743
  3 | hebei   | 2020-06-15 17:19:35.544101
(3 rows)

示例 2

使用UPDATE … (SELECT … FLASHBACK …)恢复数据。

=> update test set tx = (select tx from test flashback timestamp '2020-06-15 17:19:36' where id =1) where id = 1;
UPDATE 1
=> update test set tx = (select tx from test flashback timestamp '2020-06-15 17:19:36' where id =2) where id = 2;
UPDATE 1
=> update test set tx = (select tx from test flashback timestamp '2020-06-15 17:19:36' where id =3) where id = 3;
UPDATE 1
=> select * from test;
 id |   tx    |             ts             
----+---------+----------------------------
  1 | beijing | 2020-06-15 17:19:35.540812
  2 | tianjin | 2020-06-15 17:19:35.542743
  3 | hebei   | 2020-06-15 17:19:35.544101
(3 rows)
注意事项

目前闪回查询功能只能对DML（insert、 update 和 delete）操作进行闪回查询，对于DDL truncate对表的操作，目前闪回查询还不支持。除此之外，闪回查询也不支持表结构做出的修改操作。