Common Options

Backup Options

Restore and Validate Options

Delete Options

Retention Options

Connection parameters

pg_probackup also accepts the following command-line arguments for connection parameters:

Initial Setup

In any usage scenario, first of all Postgres Pro Enterprise server should be configured and backup catalog should be initialized.

pg_probackup initial setup, as well as further work with the utility, is performed by Postgres Pro Enterprise process owner (usually postgres).

A connection to database server is required for pg_probackup to take backups. Database user, which pg_probackup is connected as, must have sufficient privileges to execute some administrative functions. The user must also have REPLICATION attribute in order to make autonomous backups. pg_probackup can be connected as a superuser, but it is advisable to create a separate user with the following minimum required privileges:

CREATE ROLE backup WITH LOGIN REPLICATION;
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;
     

When using Postgres Pro server, additional privileges are required for taking incremental backups:

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

Postgres Pro Enterprise server configuration must accept connections for the user in pg_hba.conf. For autonomous backups, replication connections must also be accepted, and max_wal_senders value should be high enough to allow pg_probackup to connect for streaming WAL files during backup.

The wal_level parameter must be replica of higher (archive for versions below 9.5).

To initialize backup directory, execute the following command:

pg_probackup init -B backup_directory -D data_directory
     

The -B option specifies the directory where backups and meta information will be stored. As this option is required for all pg_probackup commands, it makes sense to specify it once in the BACKUP_PATH environment variable.

The -D option specifies the database cluster's data directory. It is handy to set it in the PGDATA environment variable instead of specifying it every time in command line. In the subsequent examples these options are omitted.

The utility creates the specified directory and all the necessary files and subdirectories in it:

The backup directory can be created beforehand, but in this case it must be empty.

Autonomous Backups

Autonomous backups offer the simplest way to make a backup without the need to configure Postgres Pro Enterprise for continuous archiving. Such backups contain database cluster files as well as WAL files necessary for recovery.

Without a WAL files archive, database cluster can be restored from an autonomous backup only to its state at the moment the backup was taken.

To make an autonomous backup, execute the following command:

pg_probackup backup -b full --stream
     

Additionally this command should be supplied with connection options. These options are specified exactly the same way as for other Postgres Pro Enterprise utilities: either by command line options (-h/--host, -p/--port, -U/--username, -d/--dbname) or by environment variables (PGHOST, PGPORT, PGUSER, PGDATABASE). If nothing is given, the default values are taken (local connection, both database user name and database name are the same as operating user name). Any database in the cluster can be specified to connect to.

To view the existing backups, run the command:

pg_probackup show
     

The following information is given:

To get more detailed information about the backup, specify its identifier in show command:

pg_probackup show backup_id
     

To make sure a backup is correctly written to disk, pg_probackup automatically checks its checksums immediately after the backup was taken. A backup can be explicitly revalidated by running the following command:

pg_probackup validate backup_id
     

For details, see the section called “Checking Cluster and Backup Consistency” and the section called “Validating Backups”.

To restore the database cluster from the backup, first stop the Postgres Pro Enterprise service (if it is still running) and then execute the following command:

pg_probackup restore backup_id
     

Then start the database service. During startup, Postgres Pro Enterprise will recover a self-consistent state by replaying WAL files and will be ready to accept connections.

Note that restoring from a backup can be performed exclusively by pg_probackup utility. Inside backup_directory/backups/backup_id/database/ directory you can find the files corresponding to those in the cluster's data directory. Nevertheless, you cannot copy these files directly into the data directory as pg_probackup always stores them compressed to save disk space.

Continuous Archiving and Full Backups

Continuous archiving allows to restore database cluster's state not only at the moment backup was taken, but at arbitrary point in time. In most cases pg_probackup is used along with continuous archiving.

Note that autonomous backups can still be useful together with continuous archiving:

To enable continuous archiving on Postgres Pro Enterprise server, configure the following parameters:

Utilities like rsync that copy WAL files over network, are not currently supported; files must be accessible in server's file system. To access files from a remote server, a network file system can be used.

