qbackup Backup Tool

qbackup is a tool for managing backup and recovery of QHB database clusters.

Synopsis


qbackup help [command]

qbackup list -B backup_dir [option...]

qbackup backup -B backup_dir [-D data_dir] [--incremental]
        [--compress] [--direct] [option...]

qbackup replica -D data_dir [option...]

qbackup restore -B backup_dir -D data_dir
        -i backup_id [--connection] [option...]

qbackup remove -B backup_dir -i backup_id ...
        [--single] [option...]

qbackup validate -B backup_dir -i backup_id
        [-D data_dir] [-t validate_type] [option...]

qbackup backup-wal -B backup_dir [--compress] -p wal_file_path
        -f wal_file_path [option...]

qbackup restore-wal -p wal_file_path
        -f wal_file_path [option...]

Review

Compared with other backup tools, qbackup has the following advantages, which are useful for implementing various backup strategies and working with large databases:

Page-by-page incremental backups: saves disk space and creates backups faster than full backups. Restoring incremental backups is also faster than replaying WAL files.
Implementation of creating a cumulative incremental backup with the ability to select a parent for the incremental backup.
Parallel execution: execution of the internal processes of the backup and restore commands in multiple parallel threads.
Compression: stores copied data in a compressed state to save disk space.
Cataloging backups: gets a list of backups and their corresponding meta-information in plain text or JSON.
Advanced means for working with remote backup.
Streaming backup (stream) uses the replication protocol.
Remote recovery from a backup is available.
Enhanced validation means for backups and restored clusters.

qbackup creates a backup directory to manage backups. This directory stores all backup files and additional metainformation, as well as WAL archives needed for point-in-time recovery.

Using qbackup in streaming backup mode, you can perform full or incremental backup from a local or remote cluster:

Full backups contain all the files needed to restore a database cluster from scratch.
Incremental backups are created at the page level and include only the data that has changed since the last backup. This saves disk space and creates backups faster than full backups. Restoring incremental backups is also faster than replaying WAL files.

qbackup in direct backup mode can perform a full copy of both a running and a stopped instance. The internal QHB mechanisms are used to copy WAL (see Section archive_command Scripts).

Caveats

Currently qbackup version 1.5.3 has the following limitations:

qbackup works with QHB servers 1.5.1 and above.
The QHB server where the backup was made and the server where it will be restored must have the same values of block_size and wal_block_size and the same major version. Depending on the cluster configuration, QHB may impose additional restrictions, such as processor architecture and libc/libicu version.
Direct backup is provided for creating local full backups only. For incremental backups, streaming backup is used for both the local and remote host.
It is not possible to make a stream backup on a stopped server. For this purpose, a backup in direct mode that does not require a mandatory connection to the database is provided.
Incremental backups cannot be based on backups created on a stopped server.
After enabling CDC for incremental backups, you must preliminary make a full backup copy to correctly capture subsequent data changes.

Backup directory is a directory with backups. It may contain directories with backups, information about them in catalog's catalog.json file and the directory wal. The catalog directory must be created before starting the backup.

Data Directory is a directory with database cluster.

The following fields in the backup directory file are proprietary but may be useful. They change with each minor QHB version:

Cluster version (cluster_version) is the binary version of QHB. This is the database build number.
qbackup version (qbackup_version).

Installation and Preparation

Once you have installed qbackup, follow these steps:

Create a backup directory.
Configure the database cluster to use qbackup.
Configure SSH to perform remote restores from backups if you plan to use this feature.

Creating a Backup Directory

qbackup stores backups in a backup directory, the path to which is specified in the BACKUPS_DIR environment variable or in -B (--backups-dir) command line argument. The backup directory must be created by the user before starting to work with the utility and must not contain any files. Manual editing of files in the backup directory is not allowed. The user running qbackup must have full access to backup_directory and at least access for reading to the entire contents of data_directory.

Database Cluster Configuration

Although qbackup can be used by a superuser, it is recommended to create a separate role with the minimum privileges required for chosen backup strategy. In these setup instructions, this role is the backup role.

qbackup program must read the cluster files directly, so you must run qbackup (or connect to it remotely) as an OS user who has read access to all files and directories inside the cluster data directory (PGDATA) to be copied.

Settings for Using qbackup in Direct Backup Mode

If you plan to use backup in stream mode for streaming backup, you can skip this section and immediately configure the cluster taking into account the requirements for streaming backup.

To perform a physical backup on an active database the backup role must have the following permissions on the QHB server (only on the database to be connected to**):

BEGIN;
CREATE ROLE backup WITH LOGIN;
GRANT USAGE ON SCHEMA pg_catalog TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.qhb_start_backup(text, boolean, boolean) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.qhb_stop_backup(boolean, boolean) TO backup;
COMMIT;

