Backup and restore using pgBackRest

Eugenia Kuzina

Overview

ADPG uses the functionality of the pgBackRest utility to create backups of the cluster and restore it from the backups. This solution easily scales for large databases and significant workloads. All backup actions are executed on behalf of the postgres user.

A backup is a consistent copy of a database cluster that can be used to recover the cluster from a hardware failure, to perform Point-In-Time Recovery (PITR), or to bring up a new standby. The pgBackRest utility can create the following types of backups:

  • Full backup — pgBackRest copies the entire contents of a cluster to the backup. The first backup of the database cluster is always a full backup. pgBackRest can restore a cluster directly from a full backup. The full backup does not depend on any files outside it.

  • Differential backup — pgBackRest only copies database files that have changed since the last full backup. To restore a differential backup, pgBackRest copies all files from the current differential backup and appropriate unchanged files from the previous full backup. A differential backup takes up less disk space but requires a valid full backup to be restored.

  • Incremental backup — pgBackRest only copies database files that have changed since the last backup, which can be another incremental backup, a differential backup, or a full backup. Due to the fact that incremental backups only include files changed since the prior backup, they are generally much smaller than full or differential backups. The incremental backup also depends on other backups. All prior incremental, differential, and full backups must be valid to perform a restore of the incremental backup. The backup sequence may not include differential backups.

Backup configuration

To enable creating backups, switch on the Enable backups toggle button displayed on the Primary configuration tab of the ADPG service (Clusters → ADPG cluster → Services → ADPG → Primary configuration). When this toggle button is enabled, you can expand the corresponding node and specify the pgBackRest settings described below.

Enable backups
Enable backups
NOTE
After you set all required settings on the Primary configuration tab, click Save to save the current configuration and run the Reconfigure & Restart action if you changed the Enable backups parameter. If Enable backups retained its value and only other options were changed, it is enough to execute the Reconfigure & Reload action.

In pgBackRest settings, each PostgreSQL/ADPG cluster to back up has its configuration called stanza. A stanza defines where a cluster is located, how it should be backed up, archiving options, and others. In ADPG, a stanza is created with the name specified in the Backup stanza field and the added prefix: adpg<version>-. For example, if the specified name is mystanza, the result — adpg14-mystanza.

You can also set the Archive timeout(s) parameter that specifies the amount of time (in seconds) to wait before forcing a switch to the next WAL file. The default value is 1800. It is helpful when the database is lightly loaded and the WALs are slowly filling up, to ensure that WAL archiving does not stop for a long period.

Backup storages

Use Backup repo to specify the path to the repository where pgBackRest stores backups and archives WAL segments. The repository must exist and be accessible to all ADPG nodes in the cluster.

To determine the storage type, specify the Repo type option. You can use the following types of storage to save backups:

  • posix — a storage that is compatible with POSIX. This is the default value.

  • s3 — an S3 storage. If you select the S3 type, specify settings located in the S3 configuration section. The S3 settings are listed in the table below. The bucket used to store the repository must be created in advance. The repository can be located in the bucket root (/) but it is usually best to place it in a subfolder. So, object store logs or other data can also be stored in the bucket without conflicts.

  • cifs — a storage that supports the CIFS protocol.

NOTE
You need to configure posix and cifs storages yourself for the cluster to work correctly. ADPG does not check the posix and cifs configuration and mounted directories.
Parameter Description

S3 URI style

An S3 URI style. The following URI styles are supported:

  • host — <bucket>.<endpoint>.<host>. For example, mybucket.us-east-1.amazonaws.com.

  • path — <endpoint>.<host>/<bucket>. For example, us-east-1.amazonaws.com/mybucket.

The default value is path

S3 Region

An S3 repository region where the bucket was created

S3 Bucket

An S3 bucket used to store the repository with backups

S3 Endpoint

An S3 repository endpoint. The endpoint should be valid for the specified region

S3 Key

An S3 repository access key used to access the bucket

S3 Key Secret

An S3 repository secret access key used to access this bucket

The backup retention

You can specify backup retention options. It is possible to choose whether a time (days) or a number will be used to determine which backups are expired. Retention full type determines whether the Retention full setting represents a time period (days) or a number of full backups to keep. The following Retention full type values are available:

  • count — the number of backup to keep. The default value.

  • time — full backups older than the Retention full value (in days) will be removed from the repository if there is at least one backup that expires in a number of days equal to or greater than Retention full. For example, if Retention full is 30 (days) and there are two full backups: one 25 days old and one 35 days old, no full backups will expire because once the 35-day backup expires, only the 25-day backup will remain, which would violate the retention policy of having at least one backup 30 days old before an older one can be expired. When a full backup expires, all differential and incremental backups associated with the full backup will also expire.

You can use the Retention diff option to specify the number of differential backups to retain. When a differential backup expires, all incremental backups associated with the differential backup will also expire. If this option is not specified, all differential backups will be retained until the full backups on which they depend have expired.

The backup compression

To enable the backup compression, check the Enable compression checkbox and specify the Compression type option. Use the one of the following values: bz2, gz, lz4, and zst. The default value is gz.

You can also set Compress level — a file compression level (from 0 to 9) used when Compression type does not equal none. If this option is not specified, the following levels are used based on the Compression type value:

  • bz2 — 9;

  • gz — 6;

  • lz4 — 1;

  • zst — 3.

