1.基于时间的闪回
使用时间点进行闪回时,可能会有3s的误差。想要闪回到精确的操作点,需要使用CSN进行闪回。
2.查询当前时间
select snptime,snpcsn from gs_txn_snapshot where snptime>='2024-04-09 11:08' order by snptime desc;
mydb1=> select snptime,snpcsn from gs_txn_snapshot where snptime>='2024-04-09 11:08' order by snptime desc;
snptime | snpcsn
-------------------------------+--------
2024-04-09 11:09:35.923647+08 | 24618
2024-04-09 11:09:32.892835+08 | 24615
2024-04-09 11:09:29.861663+08 | 24612
2024-04-09 11:09:26.831202+08 | 24609
2024-04-09 11:09:23.795395+08 | 24604
2024-04-09 11:09:20.767963+08 | 24600
2024-04-09 11:09:17.731541+08 | 24597
--我们等会闪回查询取这个时间点。
2024-04-09 11:09:17
3.删除数据。
mydb1=> select * from test1;
id
----
1
2
3
(3 rows)
mydb1=> delete from test1;
DELETE 3
mydb1=> select * from test1;
id
----
(0 rows)
4.查看当前时间。
mydb1=> SELECT CURRENT_TIMESTAMP;
pg_systimestamp
------------------------------
2024-04-09 11:11:48.42958+08
(1 row)
5.我们闪回到上一个时间点:2024-04-09 11:09:17
SELECT * FROM test1 TIMECAPSULE TIMESTAMP to_timestamp ('2024-04-09 11:09:17', 'YYYY-MM-DD HH24:MI:SS');
mydb1=> SELECT * FROM test1 TIMECAPSULE TIMESTAMP to_timestamp ('2024-04-09 11:09:17', 'YYYY-MM-DD HH24:MI:SS');
id
----
1
2
3
(3 rows)
果然我们又闪回成功。