Limitations

pg_probackup currently has the following limitations:

Initializing the Backup Catalog

pg_probackup stores all WAL and backup files in the corresponding subdirectories of the backup catalog.

To initialize the backup catalog, run the following command:

pg_probackup init -B backupdir

where:

pg_probackup creates the backupdir backup catalog, with the following subdirectories:

Once the backup catalog is initialized, you can add a new backup instance.

Adding a New Backup Instance

pg_probackup can store backups for multiple database clusters in a single backup catalog. To set up the required subdirectories, you must add a backup instance to the backup catalog for each database cluster you are going to back up.

To add a new backup instance, run the following command:

pg_probackup add-instance -B backupdir -D datadir --instance instance_name

where:

pg_probackup creates the instance_name subdirectories under the backups/ and wal/ directories of the backup catalog. The backups/instance_name directory contains the pg_probackup.conf configuration file that controls backup and restore settings for this backup instance. For details on how to fine-tune pg_probackup configuration, see the section called “Configuring pg_probackup”.

The backup catalog must belong to the file system of the database server. The user launching pg_probackup must have full access to the contents of the backup catalog. If you specify the path to the backup catalog in the BACKUP_PATH environment variable, you can omit the corresponding option when running pg_probackup commands.

Since pg_probackup uses a regular PostgreSQL connection and the replication protocol, pg_probackup commands require connection options. To avoid specifying these options each time on the command line, you can set them in the pg_probackup.conf configuration file using the set-config command. For details, see the section called “Configuring pg_probackup”.

Configuring the Database Cluster

Although pg_probackup can be used by a superuser, it is recommended to create a separate user or role with the minimum permissions required for the chosen backup strategy. In these configuration instructions, the backup role is used as an example.

To enable backups, the following rigths are required:

CREATE ROLE backup WITH LOGIN;
GRANT USAGE ON SCHEMA pg_catalog TO backup;
GRANT EXECUTE ON FUNCTION current_setting(text) TO backup;
GRANT EXECUTE ON FUNCTION pg_is_in_recovery() TO backup;
GRANT EXECUTE ON FUNCTION pg_start_backup(text, boolean, boolean) TO backup;
GRANT EXECUTE ON FUNCTION pg_stop_backup() TO backup;
GRANT EXECUTE ON FUNCTION pg_stop_backup(boolean) TO backup;
GRANT EXECUTE ON FUNCTION pg_create_restore_point(text) TO backup;
GRANT EXECUTE ON FUNCTION pg_switch_wal() TO backup;
GRANT EXECUTE ON FUNCTION txid_current() TO backup;
GRANT EXECUTE ON FUNCTION txid_current_snapshot() TO backup;
GRANT EXECUTE ON FUNCTION txid_snapshot_xmax(txid_snapshot) TO backup;

Depending on whether you are going to use autonomous or archive backup strategies, Postgres Pro cluster configuration will differ, as specified in the sections below. To back up the database cluster from a standby server or create PTRACK backups, additional setup is required. For details, see the section called “PTRACK Backup” and the section called “Backup from Standby”.

GRANT EXECUTE ON FUNCTION pg_ptrack_clear() TO backup;
GRANT EXECUTE ON FUNCTION pg_ptrack_get_and_clear(oid, oid) TO backup;

Commands

This section describes pg_probackup commands. Some commands require mandatory options and can take additional options. For detailed descriptions, see the section called “Options”.

Options

This section describes all command-line options for pg_probackup commands. If the option value can be derived from an environment variable, this variable is specified below the command-line option, in the uppercase. Some values can be taken from the pg_probackup.conf configuration file located in the backup catalog. For details, see the section called “Configuring pg_probackup”.

If an option is specified using more than one method, command-line input has the highest priority, while the pg_probackup.conf settings have the lowest priority.

Creating a Backup

To create a backup, run the following command:

pg_probackup backup -B backupdir --instance instance_name -b backup_mode

