Limitations

pg_probackup currently has the following limitations:

Initializing the Backup Catalog

To initialize the backup catalog, run the following command:

pg_probackup init -B backupdir -D datadir

where:

The utility creates the backupdir backup catalog, with the following files and subdirectories:

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

Depending on whether you are going to use autonomous or archive backup strategies, Postgres Pro Enterprise cluster configuration will differ. To backup 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”.

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 the sections below, the backup role is used as an example.

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_switch_xlog() 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;

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_switch_xlog() 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;

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 -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 -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, the latest available backup is 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 --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:

pg_probackup restore -B backupdir -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 --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 --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 as the server will fail if tablespaces are in use, because the backup would have to be written to the same directories as the original tablespaces.

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 -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 Enterprise 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 -b FULL -j 4

Specifying Connection Parameters

If you define connection parameters in the pg_probackup.conf configuration file, you can omit them 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 connection 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 --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

pg_probackup displays the list of all the available backups. 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 -i backup_id

The sample output is as follows:

#Configuration
backup-mode = FULL
stream = false

#Compatibility
block-size = 8192
xlog-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 -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 --wal

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

pg_probackup delete --expired

For details, see the section called “Configuring Backup Retention Policy”.

Credits

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