In qhb_hba.conf file, allow the user named backup to connect to the database cluster.

To be able to directly copy the production QHB instance, you should additionally configure WAL archiving as described in Section Configuring continuous WAL archiving.

Settings for Using qbackup in Streaming Backup Mode

The qbackup program in stream mode allows you to get a backup of the database cluster from a remote or local server over the network, using the replication protocol. If you use qbackup through an individual user (for example, backup), do not forget to give it the REPLICATION role in the database and the corresponding privileges in qhb_hba.conf.

Example:

CREATE USER backup REPLICATION PASSWORD 'backup';

Enable replication for the backup role in qhb_hba.conf file:

# qhb_hba.conf
host replication backup <master_ip> md5
host replication backup <standby_ip> md5
host all backup <master_ip> md5
host all backup <standby_ip> md5

Set the wal_level parameter to a value higher than minimal in qhb.conf file.

Example:

# qhb.conf
wal_level='replica'

For streaming backup, full and incremental backups (including remote ones) are provided on the enabled DBMS. For incremental backups, the CDC (Change Data Capture) mechanism is implemented. To use CDC, you need to configure the qcdc_registry_size parameter. It takes the size in megabytes. After restarting the database, a qhb-cdc.map file with the specified size will appear in its root. The size can be any within the limits specified below; for optimal operation, it should be selected based on the rule: estimated cluster size / 1000. The maximum value of this parameter is 10 GB (10240 MB), the minimum is 1 MB. If the value is 0, CDC is considered disabled.

Example:

# qhb.conf
# Estimated cluster size is 30GB
qcdc_registry_size=30MB

After enabling CDC, a full backup must be made to correctly capture data changes during incremental backups.

In streaming backup mode, all necessary WAL files will be automatically archived (see wal-method parameter).

CAUTION!
If you have encrypted tables in your database, it is not recommended to use streaming backup with wal-method=stream. To create an encrypted streaming backup, you must have continuous WAL archiving and streaming backup in the wal-method=fetch or wal-method=none mode.

If you plan to perform point-in-time recovery, you must additionally configure WAL archiving as described in Configuring Continuous WAL Archiving section below.

Configuring Continuous WAL Archiving

Continuous WAL archiving must be executed to perform local backups, as well as point-in-time recovery.

To configure continuous archiving via qbackup, you should modify qhb.conf configuration file:

Set wal_level to a value higher than minimal.
Set archive_mode to on or always.
Set archive_command:

archive_command='installation_path/qbackup backup-wal
 -B backup_dir -f %f -p %p'

installation_path is the path to the directory of the installed version of qbackup that you want to use.

As a result of qbackup backup-wal command, all WAL segments will be located in backup_directory/wal directory. /wal subdirectory will be created automatically after the start of the work.

Note
Instead of qbackup backup-wal command, you can use any other tool, provided that during continuous archiving, WAL segments will be placed in the backup_directory/wal.

An example of a possible configuration for backup of a running cluster

# qhb.conf
wal_level='replica'
archive_mode='on'
archive_command='/usr/bin/qbackup backup-wal -B /opt/backup_dir -f %f -p %p'

Setting archive_command is required for direct backup of a running cluster. This parameter does not need to be set for backup of a stopped cluster. For streaming backup or when using the replica subcommand, archive_mode and archive_command parameters can be left unset. All necessary WAL files will be automatically archived (archiving options are possible). See Section Replication for more information about the replication parameters specified in qhb.conf.

Setting up Backup From a Standby Server

The qbackup utility can copy data from a standby server. This requires additional configuration:

on the primary server, set the full_page_writes parameter to on and the archive_timeout parameter to a non-zero value in qhb.conf;
on the standby server, set the archive_mode parameter to always, and the hot_standby parameter to on in qhb.conf.

Example:

# qhb.conf
full_page_writes = on
hot_standby = on
# The following parameter is required for the standby server if WAL archiving is intended.
archive_mode = always

To obtain self-sufficient backups from a standby server, follow the steps described in Section Settings for Using qbackup in Streaming Backup Mode.

To obtain archive copies from the standby server, follow the steps described in Section Configuring Continuous WAL Archiving.

After these preparatory steps, you can make backups from the standby server in stream and direct modes.

Backup from a standby server has the following configuration aspects and limitations:

If the standby server is promoted to the primary server during backup, the backup will fail with an error.
On the primary server, archive_timeout must be set to a non-zero value. This is because qbackup on the standby server waits for the WAL segment to be switched on the primary server so that it can complete the archiving on the standby server. Otherwise, the following notification is displayed:

Warning! Still waiting for all required wal segments to be archived.

