Postgres Pro Backup Enterprise 3 : Документация: 3.0: Другие примеры : Компания Postgres Professional

Другие примеры
Пред.	Глава 4. Использование	След.

Другие примеры

Минимальная настройка

Все приведённые ниже примеры предполагают использование удалённого режима работы через SSH. Если вы планируете выполнять резервное копирование и восстановление локально, пропустите шаг «Настройка SSH-подключения без пароля» и не используйте параметры --remote-*.

Примеры основаны на Ubuntu 22.04, Postgres Pro 17 и pg_probackup3

backup — роль в Postgres Pro, используемая для подключения к кластеру Postgres Pro.
backupdb — база данных, через которую выполняется подключение к кластеру Postgres Pro.
backup_host — система, где находится каталог резервных копий.
backup_user — пользователь в системе backup_host, от имени которого выполняются все операции pg_probackup3.
/mnt/backups — путь к каталогу резервных копий в системе backup_host.
postgres_host — система, в которой работает Postgres Pro.
postgres — пользователь в системе postgres_host, от имени которого запускаются процессы кластера Postgres Pro.
/var/lib/pgpro/std-17/data — путь к каталогу данных Postgres Pro в системе postgres_host.

Минимальная настройка

Данный сценарий показывает настройку самодостаточного полного или разностного резервного копирования.

Настройте подключение по SSH с backup_host к postgres_host:
```
[backup_user@backup_host] ssh-copy-id postgres@postgres_host
```

Настройте кластер Postgres Pro.

В целях безопасности для резервного копирования рекомендуется использовать отдельную базу данных.

postgres=#
CREATE DATABASE backupdb;

Подключитесь к базе данных backupdb, создайте роль probackup и выдайте этой роли следующие права:

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;

Добавьте модуль pg_probackup3 pgpro_bindump в файл postgresql.conf:

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"

Инициализируйте каталог резервных копий:

[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

Определите копируемый экземпляр pg-17 в каталоге резервных копий:

[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

Создайте полную резервную копию:

[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

Посмотрим на каталог резервных копий:

[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

Создайте инкрементальную резервную копию в режиме DELTA:

[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

Добавим несколько параметров в файл конфигурации pg_probackup3, чтобы их не надо было каждый раз указывать в командной строке:

[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

Сделайте ещё одну инкрементальную копию в режиме DELTA, опустив некоторые из предыдущих параметров:

[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

Проверим конфигурацию экземпляра:

[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

Посмотрим на каталог резервных копий:

[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

Пред.	Наверх	След.
Настройка политики хранения	Начало	Глава 5. Справка

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Другие примеры

Минимальная настройка