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

Updated on 2025-09-05 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.
In B-compatible mode, tables with the on update timestamp column do not support table-level physical 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.
If parallel restoration is enabled, all primary and standby replicas will simultaneously download backup data from the storage server. Compared to serial restoration, this process demands a multiple of the bandwidth from the storage server equal to the number of replicas in each shard. If your instance has more than five shards, consult O&M personnel about the available bandwidth of the OBS server before deciding whether to enable parallel restoration. This will prevent a decrease in restoration speed due to bandwidth exhaustion.
Centralized instances support only parallel restoration.
Parallel restoration cannot be enabled if the DB engine version is earlier than V2.0-1.4.
Restoring data to existing instances is only available for authorized users. To apply for the permissions needed, submit a service ticket.

Restoring a Database or Table to a Specific Point in Time

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 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.
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 Table 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.

On the displayed page, configure parameters of the new instance, select the table or database data to be restored, and click Next.

**Table 1** Basic information
Parameter	Description
Billing Mode	GaussDB provides yearly/monthly billing and pay-per-use billing. Yearly/Monthly: You pay upfront for the amount of time you expect to use the DB instance for. You will need to make sure you have a top-up account with a sufficient balance or have a valid payment method configured first. For distributed instances using the combined deployment model, yearly/monthly billing is only available to authorized users. To apply for the permissions needed, submit a service ticket. Pay-per-use: You can start using the DB instance first and then pay as you go. Pricing is listed on a per-hour basis, but bills are calculated based on the actual usage duration.
DB Instance Name	The instance name is case-sensitive, must start with a letter, and can contain 4 to 64 characters. Only letters, digits, hyphens (-), and underscores (_) are allowed.
Failover Priority	This parameter is only available for distributed instances. This parameter is only available for authorized users. To apply for the permissions needed, submit a service ticket. The default value is Reliability. For details about how to change the failover priority for an existing instance, see Changing Failover Priority. Reliability: Data consistency is prioritized during a failover. This is recommended for applications whose highest priority is data consistency. Availability: Database availability is given priority during a failover. This is recommended for applications that require their databases to provide uninterrupted online services. If Availability is selected, exercise caution when modifying the following database parameters. For details about how to modify parameters, see Modifying Instance Parameters. recovery_time_target: If the value of this parameter is changed, the instance will experience frequent forced failovers. Contact technical support before modifying this parameter. audit_system_object: If the value of this parameter is changed, DDL audit logs will be lost. Contact technical support before modifying this parameter.
AZ	An AZ is a physical region where resources have their own independent power supply and networks. AZs are physically isolated but interconnected through an internal network. A DB instance can be deployed in one AZ or three AZs. Three AZs are recommended.
Time Zone	Select a time zone according to the region hosting your DB instance when you buy the instance.

**Table 2** Specifications and storage
Parameter	Description
Instance Specifications	CPU and memory specifications of the instance. Different instance specifications support different numbers of database connections. For details, see Instance Specifications.
Storage Space	The storage space contains the file system overhead required for inodes, reserved blocks, and database operations. The storage for a single shard starts from 40 GB and can be increased at a step of 4 GB.
Disk Encryption	Disable: Encryption is disabled. Enable: Encryption is enabled, which improves data security but affects system performance. Key Name: If disk encryption is enabled, you need to select or create a key. NOTE: When creating a pay-per-use instance with disk encryption enabled, ensure that your account has been granted the following action: kms:cmk:createGrant When you create a yearly/monthly instance with disk encryption enabled, if the KMS key shared by RAM is used for the first time, create an agency as prompted. When using the KMS key shared by RAM to create an instance, ensure that your account has also been granted the following actions: iam:agencies:listAgencies iam:agencies:createAgency iam:roles:listRoles iam:permissions:listRolesForAgency iam:permissions:grantRoleToAgency kms:cmk:createGrant If a shared KMS key is used, the corresponding CTS event is createGrant. Only the key owner can receive this event.

