AnalyticDB PostgreSQL teaches you how to implement distributed consistent backup and recovery

1. Background

AnalyticDB for PostgreSQL (ADB PG for short) is a cloud-native data warehouse product created by the Alibaba Cloud database team based on the PostgreSQL kernel (PG for short). ADB PG has unique technical advantages in business scenarios such as real-time interactive data analysis, HTAP, ETL, and BI report generation.

As an enterprise-level data warehouse product, the importance of data security is self-evident. The backup and recovery function is a basic means to ensure data security, and it is also an important guarantee for ADB PG to recover the database in case of emergencies. Backup and recovery, as the name suggests, is to back up the database data so that the data can be restored when necessary to prevent accidents. Currently, the backup and recovery function of ADB PG has been applied in the following user scenarios:

When data is destroyed or the instance is unavailable due to system failure or human error, the instance is restored based on the backup data.
Users need to quickly clone an identical instance based on an existing instance.
Under the premise that the number of nodes remains unchanged, users need to change the specifications of the source instance.
This article will introduce the principles and usage of ADB PG backup and recovery.

II. Introduction

ADB PG is a distributed database that uses the MPP horizontal expansion architecture. An ADB PG instance consists of one or more coordination nodes (Master) and multiple computing nodes (Compute Node). The coordination node is responsible for receiving user requests, formulating distributed execution plans and sending them to the computing nodes, collecting execution results and returning them to the client; the computing nodes are responsible for parallel computing analysis and data storage. Data can be randomly, hashed, or replicated distributed between computing nodes. The following is the architecture diagram of ADB PG:

The physical backup and recovery function of ADB PG, based on the basic backup and log backup of the cluster, can back up the data of each node while the distributed database continues to provide services and ensure data consistency. When necessary, the distributed database can be restored to the time of the backup.

A basic backup is a complete copy of all the data in the database. A basic backup compresses the snapshot of the full data of the cluster and stores it in other offline storage media. The cluster will not block user reading and writing during the basic backup. Therefore, the logs generated during the backup will also be backed up to ensure the integrity of the basic backup.

Log backup (also called incremental backup) refers to backing up the log files generated by the cluster to other offline storage media. The log files record the DML and DDL operations of the user on the database. Through a complete basic backup and continuous log backup, the new cluster can be restored to a certain historical event point, ensuring the data security during this period.

ADB PG can ensure backup and recovery with a minimum RPO of 10 minutes.

3. Principle

Before fully introducing the backup and recovery principles of ADB PG, let's first briefly introduce the PITR (Point in Time Recovery) backup and recovery mechanism of stand-alone PG. The backup and recovery mechanism of ADB PG is based on the PITR principle of stand-alone PG and adds a distributed data consistency guarantee mechanism.

1. PITR mechanism of stand-alone PG

WAL log:

PostgreSQL database will record all changes made by transactions to data (including DDL, DML and other operations) in the WAL (Write Ahead Log) log file. The WAL log file can be regarded as an infinitely growing append-only file. PG will divide the log data into multiple files of fixed size for storage. Each data modification operation of a transaction will be appended to the WAL file and assigned a unique LSN sequence number (Log Sequence Number). When the transaction is committed, the WAL log will be guaranteed to be persistent.

The purpose of these log files is to allow the database to be restored by "replaying" the WAL logs to recover the data that was not persisted when the database crashed but the corresponding transaction has been committed when the database needs to be restored.

Restore Point:

With WAL logs, we can perform "replay" operations, but there is another question: when do we need to replay? This requires a restore point to solve.

The recovery point is equivalent to a mark written in the WAL log, which marks the location of a log. When PG replays the log, it checks whether it has reached this mark point to decide whether to stop the "replay" operation.

The following SQL statement can create a marker point named t1 in the WAL log file:

 postgres=# select pg_create_restore_point( 't1' );LOG: restore point "t1" created at 0/2205780STATEMENT: select pg_create_restore_point( 't1' ); pg_create_restore_point ----------------------- 0/2205780(1 row)  

When the database replays the WAL log sequentially, it will check whether the current log contains this recovery point name. If it does, the replay will be stopped. In addition, PG also supports recovery to any specified time point, transaction number, LSN number, etc.

Basic backup and incremental backup:

A basic backup is a complete copy of the database data. You can use the pg_basebackup tool to perform a basic backup of a stand-alone PG. The backup data can be saved locally or stored in other offline storage media (OSS).

 $ pg_basebackup -D pg_data_dir/ -p 6000NOTICE: pg_stop_backup complete, all required WAL segments have been a

Incremental backup refers to backing up the generated WAL log files. In PG, you can use the database parameter archive_command to specify how to back up WAL log data. When PG generates a WAL log file, it will try to back up and archive the log file by executing the archive_command command. For example, the following command will send the log file to the specified OSS.

archive_command="ossutil cp %p oss://bucket/path/%f"

Full backup and incremental backup of stand-alone PG

It should be noted that the reading and writing of the database will not be blocked during the basic backup, so the WAL logs corresponding to the data updates during the backup period also need to be backed up to ensure data consistency during recovery.

PITR Recovery:

When you need to restore the database, first download the base backup data, then use the base backup to start the cluster, then download the log file backup, and "replay" it to the specified recovery point to restore the database. In a stand-alone PG, the target of the specified recovery point can be the transaction number, timestamp, WAL sequence number (LSN) and a recovery point name.

