pg_probackup

https://postgrespro.github.io/pg_probackup/
Admin
pg_probackup is a utility to manage backup and recovery of PostgreSQL database clusters, designed to...
Tokens:48,022
Snippets:319
Trust Score:9.7
Update:4 weeks ago
Show doc for...
Tokens:
Raw
### Setup repository on SUSE

Source: https://postgrespro.github.io/pg_probackup

Sets up the pg_probackup repository on SUSE Linux.

```bash
zypper in https://repo.postgrespro.ru/pg_probackup/keys/pg_probackup-repo-suse.noarch.rpm

```

--------------------------------

### Setup repository on ALT Linux 10

Source: https://postgrespro.github.io/pg_probackup

Sets up the pg_probackup repository on ALT Linux 10.

```bash
. /etc/os-release
echo "rpm http://repo.postgrespro.ru/pg_probackup/rpm/latest/altlinux-p$VERSION_ID x86_64 vanilla" | \
sudo tee /etc/apt/sources.list.d/pg_probackup.list

```

--------------------------------

### Setup repository on ALT Linux 8/9

Source: https://postgrespro.github.io/pg_probackup

Sets up the pg_probackup repository on ALT Linux 8 and 9.

```bash
. /etc/os-release
echo "rpm http://repo.postgrespro.ru/pg_probackup/rpm/latest/altlinux-$VERSION_ID x86_64 vanilla" | \
sudo tee /etc/apt/sources.list.d/pg_probackup.list

```

--------------------------------

### Install pg_probackup-16 on SUSE

Source: https://postgrespro.github.io/pg_probackup

Installs or upgrades pg_probackup version 16 on SUSE Linux.

```bash
zypper in pg_probackup-16

```

--------------------------------

### Install pg_probackup-16 on ALT Linux

Source: https://postgrespro.github.io/pg_probackup

Installs or upgrades pg_probackup version 16 on ALT Linux.

```bash
sudo apt-get install pg_probackup-16

```

--------------------------------

### Example output of pg_probackup show

Source: https://postgrespro.github.io/pg_probackup

Example output of the pg_probackup show command, displaying a table of backups for instance 'node'. Each row shows the backup ID, recovery time, mode, WAL mode, timeline, size, and status.

```text
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     

```

--------------------------------

### Install pg_probackup repository on RHEL

Source: https://postgrespro.github.io/pg_probackup

Installs the pg_probackup repository on Red Hat family systems using dnf.

```bash
dnf install https://repo.postgrespro.ru/pg_probackup/keys/pg_probackup-repo-centos.noarch.rpm

```

--------------------------------

### Install pg-probackup-16 on Debian

Source: https://postgrespro.github.io/pg_probackup

Installs or upgrades pg_probackup version 16 on Debian-based systems.

```bash
sudo apt install pg-probackup-16

```

--------------------------------

### Incremental restore in LSN mode example

Source: https://postgrespro.github.io/pg_probackup

Example of restoring an old master as a replica after switchover using incremental restore in LSN mode. Shows the backup list and the restore command with its output, including the incremental ratio and validation steps.

```text
======================================================================================================================================
 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.

```

--------------------------------

### Setup Debian source repository

Source: https://postgrespro.github.io/pg_probackup

Optionally adds the source package repository for rebuilding binaries on Debian-based systems.

```bash
echo "deb-src [arch=amd64] https://repo.postgrespro.ru/pg_probackup/deb $VERSION_CODENAME main-$VERSION_CODENAME" | \
sudo tee -a /etc/apt/sources.list.d/pg_probackup.list

```

--------------------------------

### Setup Debian binary repository

Source: https://postgrespro.github.io/pg_probackup

Sets up the binary package repository for Debian-based systems. Uses the current OS release codename.

```bash
. /etc/os-release
echo "deb [arch=amd64] https://repo.postgrespro.ru/pg_probackup/deb $VERSION_CODENAME main-$VERSION_CODENAME" | \
sudo tee /etc/apt/sources.list.d/pg_probackup.list

```

--------------------------------

### Install pg_probackup-16 on RHEL

Source: https://postgrespro.github.io/pg_probackup

Installs or upgrades pg_probackup version 16 on Red Hat family systems using dnf.

```bash
dnf install pg_probackup-16

```

--------------------------------

### Install source package on SUSE

Source: https://postgrespro.github.io/pg_probackup

Optionally installs the source package for rebuilding binaries on SUSE Linux.

```bash
zypper si pg_probackup-16

```

--------------------------------

### Install source package on Debian

Source: https://postgrespro.github.io/pg_probackup

Optionally installs the source package for pg_probackup 16 on Debian-based systems, provided the source repository is set up.

```bash
sudo apt install dpkg-dev
sudo apt source pg-probackup-16

```

--------------------------------

### Install source package with dnf

Source: https://postgrespro.github.io/pg_probackup

Optionally installs the source package for rebuilding binaries on Red Hat family systems using dnf.

```bash
dnf install 'dnf-command(download)'
dnf download --source pg_probackup-16

```

--------------------------------

### Show detailed backup information with pg_probackup show

Source: https://postgrespro.github.io/pg_probackup

Run the show command with the backup ID to get detailed information about a specific backup. Replace _backup_dir_, _instance_name_, and _backup_id_ with actual values.

```bash
pg_probackup show -B _backup_dir_ --instance=_instance_name_ -i _backup_id_

```

--------------------------------

### Output of show for pinned backup

Source: https://postgrespro.github.io/pg_probackup

Example output of the show command for a pinned backup, showing the expire-time attribute.

```text
...
recovery-time = '2024-05-02 11:21:00+00'
expire-time = '2027-05-02 11:21:00+00'
data-bytes = 22288792
...

```

--------------------------------

### pg_probackup show-config command

Source: https://postgrespro.github.io/pg_probackup

Displays the contents of the pg_probackup.conf configuration file located in the backup_dir/backups/instance_name directory. You can specify --format=json to get the result in JSON format; by default, settings are shown as plain text. The --no-scale-units option displays time and memory settings in base units, and for JSON format numeric and boolean values are not enclosed in quotes.

```bash
pg_probackup show-config -B  _backup_dir_ --instance=_instance_name_ [--format=plain|json]
[--no-scale-units] [_logging_options_]

```

--------------------------------

### Restore multiple databases with repeated --db-include

Source: https://postgrespro.github.io/pg_probackup

Example of restoring only databases db1 and db2 by specifying the --db-include option multiple times.

```bash
pg_probackup restore -B _backup_dir_ --instance=_instance_name_ --db-include=db1 --db-include=db2

```

--------------------------------

### Initialize backup catalog with pg_probackup init

Source: https://postgrespro.github.io/pg_probackup

Initializes the backup catalog at the specified path. The directory must not exist or must be empty, otherwise pg_probackup returns an error. The user must have full access to the backup directory.

```bash
pg_probackup init -B _backup_dir_

```

--------------------------------

### Initialize backup catalog with pg_probackup-16 init

Source: https://postgrespro.github.io/pg_probackup

Initializes the backup catalog at /mnt/backups. The command outputs a confirmation message when successful.

```bash
backup_user@backup_host:~$ pg_probackup-16 init -B /mnt/backups
INFO: Backup catalog '/mnt/backups' successfully initialized

```

--------------------------------

### Install debug package on Debian

Source: https://postgrespro.github.io/pg_probackup

Optionally installs the debug package for pg_probackup 16 on Debian-based systems.

```bash
sudo apt install pg-probackup-16-dbg

```

--------------------------------

### Install debug package on ALT Linux

Source: https://postgrespro.github.io/pg_probackup

Optionally installs the debug package for pg_probackup 16 on ALT Linux.

```bash
sudo apt-get install pg_probackup-16-debuginfo

```

--------------------------------

### Sample output of pg_probackup show

Source: https://postgrespro.github.io/pg_probackup

Sample output of the pg_probackup show command, showing configuration, compatibility, and result backup info attributes. The status is OK, indicating a valid backup.

```text
#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

```

--------------------------------

### pg_probackup init command

Source: https://postgrespro.github.io/pg_probackup

Initializes the backup catalog in the specified 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.

```bash
pg_probackup init -B  _backup_dir_ [--help]

```

--------------------------------

### Install debug package on RHEL

Source: https://postgrespro.github.io/pg_probackup

Optionally installs the debug package for pg_probackup 16 on Red Hat family systems.

```bash
dnf install pg_probackup-16-debuginfo

```

--------------------------------

### Show pg_probackup instance configuration with show-config

Source: https://postgrespro.github.io/pg_probackup

Displays the current configuration of the instance. Parameters not modified via set-config retain their default values.

```bash
backup_user@backup_host:~$ pg_probackup-16 show-config \
    -B /mnt/backups \
    --instance=node
# Backup instance information
pgdata = /var/lib/pgpro/std-16/data
system-identifier = 7364313570668255886
xlog-seg-size = 16777216
# Connection parameters
pgdatabase = backupdb
pghost = postgres_host
pguser = backup
# Replica parameters
replica-timeout = 5min
# Archive parameters
archive-timeout = 5min
# Logging parameters
log-level-console = INFO
log-level-file = OFF
log-format-console = PLAIN
log-format-file = PLAIN
log-filename = pg_probackup.log
log-rotation-size = 0TB
log-rotation-age = 0d
# Retention parameters
retention-redundancy = 0
retention-window = 0
wal-depth = 0
# Compression parameters
compress-algorithm = none
compress-level = 1
# Remote access parameters
remote-proto = ssh
remote-host = postgres_host
remote-user = postgres

```

--------------------------------

### Install source package with yum

Source: https://postgrespro.github.io/pg_probackup

Optionally installs the source package for rebuilding binaries on Red Hat family systems using yum.

```bash
yumdownloader --source pg_probackup-16

```

--------------------------------

### View pg_probackup configuration with show-config

Source: https://postgrespro.github.io/pg_probackup

Displays the current settings defined in pg_probackup.conf for the specified instance. Requires the backup directory and instance name.

```bash
pg_probackup show-config -B _backup_dir_ --instance=_instance_name_

```

--------------------------------

### Install amcheck extension for cluster verification

Source: https://postgrespro.github.io/pg_probackup

Installs the amcheck extension in every database of the cluster to enable logical verification. The amcheck_next extension can be used instead.

```sql
CREATE EXTENSION amcheck;

```

--------------------------------

### pg_probackup add-instance command

Source: https://postgrespro.github.io/pg_probackup

Initializes a new backup instance inside the backup catalog and generates the pg_probackup.conf configuration file that controls pg_probackup settings for the cluster with the specified data directory.

```bash
pg_probackup add-instance -B  _backup_dir_ -D _data_dir_ --instance=_instance_name_ [--help]

```

--------------------------------

### List packages with apt

Source: https://postgrespro.github.io/pg_probackup

Lists available pg_probackup packages using apt on Debian-based systems.

```bash
sudo apt update
apt search pg_probackup

```

--------------------------------

### List packages with apt-get

Source: https://postgrespro.github.io/pg_probackup

Lists available pg_probackup packages using apt-get on older Debian-based systems.

```bash
sudo apt-get update
apt-cache search pg_probackup

```

--------------------------------

### pg_probackup show command

Source: https://postgrespro.github.io/pg_probackup

Shows the contents of the backup catalog. If instance_name and backup_id are specified, shows detailed information about that backup; if --archive is specified, shows the contents of the WAL archive. By default, output is plain text; --format=json produces JSON, and --no-color disables colored output.

```bash
pg_probackup show -B  _backup_dir_
[--help] [--instance=_instance_name_ [-i _backup_id_ | --archive]] [--format=plain|json] [--no-color]

```

--------------------------------

### Sample JSON output for backup details

Source: https://postgrespro.github.io/pg_probackup

Sample JSON output from the show command with --format=json. Contains backup metadata such as backup mode, WAL mode, compression, LSNs, timestamps, and status.

```json
[
    {
        "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
            }
        ]
    }
]

```

--------------------------------

### pg_probackup help command

Source: https://postgrespro.github.io/pg_probackup

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

```bash
pg_probackup help [ _command_]

```

--------------------------------

### Delete backups by status with --status

Source: https://postgrespro.github.io/pg_probackup

Deletes all backups with a specific status, ignoring established retention policies. The example uses status ERROR.

```bash
pg_probackup delete -B _backup_dir_ --instance=_instance_name_ --status=ERROR
    
```

--------------------------------

### List pg_probackup backups with show

Source: https://postgrespro.github.io/pg_probackup

Lists all backups for the instance, showing the new DELTA backup SCUN2C alongside the previous DELTA and FULL backups.

```bash
backup_user@backup_host:~$ pg_probackup-16 show \
    -B /mnt/backups \
    --instance=node
===================================================================================================================================
 Instance  Version  ID      Recovery Time           Mode   WAL Mode  TLI  Time    Data   WAL  Zratio  Start LSN  Stop LSN   Status 
===================================================================================================================================
 node      16       SCUN2C  2024-05-02 11:18:13+03  DELTA  STREAM    1/1   10s  1139kB  16MB    1.00  0/B000028  0/B000168  OK     
 node      16       SCUN22  2024-05-02 11:18:04+03  DELTA  STREAM    1/1   10s  2357kB  32MB    1.02  0/9000028  0/9000168  OK     
 node      16       SCUN1Q  2024-05-02 11:17:53+03  FULL   STREAM    1/0   12s    40MB  16MB    2.42  0/8000028  0/800BBD0  OK     

```

--------------------------------

### Exclude multiple databases with repeated --db-exclude

Source: https://postgrespro.github.io/pg_probackup

Example of excluding databases db1 and db2 from restore by specifying the --db-exclude option multiple times.

```bash
pg_probackup restore -B _backup_dir_ --instance=_instance_name_ --db-exclude=db1 --db-exclude=db2

```

--------------------------------

### Grant verification permissions to backup role

Source: https://postgrespro.github.io/pg_probackup

Grants the backup role the permissions required for logical verification in every database of the cluster. The role name 'backup' is used as an example.

```sql
GRANT SELECT ON TABLE pg_catalog.pg_am TO backup;
GRANT SELECT ON TABLE pg_catalog.pg_class TO backup;
GRANT SELECT ON TABLE pg_catalog.pg_database TO backup;
GRANT SELECT ON TABLE pg_catalog.pg_namespace TO backup;
GRANT SELECT ON TABLE pg_catalog.pg_extension TO backup;
GRANT EXECUTE ON FUNCTION bt_index_check(regclass) TO backup;
GRANT EXECUTE ON FUNCTION bt_index_check(regclass, bool) TO backup;
GRANT EXECUTE ON FUNCTION bt_index_check(regclass, bool, bool) TO backup;

```

--------------------------------

### List backups with pg_probackup show

Source: https://postgrespro.github.io/pg_probackup

Lists all existing backups for every instance in the backup catalog. The output includes instance, version, backup ID, recovery time, mode, WAL mode, timeline, size, and status.

```bash
pg_probackup show -B _backup_dir_

```

--------------------------------

### Create backup database

Source: https://postgrespro.github.io/pg_probackup

Creates a separate database for running configuration SQL queries, as recommended for security reasons.

```sql
postgres=# CREATE DATABASE backupdb;
postgres=# \c backupdb

```

--------------------------------

### Backup list before delete --delete-expired

Source: https://postgrespro.github.io/pg_probackup

Example backup list showing backups before running delete with --delete-expired. The retention window is marked; backups outside it (SCC4HX, SCDZ6D, SC8F5G) are candidates for deletion.

```text
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     


```
Tokens:
Raw