Manager Handbook for Centralized AntDB-T — P7–1
Published: 2023-08-22 07:00:45

Flashback recovery
In the process of using AntDB database, the business is often led to abnormalities because of mistakenly deleted or updated table data, or even the whole table deleted due to operation errors or program bugs. For the above scenario, the database needs to support the data flashback function to retrieve the modified data and recover the business quickly.
The flashback plug-in is built on the basis that each page change after a checkpoint generates a full-page write (FPW), so all wal logs after a checkpoint can be perfectly parsed.
Install antdb_flashback plugin
antdb_flashback is a parsing tool for WAL (write ahead logs) logs, designed to mine the wal logs for all useful information and thus provide data recovery support. The steps are as follows:

```
Modify the parameters and reboot:
set shared_preload_libraries= 'antdb_flashback';
set shared_preload_libraries= 'antdb_flashback';
set shared_preload_libraries= 'antdb_flashback';

Connect to gtmcoord to create an extension.
create extension antdb_flashback;
```
DML flashback
This section describes how to parse DML statements directly from the database generated by wal logs and how to execute the parsed undo statements.
The DML statements can be parsed and the undo statements can be executed by calling the flashbackdml function with the following parameters.
•timestart, timestamp type, the start time point.
•timestop, timestamp type, the end time point.
•relname, text type, the name of the table.
•lsnstart, unsigned long type, the start LSN position.
•lsnstop, unsigned long type, the ending LSN location.
•xid, unsigned int type, the transaction number.
•walpath, text type, the wal log directory, uses the pg_wal directory if it is null.
•dictdir, text type, the exported metadata storage directory, uses the database metadata  if it is null.
•sqldir, text type, the flashback SQL storage directory, stores the flashback SQL in the table flashback_dml_result  if it is null.
•apply, bool type, whether to apply the flashback SQL automatically, the default is False.
Parse undo statements
Use the flashbackdml method to parse the undo statement into a table or a file by passing in different parameters.

```
To parse DML statements within a specific time interval:
select flashbackdml(timestart =>'time1',timestop=>'time2');

To resolve DML statements within a specific lsn interval:
select flashbackdml(lsnstart=>'lsn1',lsnstop=>'lsn2'); 

Parse the statement that specifies xid:
select flashbackdml(xid => 604);

Specify WAL file directory resolution:
select flashbackdml(walpath =>'pg_wal');

Specify table name resolution:
select flashbackdml(relname =>'t2');

Resolve to the specified directory:
select flashbackdml(sqldir =>'./');
```
By default, the result is parsed to the table flashback_dml_result, if sqldir is specified, it will be parsed to the corresponding directory and generate 2 files, flashback_dml_result_redo.sql and flashback_dml_result_undo.sql.
View the results in table flashback_dml_result:

```
select redo_sql, undo_sql from flashback_dml_result order by xid;
```
Apply undo statement
Apply undosql from the flashback_dml_result table:

```
select flashbackdml(apply => 'true');
```
Apply the flashback_dml_result_undo.sql file in the specified sqldir directory:

```
select flashbackdml(apply=> 'true', sqldir =>'./');
```
Or user can apply undo.sql file directly through the -f of psql command.
For example:

```
drop table if exists t1;
drop table if exists muti_test;
CREATE TABLE t1(i int, j int, k varchar default('abc'));
create table muti_test(id int, num int);
checkpoint;

INSERT INTO t1 VALUES(1,2);
INSERT INTO t1 VALUES(2,4);

SELECT now() AS time1 \gset

INSERT INTO t1 VALUES(3,5);
update t1 set k = 'updated' where i = 2;
delete from t1;

SELECT now() AS time2 \gset

insert into t1 select i,i+10 from generate_series(1,5) i;
delete from t1 where j = 12;

SELECT now() AS time3 \gset

--Apply automatically
select flashbackdml(timestart =>:'time1',timestop=>:'time3');-- tuple:0
select flashbackdml(apply => true);

```