All WAL records required for the backup must contain full pages. To achieve this, you need to enable the full_page_writes mode on the primary server and not use utilities in archive_command that remove full pages from WAL files.
In order for qbackup on the standby server to send the WAL segment switch command to the primary server, you must grant the segment switch permissions to the user who is responsible for replication, for example, the user repluser. You can see which user is responsible for replication in qhb.auto.conf.

GRANT execute ON function pg_switch_wal TO repluser

Configuring SSH to Perform Remote Recovery

qbackup supports remote recovery mode. In this mode, the backup directory appears on the local system, and the target QHB instance runs on a remote computer. Currently, remote recovery is supported using only one network protocol, SSH.

If you want to use qbackup to restore a backup remotely (using SSH), execute the following steps:

Install qbackup on both systems: backup_host (backup server) and db_host (database server). On the remote computer, qbackup must be located in a location that allows it to run without specifying the path to the program.
To establish communication between nodes, set up a passwordless SSH connection for a user (for example, backup) on the backup_host computer to connect to the db_host server as qhb:

[backup@backup_host] ssh-keygen -t rsa
[backup@backup_host] ssh-copy-id -i ~/.ssh/id_rsa.pub qhb@db_host

You may want to start the ssh agent manually:

[backup@backup_host] eval `ssh-agent`
[backup@backup_host] ssh-add

Check if the settings are correct by trying to remotely run the command on backup_host:

[backup@backup_host] ssh qhb@db_host qbackup apply-inc --help

backup_host is the system in which the backup directory is located.
db_host is the system in which the QHB cluster operates.
backup is the user in the backup_host system, under which qbackup is launched.
qhb is the user in the db_host system, under which the QHB cluster is launched.

In remote recovery mode, qbackup works as follows:

The main process starts on the backup_host system and connects to the db_host system.
During operation, backup files are copied to db_host, and incremental change files are applied by calling qbackup on db_host.
If qbackup on db_host encounters an error, the main qbackup process reports the error and exits.
Unpacking a compressed backup is always performed in the backup_host system.

Usage

Creating a Backup

Creating a Direct Backup

To create a direct backup from the local host, run the following command:

qbackup backup -B backup_dir -D data_dir --direct [--compress]
    [connection parameters...] [option...]

The --compress flag significantly reduces the backup size, but may slow down the backup process.

Creating a Streaming Backup

To create a streaming backup, run the following command:

qbackup backup -B backup_dir [--compress] [--incremental] [--from backup_id]
    [connection parameters...] [option...]

qbackup in stream mode (default) supports two main types of backup creation:

Full backups. The contents of the data_dir are copied to the backup_dir, except for optional files. The copies_directory may contain any number of independent backups.
Incremental backups. With the --incremental flag qbackup queries the server for data on changed files and, based on this data, creates a backup. An incremental backup stores only those files (or parts of files) that have changed since the full backup was created. This significantly reduces the size of the backup, but may require more time to perform file comparison operations. (This is an experimental feature, but backups will always be consistent regardless.) Note that incremental backups can take a long time to create and be large.

When restoring a cluster from an incremental backup, qbackup uses the parent full backup and all incremental backups between them, collectively forming a "chain of backups". Thus, before making incremental backups, you must make at least one full backup, and after CDC is enabled.

The --compress flag significantly reduces the backup size, but may slow down the backup process.

The --from flag is used to create an additional backup chain. It is used only for incremental backups to select which of the backups (either full or incremental) the increment will be based on. This flag is also necessary if the backup from the chain was not the last one to be restored; in this case, to continue incremental backup, you must specify --from restored_backup_id. Subsequent incremental backups can be created without the additional --from parameter. See Section Additional Parameters for backup Command for more information.

By specifying a remote host, you can get a backup of the database cluster from a remote server over the network using the replication protocol. More information about the replication parameters can be found below in Section Additional Backup Parameters.

qbackup supports the replication protocol for getting backups from a remote server.

Creating a Cluster Replica

To create a replica, run the following command on the host of the intended replica, using the connection parameters for the primary server:

qbackup replica -D backup_dir [--compress] [connection parameters...]
    [replication parameters...] [option...]

This subcommand is useful for creating a standby server.

The --compress flag significantly reduces the backup size, but may slow down the backup process.

Cluster Recovery

To restore a database cluster from a backup, run restore command:

qbackup restore -B backup_dir -D data_dir
  -i backup_id [--tablespace-mapping old_path=new_path] [--connection] [--wal-directory] [option...]

backup_dir is the directory where all backup files and metadata are stored.
data_dir is the directory where the restored cluster data will be stored. This directory must be created before executing the command, be empty and writable.
backup_id determines from which backup the cluster will be restored. If you select an incremental backup to restore, qbackup automatically restores the underlying full backup and then sequentially applies all necessary appends.

