Postgres Pro
Downloads
More Examples
Prev Chapter 4. Usage Next

More Examples

Minimal Setup

All examples below assume the remote mode of operations via SSH. If you are planning to run backup and restore operation locally, skip the “Setup passwordless SSH connection” step and omit all --remote-* options.

Examples are based on Ubuntu 22.04, Postgres Pro 17, and pg_probackup3

  • backup — Postgres Pro role used to connect to the Postgres Pro cluster.

  • backupdb — database used to connect to the Postgres Pro cluster.

  • backup_host — host with the backup catalog.

  • backup_user — user on backup_host running all pg_probackup3 operations.

  • /mnt/backups — directory on backup_host where the backup catalog is stored.

  • postgres_host — host with the Postgres Pro cluster.

  • postgres — user on postgres_host under which Postgres Pro cluster processes are running..

  • /var/lib/pgpro/std-17/data — Postgres Pro data directory on postgres_host.

Minimal Setup

This scenario illustrates setting up standalone FULL and DELTA backups.

  1. Set up passwordless SSH connection from backup_host to postgres_host:

    [backup_user@backup_host] ssh-copy-id postgres@postgres_host

  2. Configure your Postgres Pro cluster.

    For security purposes, it is recommended to use a separate database for backup operations. 

    postgres=#
CREATE DATABASE backupdb;

    Connect to the backupdb database, create the probackup role, and grant the following permissions to this role: 

    backupdb=#
BEGIN;
CREATE ROLE backup WITH LOGIN REPLICATION;
GRANT USAGE ON SCHEMA pg_catalog TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.current_setting(text) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.set_config(text, text, boolean) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_is_in_recovery() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_start_backup(text, boolean, boolean) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_stop_backup(boolean, boolean) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_create_restore_point(text) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_switch_wal() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_last_wal_replay_lsn() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.txid_current() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.txid_current_snapshot() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.txid_snapshot_xmax(txid_snapshot) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_control_checkpoint() TO backup;
COMMIT;

    Add the pg_probackup3 module pgpro_bindump in the postgresql.conf file: 

    echo "shared_preload_libraries = 'pgpro_bindump'" >> "/var/lib/pgpro/std-17/data/postgresql.conf"
echo "walsender_plugin_libraries = 'pgpro_bindump' " >> "/var/lib/pgpro/std-17/data/postgresql.conf"
echo "wal_level = 'replica'" >> "/var/lib/pgpro/std-17/data/postgresql.conf"

  3. Initialize the backup catalog:

    [backup_user@backup_host]$ pg_probackup3 init -B /mnt/backups
2024-12-09 07:40:27.198881] [363926] [135107950659968] [info] Backup catalog '/mnt/backups' successfully initialized

  4. Add instance pg-17 to the backup catalog:

    [backup_user@backup_host]$ pg_probackup3 add-instance -B /mnt/backups --instance pg-17 --remote-host=postgres_host --remote-user=postgres -D var/lib/pgpro/std-17/data
[2024-12-09 07:47:56.595727] [364390] [138813944502656] [info] Instance 'pg-17' successfully initialized

  5. Take a FULL backup:

    [backup_user@backup_host] pg_probackup3 backup -B /mnt/backups --instance pg-17 -b FULL --stream --remote-host=postgres_host --remote-user=postgres -U backup -d backupdb --backup-id=1-full
[2024-12-09 23:44:49.602026] [425177] [123209585379712] [info] START BACKUP COMMAND= PGPRO_CALL_PLUGIN pgpro_bindump start_backup(LABEL '1-full');
[2024-12-09 23:44:49.645450] [425177] [123209585379712] [info] PG_PROBACKUP 0/4000028 tli=1
[2024-12-09 23:44:49.652048] [425177] [123209585379712] [info] Created replication slot. Name='pg_probackup3_wal_streaming_425181', consistent point=0/0, snapshot name=, output plugin=
[2024-12-09 23:44:49.652185] [425177] [123209585379712] [info] BACKUP COMMAND PGPRO_CALL_PLUGIN pgpro_bindump copy_files(VERIFY_CHECKSUMS true, COMPRESS_ALG 'none', COMPRESS_LVL 1);
[2024-12-09 23:44:49.652468] [425177] [123209573729984] [info] Starting new segment 4
[2024-12-09 23:44:49.769640] [425177] [123209585379712] [info] BACKUP COMMAND PGPRO_CALL_PLUGIN pgpro_bindump stop_backup(STREAM true, COMPRESS_ALG 'none', COMPRESS_LVL 1);
[2024-12-09 23:44:49.805112] [425177] [123209573729984] [info] Stopping segment 4
[2024-12-09 23:44:49.805316] [425177] [123209573729984] [info] finished streaming WAL at 0/5000000 (timeline 1)
[2024-12-09 23:44:49.805343] [425177] [123209573729984] [info] WAL streaming: WAL streaming stop requested at 0/4000138, stopping at 0/5000000
[2024-12-09 23:44:49.805935] [425177] [123209585379712] [info] PG_PROBACKUP-STOP 0/4000138 tli=1 bytes written=39430093 bytes compressed=39430093
[2024-12-09 23:44:49.806484] [425177] [123209585379712] [info] Backup time 206
[2024-12-09 23:44:49.806515] [425177] [123209585379712] [info] Backup 1-full completed successfully.
INFO: Backup 1-full completed successfully.
[2024-12-09 23:44:49.806592] [425177] [123209585379712] [info] Start validate  1-full ...
[2024-12-09 23:44:49.807204] [425177] [123209585379712] [info] Validating backup 1-full
[2024-12-09 23:44:49.912115] [425177] [123209585379712] [info] Validate time 104
[2024-12-09 23:44:49.912398] [425177] [123209585379712] [info] INFO: Backup 1-full is valid

  6. Let's take a look at the backup catalog:

    [backup_user@backup_host] pg_probackup3 show -B /mnt/backups --instance pg-17