(II) Distributed Consistency Backup and Recovery Mechanism of ADB PG

As a distributed database, ADB PG uses two-phase transaction commit to manage distributed transactions. If you copy the PITR mechanism of a single-machine PG, data inconsistency will occur. For example, in the following scenario: distributed transactions are assigned in the order of A, B, and C, but due to various reasons (such as network delay, node load, explicit commit, etc.), the order of transaction commit in the distributed mode may be different on each node, as shown in the following figure:

Master submits in the order of A, B, and C
Compute Node 1 submits in the order of A, C, and B
Compute Node 2 submits in the order of B, C, and A

If a recovery point is created during the process, and if you specify to restore to that recovery point during recovery, it is obvious that the states of the nodes in the cluster will be inconsistent after the recovery.

Two-phase transaction commit lock and consistency recovery point:

To solve the above problems, we introduced a two-phase transaction commit lock. Distributed transaction commits acquire this lock in SHARED mode, while creating a recovery point requires acquiring this lock in EXCLUSIVE mode. Therefore, if there are distributed transactions in the cluster waiting to be committed on each node, the cluster must wait until all distributed transactions on all nodes are committed before creating a recovery point.

This fundamentally solves the problem of inconsistent data during recovery caused by creating a recovery point while a distributed transaction is still being committed. After introducing the two-phase commit lock mechanism, we can ensure that the states of each node corresponding to the created recovery point are consistent, so we call the recovery point created in ADB PG a consistent recovery point.

Distributed backup and recovery process:

With the transaction commit lock and consistent recovery point, we can safely back up each ADB PG node and create consistent recovery points without worrying about inconsistent node status.

ADB PG backups are also divided into basic backups and log backups (also called incremental backups). A basic backup is a complete copy of each node in the cluster. ADB PG will concurrently back up the compute nodes and coordination nodes, and stream the backup data to offline storage (such as OSS). During the basic backup, the read and write services of the cluster will not be blocked. Therefore, if the user has written and updated data during the basic backup, the WAL log corresponding to the data change also needs to be backed up. As shown in the following figure, ADB PG will copy the data for each node in parallel and upload the data to OSS in a streaming manner.

ADB PG basic backup process

ADB PG log backup is a backup of the WAL logs generated by the computing nodes and coordination nodes in the cluster. Each node will dump the WAL logs it generates to offline storage (such as OSS). At the same time, the cluster will periodically create consistent recovery points and back up the WAL logs containing the consistent recovery points.

When you need to restore a new cluster, you need to use both the base backup and the log backup, and first create a recovery instance with the same number of nodes as the original instance. Each node pulls the specified base backup to the local in parallel. After that, each node pulls the WAL log backup file it needs and replays it locally until it stops at the specified consistency recovery point. In the end, we can get a new cluster and ensure that the data and status are consistent with the data and status of the source instance at the consistency recovery point. The recovery process is shown in the figure below:

IV. Use

(1) Console backup related information

View the basic backup setUsers can view the basic backup data of the database on the "Backup and Restore" page of the instance console. Currently, the basic backup data is saved on OSS and the default retention period is 7 days.

Each row in the table represents a basic backup data, and records the backup start time, end time, backup status (success/failure), backup data size, and consistency time point. The consistency time point means that this basic backup data can restore the cluster to that historical time point and put the database in a consistent state.

Viewing consistent recovery points

A consistent recovery point refers to a historical time point that the cluster can be restored to. Users can view all the recovery points of the current instance on the "Recovery Point" page of the backup and recovery page.

Each row in the table represents a consistent recovery point and records the timestamp of the recovery point, indicating that the recovery point can restore the cluster to this historical time point.

View the log file list

The log file records all changes to the database. When the cluster is restored, the corresponding log file will be used to restore the cluster to a consistent state. The log files of the current user cluster recovery are all saved on OSS. Users can view the log file list in "Log Backup" on the backup and recovery page.

View backup policy

The backup strategy refers to the cycle and time period for instance backup, the frequency of creating consistent recovery points, and the number of days for data backup retention, etc.

Users can view and modify backup policies in the Backup Settings of backup and recovery.

Modify the backup policy

Click the "Modify backup configuration" button to modify the backup strategy.

(2) Instance recovery steps

First, view the data on the source instance

Enter the recovery page

Users can click Restore in the instance list, data backup list, or recovery point list on the console to enter the instance recovery page;

The recovery page is as follows:

The sales page for restoring an instance is similar to the page for purchasing an instance, but with the following restrictions:

1. The current recovery instance is the master number and you must select 1

2. The number of instance segments (computer nodes) selected must be consistent with the source instance

3. The storage space of the selected instance must be greater than or equal to the source instance

Select a recovery point in time. In the drop-down list of "Clone source backup set" on the recovery page, select the historical point in time to which you want to restore the instance, that is, specify a consistent recovery point.

Click to buy

After the user clicks Buy, the process is the same as purchasing a new instance. After the instance is created, the user needs to wait for it to be created. Then, the newly restored instance can be seen in the console.

Restored new instance

Check the data on the restored instance and you can see that the data is exactly the same as the source instance.

V. Conclusion

Backup and recovery are very important for ADB PG to ensure data security. The current backup and recovery function has been applied to multiple user scenarios and guarantees an RPO of at least 10 minutes. In the future, the ADB PG backup and recovery function will continue to optimize backup and recovery performance, support differentiated backup, support more storage media, improve user experience, and provide users with more functions, performance, and cost optimization.