The --connection parameter is used when restoring to a remote host. See Section Connection Parameters for Remote Recovery for the parameter description.

The --wal-directory parameter is used to override the directory location of the WAL file archive if it differs from backup_dir. See the description of --wal-directory below.

See the --tablespace-mapping description below.

After the restore command has completed, start the database service using qhb_ctl.

IMPORTANT
By default, recovery is performed up to the last LSN in the backup. If there is a continuous WAL archive, you can recover up to the last existing LSN in the archive by specifying --target-lsn=latest.

Performing Point-in-Time Recovery (PITR)

You can restore the cluster state to any point in time (up to a specified restore point) by using restore point parameters with restore command.

To restore the cluster state to a specific point in time, specify that time in --target-time, in the timestamp format. For example:

qbackup restore -B backup_dir -D data_dir
  -i backup_id --target-time='2017-05-18 14:18:11+03'

To restore the cluster state to a specific transaction, use the --target-xid parameter. For example:

qbackup restore -B backup_dir -D data_dir
  -i backup_id --target-xid=687

To restore the cluster state to a specific log number (LSN), use the --target-lsn parameter. For example:

qbackup restore -B backup_dir -D data_dir
  -i backup_id --target-lsn=16/B374D848

Note
If --target-lsn=latest is specified, recovery will be performed up to the last LSN in the continuous WAL archive.

To restore the cluster state to a specified named restore point, use the --target-name parameter. For example:

qbackup restore -B backup_dir -D data_dir
  -i backup_id --target-name='before_app_upgrade'

To restore the cluster to its earliest possible consistent state, specify the --immediate flag:

qbackup restore -B backup_dir -D data_dir
  -i backup_id --immediate

In addition to the parameters that set the restore point, you can also specify the --action, --timeline, and --inclusive parameters.

Checking Backups

The validate command allows you to check (and in some cases restore) the integrity of a backup copy, as well as a restored cluster. There are three types of validation:

checksum (default mode)

By default, after the backup is complete, qbackup calculates a checksum of all files inside the backup directory, which allows you to check their consistency later. This option allows you to compare this checksum with the one that will be calculated during the verification process.

If the checksums do not match, then you should switch to the qhb-checksum mode to detect the damaged table file. If the check is successful, this means that the auxiliary database files are damaged.

qhb-checksum

Allows you to verify the checksums of database table files. This function is similar to qhb_checksums except that this mode works with cataloged qbackup backups. If the backup was made with checksums enabled, this option allows you to verify them. If checksums were disabled in the cluster at the time of the backup, the -e --enable flag allows you to enable them. It can be used exclusively on full uncompressed backups in direct mode.

database

This mode allows you to binary compare the files inside the backup with the cluster files. You should perform checks on a stopped server. If damaged files are found, you will be offered to restore them. This option is useful for checking a newly restored database, as well as those table files that should not have changed since the restore. Also, in this mode you can check a newly created/restored non-archived full backup.

Example:

$ qbackup backup -B /tmp/backup -h localhost -u user1 -fc # network full compressed backup ID: QZWGEI
$ qbackup backup -B /tmp/backup -h localhost -u user1 -fi # network incremental backup ID: QZWGFJ

$ qbackup validate -B /tmp/cat -t checksum -i QZWGFJ # checksum type check
$ qbackup validate -B /tmp/cat -t qhbchecksum -i QZWGEI # qhb-checksum type check

$ qbackup restore -B /tmp/backup -i QZWGFJ -D /tmp/qhb-data/ # Recovery

# Suspected `/tmp/qhb-data/base/12677/175` file corruption

$ qbackup validate -B /tmp/backup -i QZWGFJ -D /tmp/qhb-data/ -t database # database type check
You are going to restore
/tmp/cat/QZWH7I/base/12677/175 into /tmp/qhb-data/base/12677/175
You are going to restore
/tmp/cat/QZWH7I/base/12677/175 into /tmp/qhb-data/base/12677/175
 yes

$ qbackup validate -B /tmp/backup -i QZWGFJ -D /tmp/qhb-data/ -t database # database type check (successful)

When using the database check mode, you can use the -y --yes flag, that allows you to automatically agree to restore.

Note
In what situations it will not be possible to restore a file:

The same files are corrupted within the backup and within the cluster

The file was modified using the DBMS

The file has been completely deleted

The file header (last modification information) is corrupted.

Note
If you see a warning that the file is not found, then most likely new table files have been created while the DBMS was running. If not, then a file in the backup has been lost.

Running qbackup in Parallel Threads

The backup and restore commands can be executed in several parallel threads. This can significantly speed up the work of qbackup if there are sufficient resources (processor cores, disk subsystem and network performance).