To take a backup, execute the following command (specify additional connection options if needed):

pg_probackup backup -b full
     

The backup will only contain database cluster's files. WAL files necessary for recovery will be read from archive in backup_directory/wal/.

To restore the cluster from a backup, make sure that the database service is stopped and run the following command:

pg_probackup restore
     

The database cluster will be restored from the latest backup available, and recovery.conf file will be created to access the archived WAL files. When started, Postgres Pro Enterprise server will automatically recover database cluster's state using all available WAL files in the archive.

To restore the cluster's state at some arbitrary point in time, the following options (which correspond to recovery options in recovery.conf) can be added:

pg_probackup automatically chooses the backup that is the closest to the specified recovery target, and starts the recovery.

All the described commands can operate autonomous backups the same way as full ones, using WAL files either from the backup itself or from the archive.

A backup identifier can be specified right after the restore command to restore database cluster's state at the moment shown in 'Recovery time' attribute of that backup:

pg_probackup restore backup_id
     

For autonomous backups WAL archive will not be used. Full backups will use WAL archive only to recover to self-consistent state.

When both backup identifier and one of --time or --xid options are specified for restore command, recovery will start from the specified backup and will proceed up to the specified recovery target. Usually there is no need in such mode, as backup identifier can be omitted to allow pg_probackup to choose it automatically.

Incremental Backups

In addition to full backups pg_probackup allows to take incremental backups, containing only the pages that have changed since the previous backup was taken. This way backups are smaller and may take less time to complete.

There are two modes for incremental backups: to track changes by scanning WAL files (PAGE), and to track changes on-the-fly (PTRACK).

In the first mode pg_probackup scans all WAL files in archive starting from the moment the previous backup (either full or incremental) was taken. Newly created backup will contain only the pages that were mentioned in WAL records.

This way of operation requires all the WAL files since the previous backup to be present in the archive. In case the total size of these files is comparable to total size of database cluster's files, there will be no speedup (but still backup can be smaller by size).

pg_probackup backup -b page
     

The second mode (tracking changes on-the-fly) requires Postgres Pro server and will not work with the standard PostgreSQL; continuous archiving is not necessary for it to operate.

When ptrack_enable parameter is on, Postgres Pro server tracks changes in data pages. Each time a WAL record for some relation's page is constructed, this page is marked in a special ptrack fork for this relation. As one page requires just one bit in the fork, the fork is quite small but significantly speeds up the process of taking a backup. Tracking implies some minor overhead for the database server.

While taking a backup (either full or incremental), pg_probackup clears ptrack fork of relations being processed. This ensures that the next incremental backup will contain only pages that have changed since the previous backup.

pg_probackup backup -b ptrack
     

If a backup resulted in an error (for example, it was interrupted), some of relations probably have their ptrack forks already cleared. In this case next incremental backup will contain just part of all changes, which is useless. The same is true when ptrack_enable parameter was turned on after the full backup was taken or when it was turned off for some time. Currently, pg_probackup does not verify that all changes for the increment were actually tracked. Fresh full backup should be taken before incremental ones in such circumstances.

To restore the database cluster from an incremental backup, pg_probackup first restores the full backup and then sequentially applies all the necessary increments. This is done automatically; restoration is managed exactly the same way as for full backups.

Incremental backup can be made autonomous by specifying --stream command line option. Such backup is autonomous only in regard to WAL archive: full backup and previous incremental backups are still needed to restore the cluster.

Configuring Backup Retention Policy

By default, all backup copies created with pg_probackup are stored in the specified backup directory. To save disk space, you can configure retention policy and set up your system to 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:

To view the current retention policy settings, run this command:

pg_probackup retention show

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

pg_probackup retention purge

pg_probackup deletes all backup copies and the corresponding WAL files that do not conform to the policy defined in the pg_probackup.conf file. If both redundancy and window options are set, pg_probackup keeps backup copies that satisfy both conditions. For example, if you set redundancy=2 and 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 7 days.

