Overview

For database security purposes, openGauss provides three backup types, multiple backup and restoration solutions, and data reliability assurance mechanisms.

Backup and restoration can be classified into logical backup and restoration and flashback restoration.

Logical backup and restoration: backs up data by logically exporting data. This method can dump data that is backed up at a certain time point, and restore data only to this backup point. Logical backup does not back up data processed between failure occurrence and the last backup. It is suitable when data rarely changes. If such data is damaged due to misoperations, it can be quickly restored through logical backup. To restore all the data in a database through logical backup, rebuild a database and import the backup data. Logical backup is not recommended for databases requiring high data availability because it takes a long time for data restoration. Logical backup is a major approach to migrate and transfer data because it can be performed on any platform.

Flashback restoration: This function is used to restore dropped tables from the recycle bin. Like in a Window OS, dropped tables are stored in the recycle bin of databases. The MVCC mechanism is used to restore data to a specified point in time or change sequence number (CSN).

The three data backup and restoration solutions supported by openGauss are as follows. Methods for restoring data in case of an exception differ for different backup and restoration solutions.

Table 1 Comparison between logical backup and restoration and flashback restoration

Backup Type	Application Scenario	Media Supported	Tool	Restoration Time	Advantage and Disadvantage
Logical backup and restoration	Small volume of data needs to be processed. You can back up a single table, multiple tables, a single database, or all databases. The backup data needs to be restored using gsql or gs_restore. When the data volume is large, the restoration takes a long time.	Disk SSD	gs_dump	It takes a long time to restore data in plain-text format. It takes a moderate time to restore data in archive format.	This tool is used to export database information. Users can export a database or its objects (such as schemas, tables, and views). The database can be the default postgres database or a user-specified database. The exported file can be in plain-text format or archive format. Data in plain-text format can be restored only by using gsql, which takes a long time. Data in archive format can be restored only by using gs_restore. The restoration time is shorter than that for the plain-text format.
gs_dumpall	It takes a long time to restore data.	This tool is used to export all information of the openGaussdatabase, including the data of the default postgres database, data of user-specified databases, and global objects of all openGaussdatabases. Only data in plain-text format can be exported. The exported data can be restored only by using gsql, which takes a long time.
Flashback restoration	Applicable to: 1. A table is deleted by mistake. 2. Data in a table needs to be restored to a specified time point or CSN.	None	You can restore a table to the state at a specified time point or before the table structure is deleted. The restoration takes a short time.	The flashback technology can selectively and efficiently undo the impact of a committed transaction and recover from a human error. Before the flashback technology is used, the committed database modification can be retrieved only by means of restoring backup or point-in-time recovery (PITR). The restoration takes several minutes or even hours. After the flashback technology is used, it takes only seconds to restore the committed data before the database is modified. The restoration time is irrelevant to the database size. Flashback supports two restoration modes: Multi-version data restoration based on MVCC: This method is applicable to the query and restoration of data that is deleted, updated, or inserted by mistake. You can configure the retention period of the old version and run the corresponding query or restoration command to query or restore data to a specified time point or CSN. Recovery based on the recycle bin (similar to that on Windows OS): This method is applicable to the restoration of tables that are dropped or truncated by mistake. You can configure the recycle bin switch and run the corresponding restoration command to restore the tables that are dropped or truncated by mistake.

Backup Type

Application Scenario

Media Supported

Tool

Restoration Time

Advantage and Disadvantage

Logical backup and restoration

Small volume of data needs to be processed.

You can back up a single table, multiple tables, a single database, or all databases. The backup data needs to be restored using gsql or gs_restore. When the data volume is large, the restoration takes a long time.

Disk

SSD

gs_dump

It takes a long time to restore data in plain-text format. It takes a moderate time to restore data in archive format.

