Movatterモバイル変換

• The remote mode is not supported on Windows systems.

• On Unix systems, forPostgres Pro, a backup can be made only by the same OS user that has started thePostgres Pro server. For example, ifPostgres Pro server is started by userpostgres, thebackup command must also be run by userpostgres. To satisfy this requirement when taking backups in theremote mode using SSH, you must set--remote-user option topostgres.

• ThePostgres Pro server from which the backup was taken and the restored server must be compatible by theblock_size andwal_block_size parameters and have the same major release number. Depending on cluster configuration,Postgres Pro itself may apply additional restrictions, such as CPU architecture orlibc/icu versions.

1. Installpg_probackup on bothbackup_host andpostgres_host.

2. Set up an SSH connection frombackup_host topostgres_host.

3. Configure your database cluster forSTREAM backups.

4. Initialize the backup catalog:

   backup_user@backup_host:~$ pg_probackup init -B /mnt/backupsINFO: Backup catalog '/mnt/backups' successfully initialized

5. Add a backup instance calledmydb to the backup catalog:

   backup_user@backup_host:~$ pg_probackup add-instance \    -B /mnt/backups \    -D /var/lib/pgpro/std-16/data \    --instance=node \    --remote-host=postgres_host \    --remote-user=postgresINFO: Instance 'node' successfully initialized

6. Make a FULL backup:

   backup_user@backup_host:~$ pg_probackup backup \    -B /mnt/backups \    -b FULL \    --instance=node \    --stream \    --compress-algorithm=zstd \    --remote-host=postgres_host \    --remote-user=postgres \    -U backup \    -d backupdbINFO: Backup start, pg_probackup version: 2.7.3, instance: node, backup ID: SBOL6J, backup mode: FULL, wal mode: STREAM, remote: true, compress-algorithm: zstd, compress-level: 1WARNING:  pgpro_edition() function is old-style and will be removed in future major release, use pgpro_edition GUC variable instead.INFO: This PostgreSQL instance was initialized with data block checksums. Data block corruption will be detectedINFO: Database backup startINFO: wait for pg_backup_start()INFO: PGDATA size: 96MBINFO: Current Start LSN: 0/8000028, TLI: 1INFO: Start transferring data filesINFO: Data files are transferred, time elapsed: 2sINFO: wait for pg_stop_backup()INFO: pg_stop_backup() successfully executedINFO: stop_stream_lsn 0/9000000 currentpos 0/9000000INFO: backup->stop_lsn 0/8004E40INFO: Getting the Recovery Time from WALINFO: Syncing backup files to diskINFO: Backup files are synced, time elapsed: 1sINFO: Validating backup SBOL6JINFO: Backup SBOL6J data files are validINFO: Backup SBOL6J resident size: 53MBINFO: Backup SBOL6J completed

7. List the backups of the instance:

   backup_user@backup_host:~$ pg_probackup show \        -B /mnt/backups \        --instance=node============================================================================================================================================= Instance  Version  ID      Recovery Time                  Mode  WAL Mode  TLI  Time  Data   WAL  Zalg  Zratio  Start LSN  Stop LSN   Status ============================================================================================================================================= node      17       SBOL6J  2024-04-09 18:18:21.970314+03  FULL  STREAM    1/0    4s  37MB  16MB  zstd    2.57  0/8000028  0/8004E40  OK

8. Make an incremental backup in the DELTA mode:

   backup_user@backup_host:~$ pg_probackup backup \    -B /mnt/backups \    -b DELTA \    --instance=node \    --stream \    --compress-algorithm=zstd \    --remote-host=postgres_host \    --remote-user=postgres \    -U backup \    -d backupdbINFO: Backup start, pg_probackup version: 2.7.3, instance: node, backup ID: SBOL6N, backup mode: DELTA, wal mode: STREAM, remote: true, compress-algorithm: zstd, compress-level: 1WARNING:  pgpro_edition() function is old-style and will be removed in future major release, use pgpro_edition GUC variable instead.INFO: This PostgreSQL instance was initialized with data block checksums. Data block corruption will be detectedINFO: Database backup startINFO: wait for pg_backup_start()INFO: Parent backup: SBOL6JINFO: PGDATA size: 96MBINFO: Current Start LSN: 0/9000028, TLI: 1INFO: Parent Start LSN: 0/8000028, TLI: 1INFO: Start transferring data filesINFO: Data files are transferred, time elapsed: 1sINFO: wait for pg_stop_backup()INFO: pg_stop_backup() successfully executedINFO: stop_stream_lsn 0/A000000 currentpos 0/A000000INFO: backup->stop_lsn 0/9000190INFO: Getting the Recovery Time from WALINFO: Syncing backup files to diskINFO: Backup files are synced, time elapsed: 0INFO: Validating backup SBOL6NINFO: Backup SBOL6N data files are validINFO: Backup SBOL6N resident size: 17MBINFO: Backup SBOL6N completed

9. Add or modify some parameters in thepg_probackup configuration file, so that you do not have to specify them each time on the command line:

   backup_user@backup_host:~$ pg_probackup set-config \    -B /mnt/backups \    --instance=node \    --remote-host=postgres_host \    --remote-user=postgres \    -U backup \    -d backupdb

10. Check the configuration of the instance:

    backup_user@backup_host:~$ pg_probackup show-config \    -B /mnt/backups \    --instance=node# Backup instance informationpgdata = /var/lib/pgpro/std-16/datasystem-identifier = 7355886958826772732xlog-seg-size = 16777216# Connection parameterspgdatabase = backupdbpghost = postgres_hostpguser = backup# Archive parametersarchive-timeout = 5min# Logging parameterslog-level-console = INFOlog-level-file = OFFlog-format-console = PLAINlog-format-file = PLAINlog-filename = pg_probackup.loglog-rotation-size = 0TBlog-rotation-age = 0d# Retention parametersretention-redundancy = 0retention-window = 0wal-depth = 0# Compression parameterscompress-algorithm = nonecompress-level = 1# Remote access parametersremote-proto = sshremote-host = postgres_hostremote-user = postgres

    Note that the parameters not modified viaset-config retain their default values.

11. Make another incremental backup in the DELTA mode, omitting the parameters stored in the configuration file earlier:

    backup_user@backup_host:~$ pg_probackup backup \    -B /mnt/backups \    -b DELTA \    --instance=node \    --stream \    --compress-algorithm=zstdINFO: Backup start, pg_probackup version: 2.7.3, instance: node, backup ID: SBOL6P, backup mode: DELTA, wal mode: STREAM, remote: true, compress-algorithm: zstd, compress-level: 1WARNING:  pgpro_edition() function is old-style and will be removed in future major release, use pgpro_edition GUC variable instead.INFO: This PostgreSQL instance was initialized with data block checksums. Data block corruption will be detectedINFO: Database backup startINFO: wait for pg_backup_start()INFO: Parent backup: SBOL6NINFO: PGDATA size: 96MBINFO: Current Start LSN: 0/A000028, TLI: 1INFO: Parent Start LSN: 0/9000028, TLI: 1INFO: Start transferring data filesINFO: Data files are transferred, time elapsed: 1sINFO: wait for pg_stop_backup()INFO: pg_stop_backup() successfully executedINFO: stop_stream_lsn 0/B000000 currentpos 0/B000000INFO: backup->stop_lsn 0/A000190INFO: Getting the Recovery Time from WALINFO: Syncing backup files to diskINFO: Backup files are synced, time elapsed: 0INFO: Validating backup SBOL6PINFO: Backup SBOL6P data files are validINFO: Backup SBOL6P resident size: 17MBINFO: Backup SBOL6P completed

