Set up SSH #

If you are going to use pg_probackup in remote mode via SSH, complete the following steps:

pg_probackup in the remote mode via SSH works as follows:

ARCHIVE Mode #

ARCHIVE is the default WAL delivery mode.

For example, to make a FULL backup in ARCHIVE mode, run:

pg_probackup backup -B backup_dir --instance instance_name -b FULL

ARCHIVE backups rely on continuous archiving to get WAL segments required to restore the cluster to a consistent state at the time the backup was taken.

When a backup is taken, pg_probackup ensures that WAL files containing WAL records between Start LSN and Stop LSN actually exist in backup_dir/wal/instance_name directory. pg_probackup also ensures that WAL records between Start LSN and Stop LSN can be parsed. This precaution eliminates the risk of silent WAL corruption.

STREAM Mode #

STREAM is the optional WAL delivery mode.

For example, to make a FULL backup in the STREAM mode, add the --stream flag to the command from the previous example:

pg_probackup backup -B backup_dir --instance instance_name -b FULL --stream --temp-slot

The optional --temp-slot flag ensures that the required segments remain available if the WAL is rotated before the backup is complete.

Unlike backups in ARCHIVE mode, STREAM backups include all the WAL segments required to restore the cluster to a consistent state at the time the backup was taken.

During backup pg_probackup streams WAL files containing WAL records between Start LSN and Stop LSN to backup_dir/backups/instance_name/backup_id/database/pg_wal directory. To eliminate the risk of silent WAL corruption, pg_probackup also checks that WAL records between Start LSN and Stop LSN can be parsed.

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

Page Validation #

If data_checksums are enabled in the database cluster, pg_probackup uses this information to check correctness of data files during backup. While reading each page, pg_probackup checks whether the calculated checksum coincides with the checksum stored in the page header. This guarantees that the Postgres Pro instance and the backup itself have no corrupt pages. Note that pg_probackup reads database files directly from the filesystem, so under heavy write load during backup it can show false-positive checksum mismatches because of partial writes. If a page checksum mismatch occurs, the page is re-read and checksum comparison is repeated.

A page is considered corrupt if checksum comparison has failed more than 300 times. In this case, the backup is aborted.

Even if data checksums are not enabled, pg_probackup always performs sanity checks for page headers.

External Directories #

To back up a directory located outside of the data directory, use the optional --external-dirs parameter that specifies the path to this directory. If you would like to add more than one external directory, you can provide several paths separated by colons on Linux systems or semicolons on Windows systems.

For example, to include /etc/dir1 and /etc/dir2 directories into the full backup of your instance_name instance that will be stored under the backup_dir directory on Linux, run:

pg_probackup backup -B backup_dir --instance instance_name -b FULL --external-dirs=/etc/dir1:/etc/dir2

Similarly, to include C:\dir1 and C:\dir2 directories into the full backup on Windows, run:

pg_probackup backup -B backup_dir --instance instance_name -b FULL --external-dirs=C:\dir1;C:\dir2

pg_probackup recursively copies the contents of each external directory into a separate subdirectory in the backup catalog. Since external directories included into different backups do not have to be the same, when you are restoring the cluster from an incremental backup, only those directories that belong to this particular backup will be restored. Any external directories stored in the previous backups will be ignored.

To include the same directories into each backup of your instance, you can specify them in the pg_probackup.conf configuration file using the set-config command with the --external-dirs option.

Incremental Restore #

The speed of restore from backup can be significantly improved by replacing only invalid and changed pages in already existing Postgres Pro data directory using incremental restore options with the restore command.

To restore the database cluster from a backup in incremental mode, run the restore command with the following options:

pg_probackup restore -B backup_dir --instance instance_name -D data_dir -I incremental_mode
      

Where incremental_mode can take one of the following values:

Regardless of chosen incremental mode, pg_probackup will check, that postmaster in given destination directory is not running and system-identifier is the same as in the backup.

Suppose you want to return an old master as replica after switchover using incremental restore in LSN mode:

==================================================================================================================================================
 Instance  Version  ID      Recovery Time           Mode   WAL Mode  TLI      Time    Data    WAL  Zalg   Zratio  Start LSN    Stop LSN     Status
==================================================================================================================================================
 node      12       QBRNBP  2020-06-11 17:40:58+03  DELTA  ARCHIVE   16/15     40s   194MB   16MB   zlib    8.26  15/2C000028  15/2D000128  OK
 node      12       QBRIDX  2020-06-11 15:51:42+03  PAGE   ARCHIVE   15/15     11s    18MB   16MB   lz4     5.10  14/DC000028  14/DD0000B8  OK
 node      12       QBRIAJ  2020-06-11 15:51:08+03  PAGE   ARCHIVE   15/15     20s   141MB   96MB   pglz    6.22  14/D4BABFE0  14/DA9871D0  OK
 node      12       QBRHT8  2020-06-11 15:45:56+03  FULL   ARCHIVE   15/0   2m:11s  1371MB  416MB   zstd   10.93  14/9D000028  14/B782E9A0  OK