Logging

By default, ADPG places pgBackRest log files at the following path: /var/log/adpg14-pgbackrest. Use the Log path option to change the log file directory.

You can also set a pgBackRest log level in the Log level field. Possible values are : off, error, warn, info, detail, debug, and trace. The default value is info.

Custom pgBackRest configuration

You can set pgBackRest settings that are not available in UI. For this, check the Use custom config checkbox and specify the required settings in the Global options and Custom options fields. In this case, all the pgBackRest settings except the Backup stanza parameter are applied from these fields. Settings from Global options are added to the [global] section of the pgBackRest configuration file. The configuration file is located at the following path: /etc/adpg14-pgbackrest/adpg14-pgbackrest.conf.

NOTE
If you check Use custom config and specify custom pgBackRest settings, ADPG writes these settings to the pgBackRest configuration file without checking. In case of incorrect settings, an error message will not be displayed in the ADCM UI. You need to verify the settings and check if the directory for storing backups exists and is accessible by yourself.

Create a backup

After you enable backups and set all the required settings as described above, use the Backup: Start action of the ADPG service to create a backup. When you run this action, ADCM displays a dialog window where you can select a backup type from a drop-down list.

Change the backup type
Change the backup type

In the opened window, select a backup type.

Select a backup type
Select a backup type

By default, pgBackRest creates incremental backups. However, an incremental backup must be based on a full backup. If there are no full backups, pgBackRest creates a full backup, regardless of which backup type is selected.

After the backup type is selected, click Run to confirm the action. A backup will be created in the specified repository (Backup repo). If errors occur during backup creation, you can find information about them on the Jobs page.

Restore an ADPG cluster

Use the Backup: Restore cluster action of the ADPG service to restore a cluster from the last backup on all nodes. You can find information about the last backup in the output of the Backup: Info action. If errors occur during the operation, information about them will be displayed on the Jobs page.

The action restores the cluster to the end of the archive. If you need a point recovery, it is recommended to use a manual recovery specifying the --target parameter. See Restore Command (restore).

Get a list of backups

Run the Backup: Info action of the ADPG service to get a list of created backups. You can find the action result on the Jobs page. To view the backup list, click the Backup: Info row in the task list, switch to the Ansible [check] tab of the job, find the Show backups information row at the end of the list, and click it to expand.

The result of the Backup: Info action
The result of the "Backup: Info" action

If errors occur during the operation, information about them will also be displayed on the Jobs page.

Manual PITR restore of ADPG cluster

In the automatic mode, you can restore cluster only from the last backup. To restore a cluster to a specific point in time, use the steps below:

  1. Make sure that an appropriate backup exists. You can use the Backup: Info action for this.

  2. Set the ${TIME} variable to a time to which you need to restore the cluster:

    $ TIME="2024-04-23 09:00:00+00"

  3. Use the Stop action to stop the ADPG service. Make sure that all ADPG instances are stopped. You can check this on the Jobs page.

  4. You can optionally delete the contents of the /pg_data/adpg14 directory on each ADPG node and run recovery without the --delta option. Alternatively, you can start the recovery with the --delta option, as shown below. In this case, it is not necessary to delete the contents, but you can copy them to another directory, which can be useful if the recovery fails.

  5. Remove the cluster /initialize key on any etcd node using the following command:

    $ /usr/lib/adpg14/usr/bin/etcdctl del --prefix=true "/arenadata/adpg14/<cluster_name>/<cluster_name>/initialize

    Where <cluster_name> is a cluster name in a snake case.

    For example:

    $ /usr/lib/adpg14/usr/bin/etcdctl del --prefix=true "/arenadata/adpg14/test_adpg_cluster_1/test_adpg_cluster_1/initialize"

  6. Use pgBackRest to restore the cluster on each ADPG node to the time specified by the ${TIME} variable:

    $ /usr/lib/adpg14/usr/bin/pgbackrest --config=/etc/adpg14-pgbackrest/adpg14-pgbackrest.conf --stanza=<stanza-name> --delta --type=time "--target=${TIME}" --target-action=promote restore

    Where <stanza-name> is the Backup stanza field value with the adpg<version>- prefix.

    For example:

    $ /usr/lib/adpg14/usr/bin/pgbackrest --config=/etc/adpg14-pgbackrest/adpg14-pgbackrest.conf --stanza=adpg14-mystanza --delta --type=time "--target=${TIME}" --target-action=promote restore

  7. Make sure that the postgresql.auto.conf and recovery.signal files exist in the /pg_data/adpg14 directory.

  8. Start PostgreSQL on the leader node on behalf of the postgres user using the following command:

    $ /usr/lib/adpg14/bin/pg_ctl start -D /pg_data1/adpg14

  9. Search for the starting point-in-time recovery string entry in the ADPG log files (the default path is /pg_data1/adpg14/log) to make sure that point-in-time-recovery has been started. See View log files for details.

    ...
2024-04-23 14:16:11.478 UTC [25516] LOG: starting point-in-time recovery to 2024-04-23 09:00:00+00
...

  10. Stop PostgreSQL on the leader node on behalf of the postgres user using the following command:

    $ /usr/lib/adpg14/bin/pg_ctl stop -D /pg_data1/adpg14

  11. Use the Start action to start the ADPG service.

Found a mistake? Seleсt text and press Ctrl+Enter to report it