**Table 3** Network and database configuration
Parameter	Description
VPC	A virtual network where your GaussDB instances are located. A VPC isolates networks for different workloads. You need to create or select the required VPC. If no VPC is available, GaussDB allocates a default VPC for you. After the GaussDB instance is created, the VPC cannot be changed. For details about how to create a VPC, see Creating a VPC. With VPC sharing, you can also use a VPC and subnet shared by another account. VPC owners can share the subnets in a VPC with one or multiple accounts through Resource Access Manager (RAM). This allows for more efficient use of network resources and reduces O&M costs. For more information about VPC subnet sharing, see VPC Sharing in the Virtual Private Cloud User Guide.
Subnet	A subnet provides dedicated network resources that are logically isolated from other networks for network security. Subnets take effect only within a specific AZ. Dynamic Host Configuration Protocol (DHCP) is enabled by default for subnets in which you plan to create GaussDB instances and cannot be disabled. GaussDB supports automatic IP address allocation during instance creation. NOTE: By default, a subnet supports up to 256 IP addresses. A distributed instance can require up to 1,286 IP addresses. You are advised to use a subnet that can provide 2,048 IP addresses.
Security Group	Controls access to and from an instance. When you buy an instance, the selected security group must meet the following requirements: When buying a distributed instance, ensure that the TCP ports in the inbound rule include the following: 40000-60480, 20050, 5000-5001, 2379-2380, 6000, 6001, 6500, and <database_port>-(<database_port> + 100). (For example, if the database port is 8000, the security group must contain ports 8000 to 8100.) Additionally, ensure that the outbound security group rules allow all outbound traffic. When buying a centralized instance, ensure that the TCP ports in the inbound rule include the following: 20050, 5000-5001, 2379-2380, 6000, 6500, and <database_port>-(<database_port> + 100). (For example, if the database port is 8000, the TCP ports for the security group must include 8000-8100.) The security group enhances security by controlling access to GaussDB from other services. Ensure that the security group you selected allows your client to access the instance. If you do not need to specify a security group when creating an instance, you can submit a service ticket. If no security group is available, GaussDB allocates a default security group for you.
Database Port	The port is used by applications to access the database. Value range: 1024 to 39989. Default value: 8000. The following ports are used by the system and cannot be used: 2378 to 2380, 2400, 4999 to 5001, 5100, 5500, 5999 to 6001, 6009 to 6010, 6500, 8015, 8097, 8098, 8181, 9090, 9100, 9180, 9187, 9200, 12016, 12017, 20049, 20050, 21731, 21732, 32122 to 32126, and 39001.
Single Private IP Address	Specifies whether to enable the single private IP address policy. If this policy is enabled, only one private IP address is assigned to an instance and is bound to the primary node. The private IP address does not change after a primary/standby switchover. If this policy is disabled, each node is bound to a private IP address, and the private IP address changes after a primary/standby switchover. The constraints on the single private IP address policy are as follows: This policy is only available for centralized instances of version V2.0-3.207 or later. This policy is configurable only at the time of instance creation and cannot be changed afterward.

**Table 4** Restoration scenarios
Scenario	Description
Restoring tables	If you use an instance-level backup to restore data to tables, you can select Time-specific Tables or Recent Tables for the Tables field. 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. If you use a table-level backup 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.)
Restoring databases	If you use an instance-level backup to restore data to databases, you can select Time-specific Databases or Recent Databases for the Databases field.

**Table 5** Database configuration
Parameter	Description
Administrator	Database administrator. The default username is root.
Administrator Password	Enter a strong password and periodically change it to improve security, preventing security risks such as brute force cracking. The password must contain: 8 to 32 characters. At least three types of the following: uppercase letters, lowercase letters, digits, and special characters. Supported special characters: ~!@#%^*-_=+?, Keep the password secure. Password retrieval is not supported.
Confirm Password	Enter the administrator password again.
Parameter Template	A template of parameters for creating an instance. The template contains engine configuration values that are applied to one or more instances. After creating an instance, you can modify the parameter template.
Enterprise Project	If the instance has been associated with an enterprise project, select the target project from the Enterprise Project drop-down list. You can also go to the Enterprise Project Management console to create a project. For details, see Enterprise Management User Guide.
Tag	This parameter is optional. Adding tags helps you better identify and manage your DB instances. Each instance can have up to 20 tags. If your organization has configured tag policies for GaussDB, add tags to instances based on the policies. If a tag does not comply with the policies, instance creation may fail. Contact your organization administrator to learn more about tag policies.

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 of the original one.

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 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.
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.

On the page that is displayed, select the data to be restored and click Submit.

**Table 6** Restoration scenarios
Scenario	Description
Restoring tables	If you use an instance-level backup to restore data to tables, you can select Time-specific Tables or Recent Tables for the Tables field. 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. If you use a table-level backup 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.)
Restoring databases	If you use an instance-level backup to restore data to databases, you can select Time-specific Databases or Recent Databases for the Databases field.

View the restoration results.

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

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 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.
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 selected instance must have the same engine type, engine version, OS, resource type, topology, CPU architecture, case sensitivity of M-compatible table names (for a centralized instance), replica consistency (for a centralized instance), and transaction consistency (for a distributed instance) settings as the original instance had when it was backed up.
- The storage capacity of the selected instance must be at least equal to what the original instance had when it was backed up. (For an independently deployed distributed instance whose shards have different storage: Original instance storage = Maximum shard storage x Shard quantity; Target instance storage = Minimum shard storage x Shard quantity.)
- The specifications of the selected instance must be at least equal to what the original instance had when it was backed up.
- The selected instance cannot contain read replicas.
- 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 Table 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.

On the page that is displayed, select the data to be restored and click Submit.

**Table 7** Restoration scenarios
Scenario	Description
Restoring tables	If you use an instance-level backup to restore data to tables, you can select Time-specific Tables or Recent Tables for the Tables field. 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. If you use a table-level backup 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.)
Restoring databases	If you use an instance-level backup to restore data to databases, you can select Time-specific Databases or Recent Databases for the Databases field.

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.