Backup and restore using pgBackRest
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.
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 — adpg16-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. Directories on all nodes must be synchronized with each other, for example, using network file system tools. -
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:
The default value is |
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/adpg16-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/adpg16-pgbackrest/adpg16-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.
In the opened window, 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.
IMPORTANT
The Backup: Restore cluster action restores the cluster to the end of the WAL archive. In case of an accidental deletion of database objects, use manual PITR restore with the --target parameter, since it is most likely that the Backup: Restore Cluster action will restore the state of the cluster to the moment when the database objects were already deleted/dropped.
|
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.
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:
-
Make sure that an appropriate backup exists. You can use the Backup: Info action for this.
-
Set the
${TIME}
variable to a time to which you need to restore the cluster:$ TIME="2024-04-23 09:00:00+00"
-
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.
-
You can optionally delete the contents of the /pg_data1/adpg16 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. -
Remove the cluster
/initialize
key on any etcd node using the following command:$ /usr/lib/adpg16/usr/bin/etcdctl del --prefix=true "/arenadata/adpg16/<cluster_name>/<cluster_uuid>/initialize
Where
<cluster_uuid>
is a cluster UUID (see General configuration parameters → Cluster UUID in Configure a cluster), and<cluster_name>
is a cluster name in a snake case.For example:
$ /usr/lib/adpg16/usr/bin/etcdctl del --prefix=true "/arenadata/adpg16/test_adpg_cluster_1/81cb4ace-6ee0-4daa-bb5a-d181f7196caa/initialize"
-
Use pgBackRest to restore the cluster on each ADPG node to the time specified by the
${TIME}
variable:$ /usr/lib/adpg16/usr/bin/pgbackrest --config=/etc/adpg16-pgbackrest/adpg16-pgbackrest.conf --stanza=<stanza-name> --delta --type=time "--target=${TIME}" --target-action=promote restore
Where
<stanza-name>
is the Backup stanza field value with theadpg<version>-
prefix.For example:
$ /usr/lib/adpg16/usr/bin/pgbackrest --config=/etc/adpg16-pgbackrest/adpg16-pgbackrest.conf --stanza=adpg16-mystanza --delta --type=time "--target=${TIME}" --target-action=promote restore
-
Make sure that the postgresql.auto.conf and recovery.signal files exist in the /pg_data1/adpg16 directory.
-
Start PostgreSQL on the leader node on behalf of the
postgres
user using the following command:$ /usr/lib/adpg16/bin/pg_ctl start -D /pg_data1/adpg16
-
Search for the
starting point-in-time recovery
string entry in the ADPG log files (the default path is /pg_data1/adpg16/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 ...
-
Stop PostgreSQL on the leader node on behalf of the
postgres
user using the following command:$ /usr/lib/adpg16/bin/pg_ctl stop -D /pg_data1/adpg16
-
Use the Start action to start the ADPG service.