BACKUP INSTANCE 'pg-17', version 3
============================================================================================================================
 Instance Version ID     End time                 Mode WAL Mode TLI Duration Data WAL Zalg Zratio Start LSN Stop LSN  Status 
============================================================================================================================
pg-17      16      1-full 2024-12-09 23:44:49+0000 FULL STREAM   1            38MB -   none 1.00   0/4000028 0/4000138 OK

  7. Take an incremental backup in the DELTA mode:

    [backup_user@backup_host] pg_probackup3 backup -B /mnt/backups --instance pg-17 -b delta --stream --remote-host=postgres_host --remote-user=postgres -U backup -d backupdb --parent-backup-id=1-full --backup-id=1-delta
[2024-12-10 01:00:50.804867] [430043] [130779551140224] [info] This PostgreSQL instance was initialized with data block checksums. Data block corruption will be detected
[2024-12-10 01:00:50.805233] [430043] [130779551140224] [info] START BACKUP COMMAND= PGPRO_CALL_PLUGIN pgpro_bindump start_backup(LABEL '1-delta', START_LSN '0/4000028');
[2024-12-10 01:00:50.843249] [430043] [130779551140224] [info] PG_PROBACKUP 0/6000028 tli=1
[2024-12-10 01:00:50.850799] [430043] [130779551140224] [info] Created replication slot. Name='pg_probackup3_wal_streaming_430047', consistent point=0/0, snapshot name=, output plugin=
[2024-12-10 01:00:50.850898] [430043] [130779551140224] [info] BACKUP COMMAND PGPRO_CALL_PLUGIN pgpro_bindump copy_files(VERIFY_CHECKSUMS true, START_LSN '0/4000028', COMPRESS_ALG 'none', COMPRESS_LVL 1);
[2024-12-10 01:00:50.851124] [430043] [130779470366400] [info] Starting new segment 6
[2024-12-10 01:00:50.877932] [430043] [130779551140224] [info] BACKUP COMMAND PGPRO_CALL_PLUGIN pgpro_bindump stop_backup(STREAM true, COMPRESS_ALG 'none', COMPRESS_LVL 1);
[2024-12-10 01:00:50.913070] [430043] [130779470366400] [info] Stopping segment 6
[2024-12-10 01:00:50.913284] [430043] [130779470366400] [info] finished streaming WAL at 0/7000000 (timeline 1)
[2024-12-10 01:00:50.913302] [430043] [130779470366400] [info] WAL streaming: WAL streaming stop requested at 0/6000138, stopping at 0/7000000
[2024-12-10 01:00:50.913497] [430043] [130779551140224] [info] PG_PROBACKUP-STOP 0/6000138 tli=1 bytes written=786310 bytes compressed=786310
[2024-12-10 01:00:50.913868] [430043] [130779551140224] [info] Backup time 110
[2024-12-10 01:00:50.913884] [430043] [130779551140224] [info] Backup 1-delta completed successfully.
INFO: Backup 1-delta completed successfully.
[2024-12-10 01:00:50.913918] [430043] [130779551140224] [info] Start validate  1-delta ...
[2024-12-10 01:00:50.914269] [430043] [130779551140224] [info] Validating backup 1-delta
[2024-12-10 01:00:50.934892] [430043] [130779551140224] [info] Validate time 20
[2024-12-10 01:00:50.935188] [430043] [130779551140224] [info] INFO: Backup 1-delta is valid

  8. Let's add some parameters to pg_probackup3 configuration file, so that you can omit them from the command line:

    [backup_user@backup_host] pg_probackup3 set-config -B /mnt/backups --instance pg-17 --remote-host=postgres_host --remote-user=postgres -U backup -d backupdb
[2024-12-10 01:03:18.173698] [430208] [125541616851328] [info] Instance 'pg-17' successfully updated

  9. Take another incremental backup in the DELTA mode, omitting some of the previous parameters:

    [backup_user@backup_host] pg_probackup3 backup -B /mnt/backups --instance pg-17 -b delta --stream --parent-backup-id=1-delta --backup-id=2-delta
[2024-12-10 01:26:33.325658] [431695] [135663496210816] [info] This PostgreSQL instance was initialized with data block checksums. Data block corruption will be detected
[2024-12-10 01:26:33.326140] [431695] [135663496210816] [info] START BACKUP COMMAND= PGPRO_CALL_PLUGIN pgpro_bindump start_backup(LABEL '2-delta', START_LSN '0/6000028');
[2024-12-10 01:26:33.365430] [431695] [135663496210816] [info] PG_PROBACKUP 0/8000028 tli=1
[2024-12-10 01:26:33.372681] [431695] [135663496210816] [info] Created replication slot. Name='pg_probackup3_wal_streaming_431699', consistent point=0/0, snapshot name=, output plugin=
[2024-12-10 01:26:33.372762] [431695] [135663496210816] [info] BACKUP COMMAND PGPRO_CALL_PLUGIN pgpro_bindump copy_files(VERIFY_CHECKSUMS true, START_LSN '0/6000028', COMPRESS_ALG 'none', COMPRESS_LVL 1);
[2024-12-10 01:26:33.372966] [431695] [135663483619008] [info] Starting new segment 8
[2024-12-10 01:26:33.407073] [431695] [135663496210816] [info] BACKUP COMMAND PGPRO_CALL_PLUGIN pgpro_bindump stop_backup(STREAM true, COMPRESS_ALG 'none', COMPRESS_LVL 1);
[2024-12-10 01:26:33.441125] [431695] [135663483619008] [info] Stopping segment 8
[2024-12-10 01:26:33.441303] [431695] [135663483619008] [info] finished streaming WAL at 0/9000000 (timeline 1)
[2024-12-10 01:26:33.441318] [431695] [135663483619008] [info] WAL streaming: WAL streaming stop requested at 0/8000138, stopping at 0/9000000
[2024-12-10 01:26:33.441497] [431695] [135663496210816] [info] PG_PROBACKUP-STOP 0/8000138 tli=1 bytes written=786310 bytes compressed=786310
[2024-12-10 01:26:33.441809] [431695] [135663496210816] [info] Backup time 117
[2024-12-10 01:26:33.441822] [431695] [135663496210816] [info] Backup 2-delta completed successfully.
INFO: Backup 2-delta completed successfully.
[2024-12-10 01:26:33.441850] [431695] [135663496210816] [info] Start validate  2-delta ...
[2024-12-10 01:26:33.442115] [431695] [135663496210816] [info] Validating backup 2-delta
[2024-12-10 01:26:33.463554] [431695] [135663496210816] [info] Validate time 21
[2024-12-10 01:26:33.463728] [431695] [135663496210816] [info] INFO: Backup 2-delta is valid

  10. Let's take a look at the instance configuration:

    [backup_user@backup_host] pg_probackup3 show-config -B /mnt/backups --instance pg-17

# Backup instance information
system-identifier = 7446313657913924966
# Connection parameters
pguser = backup
pgdatabase = backupdb
pgdata = /var/lib/pgpro/std-17/data
# 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 = 0
# Compression parameters
compress-algorithm = none
compress-level = 0
# Retention parameters
retention-redundancy = 0
retention-window = 0
wal-depth = 0

  11. Let's take a look at the backup catalog:

    [backup_user@backup_host] pg_probackup3 show -B /mnt/backups --instance pg-17
BACKUP INSTANCE 'pg-17', version 3
=========================================================================================================================================
 Instance Version ID      End time                 Mode  WAL Mode TLI Duration Data  WAL Zalg Zratio Start LSN Stop LSN  Status 
===============================================================================================================================
pg-17      16      1-full  2024-12-09 23:44:49+0000 FULL  STREAM   1            38MB  -   none 1.00   0/4000028 0/4000138 OK     
pg-17      16      1-delta 2024-12-10 01:00:50+0000 DELTA STREAM   1            768kB -   none 1.00   0/6000028 0/6000138 OK     
pg-17      16      2-delta 2024-12-10 01:26:33+0000 DELTA STREAM   1            768kB -   none 1.00   0/8000028 0/8000138 OK
Prev Up Next
Configuring Retention Policy Home Chapter 5. Reference