Parallel execution is controlled by the --threads command line switch. For example, to run a backup in four parallel threads, execute:

qbackup backup -B backup_dir [-D data_dir] [--direct] [--compress] [--incremental] -j 4

By default, or if the specified thread number is 0, qbackup automatically selects the number of threads that will provide maximum copy performance (at the expense of other operations).

Managing the Backup Directory

With qbackup you can manage backups from the command line:

View existing backups
Delete backups

Viewing Backup Information

To view a list of existing backups for each instance, run list command:

qbackup list -B backup_dir

qbackup lists all available backups. For example:

+----------+--------+----------+-----------------+----------------------+-----------+----------+----------------------------+----------------------------+------------+
|    ID    | status |   size   | compressed size |         kind         | start lsn |  parent  |         start time         |          end time          |  comment   |
+----------+--------+----------+-----------------+----------------------+-----------+----------+----------------------------+----------------------------+------------+
| LE4D8X48 |  Done  | 43.26MiB |     5.24MiB     |    Full (Stream)     | 0/2000028 |    —     | 2023-02-14 17:55:14 +03:00 | 2023-02-14 17:55:29 +03:00 |     —      |
+----------+--------+----------+-----------------+----------------------+-----------+----------+----------------------------+----------------------------+------------+
| LE4D99DU |  Done  | 25.49MiB |     1.82MiB     | Incremental (Stream) | 0/4000028 | LE4D8X48 | 2023-02-14 17:55:30 +03:00 | 2023-02-14 17:55:44 +03:00 |     —      |
+----------+--------+----------+-----------------+----------------------+-----------+----------+----------------------------+----------------------------+------------+
| LE4D9L7B |  Done  | 25.49MiB |     1.81MiB     | Incremental (Stream) | 0/6000028 | LE4D99DU | 2023-02-14 17:55:45 +03:00 | 2023-02-14 17:55:59 +03:00 |     —      |
+----------+--------+----------+-----------------+----------------------+-----------+----------+----------------------------+----------------------------+------------+
| LE4DB3RG |  Done  | 61.05MiB |     8.69MiB     |    Full (Direct)     | 0/8000028 |    —     | 2023-02-14 17:56:56 +03:00 | 2023-02-14 17:57:21 +03:00 | backup1402 |
+----------+--------+----------+-----------------+----------------------+-----------+----------+----------------------------+----------------------------+------------+

For each backup the following information is provided:

ID — unique identifier of the backup.

status — backup status. Possible options:

Done — the backup is complete and ready to use;
In Progress — backup is still running;
Error — backup fails;
Cancelled — backup is cancelled.

size — backup size (uncompressed).

compressed size — compressed backup size (for compressed backups only).

kind — backup type. Possible options:

Full (Stream) — full streaming backup;
Incremental (Stream) — incremental streaming backup;
Full (Direct) — full direct backup;

is compressed — whether the backup is compressed.

start lsn — LSN (WAL log sequence number) at the time the backup started. It is not provided for backup of a stopped cluster.

parent — previous backup identifier for incremental backups.

start time — system time of the backup start.

end time — system time of the backup end.

To print a table in a format more similar to the AWK language style, you can use --raw flag:

qbackup list -B backup_dir --raw

Example output:

id        status  size       compressed_size  kind                start_lsn   parent    start_time                  end_time                    comment
 M7CEHUT0  done    55745463   -                full stream         0/2000028   -         2025-02-20 00:03:48 +03:00  2025-02-20 00:03:56 +03:00  -
 M7CEJOZJ  done    678863756  -                incremental stream  0/1D000028  M7CEHUT0  2025-02-20 00:05:14 +03:00  2025-02-20 00:09:53 +03:00  -
 M7CZ6CB0  done    873995933  -                incremental stream  0/3B000028  M7CEJOZJ  2025-02-20 09:42:43 +03:00  2025-02-20 09:47:21 +03:00  -
 M7KCGZGU  done    303068384  -                incremental stream  0/3D000028  M7CZ6CB0  2025-02-25 13:29:17 +03:00  2025-02-25 13:29:20 +03:00  -
 M7KCS2DU  done    1123370    -                incremental stream  0/3F000028  M7KCGZGU  2025-02-25 13:37:54 +03:00  2025-02-25 13:37:56 +03:00  -
 M7LWEKI1  done    18051329   152191           incremental stream  0/41000028  M7KCS2DU  2025-02-26 15:35:03 +03:00  2025-02-26 15:35:06 +03:00  -
 M7LWFL65  done    1416248    84363            incremental stream  0/43000028  M7LWEKI1  2025-02-26 15:35:51 +03:00  2025-02-26 15:35:52 +03:00  -
 M7N9LC14  done    497705271  -                full direct         0/45000028  -         2025-02-27 14:32:00 +03:00  2025-02-27 14:32:02 +03:00  -

