MogDB 2.1 Support flashback transaction query & table drop & truncate table
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: MogDB 2.1 Support flashback transaction query & table drop & truncate table
MogDB2.1版本发布已经有2月有余了，春节期间一直没时间测试，今天特意抽空简单测试一下针对闪回事务查询和flashback table drop相关的功能。首先需要调整如下几个相关参数：
|
1 2 3 4 5 |
gs_guc set -N all -I all -c "undo_zone_count=10000" gs_guc set -N all -I all -c "enable_default_ustore_table=on" gs_guc set -N all -I all -c "version_retention_age=10000" gs_guc set -N all -I all -c "enable_recyclebin=on" /data/mogdb/bin/gs_ctl restart -D /opt/mogdb/data/db1/ |
因为默认情况下MogDB 2.1版本中的回收站功能和ustore存储引擎特性未启用，因此需要打开上述功能。
从目前的文档来看；支持闪回事务查询和flashback table drop的恢复。这里我通过自己的虚拟机环境进行一下简单测试验证：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 |
[omm@mogdb script]$ gsql -d enmotech -p26000 -Uroger Password for user roger: gsql ((MogDB 2.1.0 build 56189e20) compiled at 2022-01-07 18:47:53 commit 0 last mr ) Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help. enmotech=> enmotech=> create table t0214(a int,b varchar(20)); CREATE TABLE enmotech=> insert into t0214 values(1,'enmotech.com'); INSERT 0 1 enmotech=> insert into t0214 values(2,'killdb.com'); INSERT 0 1 enmotech=> insert into t0214 values(3,'baidu.com'); INSERT 0 1 enmotech=> select * from t0214; a | b ---+-------------- 1 | enmotech.com 2 | killdb.com 3 | baidu.com (3 rows) enmotech=> select current_timestamp; pg_systimestamp ------------------------------- 2022-02-14 21:16:53.717151+08 (1 row) enmotech=> update t0214 set b='mogdb.io' where a=3; UPDATE 1 enmotech=> select * from t0214; a | b ---+-------------- 1 | enmotech.com 2 | killdb.com 3 | mogdb.io (3 rows) enmotech=> enmotech=> select snptime,snpcsn from gs_txn_snapshot where snptime between '2022-02-14 21:16:53.717151+08' and '2022-02-14 21:17:53.717151+08'; snptime | snpcsn -------------------------------+-------- 2022-02-14 21:16:56.211226+08 | 2119 2022-02-14 21:16:59.243046+08 | 2121 2022-02-14 21:17:02.264878+08 | 2123 2022-02-14 21:17:05.289966+08 | 2125 2022-02-14 21:17:08.311199+08 | 2127 2022-02-14 21:17:11.330451+08 | 2129 2022-02-14 21:17:14.357411+08 | 2131 2022-02-14 21:17:17.38367+08 | 2133 2022-02-14 21:17:20.414649+08 | 2135 2022-02-14 21:17:23.437261+08 | 2137 2022-02-14 21:17:26.469704+08 | 2139 2022-02-14 21:17:29.503769+08 | 2141 2022-02-14 21:17:32.539126+08 | 2143 2022-02-14 21:17:35.560913+08 | 2145 2022-02-14 21:17:38.579216+08 | 2147 2022-02-14 21:17:41.605395+08 | 2149 2022-02-14 21:17:44.634837+08 | 2151 2022-02-14 21:17:47.657877+08 | 2153 2022-02-14 21:17:50.683656+08 | 2155 (19 rows) --基于timestamp做闪回查询 enmotech=> select * from t0214 timecapsule timestamp to_timestamp(' 2022-02-14 21:17:02.264878','YYYY-MM-DD HH24:MI:SS.FF'); a | b ---+-------------- 1 | enmotech.com 2 | killdb.com 3 | baidu.com (3 rows) --基于csn做闪回查询 enmotech=> select * from t0214 timecapsule csn 2121; a | b ---+-------------- 1 | enmotech.com 2 | killdb.com 3 | baidu.com (3 rows) enmotech=> |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 |
enmotech=> create table t0214_1(a int,b varchar(20)) with (STORAGE_TYPE=ASTORE); CREATE TABLE enmotech=> insert into t0214_1 values(1,'enmotech.com'); INSERT 0 1 enmotech=> insert into t0214_1 values(2,'killdb.com'); INSERT 0 1 enmotech=> insert into t0214_1 values(3,'baidu.com'); INSERT 0 1 enmotech=> select * from t0214_1; a | b ---+-------------- 1 | enmotech.com 2 | killdb.com 3 | baidu.com (3 rows) enmotech-> \d+ t0214_1 Table "public.t0214_1" Column | Type | Modifiers | Storage | Stats target | Description --------+-----------------------+-----------+----------+--------------+------------- a | integer | | plain | | b | character varying(20) | | extended | | Has OIDs: no Options: orientation=row, storage_type=astore, compression=no enmotech=> enmotech=> select current_timestamp; pg_systimestamp ------------------------------ 2022-02-14 21:45:41.13098+08 (1 row) enmotech=> select * from t0214_1; a | b ---+-------------- 1 | enmotech.com 2 | killdb.com 3 | baidu.com (3 rows) enmotech=> update t0214_1 set b='云和恩墨' where a=3; UPDATE 1 enmotech=> select current_timestamp; pg_systimestamp ------------------------------- 2022-02-14 21:46:43.930901+08 (1 row) enmotech=> select snptime,snpcsn from gs_txn_snapshot where snptime between '2022-02-14 21:45:41.13098+08' and '2022-02-14 21:46:43.930901+08'; snptime | snpcsn -------------------------------+-------- 2022-02-14 21:45:41.832082+08 | 3278 2022-02-14 21:45:44.854854+08 | 3280 2022-02-14 21:45:47.890183+08 | 3282 2022-02-14 21:45:50.920906+08 | 3284 2022-02-14 21:45:53.945547+08 | 3286 2022-02-14 21:45:56.965802+08 | 3288 2022-02-14 21:45:59.991219+08 | 3290 2022-02-14 21:46:03.011581+08 | 3292 2022-02-14 21:46:06.030345+08 | 3294 2022-02-14 21:46:09.055675+08 | 3296 2022-02-14 21:46:12.080262+08 | 3298 2022-02-14 21:46:15.102671+08 | 3300 2022-02-14 21:46:18.122371+08 | 3302 2022-02-14 21:46:21.148149+08 | 3304 2022-02-14 21:46:24.180653+08 | 3307 2022-02-14 21:46:27.211797+08 | 3309 2022-02-14 21:46:30.235113+08 | 3311 2022-02-14 21:46:33.252069+08 | 3313 2022-02-14 21:46:36.281825+08 | 3315 2022-02-14 21:46:39.306997+08 | 3317 2022-02-14 21:46:42.337767+08 | 3319 (21 rows) enmotech=> select * from t0214_1 timecapsule csn 3280; ERROR: Restore point too old enmotech=> select * from t0214_1 timecapsule csn 3278; ERROR: Restore point too old enmotech=> select * from t0214_1 timecapsule csn 3292; ERROR: Restore point too old |
从上述来看，默认使用了Ustore存储引擎支持闪回查询，而默认的astore存储引擎不支持闪回事务查询。
接下来继续测试drop table的情况：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
enmotech=> select rcyname,rcyoriginname,rcyrecycletime,rcytablespace from gs_recyclebin; rcyname | rcyoriginname | rcyrecycletime | rcytablespace ---------+---------------+----------------+--------------- (0 rows) enmotech=> create table t0214_1(a int,b varchar(20)) with (STORAGE_TYPE=ASTORE); CREATE TABLE enmotech=> insert into t0214_1 values(1,'enmotech.com'); INSERT 0 1 enmotech=> insert into t0214_1 values(2,'killdb.com'); INSERT 0 1 enmotech=> insert into t0214_1 values(3,'baidu.com'); INSERT 0 1 enmotech=> select * from t0214_1; a | b ---+-------------- 1 | enmotech.com 2 | killdb.com 3 | baidu.com (3 rows) enmotech-> \d+ t0214_1 Table "public.t0214_1" Column | Type | Modifiers | Storage | Stats target | Description --------+-----------------------+-----------+----------+--------------+------------- a | integer | | plain | | b | character varying(20) | | extended | | Has OIDs: no Options: orientation=row, storage_type=astore, compression=no enmotech=> drop table t0214; DROP TABLE enmotech=> drop table t0214_1; DROP TABLE enmotech=> select rcyname,rcyoriginname,rcyrecycletime,rcytablespace from gs_recyclebin; rcyname | rcyoriginname | rcyrecycletime | rcytablespace -----------------------------+---------------+-------------------------------+--------------- BIN$40004EB400B$22B2048==$0 | t0214_1 | 2022-02-14 21:33:22.091615+08 | 0 (1 row) enmotech=> timecapsule table "BIN$40004EB400B$22B2048==$0" to before drop rename to t0214_1; TimeCapsule Table enmotech=> select rcyname,rcyoriginname,rcyrecycletime,rcytablespace from gs_recyclebin; rcyname | rcyoriginname | rcyrecycletime | rcytablespace ---------+---------------+----------------+--------------- (0 rows) enmotech=> select * from t0214_1; a | b ---+-------------- 1 | enmotech.com 2 | killdb.com 3 | baidu.com (3 rows) enmotech=> |
除了支持drop table之外，实际上MogDB 2.1版本的回收站功能还支持truncate table的恢复，这一点功能非常赞！
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
enmotech=> select * from t0214_1; a | b ---+-------------- 1 | enmotech.com 2 | killdb.com 3 | 云云和恩墨 (3 rows) enmotech=> truncate table t0214_1; TRUNCATE TABLE enmotech=> select rcyname,rcyoriginname,rcyrecycletime,rcytablespace from gs_recyclebin; rcyname | rcyoriginname | rcyrecycletime | rcytablespace -----------------------------+---------------+-------------------------------+--------------- BIN$40004EB400B$24A8BC0==$0 | t0214_1 | 2022-02-15 10:15:24.168793+08 | 0 (1 row) enmotech=> TIMECAPSULE TABLE t0214_1 to before truncate; TimeCapsule Table enmotech=> select * from t0214_1; a | b ---+-------------- 1 | enmotech.com 2 | killdb.com 3 | 云云和恩墨 (3 rows) enmotech=> |
从测试来看回收站功能跟Oracle类似，也是产生BIN的表，暂时存放到回收站中。 这里进行简单总结：
1、闪回事务查询功能，仅支持ustore存储引擎，默认astore存储引擎暂不支持；
2、回收站功能仅支持astore存储引擎，暂不支持ustore存储引擎。
3、MogDB 回收站功能支持truncate table的恢复，这一点真心很赞！
话说上述两点总结看起来是不是有点绕？不管如何，这也是MogDB2.1版本提供针对误操作场景的一些应对措施，是一个良好开端。
Leave a Reply
You must be logged in to post a comment.