This tool is used to export database information. Users can export a database or its objects (such as schemas, tables, and views). The database can be the default postgres database or a user-specified database. The exported file can be in plain-text format or archive format. Data in plain-text format can be restored only by using gsql, which takes a long time. Data in archive format can be restored only by using gs_restore. The restoration time is shorter than that for the plain-text format.

gs_dumpall

It takes a long time to restore data.

This tool is used to export all information of the openGaussdatabase, including the data of the default postgres database, data of user-specified databases, and global objects of all openGaussdatabases.

Only data in plain-text format can be exported. The exported data can be restored only by using gsql, which takes a long time.

Flashback restoration

Applicable to:

1. A table is deleted by mistake.

2. Data in a table needs to be restored to a specified time point or CSN.

None

You can restore a table to the state at a specified time point or before the table structure is deleted. The restoration takes a short time.

The flashback technology can selectively and efficiently undo the impact of a committed transaction and recover from a human error. Before the flashback technology is used, the committed database modification can be retrieved only by means of restoring backup or point-in-time recovery (PITR). The restoration takes several minutes or even hours. After the flashback technology is used, it takes only seconds to restore the committed data before the database is modified. The restoration time is irrelevant to the database size.

Flashback supports two restoration modes:

Multi-version data restoration based on MVCC: This method is applicable to the query and restoration of data that is deleted, updated, or inserted by mistake. You can configure the retention period of the old version and run the corresponding query or restoration command to query or restore data to a specified time point or CSN.
Recovery based on the recycle bin (similar to that on Windows OS): This method is applicable to the restoration of tables that are dropped or truncated by mistake. You can configure the recycle bin switch and run the corresponding restoration command to restore the tables that are dropped or truncated by mistake.

While backing up and restoring data, take the following aspects into consideration:

Ensure that the backup impact on services is within an acceptable scope.
Database restoration efficiency.
To minimize the impact of database faults, minimize the restoration time to maximize the restoration efficiency.
Data restorability
If a database fails, try to minimize the data loss.
Database restoration cost

There are many factors that need to be considered while you select a backup policy on the live network, such as backup objects, data volume, and network configuration. Table 2 lists available backup policies and applicable scenarios for each backup policy.

Table 2 Typical scenarios of backup policies

Backup Policy	Key Performance Factor	Typical Data Volume	Performance Specifications
Database instance backup	Data size Network configuration	Data: PB level Objects: about 1 million	Backup: Data transfer rate on each host: 80 Mbit/s (NBU/EISOO + Disk) Disk I/O rate (SSD/HDD): about 90%
Table backup	Schema where the table to be backed up resides Network configuration (NBU)	Data: 10 TB level	Backup: depends on query performance rate and I/O rate. Note: For multi-table backup, the backup time is calculated as follows: Total time = Number of tables x Starting time + Total data volume/Data backup speed In which: The starting time of a disk is about 5s. The starting time of an NBU is longer than that of a disk (depending on the NBU deployment). The data backup speed is about 50 MB/s on a single node. (The speed is evaluated based on the backup of a 1 GB table from a physical host to a local disk.) The smaller the table is, the lower the backup performance will be.

Backup Policy

Key Performance Factor

Typical Data Volume

Performance Specifications

Database instance backup

Data size
Network configuration

Data: PB level

Objects: about 1 million

Backup:

Data transfer rate on each host: 80 Mbit/s (NBU/EISOO + Disk)
Disk I/O rate (SSD/HDD): about 90%

Table backup

Schema where the table to be backed up resides
Network configuration (NBU)

Data: 10 TB level

Backup: depends on query performance rate and I/O rate.

Note:

For multi-table backup, the backup time is calculated as follows:

Total time = Number of tables x Starting time + Total data volume/Data backup speed

In which:

The starting time of a disk is about 5s. The starting time of an NBU is longer than that of a disk (depending on the NBU deployment).
The data backup speed is about 50 MB/s on a single node. (The speed is evaluated based on the backup of a 1 GB table from a physical host to a local disk.)

The smaller the table is, the lower the backup performance will be.