For each backup the following information is provided:

ID — unique identifier of the backup.

status — backup status. Possible options:

done — the backup is complete and ready to use;
in progress — backup is still running;
error — backup fails;
cancelled — backup is cancelled.

size — backup size (uncompressed), in bytes.

compressed size — compressed backup size (for compressed backups only), in bytes.

kind — backup type. Possible options:

full stream — full streaming backup;
incremental stream — incremental streaming backup;
full direct — full direct backup;

is compressed — whether the backup is compressed.

start lsn — LSN (WAL log sequence number) at the time the backup started. It is not provided for backup of a stopped cluster.

parent — previous backup identifier for incremental backups.

start time — system time of the backup start.

end time — system time of the backup end.

comment — backup comment.

You can also get information about backups in JSON format:

qbackup list -B backup_dir --json

Example output:

[
  {
    "identifier": "LE8EH39A",
    "status": "Done",
    "size": 45364828,
    "compressed_size": 5498462,
    "kind": {
      "Full": "Stream"
    },
    "start_lsn": "0/2000028",
    "start_time": "2023-02-17T13:40:40+03:00",
    "end_time": "2023-02-17T13:40:55+03:00",
    "parent": null,
    "comment": null
  },
  {
    "identifier": "LE8EHFOO",
    "status": "Done",
    "size": 26723166,
    "compressed_size": 1911987,
    "kind": {
      "Incremental": "Stream"
    },
    "start_lsn": "0/4000028",
    "start_time": "2023-02-17T13:40:56+03:00",
    "end_time": "2023-02-17T13:41:10+03:00",
    "parent": "LE8EH39A",
    "comment": null
  },
  {
    "identifier": "LE8EHR4V",
    "status": "Done",
    "size": 26725616,
    "compressed_size": 1902556,
    "kind": {
      "Incremental": "Stream"
    },
    "start_lsn": "0/6000028",
    "start_time": "2023-02-17T13:41:11+03:00",
    "end_time": "2023-02-17T13:41:25+03:00",
    "parent": "LE8EHFOO",
    "comment": null
  }
]

More detailed system information can be found in the backup_dir/catalog.json file.

Deleting Backups

To remove a backup that is no longer needed, run the remove command:

qbackup remove -B backup_dir -i backup_id

This command will delete the backup with the given backup_id as well as all incremental backups that depend on it (if any). This way you can remove some of the last incremental backups from the "backup chain". In the -i parameter, you can pass several IDs separated by a space. Before deleting, the program displays information about the backups being deleted and requires additional confirmation; this can be disabled using the -y flag.

For more information about the advanced options of this command, see Section Additional Parameters for remove Command.

Backup Holding Policy

To delete old backups, the utility has Backup Deletion Policy feature.

Example:

qbackup remove -B backup_dir --hold day_num

This command will delete backups that were created before CURRENT_DATE - interval 'day_num day'. In case the archive backup appears in the chain with others, it will not be deleted until all the chain items will not expire.

Archive Transaction Log Deleting Policy

The utility has a feature that allows you to delete old archive transaction logs that are no longer required when restoring from an archive.

Пример:

qbackup remove-wal -B backup_dir -A transaction-dir

After executing the command, archive transaction logs that were created before the last successful archive backup will be deleted.

Before deleting, the utility displays information about the deleted archive transaction logs and requires additional confirmation; this can be turned off with -y flag. To display candidates, you must specify the --dry-run flag; in this case, the program will not perform physical deleting.

Command Line Help

Commands

This section describes the qbackup commands. The optional parameters of these commands are enclosed in square brackets. All parameters are described in detail below in Section Parameters.

help

qbackup help [command]

Provides help on qbackup commands. If one of the qbackup commands is specified in the parameters, it provides detailed information on the parameters that this command accepts.

The --help flag, available for any of the commands, performs a similar role.

list

qbackup list -B backup_dir [--json] [--help]

Shows the contents of the backup directory.

By default, the contents of the directory are presented as plain text. You can pass the --json flag to get the result in JSON format.

For more information on how to use this command, see Section Managing the Backup Directory.

backup

qbackup backup -B backup_dir [-D data_dir] [--direct]
[--compress] [--incremental]
[--help] [-j thread_number] [--progress] [replication parameters...]

Creates a backup of the QHB instance.

For more information on using this command, see Section Creating a Backup.

For more information on the advanced parameters of this command, see Section Additional Parameters for backup Command.

restore

qbackup restore -B backup_dir -D data_dir -i backup_id
[--help]
[-j thread_number] [--progress] [--connection] [--wal-directory]
[recovery point parameters...]

Restores instance QHB from a backup located in backup_directory to a local or remote host.