12. List the backups of the instance again:

    backup_user@backup_host:~$ pg_probackup show \    -B /mnt/backups \    --instance=node================================================================================================================================================ Instance  Version  ID      Recovery Time                  Mode   WAL Mode  TLI  Time    Data   WAL  Zalg  Zratio  Start LSN  Stop LSN   Status ================================================================================================================================================ node      17       SBOL6P  2024-04-09 18:18:26.630175+03  DELTA  STREAM    1/1    1s  1147kB  16MB  zstd    1.00  0/A000028  0/A000190  OK      node      17       SBOL6N  2024-04-09 18:18:25.015713+03  DELTA  STREAM    1/1    2s  1160kB  16MB  zstd    1.04  0/9000028  0/9000190  OK      node      17       SBOL6J  2024-04-09 18:18:21.970314+03  FULL   STREAM    1/0    4s    37MB  16MB  zstd    2.57  0/8000028  0/8004E40  OK

13. Restore the data from the latest available backup to an arbitrary location:

    backup_user@backup_host:~$ pg_probackup restore \    -B /mnt/backups \    -D /var/lib/pgpro/std-16/staging-data \    --instance=nodeINFO: Validating parents for backup SBOL6PINFO: Validating backup SBOL6JINFO: Backup SBOL6J data files are validINFO: Validating backup SBOL6NINFO: Backup SBOL6N data files are validINFO: Validating backup SBOL6PINFO: Backup SBOL6P data files are validINFO: Backup SBOL6P WAL segments are validINFO: Backup SBOL6P is valid.INFO: Restoring the database from the backup starting at 2024-04-09 18:18:25+03 on localhostINFO: Start restoring backup files. PGDATA size: 112MBINFO: Backup files are restored. Transferred bytes: 112MB, time elapsed: 2sINFO: Restore incremental ratio (less is better): 100% (112MB/112MB)INFO: Syncing restored files to diskINFO: Restored backup files are synced, time elapsed: 1sINFO: Restore of backup SBOL6P completed.

• wal/ — directory for WAL files.

• backups/ — directory for backup files.

• data_dir is the data directory of the cluster you are going to back up. To set up and usepg_probackup, write access to this directory is required.

• instance_name is the name of the subdirectories that will store WAL and backup files for this cluster.

• remote_options are optional parameters that need to be specified only ifdata_dir is located on a remote system.

Note

ForPostgres Pro 11 or higher, it is recommended to use thegroup access feature, so that backups can be done by any OS user in the same group as the cluster owner. In this case, the user should have read permissions for the cluster directory.

• If thebackup role does not exist, create it with theREPLICATION privilege whenConfiguring the Database Cluster:

  CREATE ROLE backup WITH LOGIN REPLICATION;

• If thebackup role already exists, grant it with theREPLICATION privilege:

  ALTER ROLE backup WITH REPLICATION;

• In thepg_hba.conf file, allow replication on behalf of thebackup role.

• Make sure the parametermax_wal_senders is set high enough to leave at least one session available for the backup process.

• Set the parameterwal_level to be higher thanminimal.

Note

If you are planning to rely on.pgpass for authentication when running backup in STREAM mode, then .pgpass must contain credentials forreplication database, used to establish connection via replication protocol. Example: pghost:5432:replication:backup_user:my_strong_password

• Make sure thewal_level parameter is higher thanminimal.

• If you are configuring archiving on the primary,archive_mode must be set toon oralways. To perform archiving on a standby, set this parameter toalways.

• Set thearchive_command parameter, as follows:

  archive_command = '"install_dir/pg_probackup" archive-push -B "backup_dir" --instance=instance_name --wal-file-name=%f [remote_options]'

Note

Instead of using thearchive-push command provided bypg_probackup, you can use any other tool to set up continuous archiving as long as it delivers WAL segments intobackup_dir/wal/instance_name directory. If compression is used, it should begzip, and.gz suffix in filename is mandatory.

Note

Instead of configuring continuous archiving by setting thearchive_mode andarchive_command parameters, you can opt for using thepg_receivewal utility. In this case,pg_receivewal-Ddirectory option should point tobackup_dir/wal/instance_name directory.pg_probackup supports WAL compression that can be done bypg_receivewal.“Zero Data Loss” archive strategy can be achieved only by usingpg_receivewal.

• On the standby server, set thehot_standby parameter toon.

• On the primary server, set thefull_page_writes parameter toon.

• To perform standalone backups on a standby, complete all the steps in sectionSetting up STREAM Backups.

• To perform archive backups on a standby, complete all steps in sectionSetting up continuous WAL archiving.

• If a standby is promoted to a primary during the backup process, the backup fails.

• All WAL records required for the backup must contain sufficient full-page writes. This requires you to enablefull_page_writes on the primary, and not to use tools likepg_compresslog asarchive_command to remove full-page writes from WAL files.

• Install theamcheck oramcheck_next extensionin every database of the cluster:

  CREATE EXTENSION amcheck;

• Grant the following permissions to thebackup rolein every database of the cluster:

• Grant the read-only access topg_catalog.pg_database to thebackup role only in the databaseused for connection toPostgres Pro server:

  GRANT SELECT ON TABLE pg_catalog.pg_database TO backup;

Set up SSH#

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

pg_probackup in the remote mode via SSH works as follows:

Note

PTRACK versions lower than 2.0 are deprecated and not supported. Postgres Pro Standard and Postgres Pro Enterprise versions starting with 11.9.1 contain PTRACK 2.0. Upgrade your server to avoid issues in backups that you will take in future and be sure to take fresh backups of your clusters with the upgraded PTRACK since the backups taken with PTRACK 1.x might be corrupt.

Note

The permissions required for the role that will perform PTRACK backups (thebackup role in the examples below) are listed inthe section called “Configuring the Database Cluster”. The role must have permissions only in the database used for connection to thePostgres Pro server.

1. Create PTRACK extension:

   CREATE EXTENSION ptrack;

2. To enable tracking page updates, setptrack.map_size parameter to a positive integer and restart the server.

   For optimal performance, it is recommended to setptrack.map_size toN / 1024, whereN is the size of thePostgres Pro cluster, in MB. If you set this parameter to a lower value, PTRACK is more likely to map several blocks together, which leads to false-positive results when tracking changed blocks and increases the incremental backup size as unchanged blocks can also be copied into the incremental backup. Settingptrack.map_size to a higher value does not affect PTRACK operation, but it is not recommended to set this parameter to a value higher than 1024.

Note

If you change theptrack.map_size parameter value, the previously created PTRACK map file is cleared, and tracking newly changed blocks starts from scratch. Thus, you have to retake a full backup before taking incremental PTRACK backups after changingptrack.map_size.

ARCHIVE Mode#

ARCHIVE is the default WAL delivery mode.

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

pg_probackup backup -Bbackup_dir --instance=instance_name -b FULL

ARCHIVE backups rely oncontinuous 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 betweenStart LSN andStop LSN actually exist inbackup_dir/wal/instance_name directory.pg_probackup also ensures that WAL records betweenStart LSN andStop 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 -Bbackup_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.