where backup_mode can take one of the following values:

When restoring a cluster from an incremental backup, pg_probackup relies on the previous full backup to restore all the data files first. Thus, you must create at least one full backup before taking incremental ones.

If you have configured PTRACK backups, pg_probackup clears PTRACK bitmap of the relation being processed each time a full or an incremental backup is taken. Thus, the next incremental PTRACK backup contains only the pages that have changed since the previous backup. If a backup failed or was interrupted, some relations can already have their PTRACK forks cleared, so the next incremental backup will be incomplete. The same is true if ptrack_enable was turned off for some time. In this case, you must take a full backup before the next incremental PTRACK backup.

To make a backup autonomous, add the --stream option to the above command. For example, to create a full autonomous backup, run:

pg_probackup backup -B backupdir --instance instance_name -b FULL --stream

Autonomous backups include all the WAL segments required to restore the cluster to a consistent state at the time the backup was taken. To restore a cluster from an incremental autonomous backup, pg_probackup still requires the full backup and all the incremental backups it depends on.

Even if you are using continuous archiving, autonomous backups can still be useful in the following cases:

Validating Backups

When checksums are enabled for the database cluster, pg_probackup uses this information to check correctness of data files. While reading each page, pg_probackup checks whether the calculated checksum coincides with the checksum stored in the page. This guarantees that the backup is free of corrupted pages. Note that pg_probackup reads database files from disk and under heavy write load during backup it can show false positive checksum failures because of partial writes.

Even if page checksums are disabled, pg_probackup calculates checksums for each file in a backup. Checksums are checked immediately after backup is taken and right before restore, to detect possible backup corruptions.

To ensure that all the required backup files are present and can be used to restore the database cluster, you can run the validate command with the exact recovery target options you are going to use for recovery. If you omit all the parameters, all backups are validated.

For example, to check that you can restore the database cluster from a backup copy up to the specified xid transaction ID, run this command:

pg_probackup validate -B backupdir --instance instance_name --xid=xid

If validation completes successfully, pg_probackup displays the corresponding message. If validation fails, you will receive an error message with the exact time and transaction ID up to which the recovery is possible.

Restoring a Cluster

To restore the database cluster from a backup, use the restore command:

pg_probackup restore -B backupdir --instance instance_name -D datadir -i backup_id

where:

If you have configured archive backups, you can restore the cluster to its state at an arbitrary point in time (recovery target) using recovery target options. pg_probackup automatically chooses the backup that is the closest to the specified recovery target and start the recovery process. By default, the recovery_target_inclusive parameter defines whether the recovery target is included into the backup. You can explicitly include or exclude the recovery target using the --inclusive=boolean option.

To restore the cluster state at the exact time, specify the time option, in the timestamp format. For example:

pg_probackup restore -B backupdir --instance instance_name --time='2017-05-18 14:18:11'

To restore the cluster state up to a specific transaction ID, use the xid:

pg_probackup restore -B backupdir --instance instance_name --xid=687

If the cluster to restore contains tablespaces, pg_probackup restores them to their original location by default. To restore tablespaces to a different location, use the --tablespace-mapping option. Otherwise, restoring the cluster on the same host will fail if tablespaces are in use, because the backup would have to be written to the same directories.

When using the --tablespace-mapping you must provide absolute paths to the old and new tablespace directories. If a path happens to contain an equals sign (=), escape it with a backslash. This option can be specified multiple times for multiple tablespaces. For example:

pg_probackup restore -B backupdir --instance instance_name -D datadir -j 4 -i backup_id -T tablespace1_dir=tablespace1_newdir -T tablespace2_dir=tablespace2_newdir

Once the restore command is complete, start the database service. Postgres Pro will restore a self-consistent state by replaying WAL files and will be ready to accept connections.

Running pg_probackup on Parallel Threads

