Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive

TIMECAPSULE TABLE

Updated on 2024-08-20 GMT+08:00

Description

The TIMECAPSULE TABLE statement restores a table to an earlier state in the event of human or application errors.

The table can flash back to a past point in time, depending on the old data stored in the system. In addition, GaussDB cannot restore a table to an earlier state through DDL operations that has changed the structure of the table.

Precautions

  • The TIMECAPSULE TABLE statement can be used to flash back the old data or the data from the recycle bin.
    • TO TIMECAPSULE and TO CSN can flash a table back to an earlier version. Currently, only the Ustore is supported.
    • The recycle bin records the objects dropped or truncated by running DROP and TRUNCATE. TO BEFORE DROP and TO BEFORE TRUNCATE can flash data back from the recycle bin. Currently, the Ustore and Astore are supported.
  • The following object types do not support flashback: system catalogs, DFS tables, global temporary tables, local temporary tables, unlogged tables, sequence tables, encrypted tables, and hash bucket tables.
  • Flashback is not supported for tables that contain user-defined types.
  • After flashback is enabled, tables in the recycle bin can be backed up but cannot be restored.
  • If a statement (DDL, DCL, or VACUUM FULL) that modifies the table structure or affects physical storage is executed between the flashback point and the current point, the flashback fails.
  • To run DROP, you must have the CREATE or USAGE permission on the schema to which the junk object belongs, and you must be the owner of the schema or the owner of the junk object.

    To run TRUNCATE, you must have the CREATE or USAGE permission on the schema to which the junk object belongs, and you must be the owner of the schema or the junk object. In addition, you must have the TRUNCATE permission on the junk object.

  • Scenarios or tables that do not support DROP or TRUNCATE:
    • Scenario where the recycle bin is disabled (enable_recyclebin is set to off)
    • Scenario where the system is in the maintenance state (xc_maintenance_mode is set to on) or is upgraded from an unsupported baseline version to a supported version
    • Scenario where multiple objects are deleted (The DROP or TRUNCATE TABLE command is executed to delete multiple objects at the same time.)
    • System catalogs, DFS tables, global temporary tables, local temporary tables, unlogged tables, sequence tables, encrypted tables, and hash bucket tables
    • Objects in the recycle bin are cleared and do not support DROP or TRUNCATE FLASHBACK. The recyclebin_retention_time parameter specifies the retention period of objects in the recycle bin.
    • The recycle bin does not support write operations such as DML, DCL, and DDL, and does not support DQL query operations.
    • If a statement (such as DDL, DCL, VACUUM FULL, and partition adding/deleting/splitting/merging) that modifies the table structure or affects physical files are executed between the TRUNCATE TABLE and TRUNCATE flashback operations, the flashback fails.
    • When a table is entirely deleted or truncated, partitions are moved to the recycle bin along with the entire table. A single deleted partition cannot be moved to the recycle bin. This avoids data consistency damage.
    • If the object on which the table depends is an external object (for example, a composite object or a user-defined object), the table is physically deleted and is not moved to the recycle bin.
  • DROP FLASHBACK constraints
    You can specify either the original user-defined name of the table or the system-generated name assigned to the object when it was dropped.
    • System-generated recycle bin object names are unique. Therefore, if you specify the system-generated name, the database retrieves that specified object. To see the content in your recycle bin, run select * from gs_recyclebin;.
    • If you specify the user-specified name and the recycle bin contains more than one object of that name, the database retrieves the object that was moved to the recycle bin most recently. To retrieve a table of an earlier version, perform the following steps:
      • Specify the system-generated recycle bin name of the table you want to retrieve.

      • Run the TIMECAPSULE TABLE ... TO BEFORE DROP statements until you retrieve the required table.

    • When a dropped table is restored, only the base table name is restored, and the names of other subobjects remain the same as those in the recycle bin. You can run the DDL command to manually change the names of subobjects as required.
    • The recycle bin does not support write operations such as DML, DCL, and DDL, and does not support DQL query operations (supported in later versions).
    • The recyclebin_retention_time parameter specifies the retention period of objects in the recycle bin. The objects will be automatically deleted after the retention period expires.
    • DROP on multiple tables cannot be restored.
    • Restoration is not supported when accounts or schemas are deleted in cascading mode.
    • When an account or schema is deleted, if the schema or schema object exists in the recycle bin, the common deletion fails and the schema or schema needs to be deleted in cascading mode.
  • TRUNCATE FLASHBACK constraints
    • After TRUNCATE FLASHBACK is performed, the statistics remain unchanged and are still displayed as 0. You can modify the statistics by manual ANALYZE during off-peak hours (to reduce the impact on performance).
    • RENAME TO supports only the DROP FLASHBACK operation to specify a new name for the retrieval table, but does not support TRUNCATE FLASHBACK.
    • TRUNCATE FLASHBACK cannot span statements that affect the table structure or physical storage. Otherwise, an error is reported. If a statement (such as DDL, DCL, VACUUM FULL, and partition adding/deleting/splitting/merging) that modifies the table structure or affects physical storages are executed between the flashback point and current point, the flashback fails. The error message "ERROR: The table definition of %s has been changed." is displayed when flashback is performed on a table where DDL operations have been performed. The error message "ERROR: recycle object %s desired does not exist" is displayed when flashback is performed on a table where DDL operations, such as changing namespaces and table names, have been performed.