For more information on using this command, see Section Cluster Recovery. For more information on recovery point parameters, see Section Performing Point-in-Time Recovery (PITR).

replica

qbackup replica -D backup_dir
[--compress] [--help] [--progress] [replication parameters...]

Creates a replica of the QHB instance.

For more information on using this command, see Section Creating a Cluster Replica.

remove

qbackup remove -B backup_dir -i backup_id [backup_id_2]... [--single]
[--help] [--progress]

Removes the backup with the given backup_id and all incremental backups that depend on it. If the --single flag is passed, removes only the specified backup.

For more information about the advanced parameters of this command, see Section Additional Parameters for remove Command.

For more information about using this command, see Section Deleting Backups.

validate

qbackup validate -B backup_dir -i backup_id [-D data_dir] [-t validate_type]
[--help] [--progress] [option...]

Checks the correctness of the backup with the specified backup_id. There are three types of check: checksum, qhb-checksum and database; for more information, see Section Checking Backups.

backup-wal

qbackup backup-wal -B backup_dir [--compress] --wal-file-name=wal_file_name --wal-file-path=wal_file_path
[--help]

Copies WAL files to the appropriate subdirectory of the backup directory. If necessary, you can use any directory other than the backup_dir for the WAL location, and take this into account when running restore-wal.

The --compress flag significantly reduces the size of the archived WAL, but may slow down the backup process.

The backup-wal command can be specified as the value of the archive_command QHB parameter when configuring continuous WAL archiving. For more information, see Section Configuring Continuous WAL Archiving.

restore-wal

qbackup restore-wal --destination-wal-file-path=wal_file_name_and_path --source-wal-file-path=source_wal_file_path
[--help]

Copies WAL files to the appropriate subdirectory with the desired name.

The restore-wal command is automatically used as restore_command in qhb.conf after a recovery.

Usage example:

restore_command='qbackup restore-wal -f /tmp/catalog/wal/%f -p %p'

Parameters

General Parameters

Common parameters are supported by most qbackup commands.

-j
--threads
Allows to specify the number of threads for operations that support parallel execution (backup and restore).
By default, if the parameter value is not set or is 0, the optimal thread number for the given system is used.

-P
--progress
Displays interactive information about the operation's progress. Intended for display to users. When running a streaming backup, also gets information about the size of tablespaces.

-v
--verbose
Displays more detailed information about the operation progress.

Connection Parameters

Connection Parameters for Backup

To connect to the database, you must specify a number of parameters. Only two of them are mandatory for a successful connection: the user name and the database host name. The need for other parameters is determined by the cluster settings.

Connection parameters are used only in the backup command and only for the running cluster backup.

-u
--user
The name of the database user under which the backup is performed. The default is qhb.
If the QBACKUP_USER environment variable is set, its value is used. The parameter specified on the command line overrides the environment variable.

-h
--host
Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket. Multiple host names can be listed separated by commas. Each name will be tried sequentially. The default value is localhost.
If the QBACKUP_HOST environment variable is set, its value is used. The parameter specified on the command line overrides the environment variable.

--db-name
The name of the database to connect to. Defaults to the username.
If the QBACKUP_DBNAME environment variable is set, its value is used. The parameter specified on the command line overrides the environment variable.

-p
--port
Port for connecting to the database. Several ports can be listed separated by commas.
The number of ports specified can be 1 (in this case, the same port is used for all hosts), or it must match the specified number of hosts. The default is 5432.
If the QBACKUP_PORT environment variable is set, its value is used. The parameter specified on the command line overrides the environment variable.

--password
User password for connecting to the database.
If the QBACKUP_PASSWORD environment variable is set, its value is used. The parameter specified on the command line overrides the environment variable. If the password is not specified, but it was required during the program operation, it will be requested in dialog mode. After the first incorrect attempt, an authentication error message will be displayed.

-w
--no-password
Never enter dialog mode to ask for password. This option can be useful in automation scripts.

Connection Parameters for Remote Recovery

-C
--connection
Parameter for connecting to a remote host via ssh to restore from a backup.
Specified in the format -C user@ip:port when an ssh connection is configured between hosts. See Section Configuring SSH to Perform Remote Recovery.

Recovery point Parameters

The restore command can have additional parameters that control point-in-time recovery (PITR).

They used in the restore command only.

--target-time=timestamp
Restore to a specific timestamp in timestamp format.
The value of this parameter is specified in the same format as accepted by the timestamp with time zone data type, except that it cannot use the abbreviated time zone name. Therefore, it is recommended to specify a numeric offset from UTC or write the time zone name in full, for example, Europe/Helsinki (but not EEST).