pg_probackup restore -B /backup --instance node -R -I lsn
INFO: Running incremental restore into nonempty directory: "/var/lib/pgsql/12/data"
INFO: Destination directory redo point 15/2E000028 on tli 16 is within reach of backup QBRIDX with Stop LSN 14/DD0000B8 on tli 15
INFO: shift LSN: 14/DD0000B8
INFO: Restoring the database from backup at 2020-06-11 17:40:58+03
INFO: Extracting the content of destination directory for incremental restore
INFO: Destination directory content extracted, time elapsed: 1s
INFO: Removing redundant files in destination directory
INFO: Redundant files are removed, time elapsed: 1s
INFO: Start restoring backup files. PGDATA size: 15GB
INFO: Backup files are restored. Transferred bytes: 1693MB, time elapsed: 43s
INFO: Restore incremental ratio (less is better): 11% (1693MB/15GB)
INFO: Restore of backup QBRNBP completed.
      

Partial Restore #

If you have enabled partial restore before taking backups, you can restore only some of the databases using partial restore options with the restore commands.

To restore the specified databases only, run the restore command with the following options:

pg_probackup restore -B backup_dir --instance instance_name --db-include=database_name
      

The --db-include option can be specified multiple times. For example, to restore only databases db1 and db2, run the following command:

pg_probackup restore -B backup_dir --instance instance_name --db-include=db1 --db-include=db2

To exclude one or more databases from restore, use the --db-exclude option:

pg_probackup restore -B backup_dir --instance instance_name --db-exclude=database_name

The --db-exclude option can be specified multiple times. For example, to exclude the databases db1 and db2 from restore, run the following command:

pg_probackup restore -B backup_dir --instance instance_name --db-exclude=db1 --db-exclude=db2

Partial restore relies on lax behavior of Postgres Pro recovery process toward truncated files. For recovery to work properly, files of excluded databases are restored as files of zero size. After the Postgres Pro cluster is successfully started, you must drop the excluded databases using DROP DATABASE command.

To decouple a single cluster containing multiple databases into separate clusters with minimal downtime, you can do partial restore of the cluster as a standby using the --restore-as-replica option for specific databases.

Viewing Backup Information #

To view the list of existing backups for every instance, run the command:

pg_probackup show -B backup_dir

pg_probackup displays the list of all the available backups. For example:

BACKUP INSTANCE 'node'
===========================================================================================================================================
 Instance  Version  ID      Recovery time           Mode    WAL Mode  TLI  Time    Data   WAL  Zalg  Zratio  Start LSN   Stop LSN    Status
===========================================================================================================================================
 node      10       PYSUE8  2019-10-03 15:51:48+03  FULL    ARCHIVE   1/0   16s  9047kB  16MB  lz4   4.31  0/12000028  0/12000160  OK
 node      10       P7XDQV  2018-04-29 05:32:59+03  DELTA   STREAM    1/1   11s    19MB  16MB  none  1.00  0/15000060  0/15000198  OK
 node      10       P7XDJA  2018-04-29 05:28:36+03  PTRACK  STREAM    1/1   21s    32MB  32MB  none  1.00  0/13000028  0/13000198  OK
 node      10       P7XDHU  2018-04-29 05:27:59+03  PAGE    STREAM    1/1   15s    33MB  16MB  none  1.00  0/11000028  0/110001D0  OK
 node      10       P7XDHB  2018-04-29 05:27:15+03  FULL    STREAM    1/0   11s    39MB  16MB  none  1.00  0/F000028   0/F000198   OK

For each backup, the following information is provided:

You can restore the cluster from the backup only if the backup status is OK or DONE.

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

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

The sample output is as follows:

#Configuration
backup-mode = FULL
stream = false
compress-alg = zlib
compress-level = 1
from-replica = false

#Compatibility
block-size = 8192
wal-block-size = 8192
checksum-version = 1
program-version = 2.1.3
server-version = 10

#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'
end-validation-time = '2017-05-16 12:58:10'
recovery-xid = 597
recovery-time = '2017-05-16 12:57:31'
expire-time = '2020-05-16 12:57:31'
data-bytes = 22288792
wal-bytes = 16777216
uncompressed-bytes = 39961833
pgdata-bytes = 39859393
status = OK
parent-backup-id = 'PT8XFX'
primary_conninfo = 'user=backup passfile=/var/lib/pgsql/.pgpass port=5432 sslmode=disable sslcompression=1 target_session_attrs=any'

Detailed output has additional attributes:

You can also get the detailed information about the backup in the JSON format:

pg_probackup show -B backup_dir --instance instance_name --format=json -i backup_id

The sample output is as follows:

[
  {
      "instance": "node",
      "backups": [
          {
              "id": "PT91HZ",
              "parent-backup-id": "PT8XFX",
              "backup-mode": "DELTA",
              "wal": "ARCHIVE",
              "compress-alg": "zlib",
              "compress-level": 1,
              "from-replica": false,
              "block-size": 8192,
              "xlog-block-size": 8192,
              "checksum-version": 1,
              "program-version": "2.1.3",
              "server-version": "10",
              "current-tli": 16,
              "parent-tli": 2,
              "start-lsn": "0/8000028",
              "stop-lsn": "0/8000160",
              "start-time": "2019-06-17 18:25:11+03",
              "end-time": "2019-06-17 18:25:16+03",
              "end-validation-time": "2019-06-17 18:25:17+03",
              "recovery-xid": 0,
              "recovery-time": "2019-06-17 18:25:15+03",
              "data-bytes": 106733,
              "wal-bytes": 16777216,
              "primary_conninfo": "user=backup passfile=/var/lib/pgsql/.pgpass port=5432 sslmode=disable sslcompression=1 target_session_attrs=any",
              "status": "OK"
          }
      ]
  }
]

Viewing WAL Archive Information #

To view the information about WAL archive for every instance, run the command:

pg_probackup show -B backup_dir [--instance instance_name] --archive

pg_probackup displays the list of all the available WAL files grouped by timelines. For example:

ARCHIVE INSTANCE 'node'
===================================================================================================================================
 TLI  Parent TLI  Switchpoint  Min Segno                 Max Segno                 N segments  Size    Zalg  Zratio  N backups  Status
===================================================================================================================================
 5    1           0/B000000    00000005000000000000000B  00000005000000000000000C  2           685kB   lz4   48.00   0          OK
 4    3           0/18000000   000000040000000000000018  00000004000000000000001A  3           648kB   zstd  77.00   0          OK
 3    2           0/15000000   000000030000000000000015  000000030000000000000017  3           648kB   zstd  77.00   0          OK
 2    1           0/B000108    00000002000000000000000B  000000020000000000000015  5           892kB   zstd  94.00   1          DEGRADED
 1    0           0/0          000000010000000000000001  00000001000000000000000A  10          8774kB  zstd  19.00   1          OK

For each timeline, the following information is provided:

To get more detailed information about the WAL archive in the JSON format, run the command:

pg_probackup show -B backup_dir [--instance instance_name] --archive --format=json

The sample output is as follows:

[
  {
      "instance": "replica",
      "timelines": [
          {
              "tli": 5,
              "parent-tli": 1,
              "switchpoint": "0/B000000",
              "min-segno": "00000005000000000000000B",
              "max-segno": "00000005000000000000000C",
              "n-segments": 2,
              "size": 685320,
              "zratio": 48.00,
              "closest-backup-id": "PXS92O",
              "status": "OK",
              "lost-segments": [],
              "backups": []
          },
          {
              "tli": 4,
              "parent-tli": 3,
              "switchpoint": "0/18000000",
              "min-segno": "000000040000000000000018",
              "max-segno": "00000004000000000000001A",
              "n-segments": 3,
              "size": 648625,
              "zratio": 77.00,
              "closest-backup-id": "PXS9CE",
              "status": "OK",
              "lost-segments": [],
              "backups": []
          },
          {
              "tli": 3,
              "parent-tli": 2,
              "switchpoint": "0/15000000",
              "min-segno": "000000030000000000000015",
              "max-segno": "000000030000000000000017",
              "n-segments": 3,
              "size": 648911,
              "zratio": 77.00,
              "closest-backup-id": "PXS9CE",
              "status": "OK",
              "lost-segments": [],
              "backups": []
          },
          {
              "tli": 2,
              "parent-tli": 1,
              "switchpoint": "0/B000108",
              "min-segno": "00000002000000000000000B",
              "max-segno": "000000020000000000000015",
              "n-segments": 5,
              "size": 892173,
              "zratio": 94.00,
              "closest-backup-id": "PXS92O",
              "status": "DEGRADED",
              "lost-segments": [
                  {
                      "begin-segno": "00000002000000000000000D",
                      "end-segno": "00000002000000000000000E"
                  },
                  {
                      "begin-segno": "000000020000000000000010",
                      "end-segno": "000000020000000000000012"
                  }
              ],
              "backups": [
                  {
                      "id": "PXS9CE",
                      "backup-mode": "FULL",
                      "wal": "ARCHIVE",
                      "compress-alg": "none",
                      "compress-level": 1,
                      "from-replica": "false",
                      "block-size": 8192,
                      "xlog-block-size": 8192,
                      "checksum-version": 1,
                      "program-version": "2.1.5",
                      "server-version": "10",
                      "current-tli": 2,
                      "parent-tli": 0,
                      "start-lsn": "0/C000028",
                      "stop-lsn": "0/C000160",
                      "start-time": "2019-09-13 21:43:26+03",
                      "end-time": "2019-09-13 21:43:30+03",
                      "end-validation-time": "2019-09-13 21:43:31+03",
                      "recovery-xid": 0,
                      "recovery-time": "2019-09-13 21:43:29+03",
                      "data-bytes": 104674852,
                      "wal-bytes": 16777216,
                      "primary_conninfo": "user=backup passfile=/var/lib/pgsql/.pgpass port=5432 sslmode=disable sslcompression=1 target_session_attrs=any",
                      "status": "OK"
                  }
              ]
          },
          {
              "tli": 1,
              "parent-tli": 0,
              "switchpoint": "0/0",
              "min-segno": "000000010000000000000001",
              "max-segno": "00000001000000000000000A",
              "n-segments": 10,
              "size": 8774805,
              "zratio": 19.00,
              "closest-backup-id": "",
              "status": "OK",
              "lost-segments": [],
              "backups": [
                  {
                      "id": "PXS92O",
                      "backup-mode": "FULL",
                      "wal": "ARCHIVE",
                      "compress-alg": "none",
                      "compress-level": 1,
                      "from-replica": "true",
                      "block-size": 8192,
                      "xlog-block-size": 8192,
                      "checksum-version": 1,
                      "program-version": "2.1.5",
                      "server-version": "10",
                      "current-tli": 1,
                      "parent-tli": 0,
                      "start-lsn": "0/4000028",
                      "stop-lsn": "0/6000028",
                      "start-time": "2019-09-13 21:37:36+03",
                      "end-time": "2019-09-13 21:38:45+03",
                      "end-validation-time": "2019-09-13 21:38:46+03",
                      "recovery-xid": 0,
                      "recovery-time": "2019-09-13 21:37:30+03",
                      "data-bytes": 25987319,
                      "wal-bytes": 50331648,
                      "primary_conninfo": "user=backup passfile=/var/lib/pgsql/.pgpass port=5432 sslmode=disable sslcompression=1 target_session_attrs=any",
                      "status": "OK"
                  }
              ]
          }
      ]
  },
  {
      "instance": "master",
      "timelines": [
          {
              "tli": 1,
              "parent-tli": 0,
              "switchpoint": "0/0",
              "min-segno": "000000010000000000000001",
              "max-segno": "00000001000000000000000B",
              "n-segments": 11,
              "size": 8860892,
              "zratio": 20.00,
              "status": "OK",
              "lost-segments": [],
              "backups": [
                  {
                      "id": "PXS92H",
                      "parent-backup-id": "PXS92C",
                      "backup-mode": "PAGE",
                      "wal": "ARCHIVE",
                      "compress-alg": "none",
                      "compress-level": 1,
                      "from-replica": "false",
                      "block-size": 8192,
                      "xlog-block-size": 8192,
                      "checksum-version": 1,
                      "program-version": "2.1.5",
                      "server-version": "10",
                      "current-tli": 1,
                      "parent-tli": 1,
                      "start-lsn": "0/4000028",
                      "stop-lsn": "0/50000B8",
                      "start-time": "2019-09-13 21:37:29+03",
                      "end-time": "2019-09-13 21:37:31+03",
                      "end-validation-time": "2019-09-13 21:37:32+03",
                      "recovery-xid": 0,
                      "recovery-time": "2019-09-13 21:37:30+03",
                      "data-bytes": 1328461,
                      "wal-bytes": 33554432,
                      "primary_conninfo": "user=backup passfile=/var/lib/pgsql/.pgpass port=5432 sslmode=disable sslcompression=1 target_session_attrs=any",
                      "status": "OK"
                  },
                  {
                      "id": "PXS92C",
                      "backup-mode": "FULL",
                      "wal": "ARCHIVE",
                      "compress-alg": "none",
                      "compress-level": 1,
                      "from-replica": "false",
                      "block-size": 8192,
                      "xlog-block-size": 8192,
                      "checksum-version": 1,
                      "program-version": "2.1.5",
                      "server-version": "10",
                      "current-tli": 1,
                      "parent-tli": 0,
                      "start-lsn": "0/2000028",
                      "stop-lsn": "0/2000160",
                      "start-time": "2019-09-13 21:37:24+03",
                      "end-time": "2019-09-13 21:37:29+03",
                      "end-validation-time": "2019-09-13 21:37:30+03",
                      "recovery-xid": 0,
                      "recovery-time": "2019-09-13 21:37:28+03",
                      "data-bytes": 24871902,
                      "wal-bytes": 16777216,
                      "primary_conninfo": "user=backup passfile=/var/lib/pgsql/.pgpass port=5432 sslmode=disable sslcompression=1 target_session_attrs=any",
                      "status": "OK"
                  }
              ]
          }
      ]
  }
]

Most fields are consistent with the plain format, with some exceptions:

Removing Redundant Backups #

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 to remove redundant backup copies.

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

--retention-redundancy=redundancy

Specifies the number of full backup copies to keep in the backup catalog.

--retention-window=window

Defines the earliest point in time for which pg_probackup can complete the recovery. This option is set in the number of days from the current moment. For example, if retention-window=7, pg_probackup must keep at least one backup copy that is older than seven days, with all the corresponding WAL files, and all the backups that follow.

If both --retention-redundancy and --retention-window options are set, both these conditions have to be taken into account when purging the backup catalog. For example, if you set --retention-redundancy=2 and --retention-window=7, pg_probackup has to keep two full backup copies, as well as all the backups required to ensure recoverability for the last seven days:

pg_probackup set-config -B backup_dir --instance instance_name --retention-redundancy=2 --retention-window=7

It is recommended to always keep at least two last parent full backups to avoid errors when creating incremental backups.

To clean up the backup catalog in accordance with retention policy, you have to run the delete command with retention flags, as shown below, or use the backup command with these flags to process the outdated backup copies right when the new backup is created.

For example, to remove all backup copies that no longer satisfy the defined retention policy, run the following command with the --delete-expired flag:

pg_probackup delete -B backup_dir --instance instance_name --delete-expired

If you would like to also remove the WAL files that are no longer required for any of the backups, you should also specify the --delete-wal flag:

pg_probackup delete -B backup_dir --instance instance_name --delete-expired --delete-wal

You can also set or override the current retention policy by specifying --retention-redundancy and --retention-window options directly when running delete or backup commands:

pg_probackup delete -B backup_dir --instance instance_name --delete-expired --retention-window=7 --retention-redundancy=2

Since incremental backups require that their parent full backup and all the preceding incremental backups are available, if any of such backups expire, they still cannot be removed while at least one incremental backup in this chain satisfies the retention policy. To avoid keeping expired backups that are still required to restore an active incremental one, you can merge them with this backup using the --merge-expired flag when running backup or delete commands.

Suppose you have backed up the node instance in the backup_dir directory, with the --retention-window option set to 7, and you have the following backups available on April 10, 2019:

BACKUP INSTANCE 'node'
========================================================================================================================================
 Instance  Version  ID      Recovery time           Mode   WAL     TLI  Time    Data   WAL  Zalg  Zratio  Start LSN   Stop LSN    Status
========================================================================================================================================
 node      10       P7XDHR  2019-04-10 05:27:15+03  FULL   STREAM  1/0   11s   200MB  16MB   none  1.0  0/18000059  0/18000197  OK
 node      10       P7XDQV  2019-04-08 05:32:59+03  PAGE   STREAM  1/0   11s    19MB  16MB   none  1.0  0/15000060  0/15000198  OK
 node      10       P7XDJA  2019-04-03 05:28:36+03  DELTA  STREAM  1/0   21s    32MB  16MB   none  1.0  0/13000028  0/13000198  OK
 -------------------------------------------------------retention window--------------------------------------------------------
 node      10       P7XDHU  2019-04-02 05:27:59+03  PAGE   STREAM  1/0   31s    33MB  16MB   none  1.0  0/11000028  0/110001D0  OK
 node      10       P7XDHB  2019-04-01 05:27:15+03  FULL   STREAM  1/0   11s   200MB  16MB   none  1.0  0/F000028   0/F000198   OK
 node      10       P7XDFT  2019-03-29 05:26:25+03  FULL   STREAM  1/0   11s   200MB  16MB   none  1.0  0/D000028   0/D000198   OK

Even though P7XDHB and P7XDHU backups are outside the retention window, they cannot be removed as it invalidates the succeeding incremental backups P7XDJA and P7XDQV that are still required, so, if you run the delete command with the --delete-expired flag, only the P7XDFT full backup will be removed.

With the --merge-expired option, the P7XDJA backup is merged with the underlying P7XDHU and P7XDHB backups and becomes a full one, so there is no need to keep these expired backups anymore:

pg_probackup delete -B backup_dir --instance node --delete-expired --merge-expired
pg_probackup show -B backup_dir

BACKUP INSTANCE 'node'
=======================================================================================================================================
 Instance  Version  ID      Recovery time           Mode  WAL     TLI  Time    Data   WAL  Zalg  Zratio  Start LSN   Stop LSN    Status
=======================================================================================================================================
 node      10       P7XDHR  2019-04-10 05:27:15+03  FULL  STREAM  1/0   11s   200MB  16MB  none   1.0  0/18000059  0/18000197  OK
 node      10       P7XDQV  2019-04-08 05:32:59+03  PAGE  STREAM  1/0   11s    19MB  16MB  none   1.0  0/15000060  0/15000198  OK
 node      10       P7XDJA  2019-04-03 05:28:36+03  FULL  STREAM  1/0   21s    32MB  16MB  none   1.0  0/13000028  0/13000198  OK

The Time field for the merged backup displays the time required for the merge.

Pinning Backups #

If you need to keep certain backups longer than the established retention policy allows, you can pin them for arbitrary time. For example:

pg_probackup set-backup -B backup_dir --instance instance_name -i backup_id --ttl=30d

This command sets the expiration time of the specified backup to 30 days starting from the time indicated in its recovery-time attribute.

You can also explicitly set the expiration time for a backup using the --expire-time option. For example:

pg_probackup set-backup -B backup_dir --instance instance_name -i backup_id --expire-time="2020-01-01 00:00:00+03"

Alternatively, you can use the --ttl and --expire-time options with the backup command to pin the newly created backup:

pg_probackup backup -B backup_dir --instance instance_name -b FULL --ttl=30d
pg_probackup backup -B backup_dir --instance instance_name -b FULL --expire-time="2020-01-01 00:00:00+03"

To check if the backup is pinned, run the show command:

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

If the backup is pinned, it has the expire-time attribute that displays its expiration time:

...
recovery-time = '2017-05-16 12:57:31'
expire-time = '2020-01-01 00:00:00+03'
data-bytes = 22288792
...

You can unpin the backup by setting the --ttl option to zero:

pg_probackup set-backup -B backup_dir --instance instance_name -i backup_id --ttl=0

Configuring WAL Archive Retention Policy #

When continuous WAL archiving is enabled, archived WAL segments can take a lot of disk space. Even if you delete old backup copies from time to time, the --delete-wal flag can purge only those WAL segments that do not apply to any of the remaining backups in the backup catalog. However, if point-in-time recovery is critical only for the most recent backups, you can configure WAL archive retention policy to keep WAL archive of limited depth and win back some more disk space.

To configure WAL archive retention policy, you have to run the set-config command with the --wal-depth option that specifies the number of backups that can be used for PITR. This setting applies to all the timelines, so you should be able to perform PITR for the same number of backups on each timeline, if available. Pinned backups are not included into this count: if one of the latest backups is pinned, pg_probackup ensures that PITR is possible for one extra backup.

To remove WAL segments that do not satisfy the defined WAL archive retention policy, you simply have to run the delete or backup command with the --delete-wal flag. For archive backups, WAL segments between Start LSN and Stop LSN are always kept intact, so such backups remain valid regardless of the --wal-depth setting and can still be restored, if required.

You can also use the --wal-depth option with the delete and backup commands to override the previously defined WAL archive retention policy and purge old WAL segments on the fly.

Suppose you have backed up the node instance in the backup_dir directory and configured continuous WAL archiving:

pg_probackup show -B backup_dir --instance node

BACKUP INSTANCE 'node'
=======================================================================================================================================
 Instance  Version  ID      Recovery Time           Mode   WAL Mode  TLI  Time   Data   WAL  Zalg  Zratio  Start LSN   Stop LSN  Status
=======================================================================================================================================
 node      11       PZ9442  2019-10-12 10:43:21+03  DELTA  STREAM    1/0   10s  121kB  16MB  none  1.00  0/46000028  0/46000160  OK
 node      11       PZ943L  2019-10-12 10:43:04+03  FULL   STREAM    1/0   10s  180MB  32MB  none  1.00  0/44000028  0/44000160  OK
 node      11       PZ7YR5  2019-10-11 19:49:56+03  DELTA  STREAM    1/1   10s  112kB  32MB  none  1.00  0/41000028  0/41000160  OK
 node      11       PZ7YMP  2019-10-11 19:47:16+03  DELTA  STREAM    1/1   10s  376kB  32MB  none  1.00  0/3E000028  0/3F0000B8  OK
 node      11       PZ7YK2  2019-10-11 19:45:45+03  FULL   STREAM    1/0   11s  180MB  16MB  none  1.00  0/3C000028  0/3C000198  OK
 node      11       PZ7YFO  2019-10-11 19:43:04+03  FULL   STREAM    1/0   10s   30MB  16MB  none  1.00  0/2000028   0/200ADD8   OK

You can check the state of the WAL archive by running the show command with the --archive flag:

pg_probackup show -B backup_dir --instance node --archive

ARCHIVE INSTANCE 'node'
====================================================================================================================================
 TLI  Parent TLI  Switchpoint  Min Segno                 Max Segno                 N segments  Size  Zalg  Zratio  N backups  Status
====================================================================================================================================
 1    0           0/0          000000010000000000000001  000000010000000000000047  71          36MB  zstd  31.00   6          OK

WAL purge without --wal-depth cannot achieve much, only one segment is removed:

pg_probackup delete -B backup_dir --instance node --delete-wal

ARCHIVE INSTANCE 'node'
=====================================================================================================================================
 TLI  Parent TLI  Switchpoint  Min Segno                 Max Segno                 N segments  Size  Zalg  Zratio  N backups  Status
=====================================================================================================================================
 1    0           0/0          000000010000000000000002  000000010000000000000047  70          34MB  zstd  32.00   6          OK

If you would like, for example, to keep only those WAL segments that can be applied to the latest valid backup, set the --wal-depth option to 1:

pg_probackup delete -B backup_dir --instance node --delete-wal --wal-depth=1

ARCHIVE INSTANCE 'node'
=====================================================================================================================================
 TLI  Parent TLI  Switchpoint  Min Segno                 Max Segno                 N segments  Size   Zalg  Zratio  N backups  Status
=====================================================================================================================================
 1    0           0/0          000000010000000000000046  000000010000000000000047  2           143kB  zstd  228.00  6          OK

Alternatively, you can use the --wal-depth option with the backup command:

pg_probackup backup -B backup_dir --instance node -b DELTA --wal-depth=1 --delete-wal

ARCHIVE INSTANCE 'node'
====================================================================================================================================
 TLI  Parent TLI  Switchpoint  Min Segno                 Max Segno                 N segments  Size  Zalg  Zratio  N backups  Status
====================================================================================================================================
 1    0           0/0          000000010000000000000048  000000010000000000000049  1           72kB  zstd  228.00  7          OK

version #

pg_probackup version

Prints pg_probackup version and edition, as well as Postgres Pro version and edition.

help #

pg_probackup help [command]

Displays the synopsis of pg_probackup commands. If one of the pg_probackup commands is specified, shows detailed information about the options that can be used with this command.

init #

pg_probackup init -B backup_dir [--skip-if-exists] [--help]
[logging_options]

Initializes the backup catalog in backup_dir that will store backup copies, WAL archive, and meta information for the backed up database clusters. If the specified backup_dir already exists, it must be empty. Otherwise, pg_probackup displays a corresponding error message. You can ignore this error by specifying the --skip-if-exists option. Although the backup will not be initialized, the application will return 0 code.

For details, see the section Initializing the Backup Catalog.

add-instance #

pg_probackup add-instance -B backup_dir -D data_dir --instance instance_name
[--skip-if-exists] [--help] [logging_options]

Initializes a new backup instance inside the backup catalog backup_dir and generates the pg_probackup.conf configuration file that controls pg_probackup settings for the cluster with the specified data_dir data directory. If the catalog was already initialized, you can ignore the error by specifying --skip-if-exists.

For details, see the section Adding a New Backup Instance.

del-instance #

pg_probackup del-instance -B backup_dir --instance instance_name [--help]
[logging_options]

Deletes all backups and WAL files associated with the specified instance.

set-config #

pg_probackup set-config -B backup_dir --instance instance_name
[--help] [--pgdata=pgdata-path]
[--retention-redundancy=redundancy][--retention-window=window][--wal-depth=wal_depth]
[--compress-algorithm=compression_algorithm] [--compress-level=compression_level]
[-d dbname] [-h host] [-p port] [-U username]
[--archive-timeout=timeout] [--external-dirs=external_directory_path]
[--restore-command=cmdline]
[remote_options] [remote_wal_archive_options] [logging_options]

Adds the specified connection, compression, retention, logging, and external directory settings into the pg_probackup.conf configuration file, or modifies the previously defined values.

For all available settings, see the Options section.

It is not recommended to edit pg_probackup.conf manually.

set-backup #

pg_probackup set-backup -B backup_dir --instance instance_name -i backup_id
{--ttl=ttl | --expire-time=time}
[--note=backup_note] [--help] [logging_options]

Sets the provided backup-specific settings into the backup.control configuration file, or modifies the previously defined values.

For all available pinning settings, see the section Pinning Options.

show-config #

pg_probackup show-config -B backup_dir --instance instance_name [--format=plain|json]
[--no-scale-units] [logging_options]

Displays all the current pg_probackup configuration settings, including those that are specified in the pg_probackup.conf configuration file located in the backup_dir/backups/instance_name directory and those that were provided on a command line. You can specify the --format=json option to get the result in the JSON format. By default, configuration settings are shown as plain text.

You can also specify the --no-scale-units option to display time and memory configuration settings in their base (unscaled) units. Otherwise, the values are scaled to larger units for optimal display. For example, if archive-timeout is 300, then 5min is displayed, but if archive-timeout is 301, then 301s is displayed. Also, if the --no-scale-units option is specified, configuration settings are displayed without units and for the JSON format, numeric and boolean values are not enclosed in quotes. This facilitates parsing the output.

To edit pg_probackup.conf, use the set-config command.

show #

pg_probackup show -B backup_dir
[--help] [--instance instance_name [-i backup_id | --archive]] [--format=plain|json] [--no-color] [--show-symlinks]
[logging_options]

Shows the contents of the backup catalog. If instance_name and backup_id are specified, shows detailed information about this backup. If the --archive option is specified, shows the contents of WAL archive of the backup catalog.

By default, the contents of the backup catalog is shown as plain text. You can specify the --format=json option to get the result in the JSON format. If --no-color flag is used, then the output is not colored.

If the --show-symlinks option is specified, the command also shows the links between merged backups and the original full backups that incremental backups were merged to.

For details on usage, see the sections Managing the Backup Catalog and Viewing WAL Archive Information.

backup #

pg_probackup backup -B backup_dir -b backup_mode --instance instance_name
[--help] [-j num_threads] [--progress]
[-C] [--stream [-S slot_name] [--temp-slot[=true|false|on|off]]] [--backup-pg-log]
[--no-validate] [--skip-block-validation]
[-w --no-password] [-W --password]
[--write-rate-limit=bitrate]
[--archive-timeout=timeout] [--external-dirs=external_directory_path]
[--no-sync] [--note=backup_note]
[connection_options] [compression_options] [remote_options]
[retention_options] [pinning_options] [logging_options]

Creates a backup copy of the Postgres Pro instance.

Additionally, connection options, retention options, pinning options, remote mode options, compression options, logging options, and common options can be used.

For details on usage, see the section Creating a Backup.

restore #

pg_probackup restore -B backup_dir --instance instance_name
[--help] [-D data_dir] [-i backup_id]
[-j num_threads] [--progress]
[-T OLDDIR=NEWDIR] [--external-mapping=OLDDIR=NEWDIR] [--skip-external-dirs]
[-R | --restore-as-replica] [--no-validate] [--skip-block-validation]
[--force] [--no-sync]
[--restore-command=cmdline]
[--primary-conninfo=primary_conninfo]
[-S | --primary-slot-name=slot_name]
[-X wal_dir | --waldir=wal_dir]
[recovery_target_options] [logging_options] [remote_options]
[partial_restore_options] [remote_wal_archive_options]

Restores the Postgres Pro instance from a backup copy located in the backup_dir backup catalog. If you specify a recovery target option, pg_probackup finds the closest backup and restores it to the specified recovery target. If neither the backup ID nor recovery target options are provided, pg_probackup uses the most recent backup to perform the recovery.

Additionally, recovery target options, remote mode options, remote WAL archive options, logging options, partial restore options, and common options can be used.

For details on usage, see the section Restoring a Cluster.

checkdb #

pg_probackup checkdb
[-B backup_dir] [--instance instance_name] [-D data_dir]
[--help] [-j num_threads] [--progress]
[--amcheck [--skip-block-validation] [--checkunique] [--heapallindexed]]
[connection_options] [logging_options]

Verifies the Postgres Pro database cluster correctness by detecting physical and logical corruption.

Additionally, connection options and logging options can be used.

For details on usage, see the section Verifying a Cluster.

validate #

pg_probackup validate -B backup_dir
[--help] [--instance instance_name] [-i backup_id]
[-j num_threads] [--progress]
[--skip-block-validation]
[recovery_target_options] [logging_options]

Verifies that all the files required to restore the cluster are present and are not corrupt. If instance_name is not specified, pg_probackup validates all backups available in the backup catalog. If you specify the instance_name without any additional options, pg_probackup validates all the backups available for this backup instance. If you specify the instance_name with a recovery target option and/or a backup_id, pg_probackup checks whether it is possible to restore the cluster using these options.

For details, see the section Validating a Backup.

merge #

pg_probackup merge -B backup_dir --instance instance_name -i backup_id
[--help] [-j num_threads] [--progress] [--no-validate] [--no-sync]
[logging_options]

Merges backups that belong to a common incremental backup chain. If you specify a full backup, it will be merged with its first incremental backup. If you specify an incremental backup, it will be merged to its parent full backup, together with all incremental backups between them. Once the merge is complete, the full backup takes in all the merged data, and the incremental backups are removed as redundant.

For details, see the section Merging Backups.

delete #

pg_probackup delete -B backup_dir --instance instance_name
[--help] [-j num_threads] [--progress]
[--retention-redundancy=redundancy][--retention-window=window][--wal-depth=wal_depth] [--delete-wal]
{-i backup_id | --delete-expired [--merge-expired] | --merge-expired | --status=backup_status}
[--dry-run] [--no-validate] [--no-sync] [logging_options]

Deletes backup with specified backup_id or launches the retention purge of backups and archived WAL that do not satisfy the current retention policies.

For details, see the sections Deleting Backups, Retention Options, and Configuring Retention Policy.

archive-push #

pg_probackup archive-push -B backup_dir --instance instance_name
--wal-file-name=wal_file_name [--wal-file-path=wal_file_path]
[--help] [--no-sync] [--compress] [--no-ready-rename] [--overwrite]
[-j num_threads] [--batch-size=batch_size]
[--archive-timeout=timeout]
[--compress-algorithm=compression_algorithm]
[--compress-level=compression_level]
[remote_options] [logging_options]

Copies WAL files into the corresponding subdirectory of the backup catalog and validates the backup instance by instance_name and system-identifier. If parameters of the backup instance and the cluster do not match, this command fails with the following error message: Refuse to push WAL segment segment_name into archive. Instance parameters mismatch.

If the files to be copied already exists in the backup catalog, pg_probackup computes and compares their checksums. If the checksums match, archive-push skips the corresponding file and returns a successful execution code. Otherwise, archive-push fails with an error. If you would like to replace WAL files in the case of checksum mismatch, run the archive-push command with the --overwrite flag.

Each file is copied to a temporary file with the .part suffix. If the temporary file already exists, pg_probackup will wait archive_timeout seconds before discarding it. After the copy is done, atomic rename is performed. This algorithm ensures that a failed archive-push will not stall continuous archiving and that concurrent archiving from multiple sources into a single WAL archive has no risk of archive corruption.

The postgres server requests WAL segments one at a time. To speed up archiving, you can specify the --batch-size option to copy WAL segments in batches of the specified size. If --batch-size option is used, then you can also specify the -j option to copy the batch of WAL segments on multiple threads.

WAL segments copied to the archive are synced to disk unless the --no-sync flag is used.

You can use archive-push in the archive_command Postgres Pro parameter to set up continuous WAL archiving.

For details, see sections Archiving Options and Compression Options.

archive-get #

pg_probackup archive-get -B backup_dir --instance instance_name --wal-file-path=wal_file_path --wal-file-name=wal_file_name
[-j num_threads] [--batch-size=batch_size]
[--prefetch-dir=prefetch_dir_path] [--no-validate-wal]
[--help] [remote_options] [logging_options]

Copies WAL files from the corresponding subdirectory of the backup catalog to the cluster's write-ahead log location. This command is automatically set by pg_probackup as part of the restore_command when restoring backups using a WAL archive. You do not need to set it manually.

The postgres server requests WAL segments one at a time. To speed up recovery, you can specify the --batch-size option to copy WAL segments in batches of the specified size. If --batch-size option is used, then you can also specify the -j option to copy the batch of WAL segments on multiple threads.

For details, see section Archiving Options.

catchup #

pg_probackup catchup -b catchup_mode
--source-pgdata=path_to_pgdata_on_remote_server
--destination-pgdata=path_to_local_dir
[--help] [-j | --threads=num_threads] [--dry-run]
[--write-rate-limit=bitrate]
[--stream [--temp-slot[=true|false|on|off]] [-P | --perm-slot] [-S | --slot=slot_name]]
[--exclude-path=PATHNAME]
[-T OLDDIR=NEWDIR]
[-X | --waldir=wal_dir]
[connection_options] [remote_options]
[logging_options]

Creates a copy of a Postgres Pro instance without using the backup catalog.

Additionally, connection options, remote mode options can be used.

For details on usage, see the section Cloning and Synchronizing Postgres Pro Instance.

Common Options #

The list of general options.

Recovery Target Options #

If continuous WAL archiving is configured, you can use one of these options together with restore or validate commands to specify the moment up to which the database cluster must be restored or validated.

Retention Options #

You can use these options together with backup and delete commands.

For details on configuring retention policy, see the section Configuring Retention Policy.

Pinning Options #

You can use these options together with backup and set-backup commands.

For details on backup pinning, see the section Backup Pinning.

Logging Options #

You can use these options with any command.

Connection Options #

You can use these options together with backup, catchup, and checkdb commands.

All libpq environment variables are supported.

Compression Options #

You can use these options together with backup and archive-push commands.

Archiving Options #

These options can be used with the archive-push command in the archive_command setting and the archive-get command in the restore_command setting.

Additionally, remote mode options and logging options can be used.

Remote Mode Options #

This section describes the options related to running pg_probackup operations remotely via SSH. These options can be used with add-instance, set-config, backup, catchup, restore, archive-push, and archive-get commands.

For details on configuring and using the remote mode, see the section called “Configuring the Remote Mode” and the section called “Using pg_probackup in the Remote Mode”.

Remote WAL Archive Options #

This section describes the options used to provide the arguments for remote mode options in archive-get used in the restore_command command when restoring ARCHIVE backups or performing PITR.

Incremental Restore Options #

This section describes the options for incremental cluster restore. These options can be used with the restore command.

Partial Restore Options #

This section describes the options for partial cluster restore. These options can be used with the restore command.

Testing and Debugging Options #

This section describes options useful only in a test or development environment.