flashback recovery
The flashback recovery function is a part of the database recovery technology, which can selectively undo the impact of a committed transaction and recover data from human-incorrect operations. Before the flashback technology was adopted, the submitted database modifications could only be retrieved through backup recovery, PITR, etc., and the recovery time would take minutes or even hours. After using the flashback technology, it only takes seconds to restore the submitted data before the database modification, and the recovery time has nothing to do with the database size.
illustrate:
The ASTORE engine does not currently support flashback DROP/TRUNCATE.
flashback query
flashback table
Flashback DROP/TRUNCATE
The flashback recovery function is a part of the database recovery technology, which can selectively undo the impact of a committed transaction and recover data from human-incorrect operations. Before the flashback technology was adopted, the submitted database modifications could only be retrieved through backup recovery, PITR, etc., and the recovery time would take minutes or even hours. After using the flashback technology, it only takes seconds to restore the submitted data before the database modification, and the recovery time has nothing to do with the database size.
flashback query
Background Information
Flashback query can query a certain snapshot data of a table at a certain point in time in the past. This feature can be used to view and logically reconstruct damaged data that has been accidentally deleted or changed. Flashback Query is based on the MVCC multi-version mechanism, and obtains the data of the specified old version by searching and querying the old version.
prerequisite
The undo_retention_time parameter is used to set the retention time of undo old versions.
grammar
{[ ONLY ] table_name [ * ] [ partition_clause ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
[ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]
[TIMECAPSULE { TIMESTAMP | CSN } expression ]
|( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
|with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
|function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]
|function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
|from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]}
"TIMECAPSULE {TIMESTAMP | CSN} expression" in the syntax tree is a new expression for the flashback function, where TIMECAPSULE indicates the use of the flashback function, TIMESTAMP and CSN indicate that the flashback function uses specific time point information or CSN (commit sequence number) information .
Parameter Description
TIMESTAMP
It means to query the data of a certain table at the time point of TIMESTAMP, and TIMESTAMP refers to a specific historical time.
CSN
Refers to querying the data of a certain CSN point under the logical submission sequence of the entire database. CSN refers to a specific logical submission time point. The CSN in the database is a write consistency point. Each CSN represents a consistency point of the entire database. The data under the CSN represent the relevant data of the SQL query database at the consistency point.
Example of use
Example 1:
SELECT * FROM t1 TIMECAPSULE TIMESTAMP to_timestamp ('2020-02-11 10:13:22.724718', 'YYYY-MM-DD HH24:MI:SS.FF');
Example 2:
SELECT * FROM t1 TIMECAPSULE CSN 9617;
Example 3:
SELECT * FROM t1 AS t TIMECAPSULE TIMESTAMP to_timestamp ('2020-02-11 10:13:22.724718', 'YYYY-MM-DD HH24:MI:SS.FF');
Example 4:
SELECT * FROM t1 AS t TIMECAPSULE CSN 9617;
flashback table
Background Information
Flashback Table can restore a table to a specific point in time. This feature can quickly restore the data of the table when the logical damage is limited to a table or a group of tables, rather than the entire database. Based on the MVCC multi-version mechanism, Flashback Table can restore table-level data by deleting the incremental data at and after the specified time point and retrieving the deleted data at the specified time point and the current time point.
prerequisite
The undo_retention_time parameter is used to set the retention time of undo old versions.
grammar
TIMECAPSULE TABLE table_name TO { TIMESTAMP | CSN } expression
Example of use
TIMECAPSULE TABLE t1 TO TIMESTAMP to_timestamp ('2020-02-11 10:13:22.724718', 'YYYY-MM-DD HH24:MI:SS.FF');
TIMECAPSULE TABLE t1 TO CSN 9617;
Flashback DROP/TRUNCATE
Background Information
Flashback DROP: You can restore accidentally deleted tables, and restore deleted tables and their subsidiary structures such as indexes and table constraints from the recycle bin. Flashback drop is based on the recycle bin mechanism, and restores the dropped table by restoring the physical files of the table recorded in the recycle bin.
Flashback TRUNCATE: You can recover the misoperation or accidental truncate table, restore the physical data of the truncate table and index from the recycle bin. Flashback truncate is based on the recycle bin mechanism, and restores the truncate table by restoring the physical files of the table recorded in the recycle bin.
prerequisite
Turn on the enable_recyclebin parameter to enable the recycle bin.
The recyclebin_retention_time parameter is used to set the retention time of recycle bin objects, and recycle bin objects exceeding this time will be automatically cleaned up.
related grammar
delete table
DROP TABLE table_name [PURGE]
Clean up recycle bin objects
PURGE { TABLE { table_name }
| INDEX { index_name }
| RECYCLEBIN
}
Flashback a dropped table
TIMECAPSULE TABLE { table_name } TO BEFORE DROP [RENAME TO new_tablename]
truncate table
TRUNCATE TABLE { table_name } [ PURGE ]
flashback truncated table
TIMECAPSULE TABLE { table_name } TO BEFORE TRUNCATE
Parameter Description
DROP/TRUNCATE TABLE table_name PURGE
By default, the table data will be put into the recycle bin, and PURGE will clean it up directly.
PURGE RECYCLEBIN
Represents cleaning up recycle bin objects.
TO BEFORE DROP
Use this clause to retrieve dropped tables and their child objects from the recycle bin.
You can specify the name of the original user-specified table, or a system-generated name assigned by the database when the object was deleted.
System-generated object names are unique within the recycle bin. Therefore, if a system-generated name is specified, the database retrieves the specified object. Use the "select * from pg_recyclebin;" statement to view the contents of the recycle bin.
If a user-specified name is specified, and if the recycle bin contains more than one object of that name, then the database retrieves the most recently moved object in the recycle bin. If you want to retrieve an earlier version of the table, you can do this:
Specifies the system-generated name of the table you want to retrieve.
Execute TIMECAPSULE TABLE ... TO BEFORE DROP statements until the table you want to retrieve.
When restoring the DROP table, only the name of the base table is restored, and the names of other sub-objects remain the name of the recycle bin object. Users can execute DDL commands to manually adjust sub-object names as needed.
Recycle bin objects do not support write operations such as DML, DCL, and DDL, and do not support DQL query operations (subsequent support).
Between the flashback point and the current point, a statement that modifies the table structure or affects the physical structure is executed, and the flashback fails. DDL execution involving namespace, table name change and other operations flashback error: ERROR: recycle object %s desired does not exis; DDL execution flashback error of adding/deleting/cutting/synthesizing and other operations such as partition changes: ERROR: relation %s does not exis; in other cases, an error is reported: "ERROR: The table definition of %s has been changed.".
RENAME TO
Specifies a new name for the table retrieved from the recycle bin.
TO BEFORE TRUNCATE
Flash back to before TRUNCATE.
syntax example
DROP TABLE t1 PURGE;
PURGE TABLE t1;
PURGE TABLE "BIN$04LhcpndanfgMAAAAAANPw==$0";
PURGE INDEX i1;
PURGE INDEX "BIN$04LhcpndanfgMAAAAAANPw==$0";
PURGE RECYCLEBIN;
TIMECAPSULE TABLE t1 TO BEFORE DROP;
TIMECAPSULE TABLE t1 TO BEFORE DROP RENAME TO new_t1;
TIMECAPSULE TABLE "BIN$04LhcpndanfgMAAAAAANPw==$0" TO BEFORE DROP;
TIMECAPSULE TABLE "BIN$04LhcpndanfgMAAAAAANPw==$0" TO BEFORE DROP RENAME TO new_t1;
Top comments (0)