Syntax

TIMECAPSULE TABLE [schema.]table_name TO { CSN expr | TIMESTAMP expr | BEFORE { DROP [RENAME TO table_name] | TRUNCATE } };

Parameters

  • schema

    Specifies a schema containing the table to be flashed back. If this parameter is not specified, the current schema is used.

  • table_name

    Specifies a table name.

  • TO CSN

    Specifies the CSN corresponding to the time point when the table is to be flashed back. expr must be a number representing a valid CSN.

  • TO TIMESTAMP

    Specifies a timestamp value corresponding to the point in time to which you want to flash back the table. The result of expr must be a valid past timestamp (convert a string to a time type using the TO_TIMESTAMP function). The table will be flashed back to a time within approximately 3 seconds of the specified timestamp.

    Note: When the flashback point is too old, the source version cannot be obtained because it is recycled. As a result, the flashback fails and the error message "Restore point too old" is displayed.

  • TO BEFORE DROP

    Retrieves dropped tables and their subobjects from the recycle bin.

  • RENAME TO

    Specifies a new name for the table retrieved from the recycle bin.

  • TO BEFORE TRUNCATE

    Flashes back to the point in time before the TRUNCATE operation.

Examples

  • Flashing data back to a specified time

    You need to set the undo_retention_time parameter to set the retention period of old undo logs.

    Contact the administrator for information about how to use the parameter.
    -- Create a table and insert data into the table.
    gaussdb=# CREATE TABLE tbl_test(c1 int, c2 int);
    gaussdb=# INSERT INTO tbl_test VALUES (1,1),(2,2),(3,3);
    
    -- Query the current time and next_csn on all global nodes.
    gaussdb=# SELECT now();
                  now              
    -------------------------------
     2023-11-27 17:06:34.840698+08
    (1 row)
    
    gaussdb=# SELECT int8in(xidout(next_csn)) FROM gs_get_next_xid_csn();
     int8in 
    --------
      25391
    (6 row)
    
    -- Modify data.
    gaussdb=# UPDATE tbl_test SET c1=111, c2=222 WHERE c1=1;
    
    -- Query data.
    gaussdb=# SELECT * FROM tbl_test;
     c1  | c2  
    -----+-----
     111 | 222
       2 |   2
       3 |   3
    (3 rows)
    -- Flash back the data to the time before the modification. Replace the time based on the actual situation.
    gaussdb=# TIMECAPSULE table tbl_test TO TIMESTAMP to_timestamp('2023-11-27 17:06:34.840698','YYYY-MM-DD HH24:MI:SS.FF');
    
    -- You can also use the following SQL statements:
    gaussdb=# TIMECAPSULE table tbl_test TO CSN 25391;
    gaussdb=# SELECT * FROM tbl_test;
     c1 | c2 
    ----+----
      2 |  2
      3 |  3
      1 |  1
    (3 rows)
    
    -- Delete.
    gaussdb=# DROP TABLE tbl_test;
  • Flashing back data from the recycle bin

    Prerequisites:

    • The enable_recyclebin parameter has been enabled to enable the recycle bin. Contact the administrator for details about how to use the parameter.
    • The recyclebin_retention_time parameter has been set for specifying the retention period of objects in the recycle bin. The objects will be automatically deleted after the retention period expires. Contact the administrator for details about how to use the parameter.
    -- Create a table and insert data into the table.
    gaussdb=# CREATE TABLE tbl_test1(c1 int, c2 varchar(10));
    gaussdb=# INSERT INTO tbl_test1 VALUES (1,'AAA'),(2,'BBB');
    
    -- Delete the table.
    gaussdb=# DROP TABLE tbl_test1;
    
    -- Flash back to the time before the table is deleted.
    gaussdb=# TIMECAPSULE TABLE tbl_test1 TO BEFORE DROP;
    
    -- Query data.
    gaussdb=# SELECT * FROM tbl_test1;
     c1 | c2  
    ----+-----
      1 | AAA
      2 | BBB
    (2 rows)
    
    -- Delete a table. (The PURGE parameter is added to delete the table data from the recycle bin.)
    gaussdb=# DROP TABLE tbl_test1 PURGE;

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback