Help Center/ GaussDB/ User Guide/ Data Restoration/ Restoring a GaussDB Database or Table to a Specific Point in Time
Updated on 2025-08-18 GMT+08:00
View PDF

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

Scenarios

You can use an instance-level automated backup to restore data of specified 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.
  • Data from the time period of the following operations cannot be restored: nodes are being added to an instance, the instance version is being upgraded, or the instance data is being restored.
  • If an instance is faulty or a CN is removed, archive logs cannot be generated, and data from the affected time period cannot be restored.
  • 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, new databases or tables are created on the original instance and the original databases are still available during the restoration. 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.
  • System schemas (for example, public) and their tables cannot be selected for database- and table-level restoration.
  • M-compatible databases and their 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.
  • If transparent data encryption (TDE) is enabled for an instance and the instance version is earlier than V2.0-8.200, database- and table-level restoration is not supported.
  1. Log in to the management console.
  2. Click in the upper left corner and select a region and project.
  3. Click in the upper left corner of the page and choose Databases > GaussDB.
  4. On the Instances page, click the name of the target instance to go to the Basic Information page.
  5. In the navigation pane, choose Backups. On the displayed page, click the Instance Backup or Table Backup tab.

    • On the Instance Backup tab, click Restore to Point in Time to restore data of specified databases or tables.
    • On the Table Backup tab, click Restore to Point in Time to restore data of specified tables.

  6. Set Restoration Method to Create New Instance and click OK.

    • The new DB instance must have the same major version as the original instance when it was backed up. For example, backup data of an instance of version V2.0-3.200.x can only be restored 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.
    • The backup media, CPU architecture, OS type and version, instance type (distributed or centralized), deployment model, replica consistency protocol, and transaction consistency settings of the new instance must be the same as those of the original instance to be restored.
    • If there are M-compatible databases in the instance to be restored, the Tables Names in M-compatible Databases parameter of the new instance must be set to the same value as that of the original instance. The default value is Case-sensitive.
    • 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.

  7. On the displayed page, configure parameters of the new instance, select the table or database data to be restored, and click Create Now.
  8. View the restoration results.

    A new instance that contains the same data as the backup is created. When the instance status changes from Creating to Available, the restoration is complete.

    The new instance is independent from the original one.

  1. Log in to the management console.
  2. Click in the upper left corner and select a region and project.
  3. Click in the upper left corner of the page and choose Databases > GaussDB.
  4. On the Instances page, click the name of the target instance to go to the Basic Information page.
  5. In the navigation pane, choose Backups. On the displayed page, click the Instance Backup or Table Backup tab.

    • On the Instance Backup tab, click Restore to Point in Time to restore data of specified databases or tables.
    • On the Table Backup tab, click Restore to Point in Time to restore data of specified tables.

  6. Set Restoration Method to Restore to Original, select the confirmation check box, and click Next.

    • The instance version and node configuration must be the same as those of the original instance when the backup was created.
    • New databases or tables will be created on the original 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 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 restoration, 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.

  7. On the page that is displayed, select the data to be restored and click Submit.
  8. View the restoration results.

    On the Instances page, when the instance status changes from Restoring to Available, the restoration is complete.

  1. Log in to the management console.
  2. Click in the upper left corner and select a region and project.
  3. Click in the upper left corner of the page and choose Databases > GaussDB.
  4. On the Instances page, click the name of the target instance to go to the Basic Information page.
  5. In the navigation pane, choose Backups. On the displayed page, click the Instance Backup or Table Backup tab.

    • On the Instance Backup tab, click Restore to Point in Time to restore data of specified databases or tables.
    • On the Table Backup tab, click Restore to Point in Time to restore data of specified tables.

  6. Set Restoration Method to Restore to Existing, select the confirmation check box, select an existing instance, and click Next.

    • Databases and tables will be created on the target instance. During the restoration, the databases on the instance can be used properly.
    • You are advised to manually back up data of the selected instance before the restoration.
    • The backup media, CPU architecture, OS type and version, instance type (distributed or centralized), deployment model, replica consistency protocol, transaction consistency, resource specifications, and failover priority settings of the selected instance must be the same as those of the original instance to be restored.
    • An existing instance cannot be selected as the restoration target if it is currently undergoing a full or differential backup. If the backup is not required, you can stop it before proceeding with the restoration.
    • If there are M-compatible databases in the instance to be restored, the Tables Names in M-compatible Databases parameter of the new instance must be set to the same value as that of the original instance. The default value is Case-sensitive.

  7. On the page that is displayed, select the data to be restored and click Submit.
  8. View the restoration results.

    On the Instances page, when the instance status changes from Restoring to Available, the restoration is complete. After the restoration is complete, an instance-level full backup will be automatically triggered.

Parent Topic: Data Restoration