On this page

Show all

Help Center/ GaussDB/ User Guide/ Data Restoration/ Restoring a GaussDB Database or Table Using a Backup File

Restoring a GaussDB Database or Table Using a Backup File

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

View PDF

Scenarios

You can use an instance-level automated or manual backup to restore data of specified databases or tables to the point in time when the backup was created. This operation restores data in specified databases or tables.

You can use a table-level automated or manual backup to restore data in tables to the point in time when the backup was created. This operation restores data only in specified tables.

Data can be restored to a new DB instance, an existing DB instance, or the original DB instance.

Constraints

Keep your account balance above zero so that backups can be restored to a new instance.
Only DB instances of version V2.0-3.200 or later can be restored.
Restoration will fail if the instance is in the Abnormal, or Storage full state.
Backup data cannot be restored across major versions. For example, backup data can only be restored from version V2.0-3.200.x to version V2.0-3.200.y.
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.
The table names in a given database and schema as well as the database names must be different before and after the 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.
Snapshot-based backups cannot be used to restore databases or tables.
Data cannot be restored to an existing instance for which a DR relationship has been established.
To enable the function of restoring databases and tables from backup files 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.
In the navigation pane, choose Backups. On the Backups page, locate the backup to be restored and click Restore in the Operation column.
- If the selected backup is an instance-level backup, you can restore data to specified databases or tables.
- If the selected backup is a table-level backup, you can only restore data to specified tables.
Alternatively, on the Instances page, click the instance name to go to the Basic Information page. In the navigation pane on the left, choose Backups. Click Instance Backup or Table Backup under Full Backups. On the displayed page, select the backup to be restored and click Restore in the Operation column.
Click OK.

When you attempt to restore data 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.)

NOTE:

In addition to table-level full backups and incremental backups, the system backs up incremental log files to ensure data consistency. It takes some time to back up and upload incremental log files (The time depends on the network and OBS traffic control). Note that the backup completion time does not represent the data consistency point that can be specified when this backup set is used to restore data. (Generally, the data consistency point is within several minutes before the backup completion time.) If you have strict requirements on data consistency after restoration, restore data to a specified point in time.
- 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 specifications of the new instance are the same as those of the original instance. You can configure higher specifications for the new instance. For instances of version V2.0-8.200 or later, an instance backup can be restored to an instance with lower specifications than the original instance. (Minimum specifications for the destination instance: 8 vCPUs | 32 GB)
  - 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.
  - Databases and tables will be created on the target DB instance. During the restoration, the source database can be used properly.
  - You are advised to manually back up data before 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.
- 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.
  - Manually backing up data of the selected DB instance before the restoration.
View the restoration results.
- Restoring data to a new DB instance
  A new instance is created. The backup data is stored in the instance database or table. 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 instance
  On the Instances page, the status of the DB instance changes from Restoring to Available.