Duringbackuppg_probackup streams WAL files containing WAL records betweenStart LSN andStop LSN tobackup_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 betweenStart LSN andStop LSN can be parsed.

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

Page Validation#

Ifdata_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 thePostgres Pro instance and the backup itself have no corrupt pages. Note thatpg_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 yourinstance_name instance that will be stored under thebackup_dir directory on Linux, run:

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

Similarly, to includeC:\dir1 andC:\dir2 directories into the full backup on Windows, run:

pg_probackup backup -Bbackup_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 thepg_probackup.conf configuration file using theset-config command with the--external-dirs option.

• backup_dir is the backup catalog that stores all backup files and meta information.

• instance_name is the backup instance for the cluster to be restored.

• backup_id specifies the backup to restore the cluster from. If you omit this option,pg_probackup uses the latest valid backup available for the specified instance. If you specify an incremental backup to restore,pg_probackup automatically restores the underlying full backup and then sequentially applies all the necessary increments.

Note

By default, therestore command validates the specified backup before restoring the cluster. If you run regular backup validations and would like to save time when restoring the cluster, you can specify the--no-validate flag to skip validation and speed up the recovery.

Incremental Restore#

The speed of restore from backup can be significantly improved by replacing only invalid and changed pages in already existingPostgres Pro data directory usingincremental restore options with therestore command.

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

pg_probackup restore -Bbackup_dir --instance=instance_name -Ddata_dir -Iincremental_mode

Whereincremental_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 andsystem-identifier is the same as in the backup.

Suppose you want to return an old primary as a 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      17       SBOL8S  2024-04-09 18:19:43.707720+03  DELTA   STREAM    16/15    3s  114MB  64MB   lz4    1.42  0/3C003020  0/3E8D4930  OK      node      17       SBOL8G  2024-04-09 18:19:32.594670+03  PTRACK  STREAM    15/15    4s   30MB  16MB  zlib    2.23  0/31000028  0/310029E0  OK      node      17       SBOL83  2024-04-09 18:19:22.269595+03  PAGE    STREAM    15/15    7s   46MB  32MB  pglz    1.44  0/29000028  0/2A0000F8  OK      node      17       SBOL7P  2024-04-09 18:19:06.557301+03  FULL    STREAM    15/0     6s  144MB  16MB  zstd    2.47  0/22000028  0/220001C8  OK     backup_user@backup_host:~$ pg_probackup restore -B /mnt/backups --instance=node -R -I lsnINFO: Destination directory and tablespace directories are empty, disable incremental restoreINFO: Validating parents for backup SBOL8SINFO: Validating backup SBOL7PINFO: Backup SBOL7P data files are validINFO: Validating backup SBOL83INFO: Backup SBOL83 data files are validINFO: Validating backup SBOL8GINFO: Backup SBOL8G data files are validINFO: Validating backup SBOL8SINFO: Backup SBOL8S data files are validINFO: Backup SBOL8S WAL segments are validINFO: Backup SBOL8S is valid.INFO: Restoring the database from the backup starting at 2024-04-09 18:19:40+03INFO: Start restoring backup files. PGDATA size: 616MBINFO: Backup files are restored. Transferred bytes: 616MB, time elapsed: 2sINFO: Restore incremental ratio (less is better): 100% (616MB/616MB)INFO: Syncing restored files to diskINFO: Restored backup files are synced, time elapsed: 2sINFO: Restore of backup SBOL8S completed.

Partial Restore#

If you have enabledpartial restore before taking backups, you can restore only some of the databases usingpartial restore options with therestore commands.

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

pg_probackup restore -Bbackup_dir --instance=instance_name --db-include=database_name

The--db-include option can be specified multiple times. For example, to restore only databasesdb1 anddb2, run the following command:

pg_probackup restore -Bbackup_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 -Bbackup_dir --instance=instance_name --db-exclude=database_name

The--db-exclude option can be specified multiple times. For example, to exclude the databasesdb1 anddb2 from restore, run the following command:

pg_probackup restore -Bbackup_dir --instance=instance_name --db-exclude=db1 --db-exclude=db2

Partial restore relies on lax behavior ofPostgres Pro recovery process toward truncated files. For recovery to work properly, files of excluded databases are restored as files of zero size. After thePostgres Pro cluster is successfully started, you must drop the excluded databases usingDROP 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.

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

  pg_probackup restore -Bbackup_dir --instance=instance_name --recovery-target-time="2024-04-10 18:18:26+03"

• To restore the cluster state up to a specific transaction ID, use the--recovery-target-xid option:

  pg_probackup restore -Bbackup_dir --instance=instance_name --recovery-target-xid=687

• To restore the cluster state up to the specific LSN, use--recovery-target-lsn option:

  pg_probackup restore -Bbackup_dir --instance=instance_name --recovery-target-lsn=16/B374D848

• To restore the cluster state up to the specific named restore point, use--recovery-target-name option:

  pg_probackup restore -Bbackup_dir --instance=instance_name --recovery-target-name="before_app_upgrade"

• To restore the backup to the latest state available in the WAL archive, use--recovery-target option withlatest value:

  pg_probackup restore -Bbackup_dir --instance=instance_name --recovery-target="latest"

• To restore the cluster to the earliest point of consistency, use--recovery-target option with theimmediate value:

  pg_probackup restore -Bbackup_dir --instance=instance_name --recovery-target='immediate'

Note

pg_probackup relies on passwordless SSH connection for communication between the hosts.

Note

In addition to SSH connection,pg_probackup uses a regular connection to the database to manage the remote operation. See the sectionConfiguring the Database Cluster for details of how to set up a database connection.

• On your backup host, configurepg_probackup as explained in the sectionInstallation and Setup. For theadd-instance andset-config commands, make sure to specifyremote options that point to the database host with thePostgres Pro instance.

• If you would like to take remote backups inPAGE mode, or rely onARCHIVE WAL delivery mode, or usePITR, configure continuous WAL archiving from the database host to the backup host as explained in the sectionSetting up continuous WAL archiving. For thearchive-push andarchive-get commands, you must specify theremote options that point to the backup host with the backup catalog.

• Runbackup orrestore commands withremote optionson the backup host.pg_probackup connects to the remote system via SSH and creates a backup locally or restores the previously taken backup on the remote system, respectively.

Note

The remote mode is currently unavailable for Windows systems.

Note

Parallel restore applies only to copying data from the backup catalog to the data directory of the cluster. WhenPostgres Pro server is started, WAL records need to be replayed, and this cannot be done in parallel.

Important

A technique is implemented that prevents repeatable copying of one file whenpg_probackup runs on multiple threads. With this technique, however, when disks are slow or the system is overloaded, parallel copying might fail. To handle this situation, resolve issues with your system resources.

Note

It isnot recommended to editpg_probackup.conf manually.

• PGDATA — the path to the data directory of the cluster to back up.

• system-identifier — the unique identifier of thePostgres Pro instance.

• View backup information

• View WAL Archive Information

• Validate backups

• Merge backups

• Delete backups

Viewing Backup Information#

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

