pg_probackup: Backup and recovery manager for PostgreSQL

Summary: pg_probackup is a utility to manage backup and recovery of PostgreSQL database clusters.

Table of Contents

Description

pg_probackup is a utility to manage backup and recovery of PostgreSQL database clusters. It is designed to perform periodic backups of the PostgreSQL instance that enable you to restore the server in case of a failure. pg_probackup supports PostgreSQL 11 or higher.

Overview

As compared to other backup solutions, pg_probackup offers the following benefits that can help you implement different backup strategies and deal with large amounts of data:

• Incremental backup: with three different incremental modes, you can plan the backup strategy in accordance with your data flow. Incremental backups allow you to save disk space and speed up backup as compared to taking full backups. It is also faster to restore the cluster by applying incremental backups than by replaying WAL files.
• Incremental restore: speed up restore from backup by reusing valid unchanged pages available in PGDATA.
• Validation: automatic data consistency checks and on-demand backup validation without actual data recovery.
• Verification: on-demand verification of PostgreSQL instance with the checkdb command.
• Retention: managing WAL archive and backups in accordance with retention policy. You can configure retention policy based on recovery time or the number of backups to keep, as well as specify time to live (TTL) for a particular backup. Expired backups can be merged or deleted.
• Parallelization: running backup, restore, merge, delete, validate, and checkdb processes on multiple parallel threads.
• Compression: storing backup data in a compressed state to save disk space.
• Deduplication: saving disk space by excluding non-data files (such as _vm or _fsm) from incremental backups if these files have not changed since they were copied into one of the previous backups in this incremental chain.
• Remote operations: backing up PostgreSQL instance located on a remote system or restoring a backup remotely.
• Backup from standby: avoiding extra load on master by taking backups from a standby server.
• External directories: backing up files and directories located outside of the PostgreSQL data directory (PGDATA), such as scripts, configuration files, logs, or SQL dump files.
• Backup catalog: getting the list of backups and the corresponding meta information in plain text or JSON formats.
• Archive catalog: getting the list of all WAL timelines and the corresponding meta information in plain text or JSON formats.
• Partial restore: restoring only the specified databases.
• Catchup: cloning a PostgreSQL instance for a fallen-behind standby server to “catch up” with master.

To manage backup data, pg_probackup creates a backup catalog. This is a directory that stores all backup files with additional meta information, as well as WAL archives required for point-in-time recovery. You can store backups for different instances in separate subdirectories of a single backup catalog.

Using pg_probackup, you can take full or incremental backups:

• FULL backups contain all the data files required to restore the database cluster.
• Incremental backups operate at the page level, only storing the data that has changed since the previous backup. It allows you to save disk space and speed up the backup process as compared to taking full backups. It is also faster to restore the cluster by applying incremental backups than by replaying WAL files. pg_probackup supports the following modes of incremental backups:
  • DELTA backup. In this mode, pg_probackup reads all data files in the data directory and copies only those pages that have changed since the previous backup. This mode can impose read-only I/O pressure equal to a full backup.
  • PAGE backup. In this mode, pg_probackup scans all WAL files in the archive from the moment the previous full or incremental backup was taken. Newly created backups contain only the pages that were mentioned in WAL records. This requires all the WAL files since the previous backup to be present in the WAL archive. If the size of these files is comparable to the total size of the database cluster files, speedup is smaller, but the backup still takes less space. You have to configure WAL archiving as explained in Setting up continuous WAL archiving to make PAGE backups.
  • PTRACK backup. In this mode, PostgreSQL tracks page changes on the fly. Continuous archiving is not necessary for it to operate. Each time a relation page is updated, this page is marked in a special PTRACK bitmap. Tracking implies some minor overhead on the database server operation, but speeds up incremental backups significantly.

pg_probackup can take only physical online backups, and online backups require WAL for consistent recovery. So regardless of the chosen backup mode (FULL, PAGE or DELTA), any backup taken with pg_probackup must use one of the following WAL delivery modes:

• ARCHIVE. Such backups rely on continuous archiving to ensure consistent recovery. This is the default WAL delivery mode.
• STREAM. Such backups include all the files required to restore the cluster to a consistent state at the time the backup was taken. Regardless of continuous archiving having been set up or not, the WAL segments required for consistent recovery are streamed via replication protocol during backup and included into the backup files. That’s why such backups are called autonomous, or standalone.

Limitations

pg_probackup currently has the following limitations:

• pg_probackup only supports PostgreSQL 9.5 and higher.
• The remote mode is not supported on Windows systems.
• On Unix systems, for PostgreSQL 11, a backup can be made only by the same OS user that has started the PostgreSQL server. For example, if PostgreSQL server is started by user postgres, the backup command must also be run by user postgres. To satisfy this requirement when taking backups in the remote mode using SSH, you must set --remote-user option to postgres.
• For PostgreSQL 9.5, functions pg_create_restore_point(text) and pg_switch_xlog() can be executed only if the backup role is a superuser, so backup of a cluster with low amount of WAL traffic by a non-superuser role can take longer than the backup of the same cluster by a superuser role.
• The PostgreSQL server from which the backup was taken and the restored server must be compatible by the block_size and wal_block_size parameters and have the same major release number. Depending on cluster configuration, PostgreSQL itself may apply additional restrictions, such as CPU architecture or libc/icu versions.