--target-xid=transaction_xid
Recover to a specific transaction ID.
Note that the numeric value of the ID reflects the sequence of the start of transactions, and they may be committed in a different order. All transactions that were committed before the specified one (and, possibly including it, depending on the value of --inclusive) will be subject to recovery.

--target-lsn=[ latest | LSN ]
Restore to a specific LSN. This parameter takes the value of the pg_lsn system data type. With the keyword latest, it will restore to the latest LSN in the archive.

--target-name=restore_point_name
Restore to a specific named restore point created using the pg_create_restore_point() function.

--immediate
This parameter specifies that the restore process should end as soon as a consistent state is reached, i.e. as soon as possible. When restoring from an online backup, this will be the point at which the backup process ended.

--action=[ pause | promote | shutdown ]
Specifies what action the server should take after the recovery target is reached. The default option is pause, which means to pause the recovery. The second option, promote, means that the recovery process will complete and the server will start accepting connections. Finally, with the shutdown option, the server will stop once the recovery target is reached.
The pause option allows you to execute queries to the database and verify that the target reached was the desired recovery point. To resume the action, you must call the pg_wal_replay_resume() function, which will eventually complete the recovery. If it turns out that the desired recovery point has not yet been reached, you must stop the server, set a later target, and restart the server to continue the recovery. The shutdown option is useful for getting a server instance ready at a desired point. This will allow the instance to replay additional WAL records (and when restarted, it will have to replay WAL records after the last checkpoint). Note that since recovery.signal is not renamed when shutdown is selected in --action, subsequent startups will stop immediately unless you change the configuration or manually delete the recovery.signal file.
This option has no effect if no recovery target is set.

--timeline=[ current | latest | timeline_id ]
Specifies the timeline to restore. The value can be specified by a numeric timeline identifier (timeline_id) or a keyword. The keyword current restores the timeline that was active when the base backup was created. The keyword latest restores the latest timeline found in the archive, which is useful for a standby server. The default is latest.

--inclusive=[ on | off ]
Stop recovery when the specified point is reached (on) or just before it is reached (off). The default is on.

--wal-directory=wal_dir
Override the WAL archive directory. Required when specifying a directory other than backup_dir in archive_command. By default, the parameter matches -B backup_dir.

Additional Backup Parameters

-R
--write-recovery-conf
Write the connection string to the primary server to qhb.auto.conf. Writes the primary_slot_name and primary_conninfo parameters. This is the default parameter; if the connection to the primary server fails, you need to edit these parameters manually.

-r
--max-rate=max_rate
Limit file transfer rate. Accepts suffixes k — KB and M - MB. Accepts values from 32 KB/s to 1024 MB/s.

-X
--wal-method=[ stream | fetch | none ]
Method for archiving WAL files during replication.

stream — archive WAL in a separate stream in parallel. This is the default. Files are archived in $PGDATA/pg_wal/.
fetch — archive WAL together with cluster files. They will be located within the $PGDATA/pg_wal/ directory.
none — do not archive WAL files.

CAUTION!
If you select the none method, WAL files will not be archived! You must archive WAL files manually, otherwise the backup will be invalid!

WARNING!
If there are encrypted tables, the stream method (wal-method=stream) is not recommended.

-S
--slotname=slotname
Create a replication slot for archiving WAL files. By default, a temporary replication slot is created, and this parameter creates a permanent replication slot. If the slot slotname already exists, it will be used for replication, otherwise it will be created.

Note
The replication slot name can include only lowercase English letters (a-z), numbers (0-9), and the underscore character (_).

-T
--tablespace-mapping old_path=new_path
Performs tablespace addressing. Addressing is a changing the original tablespace directory path to a new one. This parameter can be specified multiple times.

old_path — original tablespace path.
new_path — new tablespace path (addressing path).

Other Parameters

Additional Parameters for backup Command

-i
--incremental
Allows to create incremental backups.

-c
--compress
Allows to reduce the backup size.

-f
--fast-checkpoint
Requests a quick checkpoint to be performed instead of a regular checkpoint. It is used for hot server backups or replication.

--skip-broken
Allows new incremental backups to be based on the last successful backup, skipping failed ones without deleting them directly.

-C
--comment
Allows to add a comment for the backup.

--no-checksum
Allows you to disable backup checksum calculation. Use only if you are sure you understand all the consequences.

--from
Allows you to select a parent backup to create an incremental backup from. It is used to create an additional backup chain. The backup ID is used for specifying.

Additional Parameters for remove Command

-y --yes
Allows to delete backups without any additional warning.

--prune
Allows to delete all backups.

--single
Allows to delete one backup without deleting the chain.

-L --dry-run
Allows to display a list of archive backups to be deleted.

WARNING!
Once a backup is removed from the chain, all backups made after it become invalid!

Versioning

qbackup is developed using semantic versioning semver.