Alternatively, you can override the default policy using --redundancy and --window command-line options. For example:

pg_probackup retention purge --redundancy=3 --window=5

As a result, pg_probackup keeps three backup copies, at least one of which is older than five days.

Deleting Backups

Unnecessary backup can be deleted by specifying its identifier in delete command:

pg_probackup delete backup_id
     

This command will delete the specified backup along with all the following incremental backups, if any.

This way it is possible to delete some recent incremental backups, retaining an underlying full backup and some of incremental backups that follow it. In this case the next backup in PTRACK mode will not be correct as some changes since the last retained backup will be lost. Either full backup or incremental backup in PAGE mode (given that all necessary WAL files are still in the archive) should be taken then.

If --wal option is specified, WAL files not necessary to restore any of remaining backups will be deleted as well. This is safe, because deletion of any backup will keep every possibly necessary WAL files.

To delete unnecessary WAL files without deleting any of backups, execute delwal command:

pg_probackup delwal
     

This command operates the same way as --wal option of delete command, except that it does not delete any backups.

Backup identifier can be specified in delwal command. In this case all WAL files will be deleted, except for those needed to restore from the specified backup and more recent backups.

pg_probackup delwal backup_id
     

This mode should be used with caution as it allows to delete WAL files required for some of the existing backups.

Alternatively, you can configure backup retention policy and set up your system to periodically clean up redundant backup copies accordingly. For details, see the section called “Configuring Backup Retention Policy”.

Backup from Standby

If replication is in use, starting with server version 9.6 a backup can be taken not only from primary server, but also from standby. Backup taken from standby is absolutely interchangeable with backup taken from primary (bearing in mind possible replication delay). Page-level incremental backups from standby are not allowed.

Currently it is required for primary database server to have full_page_writes turned on (in future this requirement may be relaxed in the case checksums are enabled on data pages).

The same backup directory can be used for pg_probackup on both servers, primary and standby, as long as it is accessible in both server's file systems. This way all backups, taken from either primary or standby, are shown together and could be managed from one server or from the other.

A backup can be used to restore primary database server as well as standby. It depends on the server on which pg_probackup is executed with restore command. Note that recovered Postgres Pro Enterprise will always run as primary server if started right after the pg_probackup. To run it as standby, edit recovery.conf file created by pg_probackup: at least delete every parameter that specify recovery target (recovery_target, recovery_target_time, and recovery_target_xid), change target timeline to 'latest', and add standby_mode = 'on'. Probably primary_conninfo should be added too for streaming replication, and hot_standby = 'on' in database configuration parameters for hot standby mode.

Parallel Execution

Backup, recovery, and validating process can be executed in several parallel threads. This can significantly speed up the operation given enough resources (CPU cores, disk, and network throughput).

Parallel execution is controlled by -j/--threads command line option, for example:

pg_probackup backup -b full -j 4
     

or

pg_probackup restore -j 4
     

Note that parallel recovery applies only to copying data from backup to cluster's data directory. When Postgres Pro Enterprise server is started, it starts to replay WAL records (either from the archive or from local directory), and this currently cannot be done in parallel.

Checking Cluster and Backup Consistency

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 backup is free of corrupted pages; taking full backup effectively checks correctness of all cluster's data files.

Pages are packed before going to backup, leaving unused parts of pages behind (see Section 63.6). Hence the restored database cluster is not an exact copy of the original, but is binary-compatible with it.

Whether page checksums are enabled or not, pg_probackup calculates checksums for each file in a backup. Checksums are checked immediately after backup is taken and right before restore, to timely detect possible backup corruptions.

Validating Backups

Before restoring the database cluster, you can validate the available backups. To make sure the recovery is possible, run the validate command with the exact options you are going to use for recovery. pg_probackup checks that all the required backup files are present and can be used to restore the database cluster if needed.

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

pg_probackup validate --xid=xid backup_id

For the list of available options, see the section called “Options”.

If validation completed 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.