On this page

Show all

Help Center/ GaussDB/ User Guide/ Data Restoration/ Restoring a GaussDB Database or Table to a Specific Point in Time

Restoring a GaussDB Database or Table to a Specific Point in Time

Updated on 2025-02-17 GMT+08:00

View PDF

Scenarios

You can use an instance-level automated backup to restore data in databases or tables to a specified point in time.

You can use a table-level automated backup to restore data in tables to a specified point in time.

You can restore backup data to the original GaussDB instance, an existing instance, or a new one.

Precautions

To use this function, your DB instance cannot be a single-replica instance and its version must be V2.0-3.200 or later.
If nodes are being added, instance version is being upgraded, or data is being restored to an existing instance, the data cannot be restored a specific point in time.
If a DB instance is faulty or a CN is removed, archive logs cannot be generated and the instance cannot be restored to a specific point in time.
If you restore backup data to a new DB instance:
- The DB engine and major version are the same as those of the original DB instance and cannot be changed.
- The administrator password needs to be reset.
If you restore backup data to the original DB instance, a new database or table is created and the original database or table is still available. The archive logs are normal.
When a DB instance is deleted, all archive logs are deleted by default and cannot be retained. After an instance is deleted, it cannot be rebuilt or restored to any point in time.
The table names in a given database and schema as well as the database names must be different before and after the restoration. If they are same, you need to rename the tables and databases that the data is restored to.
If ALTER DATABASE SET TABLESPACE and ALTER TABLE SET TABLESPACE are executed in the original instance, table-level data can be restored only after a differential backup or full backup is triggered in the original instance.
A maximum of 100 databases or tables can be restored at the same time. If there are more than 100 databases or tables, you are advised to use instance-level restoration.
Table-level restoration does not support column-store tables, user-defined tables, synonym tables, temporary tables (including global tables), unlogged tables, tables of private users, and tables of ledger databases.
Currently, only heap data, index data, and auto-increment column data related to tables can be restored. Other data related to tables, such as foreign key information, triggers, and SQL throttling rules, cannot be restored.
After table-level restoration, row-level access control and dynamic masking information will be lost.
System databases (postgres, template0, template1, templatem, templatea, and templatepdb) and their tables cannot be selected for database- and table-level restoration.
The system schema (for example, public) and its tables cannot be selected for database- and table-level restoration.
The M-compatible database and its tables cannot be selected for database- and table-level restoration.
Single-replica instances do not support database- and table-level backup and restoration.
PITR based on snapshots does not support database- or table-level restoration.
Data cannot be restored to an existing instance for which a DR relationship has been established.
To apply for the permission needed for restoring data to existing instances, submit an application by choosing Service Tickets > Create Service Ticket in the upper right corner of the management console.

Procedure

Log in to the management console.
Click in the upper left corner and select a region and project.
Click in the upper left corner of the page and choose Databases > GaussDB.
On the Instances page, click the name of the target instance to go to the Basic Information page.
In the navigation pane, choose Backups. On the displayed page, click the Instance Backup or Table Backup tab.
- On the Instance Backup page, click Restore to Point in Time to restore data of specified databases or tables.
- On the Table Backup page, click Restore to Point in Time to restore data of specified tables.
Click OK.

When Restore Data From is set to Tables, a maximum of 200 tables can be displayed for Backup Tables by default. If the required table is not displayed, click the + icon in the upper right corner to add a custom table. (You can also use this method to add required tables if the instance is faulty or deleted.)
- Restoring data to a new DB instance:
  - The original and new DB instances must have the same major version. For example, backup data can only be restored from version V2.0-3.200.x to version V2.0-3.200.y.
  - The storage space of the new instance is the same as that of the original DB instance by default and the new instance must be at least as large as the original DB instance.
  - The administrator password needs to be reset.
  - By default, the instance specifications of the new instance are the same as those of the original instance. To change the instance specifications, ensure that the instance specifications of the new instance are at least those of the original instance.
  - Parameters of the original instance will not be automatically restored to the new instance. To use the original parameter settings, select the required parameter template for Parameter Template when creating an instance for restoring data to a new instance. After the new instance is created, click the instance name and manually change the parameter values on the Parameters page.
  Configure the basic information about the new instance and click Apply Now.
- Restoring data to the original DB instance:
  - The instance version and node configuration must be the same as those of the original DB instance.
  - New databases or tables are created on the original DB instance. The databases to be restored are available during the restoration.
  - After a database (for example, db1) is restored to the original instance, you need to wait for the DB instance to automatically perform a full backup or differential backup before restoring the data of db1 using other restoration processes. The time to wait depends on the backup policy. During point-in-time recovery, if you select a time point that is later than when the current database-level restoration is complete but earlier than when the next backup is performed, db1 cannot be restored.
  - You are advised to manually back up data before the restoration.
- Restoring data to an existing DB instance:
  - Databases and tables will be created on the target instance. During the restoration, the databases can be used properly.
  - You are advised to manually back up data of the selected instance before the restoration.
  - To restore data to an existing instance, the backup media, instance type, deployment model, replica consistency protocol, transaction consistency, CPU architecture, resource specifications, and failover priority of the target instance must be the same as those of the source instance.
  - For instances of version V2.0-8.200.0 or later, an instance backup can be restored to an existing instance with a different compute resource type, deployment model (as long as it has the same number of DN shards and is not a single-node instance), and different specifications (with different vCPUs and memory).
    NOTICE:
    
    You can restore an instance with high specifications to one with low specifications; the reverse is also supported. To restore an instance with high specifications to one with low specifications, ensure that the specifications of the destination instance are greater than or equal to 8 vCPUs | 32 GB.
  - If TDE is enabled for the source instance, table-level data cannot be restored to an existing instance.
- If you use an instance-level backup to restore data to databases, you can select Time-specific Databases or Recent Databases on the Restore to Point in Time page. If you use an instance-level backup to restore data to tables, you can select Time-specific Tables or Recent Tables on the Restore to Point in Time page.
  There are some precautions to take when restoring data to databases or tables. The following uses table restoration as an example:
  - Operations such as table creation may have been performed on the instance during the period from the time when the backup was created to the current time. Therefore, the tables listed when Time-specific Tables is selected may be different from those listed when Recent Tables is selected. If you attempt to restore a table in Recent Tables to a specified time point, the restoration may fail.
    - Time-specific Tables indicates the table information stored when the backup was created.
    - Recent Tables indicates the most recent table information of the current instance.
  - If some tables have been deleted from the instance, specific tables may not be available regardless of whether Time-specific Tables or Recent Tables is selected. If you are sure that a particular table is available when the backup was created, click the + icon in the upper right corner to add the table.
View the restoration results.
- Restoring data to a new DB instance
  A new instance is created using the backup data. The instance status changes from Creating to Available.
  
  The new instance is independent from the original one.
- Restoring data to the original DB instance
  On the Instances page, the status of the DB instance changes from Restoring to Available.
- Restoring data to an existing DB instance
  On the Instances page, the status of the DB instance changes from Restoring to Available. After the restoration is complete, an instance-level full backup will be automatically triggered.

Parent Topic: Data Restoration

Previous topic: Restoring a GaussDB Instance to a Specific Point in Time

Next topic: Parameter Management

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot