pg_probackup Documentation

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 PostgreSQL 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 100 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 PostgreSQL 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  Zratio  Start LSN   Stop LSN    Status 
======================================================================================================================================
 node      16       SCUN3Y  2024-05-02 11:19:16+03  DELTA   STREAM    16/15   7s   92MB  208MB    2.27  0/3C0043A8  0/46159C70  OK     
 node      16       SCUN3M  2024-05-02 11:19:01+03  PTRACK  STREAM    15/15  10s   30MB   16MB    2.23  0/32000028  0/32005ED0  OK     
 node      16       SCUN39  2024-05-02 11:18:50+03  PAGE    STREAM    15/15  12s   46MB   32MB    1.44  0/2A000028  0/2B0000B8  OK     
 node      16       SCUN2V  2024-05-02 11:18:38+03  FULL    STREAM    15/0   11s  154MB   16MB    2.32  0/23000028  0/23000168  OK     

backup_user@backup_host:~$ pg_probackup-16 restore -B /mnt/backups --instance=node -R -I lsn
INFO: Destination directory and tablespace directories are empty, disable incremental restore
INFO: Validating parents for backup SCUN3Y
INFO: Validating backup SCUN2V
INFO: Backup SCUN2V data files are valid
INFO: Validating backup SCUN39
INFO: Backup SCUN39 data files are valid
INFO: Validating backup SCUN3M
INFO: Backup SCUN3M data files are valid
INFO: Validating backup SCUN3Y
INFO: Backup SCUN3Y data files are valid
INFO: Backup SCUN3Y WAL segments are valid
INFO: Backup SCUN3Y is valid.
INFO: Restoring the database from backup SCUN3Y
INFO: Start restoring backup files. PGDATA size: 759MB
INFO: Backup files are restored. Transfered bytes: 759MB, time elapsed: 3s
INFO: Restore incremental ratio (less is better): 100% (759MB/759MB)
INFO: Syncing restored files to disk
INFO: Restored backup files are synced, time elapsed: 1s
INFO: Restore of backup SCUN3Y 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 PostgreSQL recovery process toward truncated files. For recovery to work properly, files of excluded databases are restored as files of zero size. After the PostgreSQL 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  Zratio  Start LSN   Stop LSN    Status 
======================================================================================================================================
 node      16       SCUN4E  2024-05-02 11:19:37+03  FULL    ARCHIVE   1/0   13s  239MB   16MB    2.31  0/4C000028  0/4D0000B8  OK     
 node      16       SCUN3Y  2024-05-02 11:19:16+03  DELTA   STREAM    1/1    7s   92MB  208MB    2.27  0/3C0043A8  0/46159C70  OK     
 node      16       SCUN3M  2024-05-02 11:19:01+03  PTRACK  STREAM    1/1   10s   30MB   16MB    2.23  0/32000028  0/32005ED0  OK     
 node      16       SCUN39  2024-05-02 11:18:50+03  PAGE    STREAM    1/1   12s   46MB   32MB    1.44  0/2A000028  0/2B0000B8  OK     
 node      16       SCUN2V  2024-05-02 11:18:38+03  FULL    STREAM    1/0   11s  154MB   16MB    2.32  0/23000028  0/23000168  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
xlog-block-size = 8192
checksum-version = 1
program-version = 2.5.15
server-version = 16

#Result backup info
timelineid = 1
start-lsn = 0/4C000028
stop-lsn = 0/4D0000B8
start-time = '2024-05-02 11:19:26+03'
end-time = '2024-05-02 11:19:39+03'
recovery-xid = 743
recovery-time = '2024-05-02 11:19:37+03'
data-bytes = 250827955
wal-bytes = 16777216
uncompressed-bytes = 578216425
pgdata-bytes = 578216107
status = OK
primary_conninfo = 'user=backup channel_binding=prefer host=localhost port=5432 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'
content-crc = 802820606

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": "SCUN4E",
                "backup-mode": "FULL",
                "wal": "ARCHIVE",
                "compress-alg": "zlib",
                "compress-level": 1,
                "from-replica": "false",
                "block-size": 8192,
                "xlog-block-size": 8192,
                "checksum-version": 1,
                "program-version": "2.5.15",
                "server-version": "16",
                "current-tli": 16,
                "parent-tli": 2,
                "start-lsn": "0/4C000028",
                "stop-lsn": "0/4D0000B8",
                "start-time": "2024-05-02 11:19:26+03",
                "end-time": "2024-05-02 11:19:39+03",
                "recovery-xid": 743,
                "recovery-time": "2024-05-02 11:19:37+03",
                "data-bytes": 250827955,
                "wal-bytes": 16777216,
                "uncompressed-bytes": 578216425,
                "pgdata-bytes": 578216107,
                "primary_conninfo": "user=backup channel_binding=prefer host=localhost port=5432 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable",
                "status": "OK",
                "content-crc": 802820606
            }
        ]
    }
]

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   Zratio  N backups  Status 
================================================================================================================================
 1    0           0/0          000000010000000000000019  00000001000000000000004D  53          848MB  1.00    5          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": "node",
        "timelines": [
            {
                "tli": 1,
                "parent-tli": 0,
                "switchpoint": "0/0",
                "min-segno": "000000010000000000000019",
                "max-segno": "00000001000000000000004D",
                "n-segments": 53,
                "size": 889192448,
                "zratio": 1.00,
                "closest-backup-id": "",
                "status": "OK",
                "lost-segments": [],
                "backups": [
                    {
                        "id": "SCUN4E",
                        "backup-mode": "FULL",
                        "wal": "ARCHIVE",
                        "compress-alg": "zlib",
                        "compress-level": 1,
                        "from-replica": "false",
                        "block-size": 8192,
                        "xlog-block-size": 8192,
                        "checksum-version": 1,
                        "program-version": "2.5.15",
                        "server-version": "16",
                        "current-tli": 1,
                        "parent-tli": 0,
                        "start-lsn": "0/4C000028",
                        "stop-lsn": "0/4D0000B8",
                        "start-time": "2024-05-02 11:19:26+03",
                        "end-time": "2024-05-02 11:19:39+03",
                        "recovery-xid": 743,
                        "recovery-time": "2024-05-02 11:19:37+03",
                        "data-bytes": 250827955,
                        "wal-bytes": 16777216,
                        "uncompressed-bytes": 578216425,
                        "pgdata-bytes": 578216107,
                        "primary_conninfo": "user=backup channel_binding=prefer host=localhost port=5432 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable",
                        "status": "OK",
                        "content-crc": 802820606
                    },
                    {
                        "id": "SCUN3Y",
                        "parent-backup-id": "SCUN3M",
                        "backup-mode": "DELTA",
                        "wal": "STREAM",
                        "compress-alg": "zlib",
                        "compress-level": 1,
                        "from-replica": "false",
                        "block-size": 8192,
                        "xlog-block-size": 8192,
                        "checksum-version": 1,
                        "program-version": "2.5.15",
                        "server-version": "16",
                        "current-tli": 1,
                        "parent-tli": 1,
                        "start-lsn": "0/3C0043A8",
                        "stop-lsn": "0/46159C70",
                        "start-time": "2024-05-02 11:19:10+03",
                        "end-time": "2024-05-02 11:19:17+03",
                        "recovery-xid": 743,
                        "recovery-time": "2024-05-02 11:19:16+03",
                        "data-bytes": 96029293,
                        "wal-bytes": 218103808,
                        "uncompressed-bytes": 217639806,
                        "pgdata-bytes": 578216107,
                        "primary_conninfo": "user=backup channel_binding=prefer host=localhost port=5432 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable",
                        "status": "OK",
                        "content-crc": 3074300814
                    },
                    {
                        "id": "SCUN3M",
                        "parent-backup-id": "SCUN39",
                        "backup-mode": "PTRACK",
                        "wal": "STREAM",
                        "compress-alg": "zlib",
                        "compress-level": 1,
                        "from-replica": "false",
                        "block-size": 8192,
                        "xlog-block-size": 8192,
                        "checksum-version": 1,
                        "program-version": "2.5.15",
                        "server-version": "16",
                        "current-tli": 1,
                        "parent-tli": 1,
                        "start-lsn": "0/32000028",
                        "stop-lsn": "0/32005ED0",
                        "start-time": "2024-05-02 11:18:58+03",
                        "end-time": "2024-05-02 11:19:08+03",
                        "recovery-xid": 742,
                        "recovery-time": "2024-05-02 11:19:01+03",
                        "data-bytes": 31205704,
                        "wal-bytes": 16777216,
                        "uncompressed-bytes": 69585790,
                        "pgdata-bytes": 509927595,
                        "primary_conninfo": "user=backup channel_binding=prefer host=localhost port=5432 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable",
                        "status": "OK",
                        "content-crc": 3446949708
                    },
                    {
                        "id": "SCUN39",
                        "parent-backup-id": "SCUN2V",
                        "backup-mode": "PAGE",
                        "wal": "STREAM",
                        "compress-alg": "pglz",
                        "compress-level": 1,
                        "from-replica": "false",
                        "block-size": 8192,
                        "xlog-block-size": 8192,
                        "checksum-version": 1,
                        "program-version": "2.5.15",
                        "server-version": "16",
                        "current-tli": 1,
                        "parent-tli": 1,
                        "start-lsn": "0/2A000028",
                        "stop-lsn": "0/2B0000B8",
                        "start-time": "2024-05-02 11:18:45+03",
                        "end-time": "2024-05-02 11:18:57+03",
                        "recovery-xid": 741,
                        "recovery-time": "2024-05-02 11:18:50+03",
                        "data-bytes": 48381612,
                        "wal-bytes": 33554432,
                        "uncompressed-bytes": 69569406,
                        "pgdata-bytes": 441639083,
                        "primary_conninfo": "user=backup channel_binding=prefer host=localhost port=5432 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable",
                        "status": "OK",
                        "content-crc": 3492989773
                    },
                    {
                        "id": "SCUN2V",
                        "backup-mode": "FULL",
                        "wal": "STREAM",
                        "compress-alg": "zlib",
                        "compress-level": 1,
                        "from-replica": "false",
                        "block-size": 8192,
                        "xlog-block-size": 8192,
                        "checksum-version": 1,
                        "program-version": "2.5.15",
                        "server-version": "16",
                        "current-tli": 1,
                        "parent-tli": 0,
                        "start-lsn": "0/23000028",
                        "stop-lsn": "0/23000168",
                        "start-time": "2024-05-02 11:18:31+03",
                        "end-time": "2024-05-02 11:18:42+03",
                        "recovery-xid": 740,
                        "recovery-time": "2024-05-02 11:18:38+03",
                        "data-bytes": 161084290,
                        "wal-bytes": 16777216,
                        "uncompressed-bytes": 373359081,
                        "pgdata-bytes": 373358763,
                        "primary_conninfo": "user=backup channel_binding=prefer host=localhost port=5432 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable",
                        "status": "OK",
                        "content-crc": 1621343133
                    }
                ]
            }
        ]
    }
]

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

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 May 02, 2024:

BACKUP INSTANCE 'node'
=====================================================================================================================================
 Instance  Version  ID      Recovery Time           Mode   WAL Mode  TLI  Time    Data   WAL  Zratio  Start LSN   Stop LSN    Status 
=====================================================================================================================================
 node      16       SCUN6L  2024-05-02 11:20:48+03  FULL   ARCHIVE   1/0    5s   296MB  16MB    2.30  0/46000028  0/470000B8  OK     
 node      16       SCQXUI  2024-04-30 11:20:45+03  PAGE   ARCHIVE   1/1    5s  6280kB  16MB    1.00  0/44000028  0/450000F0  OK     
 node      16       SCFTUG  2024-04-24 11:20:43+03  DELTA  ARCHIVE   1/1    5s  6280kB  16MB    1.00  0/42000028  0/430000B8  OK     
----------------------------------------------------------retention window-----------------------------------------------------------
 node      16       SCDZ6D  2024-04-23 11:20:40+03  PAGE   ARCHIVE   1/1    5s  6280kB  16MB    1.00  0/40000028  0/410000B8  OK     
 node      16       SCC4HX  2024-04-22 11:20:24+03  FULL   ARCHIVE   1/0    5s   296MB  16MB    2.30  0/3E000028  0/3F0000F0  OK     
 node      16       SC8F5G  2024-04-20 11:20:07+03  FULL   ARCHIVE   1/0    5s   296MB  16MB    2.30  0/3C0000D8  0/3D00BB58  OK     

Even though SCC4HX and SCDZ6D backups are outside the retention window, they cannot be removed as it invalidates the succeeding incremental backups SCFTUG and SCQXUI that are still required, so, if you run the delete command with the --delete-expired flag, only the SC8F5G full backup will be removed.

With the --merge-expired option, the SCFTUG backup is merged with the underlying SCDZ6D and SCC4HX 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 Mode  TLI  Time    Data   WAL  Zratio  Start LSN   Stop LSN    Status 
=====================================================================================================================================
 node      16       SCUN6L  2024-05-02 11:20:48+03  FULL   ARCHIVE   1/0    5s   296MB  16MB    2.30  0/46000028  0/470000B8  OK     
 node      16       SCQXUI  2024-04-30 11:20:45+03  PAGE   ARCHIVE   1/1    5s  6280kB  16MB    1.00  0/44000028  0/450000F0  OK     
 node      16       SCFTUG  2024-04-24 11:20:43+03  FULL   ARCHIVE   1/1    5s   296MB  16MB    1.00  0/42000028  0/430000B8  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="2027-05-02 11:21:00+00"

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="2027-05-02 11:21:00+00"

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 = '2024-05-02 11:21:00+00'
expire-time = '2027-05-02 11:21:00+00'
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

======================================================================================================================================
 Instance  Version  ID      Recovery Time           Mode   WAL Mode  TLI  Time    Data    WAL  Zratio  Start LSN   Stop LSN    Status 
======================================================================================================================================
 node      16       SCUN92  2024-05-02 11:22:16+03  DELTA  STREAM    1/1    9s  1162kB   32MB    1.08  0/7C000028  0/7C000168  OK     
 node      16       SCUN8N  2024-05-02 11:22:09+03  FULL   STREAM    1/0   12s   296MB   16MB    2.30  0/7A000028  0/7A009A08  OK     
 node      16       SCUN8I  2024-05-02 11:21:55+03  DELTA  STREAM    1/1    5s  1148kB   32MB    1.01  0/78000028  0/78000168  OK     
 node      16       SCUN86  2024-05-02 11:21:47+03  DELTA  STREAM    1/1   11s   120MB   16MB    2.27  0/76000028  0/760001A0  OK     
 node      16       SCUN7I  2024-05-02 11:21:29+03  FULL   STREAM    1/0   22s   296MB  288MB    2.30  0/63012FE8  0/74E7ADA0  OK     
 node      16       SCUN71  2024-05-02 11:21:12+03  FULL   STREAM    1/0   13s   296MB  272MB    2.30  0/49000028  0/573683B8  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   Zratio  N backups  Status 
================================================================================================================================
 1    0           0/0          000000010000000000000048  00000001000000000000007C  53          848MB  1.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   Zratio  N backups  Status 
================================================================================================================================
 1    0           0/0          000000010000000000000049  00000001000000000000007C  52          832MB  1.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  Zratio  N backups  Status 
===============================================================================================================================
 1    0           0/0          00000001000000000000007C  00000001000000000000007C  1           16MB  1.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  Zratio  N backups  Status 
===============================================================================================================================
 1    0           0/0          00000001000000000000007E  00000001000000000000007E  1           16MB  1.00    7          OK     

version

pg_probackup version

Prints pg_probackup version.

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 [--help]

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.

For details, see the section Initializing the Backup Catalog.

add-instance

pg_probackup add-instance -B backup_dir -D data_dir --instance=instance_name [--help]

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.

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

del-instance

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

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]

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 the contents of the pg_probackup.conf configuration file located in the backup_dir/backups/instance_name directory. 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]

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.

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]] [--backup-pg-log]
[--no-validate] [--skip-block-validation]
[-w --no-password] [-W --password]
[--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 PostgreSQL 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 PostgreSQL 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 PostgreSQL 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.

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 PostgreSQL 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.

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] [--stream] [--dry-run]
[--temp-slot] [-P | --perm-slot] [-S | --slot=slot_name]
[--exclude-path=PATHNAME]
[-T OLDDIR=NEWDIR]
[connection_options] [remote_options]

Creates a copy of a PostgreSQL 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 PostgreSQL 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.