Backup, recovery, and validation processes can be executed on several parallel threads. This can significantly speed up pg_probackup operation given enough resources (CPU cores, disk, and network throughput).

Parallel execution is controlled by the -j/--threads command line option. For example, to create a backup using four parallel threads, run:

pg_probackup backup -B backupdir --instance instance_name -b FULL -j 4

Specifying Connection Settings

If you define connection settings in the pg_probackup.conf configuration file, you can omit connection options in all the subsequent pg_probackup commands. However, if the corresponding environment variables are set, they get higher priority. The options provided on the command line overwrite both environment variables and configuration file settings.

If nothing is given, the default values are taken. pg_probackup tries to use local connection and tries to get the database name and the user name from the PGUSER environment variable or the current OS user name.

Configuring Backup Retention Policy

By default, all backup copies created with pg_probackup are stored in the specified backup catalog. To save disk space, you can configure retention policy and periodically clean up redundant backup copies accordingly.

To configure retention policy, set one or more of the following variables in the pg_probackup.conf file:

If both retention-redundancy and retention-window options are set, pg_probackup keeps backup copies that satisfy both conditions. For example, if you set retention-redundancy=2 and retention-window=7, pg_probackup cleans up the backup directory to keep only two full backup copies if at least one of them is older than seven days.

To clean up the backup catalog in accordance with retention policy, run:

pg_probackup delete -B backupdir --instance instance_name --expired

pg_probackup deletes all backup copies that do not conform to the defined retention policy.

Alternatively, you can configure backup retention policy and use the --delete-expired option together with the backup command to remove the outdated backup copies once the new backup is created.

Viewing Backup Information

To view the list of existing backups, run the command:

pg_probackup show -B backupdir

pg_probackup displays the list of all the available backups. You will see the following output:

BACKUP INSTANCE 'two'
===============================================================================================================================
 Instance    ID      Recovery time        Mode    WAL      Current/Parent TLI    Time    Data    Start LSN    Stop LSN   Status
===============================================================================================================================
 two         ORHG08  2017-06-13 14:02:33  FULL    STREAM     1 / 0                 9s    55MB   0/5000028    0/50001B8   OK

BACKUP INSTANCE 'one'
===============================================================================================================================
 Instance    ID      Recovery time        Mode    WAL      Current/Parent TLI    Time    Data    Start LSN    Stop LSN   Status
===============================================================================================================================
 one         ORHFY4  2017-06-13 14:01:18  FULL    ARCHIVE    1 / 0                 3s    22MB   0/2000028    0/30000C8   OK
===============================================================================================================================

For each backup, the following information is provided:

To get more detailed information about the backup, run the show with the backup ID:

pg_probackup show -B backupdir --instance instance_name -i backup_id

The sample output is as follows:

#Configuration
backup-mode = FULL
stream = false

#Compatibility
block-size = 8192
wal-block-size = 8192
checksum-version = 0

#Result backup info
timelineid = 1
start-lsn = 0/04000028
stop-lsn = 0/040000f8
start-time = '2017-05-16 12:57:29'
end-time = '2017-05-16 12:57:31'
recovery-xid = 597
recovery-time = '2017-05-16 12:57:31'
data-bytes = 22288792
status = OK

Deleting Backups

To delete a backup that is no longer required, run the following command:

pg_probackup delete -B backupdir --instance instance_name -i backup_id

This command will delete the backup with the specified backup_id, together with all the incremental backups that followed, if any. This way, you can delete some recent incremental backups, retaining the underlying full backup and some of the incremental backups that follow it.

To delete obsolete WAL files that are not necessary to restore any of the remaining backups, use the --wal option:

pg_probackup delete -B backupdir --instance instance_name --wal

To delete backups that are expired according to the current retention policy, use the --expired option:

pg_probackup delete -B backupdir --instance instance_name --expired

Credits

pg_probackup utility is based on pg_arman, that was originally written by NTT and then developed and maintained by Michael Paquier.