pg_probackup show -Bbackup_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      17       SBOL94  2024-04-09 18:19:56.603355+03  FULL    ARCHIVE   1/0    6s  377MB  16MB   lz4    1.46  0/41000028  0/420000C0  OK node      17       SBOL8S  2024-04-09 18:19:43.707720+03  DELTA   STREAM    1/1    3s  114MB  64MB   lz4    1.42  0/3C003020  0/3E8D4930  OK node      17       SBOL8G  2024-04-09 18:19:32.594670+03  PTRACK  STREAM    1/1    4s   30MB  16MB  zlib    2.23  0/31000028  0/310029E0  OK node      17       SBOL83  2024-04-09 18:19:22.269595+03  PAGE    STREAM    1/1    7s   46MB  32MB  pglz    1.44  0/29000028  0/2A0000F8  OK node      17       SBOL7P  2024-04-09 18:19:06.557301+03  FULL    STREAM    1/0    6s  144MB  16MB  zstd    2.47  0/22000028  0/220001C8  OK

For each backup, the following information is provided:

You can restore the cluster from the backup only if the backup status isOK orDONE.

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

pg_probackup show -Bbackup_dir --instance=instance_name -ibackup_id

The sample output is as follows:

#Configurationbackup-mode = FULLstream = falsecompress-alg = lz4compress-level = 1from-replica = false#Compatibilityblock-size = 8192xlog-block-size = 8192checksum-version = 1program-version = 2.7.3server-version = 17#Result backup infotimelineid = 1start-lsn = 0/41000028stop-lsn = 0/420000C0start-time = '2024-04-09 18:19:52+03'end-time = '2024-04-09 18:19:58+03'end-validation-time = '2024-04-09 18:19:59+03'recovery-xid = 757recovery-time = '2024-04-09 18:19:56.603355+03'data-bytes = 395651278wal-bytes = 16777216uncompressed-bytes = 578552566pgdata-bytes = 578552248status = OKprimary_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 = 3862224379

Detailed output has additional attributes:

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

pg_probackup show -Bbackup_dir --instance=instance_name --format=json -i backup_id

The sample output is as follows:

[    {        "instance": "node",        "backups": [            {                "id": "SBOL94",                "status": "OK",                "start-time": "2024-04-09 18:19:52+03",                "backup-mode": "FULL",                "wal": "ARCHIVE",                "compress-alg": "lz4",                "compress-level": 1,                "from-replica": "false",                "block-size": 8192,                "xlog-block-size": 8192,                "checksum-version": 1,                "program-version": "2.7.3",                "server-version": "17",                "current-tli": 16,                "parent-tli": 2,                "start-lsn": "0/41000028",                "stop-lsn": "0/420000C0",                "end-time": "2024-04-09 18:19:58+03",                "end-validation-time": "2024-04-09 18:19:59+03",                "recovery-xid": 757,                "recovery-time": "2024-04-09 18:19:56.603355+03",                "data-bytes": 395651278,                "wal-bytes": 16777216,                "uncompressed-bytes": 578552566,                "pgdata-bytes": 578552248,                "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": 3862224379            }        ]    }]

Viewing WAL Archive Information#

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

pg_probackup show -Bbackup_dir [--instance=instance_name] --archive

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

INFO: checking WAL file name "00000001000000000000001B"INFO: checking WAL file name "00000001000000000000001C"INFO: checking WAL file name "00000001000000000000001D"INFO: checking WAL file name "00000001000000000000001E"INFO: checking WAL file name "00000001000000000000001F"INFO: checking WAL file name "000000010000000000000020"INFO: checking WAL file name "000000010000000000000021"INFO: checking WAL file name "000000010000000000000022"INFO: checking WAL file name "000000010000000000000022.00000028.backup"INFO: checking WAL file name "000000010000000000000023"INFO: checking WAL file name "000000010000000000000024"INFO: checking WAL file name "000000010000000000000025"INFO: checking WAL file name "000000010000000000000026"INFO: checking WAL file name "000000010000000000000027"INFO: checking WAL file name "000000010000000000000028"INFO: checking WAL file name "000000010000000000000029"INFO: checking WAL file name "000000010000000000000029.00000028.backup"INFO: checking WAL file name "00000001000000000000002A"INFO: checking WAL file name "00000001000000000000002B"INFO: checking WAL file name "00000001000000000000002C"INFO: checking WAL file name "00000001000000000000002D"INFO: checking WAL file name "00000001000000000000002E"INFO: checking WAL file name "00000001000000000000002F"INFO: checking WAL file name "000000010000000000000030"INFO: checking WAL file name "000000010000000000000031"INFO: checking WAL file name "000000010000000000000031.00000028.backup"INFO: checking WAL file name "000000010000000000000032"INFO: checking WAL file name "000000010000000000000033"INFO: checking WAL file name "000000010000000000000034"INFO: checking WAL file name "000000010000000000000035"INFO: checking WAL file name "000000010000000000000036"INFO: checking WAL file name "000000010000000000000037"INFO: checking WAL file name "000000010000000000000038"INFO: checking WAL file name "000000010000000000000039"INFO: checking WAL file name "00000001000000000000003A"INFO: checking WAL file name "00000001000000000000003B"INFO: checking WAL file name "00000001000000000000003C"INFO: checking WAL file name "00000001000000000000003C.00003020.backup"INFO: checking WAL file name "00000001000000000000003D"INFO: checking WAL file name "00000001000000000000003E"INFO: checking WAL file name "00000001000000000000003F"INFO: checking WAL file name "000000010000000000000040"INFO: checking WAL file name "000000010000000000000041"INFO: checking WAL file name "000000010000000000000041.00000028.backup"INFO: checking WAL file name "000000010000000000000042"ARCHIVE INSTANCE 'node'================================================================================================================================ TLI  Parent TLI  Switchpoint  Min Segno                 Max Segno                 N segments  Size   Zratio  N backups  Status ================================================================================================================================ 1    0           0/0          00000001000000000000001B  000000010000000000000042  40          640MB  1.00    5          OK

For each timeline, the following information is provided:

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

pg_probackup show -Bbackup_dir [--instance=instance_name] --archive --format=json

The sample output is as follows:

INFO: checking WAL file name "00000001000000000000001B"INFO: checking WAL file name "00000001000000000000001C"INFO: checking WAL file name "00000001000000000000001D"INFO: checking WAL file name "00000001000000000000001E"INFO: checking WAL file name "00000001000000000000001F"INFO: checking WAL file name "000000010000000000000020"INFO: checking WAL file name "000000010000000000000021"INFO: checking WAL file name "000000010000000000000022"INFO: checking WAL file name "000000010000000000000022.00000028.backup"INFO: checking WAL file name "000000010000000000000023"INFO: checking WAL file name "000000010000000000000024"INFO: checking WAL file name "000000010000000000000025"INFO: checking WAL file name "000000010000000000000026"INFO: checking WAL file name "000000010000000000000027"INFO: checking WAL file name "000000010000000000000028"INFO: checking WAL file name "000000010000000000000029"INFO: checking WAL file name "000000010000000000000029.00000028.backup"INFO: checking WAL file name "00000001000000000000002A"INFO: checking WAL file name "00000001000000000000002B"INFO: checking WAL file name "00000001000000000000002C"INFO: checking WAL file name "00000001000000000000002D"INFO: checking WAL file name "00000001000000000000002E"INFO: checking WAL file name "00000001000000000000002F"INFO: checking WAL file name "000000010000000000000030"INFO: checking WAL file name "000000010000000000000031"INFO: checking WAL file name "000000010000000000000031.00000028.backup"INFO: checking WAL file name "000000010000000000000032"INFO: checking WAL file name "000000010000000000000033"INFO: checking WAL file name "000000010000000000000034"INFO: checking WAL file name "000000010000000000000035"INFO: checking WAL file name "000000010000000000000036"INFO: checking WAL file name "000000010000000000000037"INFO: checking WAL file name "000000010000000000000038"INFO: checking WAL file name "000000010000000000000039"INFO: checking WAL file name "00000001000000000000003A"INFO: checking WAL file name "00000001000000000000003B"INFO: checking WAL file name "00000001000000000000003C"INFO: checking WAL file name "00000001000000000000003C.00003020.backup"INFO: checking WAL file name "00000001000000000000003D"INFO: checking WAL file name "00000001000000000000003E"INFO: checking WAL file name "00000001000000000000003F"INFO: checking WAL file name "000000010000000000000040"INFO: checking WAL file name "000000010000000000000041"INFO: checking WAL file name "000000010000000000000041.00000028.backup"INFO: checking WAL file name "000000010000000000000042"[    {        "instance": "node",        "timelines": [            {                "tli": 1,                "parent-tli": 0,                "switchpoint": "0/0",                "min-segno": "00000001000000000000001B",                "max-segno": "000000010000000000000042",                "n-segments": 40,                "size": 671088640,                "zratio": 1.00,                "closest-backup-id": "",                "status": "OK",                "lost-segments": [],                "backups": [                    {                        "id": "SBOL94",                        "status": "OK",                        "start-time": "2024-04-09 18:19:52+03",                        "backup-mode": "FULL",                        "wal": "ARCHIVE",                        "compress-alg": "lz4",                        "compress-level": 1,                        "from-replica": "false",                        "block-size": 8192,                        "xlog-block-size": 8192,                        "checksum-version": 1,                        "program-version": "2.7.3",                        "server-version": "17",                        "current-tli": 2,                        "parent-tli": 0,                        "start-lsn": "0/41000028",                        "stop-lsn": "0/420000C0",                        "end-time": "2024-04-09 18:19:58+03",                        "end-validation-time": "2024-04-09 18:19:59+03",                        "recovery-xid": 757,                        "recovery-time": "2024-04-09 18:19:56.603355+03",                        "data-bytes": 395651278,                        "wal-bytes": 16777216,                        "uncompressed-bytes": 578552566,                        "pgdata-bytes": 578552248,                        "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": 3862224379                    },                    {                        "id": "SBOL8S",                        "status": "OK",                        "start-time": "2024-04-09 18:19:40+03",                        "parent-backup-id": "SBOL8G",                        "backup-mode": "DELTA",                        "wal": "STREAM",                        "compress-alg": "lz4",                        "compress-level": 1,                        "from-replica": "false",                        "block-size": 8192,                        "xlog-block-size": 8192,                        "checksum-version": 1,                        "program-version": "2.7.3",                        "server-version": "17",                        "current-tli": 1,                        "parent-tli": 1,                        "start-lsn": "0/3C003020",                        "stop-lsn": "0/3E8D4930",                        "end-time": "2024-04-09 18:19:43+03",                        "end-validation-time": "2024-04-09 18:19:44+03",                        "recovery-xid": 757,                        "recovery-time": "2024-04-09 18:19:43.707720+03",                        "data-bytes": 119350434,                        "wal-bytes": 67108864,                        "uncompressed-bytes": 170044286,                        "pgdata-bytes": 578552248,                        "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": 1259851036                    },                    {                        "id": "SBOL8G",                        "status": "OK",                        "start-time": "2024-04-09 18:19:28+03",                        "parent-backup-id": "SBOL83",                        "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.7.3",                        "server-version": "17",                        "current-tli": 1,                        "parent-tli": 1,                        "start-lsn": "0/31000028",                        "stop-lsn": "0/310029E0",                        "end-time": "2024-04-09 18:19:32+03",                        "end-validation-time": "2024-04-09 18:19:33+03",                        "recovery-xid": 756,                        "recovery-time": "2024-04-09 18:19:32.594670+03",                        "data-bytes": 31218302,                        "wal-bytes": 16777216,                        "uncompressed-bytes": 69610366,                        "pgdata-bytes": 510263736,                        "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": 2293248595                    },                    {                        "id": "SBOL83",                        "status": "OK",                        "start-time": "2024-04-09 18:19:15+03",                        "parent-backup-id": "SBOL7P",                        "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.7.3",                        "server-version": "17",                        "current-tli": 1,                        "parent-tli": 1,                        "start-lsn": "0/29000028",                        "stop-lsn": "0/2A0000F8",                        "end-time": "2024-04-09 18:19:22+03",                        "end-validation-time": "2024-04-09 18:19:22+03",                        "recovery-xid": 755,                        "recovery-time": "2024-04-09 18:19:22.269595+03",                        "data-bytes": 48394744,                        "wal-bytes": 33554432,                        "uncompressed-bytes": 69577598,                        "pgdata-bytes": 441975224,                        "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": 343227200                    },                    {                        "id": "SBOL7P",                        "status": "OK",                        "start-time": "2024-04-09 18:19:01+03",                        "backup-mode": "FULL",                        "wal": "STREAM",                        "compress-alg": "zstd",                        "compress-level": 1,                        "from-replica": "false",                        "block-size": 8192,                        "xlog-block-size": 8192,                        "checksum-version": 1,                        "program-version": "2.7.3",                        "server-version": "17",                        "current-tli": 1,                        "parent-tli": 0,                        "start-lsn": "0/22000028",                        "stop-lsn": "0/220001C8",                        "end-time": "2024-04-09 18:19:07+03",                        "end-validation-time": "2024-04-09 18:19:09+03",                        "recovery-xid": 754,                        "recovery-time": "2024-04-09 18:19:06.557301+03",                        "data-bytes": 151177272,                        "wal-bytes": 16777216,                        "uncompressed-bytes": 373695222,                        "pgdata-bytes": 373694904,                        "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": 1636300818                    }                ]            }        ]    }]

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

Removing Redundant Backups#

By default, all backup copies created withpg_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 thepg_probackup.conf file viaset-config:

--retention-redundancy=redundancy

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

--retention-window=window

Defines the earliest point in time for whichpg_probackup can complete the recovery. This option is set inthe number of days from the current moment. For example, ifretention-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 -Bbackup_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 thedelete command withretention flags, as shown below, or use thebackup 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 -Bbackup_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 -Bbackup_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 runningdelete orbackup commands:

pg_probackup delete -Bbackup_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 runningbackup ordelete commands.

Suppose you have backed up thenode instance in thebackup_dir directory, with the--retention-window option set to6 and--retention-redundancy option set to2, and you have the following backups available on August 01, 2024:

BACKUP INSTANCE 'node'=========================================================================================================================================== Instance  Version  ID      Recovery Time           Mode   WAL Mode  TLI  Time    Data   WAL  Zalg  Zratio  Start LSN   Stop LSN    Status =========================================================================================================================================== node      17       SHJ1N9  2024-08-01 09:50:00+03  FULL   ARCHIVE   1/0    5s    13MB  16MB  zstd    2,81  0/1D000028  0/1E0000C0  OK node      17       SHJ1N8  2024-08-01 09:49:59+03  DELTA  ARCHIVE   1/1    5s  6432kB  16MB  zstd    1,06  0/1A000028  0/1B0000C0  OK node      17       SHH6Z8  2024-07-31 09:49:59+03  PAGE   ARCHIVE   1/1    5s  6431kB  16MB  zstd    1,06  0/17000028  0/180000C0  OK node      17       SHFCB6  2024-07-30 09:49:57+03  FULL   ARCHIVE   1/0    5s    12MB  16MB  zstd    2,83  0/14000028  0/150000C0  OK node      17       SH9SB5  2024-07-27 09:49:56+03  PAGE   ARCHIVE   1/1    5s  6432kB  16MB  zstd    1,06  0/11000028  0/120000C0  OK ----------------------------------------------------------retention window----------------------------------------------------------- node      17       SH62Z5  2024-07-25 09:49:56+03  DELTA  ARCHIVE   1/1    5s  6431kB  16MB  zstd    1,06  0/E000028   0/F0000C0   OK node      17       SH48B3  2024-07-24 09:49:54+03  FULL   ARCHIVE   1/0    5s    12MB  16MB  zstd    2,86  0/B000028   0/C0000C0   OK node      17       SGWTN3  2024-07-20 09:49:54+03  PAGE   ARCHIVE   1/1    5s  6432kB  16MB  zstd    1,06  0/8000028   0/90000C0   OK node      17       SGUYZ2  2024-07-19 09:49:53+03  DELTA  ARCHIVE   1/1    5s  6442kB  16MB  zstd    1,07  0/5000028   0/60000C0   OK node      17       SGT4B1  2024-07-18 09:49:52+03  FULL   ARCHIVE   1/0    5s    11MB  16MB  zstd    2,89  0/2000028   0/3003A28   OK

If you run thedelete command with the--delete-expired flag, the backups with IDsSGT4B1,SGUYZ2, andSGWTN3 will be removed as they are expired both according to the retention window and due to redundancy (the required set of full backups has already been retained).SGUYZ2 andSGWTN3 will also be removed since the base full backup is expired.

Running thedelete command with the--merge-expired flag will merge backupsSH48B3 andSH62Z5 withSH9SB5. The merge will occur withSH9SB5 as it is the first non-expired delta backup, which can be merged with expired delta backupsSH62Z5 and expired full backupSH48B3.

pg_probackup delete -Bbackup_dir --instance=node --delete-expired --merge-expiredpg_probackup show -Bbackup_dir

BACKUP INSTANCE 'node'=========================================================================================================================================== Instance  Version  ID      Recovery Time           Mode   WAL Mode  TLI  Time    Data   WAL  Zalg  Zratio  Start LSN   Stop LSN    Status=========================================================================================================================================== node      17       SHJ1N9  2024-08-01 09:50:00+03  FULL   ARCHIVE   1/0    5s    13MB  16MB  zstd    2,81  0/1D000028  0/1E0000C0  OK node      17       SHJ1N8  2024-08-01 09:49:59+03  DELTA  ARCHIVE   1/1    5s  6432kB  16MB  zstd    1,06  0/1A000028  0/1B0000C0  OK node      17       SHH6Z8  2024-07-31 09:49:59+03  PAGE   ARCHIVE   1/1    5s  6431kB  16MB  zstd    1,06  0/17000028  0/180000C0  OK node      17       SHFCB6  2024-07-30 09:49:57+03  FULL   ARCHIVE   1/0    5s    12MB  16MB  zstd    2,83  0/14000028  0/150000C0  OK node      17       SH9SB5  2024-07-27 09:49:56+03  FULL   ARCHIVE   1/0    1s    12MB  16MB  zstd    2,84  0/11000028  0/120000C0  OK

TheTime 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 -Bbackup_dir --instance=instance_name -ibackup_id --ttl=30d

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

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

pg_probackup set-backup -Bbackup_dir --instance=instance_name -ibackup_id --expire-time="2027-04-09 18:21:32+00"

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

pg_probackup backup -Bbackup_dir --instance=instance_name -b FULL --ttl=30dpg_probackup backup -Bbackup_dir --instance=instance_name -b FULL --expire-time="2027-04-09 18:21:32+00"

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

pg_probackup show -Bbackup_dir --instance=instance_name -ibackup_id

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

...recovery-time = '2024-04-09 18:21:32+00'expire-time = '2027-04-09 18:21:32+00'data-bytes = 22288792...

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

pg_probackup set-backup -Bbackup_dir --instance=instance_name -ibackup_id --ttl=0

Configuring WAL Archive Retention Policy#

Whencontinuous 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 theset-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 thedelete orbackup command with the--delete-wal flag. For archive backups, WAL segments betweenStart LSN andStop 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 thedelete andbackup commands to override the previously defined WAL archive retention policy and purge old WAL segments on the fly.

Suppose you have backed up thenode instance in thebackup_dir directory and configuredcontinuous WAL archiving:

pg_probackup show -Bbackup_dir --instance=node

================================================================================================================================================== Instance  Version  ID      Recovery Time                  Mode   WAL Mode  TLI  Time    Data   WAL  Zalg  Zratio  Start LSN   Stop LSN    Status ================================================================================================================================================== node      17       SBOLDA  2024-04-09 18:22:23.147138+03  DELTA  STREAM    1/1    1s  1165kB  16MB  zstd    1.09  0/6F000028  0/6F000190  OK      node      17       SBOLCY  2024-04-09 18:22:16.079841+03  FULL   STREAM    1/0   10s   278MB  16MB  zstd    2.46  0/6D000028  0/6D000190  OK      node      17       SBOLCW  2024-04-09 18:22:10.154022+03  DELTA  STREAM    1/1    2s  1364kB  16MB  zstd    1.01  0/6B000028  0/6B000190  OK      node      17       SBOLCS  2024-04-09 18:22:07.521646+03  DELTA  STREAM    1/1    4s    78MB  16MB  zstd    2.41  0/69000028  0/69000190  OK      node      17       SBOLCC  2024-04-09 18:21:55.830115+03  FULL   STREAM    1/0   15s   278MB  96MB  zstd    2.46  0/600060C8  0/64FE6640  OK      node      17       SBOLBW  2024-04-09 18:21:38.399702+03  FULL   STREAM    1/0   12s   278MB  96MB  zstd    2.46  0/54001830  0/589E5908  OK

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

pg_probackup show -Bbackup_dir --instance=node --archive

INFO: checking WAL file name "000000010000000000000052"INFO: checking WAL file name "000000010000000000000053"INFO: checking WAL file name "000000010000000000000054"INFO: checking WAL file name "000000010000000000000054.00001830.backup"INFO: checking WAL file name "000000010000000000000055"INFO: checking WAL file name "000000010000000000000056"INFO: checking WAL file name "000000010000000000000057"INFO: checking WAL file name "000000010000000000000058"INFO: checking WAL file name "000000010000000000000059"INFO: checking WAL file name "00000001000000000000005A"INFO: checking WAL file name "00000001000000000000005B"INFO: checking WAL file name "00000001000000000000005C"INFO: checking WAL file name "00000001000000000000005D"INFO: checking WAL file name "00000001000000000000005E"INFO: checking WAL file name "00000001000000000000005F"INFO: checking WAL file name "000000010000000000000060"INFO: checking WAL file name "000000010000000000000060.000060C8.backup"INFO: checking WAL file name "000000010000000000000061"INFO: checking WAL file name "000000010000000000000062"INFO: checking WAL file name "000000010000000000000063"INFO: checking WAL file name "000000010000000000000064"INFO: checking WAL file name "000000010000000000000065"INFO: checking WAL file name "000000010000000000000066"INFO: checking WAL file name "000000010000000000000067"INFO: checking WAL file name "000000010000000000000068"INFO: checking WAL file name "000000010000000000000069"INFO: checking WAL file name "000000010000000000000069.00000028.backup"INFO: checking WAL file name "00000001000000000000006A"INFO: checking WAL file name "00000001000000000000006B"INFO: checking WAL file name "00000001000000000000006B.00000028.backup"INFO: checking WAL file name "00000001000000000000006C"INFO: checking WAL file name "00000001000000000000006D"INFO: checking WAL file name "00000001000000000000006D.00000028.backup"INFO: checking WAL file name "00000001000000000000006E"INFO: checking WAL file name "00000001000000000000006F"INFO: checking WAL file name "00000001000000000000006F.00000028.backup"ARCHIVE INSTANCE 'node'================================================================================================================================ TLI  Parent TLI  Switchpoint  Min Segno                 Max Segno                 N segments  Size   Zratio  N backups  Status ================================================================================================================================ 1    0           0/0          000000010000000000000052  00000001000000000000006F  30          480MB  1.00    6          OK

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

pg_probackup delete -Bbackup_dir --instance=node --delete-wal

INFO: checking WAL file name "000000010000000000000054"INFO: checking WAL file name "000000010000000000000054.00001830.backup"INFO: checking WAL file name "000000010000000000000055"INFO: checking WAL file name "000000010000000000000056"INFO: checking WAL file name "000000010000000000000057"INFO: checking WAL file name "000000010000000000000058"INFO: checking WAL file name "000000010000000000000059"INFO: checking WAL file name "00000001000000000000005A"INFO: checking WAL file name "00000001000000000000005B"INFO: checking WAL file name "00000001000000000000005C"INFO: checking WAL file name "00000001000000000000005D"INFO: checking WAL file name "00000001000000000000005E"INFO: checking WAL file name "00000001000000000000005F"INFO: checking WAL file name "000000010000000000000060"INFO: checking WAL file name "000000010000000000000060.000060C8.backup"INFO: checking WAL file name "000000010000000000000061"INFO: checking WAL file name "000000010000000000000062"INFO: checking WAL file name "000000010000000000000063"INFO: checking WAL file name "000000010000000000000064"INFO: checking WAL file name "000000010000000000000065"INFO: checking WAL file name "000000010000000000000066"INFO: checking WAL file name "000000010000000000000067"INFO: checking WAL file name "000000010000000000000068"INFO: checking WAL file name "000000010000000000000069"INFO: checking WAL file name "000000010000000000000069.00000028.backup"INFO: checking WAL file name "00000001000000000000006A"INFO: checking WAL file name "00000001000000000000006B"INFO: checking WAL file name "00000001000000000000006B.00000028.backup"INFO: checking WAL file name "00000001000000000000006C"INFO: checking WAL file name "00000001000000000000006D"INFO: checking WAL file name "00000001000000000000006D.00000028.backup"INFO: checking WAL file name "00000001000000000000006E"INFO: checking WAL file name "00000001000000000000006F"INFO: checking WAL file name "00000001000000000000006F.00000028.backup"ARCHIVE INSTANCE 'node'================================================================================================================================ TLI  Parent TLI  Switchpoint  Min Segno                 Max Segno                 N segments  Size   Zratio  N backups  Status ================================================================================================================================ 1    0           0/0          000000010000000000000054  00000001000000000000006F  28          448MB  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 -Bbackup_dir --instance=node --delete-wal --wal-depth=1

INFO: checking WAL file name "00000001000000000000006F"INFO: checking WAL file name "00000001000000000000006F.00000028.backup"ARCHIVE INSTANCE 'node'=============================================================================================================================== TLI  Parent TLI  Switchpoint  Min Segno                 Max Segno                 N segments  Size  Zratio  N backups  Status =============================================================================================================================== 1    0           0/0          00000001000000000000006F  00000001000000000000006F  1           16MB  1.00    6          OK

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

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

INFO: checking WAL file name "000000010000000000000071"INFO: checking WAL file name "000000010000000000000071.00000028.backup"ARCHIVE INSTANCE 'node'=============================================================================================================================== TLI  Parent TLI  Switchpoint  Min Segno                 Max Segno                 N segments  Size  Zratio  N backups  Status =============================================================================================================================== 1    0           0/0          000000010000000000000071  000000010000000000000071  1           16MB  1.00    7          OK

• To add a new standby server.

  Usually,pg_basebackup is used to create a copy of aPostgres Pro instance. If the data directory of the destination instance is empty, thecatchup command works similarly, but it can be faster if run in parallel mode.

• To have a fallen-behind standby server“catch up” with the primary.

  Under write-intensive load, replicas may fail to replay WAL fast enough to keep up with the primary and hence may lag behind. A usual solution to create a new replica and switch to it requires a lot of extra space and data transfer. Thecatchup command allows you to update an existing replica much faster by bringing differences from the primary.

• The backup catalog is not required.

• STREAM WAL delivery mode is only supported.

• Copying external directories is not supported.

• DDL commandsCREATE TABLESPACE/DROP TABLESPACE cannot be run simultaneously withcatchup.

• catchup takes configuration files, such aspostgresql.conf,postgresql.auto.conf, orpg_hba.conf, from the source server and overwrites them on the target server. The--exclude-path option allows you to keep the configuration files intact.

• Configure the database cluster for the instance to copy.

• To copy from a remote server,configure the remote mode.

• To use the PTRACK catchup mode,set up PTRACK backups.

• FULL — creates a full copy of thePostgres Pro instance. The data directory of the destination instance must be empty for this mode.

• DELTA — reads all data files in the data directory and creates an incremental copy for pages that have changed since the destination instance was shut down.

• PTRACK — tracking page changes on the fly, only reads and copies pages that have changed since the point of divergence of the source and destination instances.

  Warning

  PTRACK catchup mode requiresPTRACK not earlier than 2.0 and hence,Postgres Pro not earlier than 11.

version#

pg_probackup version [--format=json]

Printspg_probackup version and edition, as well asPostgres Pro version and edition.

If--format=json is specified, the output is printed in the JSON format. This may be needed for native integration with JSON-based applications, such as PPEM. Example of a JSON output:

pg_probackup version --format=json{    "pg_probackup":    {        "version": "2.8.2",        "edition": "community"    },    "database":    {        "type": "Postgres Pro Standard",        "version": "16.3.1"    },    "compressions": [zlib, pglz, lz4, zstd]}

help#

pg_probackup help [command]

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

init#

pg_probackup init -Bbackup_dir [--skip-if-exists] [--help][logging_options]

Initializes the backup catalog inbackup_dir that will store backup copies, WAL archive, and meta information for the backed up database clusters. If the specifiedbackup_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 sectionInitializing the Backup Catalog.

add-instance#

pg_probackup add-instance -Bbackup_dir -Ddata_dir --instance=instance_name[--skip-if-exists] [--help] [logging_options]

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

For details, see the sectionAdding a New Backup Instance.

del-instance#

pg_probackup del-instance -Bbackup_dir --instance=instance_name [--help][logging_options]

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

set-config#

pg_probackup set-config -Bbackup_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][-ddbname] [-hhost] [-pport] [-Uusername][--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 thepg_probackup.conf configuration file, or modifies the previously defined values.

For all available settings, see theOptions section.

It isnot recommended to editpg_probackup.conf manually.

set-backup#

pg_probackup set-backup -Bbackup_dir --instance=instance_name -ibackup_id{--ttl=ttl | --expire-time=time}[--note=backup_note] [--help] [logging_options]

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

For all available pinning settings, see the sectionPinning Options.

show-config#

pg_probackup show-config -Bbackup_dir --instanceinstance_name [--format=plain|json][--no-scale-units] [logging_options]

Displays all the currentpg_probackup configuration settings, including those that are specified in thepg_probackup.conf configuration file located in thebackup_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 theJSON 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, ifarchive-timeout is 300, then5min is displayed, but ifarchive-timeout is 301, then301s 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 editpg_probackup.conf, use theset-config command.

show#

pg_probackup show -Bbackup_dir[--help] [--instance=instance_name [-ibackup_id | --archive]] [--format=plain|json] [--no-color] [--show-symlinks][logging_options]

Shows the contents of the backup catalog. Ifinstance_name andbackup_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 theJSON 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 betweenmerged backups and the original full backups that incremental backups were merged to.

For details on usage, see the sectionsManaging the Backup Catalog andViewing WAL Archive Information.

backup#

pg_probackup backup -Bbackup_dir -bbackup_mode --instance=instance_name[--help] [-jnum_threads] [--progress][--backup-threadsnum_threads] [--validate-threadsnum_threads][-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 thePostgres Pro instance.

For more details of the command settings, see sectionsCommon Options,Connection Options,Retention Options,Pinning Options,Remote Mode Options,Compression Options, andLogging Options.

For details on usage, see the sectionCreating a Backup.

restore#

pg_probackup restore -Bbackup_dir --instance=instance_name[--help] [--dry-run] [-Ddata_dir] [-ibackup_id][-jnum_threads] [--progress][-TOLDDIR=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][-Xwal_dir | --waldir=wal_dir][recovery_target_options] [logging_options] [remote_options][partial_restore_options] [remote_wal_archive_options]

Restores thePostgres Pro instance from a backup copy located in thebackup_dir backup catalog. If you specify arecovery 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.

For more details of the command settings, see sectionsCommon Options,Recovery Target Options,Remote Mode Options,Remote WAL Archive Options,Logging Options, andPartial Restore Options.

For details on usage, see the sectionRestoring a Cluster.

checkdb#

pg_probackup checkdb[-Bbackup_dir] [--instance=instance_name] [-Ddata_dir][--help] [-jnum_threads] [--progress][--amcheck [--skip-block-validation] [--checkunique] [--heapallindexed]][connection_options] [logging_options]

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

For more details of the command settings, see sectionsCommon Options,Connection Options, andLogging Options.

For details on usage, see the sectionVerifying a Cluster.

validate#

pg_probackup validate -Bbackup_dir[--help] [--instance=instance_name] [-ibackup_id][-jnum_threads] [--progress] [--wal][--skip-block-validation][recovery_target_options] [logging_options]

Verifies that all the files required to restore the cluster are present and are not corrupt. Ifinstance_name is not specified,pg_probackup validates all backups available in the backup catalog. If you specify theinstance_name without any additional options,pg_probackup validates all the backups available for this backup instance. If you specify theinstance_name with arecovery target option and/or abackup_id,pg_probackup checks whether it is possible to restore the cluster using these options. If the--wal option is specified, full check of the WAL archive will be performed instead of only checking WAL segments needed to restore the cluster.

For details, see the sectionValidating a Backup.

merge#

pg_probackup merge -Bbackup_dir --instance=instance_name -ibackup_id[--dry-run] [--help] [-jnum_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 more details of the command settings, see sectionsCommon Options andMerging Backups.

delete#

pg_probackup delete -Bbackup_dir --instance=instance_name[--help] [-jnum_threads] [--progress][--retention-redundancy=redundancy][--retention-window=window][--wal-depth=wal_depth] [--delete-wal]{-ibackup_id | --delete-expired [--merge-expired] | --merge-expired | --status=backup_status}[--dry-run] [--no-validate] [--no-sync] [logging_options]

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

For details, see the sectionsDeleting Backups,Retention Options, andConfiguring Retention Policy.

archive-push#

pg_probackup archive-push -Bbackup_dir --instance=instance_name--wal-file-name=wal_file_name [--wal-file-path=wal_file_path][--help] [--dry-run] [--no-sync] [--compress] [--no-ready-rename] [--overwrite][-jnum_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 byinstance_name andsystem-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 thearchive-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 waitarchive_timeout seconds before discarding it. After the copy is done, atomic rename is performed. This algorithm ensures that a failedarchive-push will not stall continuous archiving and that concurrent archiving from multiple sources into a single WAL archive has no risk of archive corruption.

ThePostgres Pro 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 usearchive-push in thearchive_commandPostgres Pro parameter to set upcontinuous WAL archiving.

For more details of the command settings, see sectionsCommon Options,Archiving Options, andCompression Options.

archive-get#

pg_probackup archive-get -Bbackup_dir --instance=instance_name --wal-file-path=wal_file_path --wal-file-name=wal_file_name[-jnum_threads] [--batch-size=batch_size][--prefetch-dir=prefetch_dir_path] [--no-validate-wal][--dry-run] [--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 bypg_probackup as part of therestore_command when restoring backups using a WAL archive. You do not need to set it manually.

ThePostgres Pro 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 more details of the command settings, see sectionsCommon Options,Archiving Options, andCompression Options.

catchup#

pg_probackup catchup -bcatchup_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][-TOLDDIR=NEWDIR][-X | --waldir=wal_dir][connection_options] [remote_options][logging_options]

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

For more details of the command settings, see sectionsCommon Options,Connection Options, andRemote Mode Options.

For details on usage, see the sectionCloning and SynchronizingPostgres Pro Instance.

Common Options#

The list of general options.

Recovery Target Options#

Ifcontinuous WAL archiving is configured, you can use one of these options together withrestore orvalidate commands to specify the moment up to which the database cluster must be restored or validated.

Retention Options#

You can use these options together withbackup anddelete commands.

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

Pinning Options#

You can use these options together withbackup andset-backup commands.

For details on backup pinning, see the sectionBackup Pinning.

Logging Options#

You can use these options with any command.

Connection Options#

You can use these options together withbackup,catchup, andcheckdb commands.

Alllibpq environment variables are supported.

Compression Options#

You can use these options together withbackup andarchive-push commands.

Archiving Options#

These options can be used with thearchive-push command in thearchive_command setting and thearchive-get command in therestore_command setting.

Additionally,remote mode options andlogging options can be used.

Remote Mode Options#

This section describes the options related to runningpg_probackup operations remotely via SSH. These options can be used withadd-instance,set-config,backup,catchup,restore,archive-push, andarchive-get commands.

For details on configuring and using the remote mode, seethe section called “Configuring the Remote Mode” andthe section called “Usingpg_probackup in the Remote Mode”.

Remote WAL Archive Options#

This section describes the options used to provide the arguments forremote mode options inarchive-get used in therestore_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 therestore command.

Partial Restore Options#

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

Testing and Debugging Options#

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