Dreamcatcher.ru

Для того, чтобы проделанная работа не пропала даром, и чтобы легче было потом вспоминать пройденное, выкладываю краткую инструкцию по настройке кластера PostgreSQL 10 и резервного копирования с помощью BARMAN.

Во первых, неиссякаемый источник знаний:

https://postgrespro.ru/education/courses

https://github.com/lesovsky/uber-scripts/tree/master/postgresql/sql

Окружение представляет собой три сервера pgsql-test-01, pgsql-test-01, pgsql-barman. IP адреса 10.232.84.231, 10.232.84.232, 10.232.84.235 соответственно. Процесс установки пакета postgres не рассматривается.

Вместо стандартного кластера main создаем кластер с нужным нам именем на ноде pgsql-test-01. Порт для подключения 5433 вместо стандартного 5432.

root@pgsql-test-01:~# su - postgres
postgres@pgsql-test-01:~$ mkdir 10/clust_dvdrental
postgres@pgsql-test-01:~$ pg_createcluster -u postgres -p 5433 -d /var/lib/postgresql/10/clust_dvdrental --start-conf=auto -l /var/log/postgresql/postgresql-clust_dvdrental.log 10 clust_dvdrental # 10 - version clust_dvdrental - name

Creating new PostgreSQL cluster 10/clust_dvdrental ...
/usr/lib/postgresql/10/bin/initdb -D /var/lib/postgresql/10/clust_dvdrental --auth-local peer --auth-host md5
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "ru_RU.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "russian".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/10/clust_dvdrental ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Etc/UTC
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/clust_dvdrental -l logfile start

Warning: systemd does not know about the new cluster yet. Operations like "service postgresql start" will not handle it. To fix, run:
  sudo systemctl daemon-reload
Ver Cluster         Port Status Owner    Data directory                         Log file
10  clust_dvdrental 5433 down   postgres /var/lib/postgresql/10/clust_dvdrental /var/log/postgresql/postgresql-clust_dvdrental.log

root@pgsql-test-01:~# systemctl daemon-reload
root@pgsql-test-01:~# systemctl start postgresql@10-clust_dvdrental.service
root@pgsql-test-01:~# systemctl stop postgresql@10-main.service
root@pgsql-test-01:~# systemctl disable postgresql@10-main.service

Наводим красоту, добавив вывод 5 самых больших таблиц по команде \top5, включив постраничный просмотр, отображение времени выполнения операции и отображения роли:

postgres@pgsql-test-01:~$ cat .psqlrc

\set top5 'SELECT tablename, pg_total_relation_size(schemaname||''.''||tablename) AS bytes FROM pg_tables ORDER BY bytes DESC LIMIT 5;'
\setenv PAGER 'less -XS'             
\timing on                           
\set PROMPT1 '%n@%/%R%# '           
\set PROMPT2 '%n@%/%R%# '

Устанавливаем необходимые модули:

root@pgsql-test-01:~# apt install postgresql-10-repack
root@pgsql-test-01:~# su - postgres
postgres@pgsql-test-01:~$ psql -p5433 -c "CREATE EXTENSION pg_repack" -d postgres
postgres@pgsql-test-01:~$ psql -p5433 -c "CREATE EXTENSION pg_repack" -d template1

В базу template1 модули устанавливаем потому, что в дальнейшем именно из этого шаблона будет создана наша база.

Также необходимо отредактировать postgresql.conf, найти и раскомментировать строку, она понадобится для расширенного сбора статистики:

shared_preload_libraries = 'pg_stat_statements'

После этого необходимо рестартовать кластер и установить модуль ‘pg_stat_statements’

postgres@pgsql-test-01:~$systemctl restart postgresql@10-clust_dvdrental.service
postgres@pgsql-test-01:~$psql -p 5433 -c "CREATE EXTENSION pg_stat_statements" -d template1
postgres@pgsql-test-01:~$psql -p 5433 -c "CREATE EXTENSION pg_stat_statements" -d postgres

Если планируется использовать pgBadger, то необходимо внести следующие настройки.

ALTER SYSTEM SET log_line_prefix TO '%t [%p]: [%l-1] '; 
ALTER SYSTEM SET log_duration TO 'on'; 
ALTER SYSTEM SET log_min_duration_statement TO '0'; 
ALTER SYSTEM SET log_checkpoints TO 'on'; 
ALTER SYSTEM SET log_connections TO 'on'; 
ALTER SYSTEM SET log_disconnections TO 'on'; 
ALTER SYSTEM SET log_lock_waits TO 'on'; 
ALTER SYSTEM SET log_temp_files TO '0'; 
ALTER SYSTEM SET log_statement TO 'mod'; 
ALTER SYSTEM SET logging_collector TO 'on'; 
ALTER SYSTEM SET log_directory TO '/var/log/postgresql/';
ALTER SYSTEM SET log_filename TO 'postgresql.log';
ALTER SYSTEM SET log_destination TO 'csvlog,stderr';
SELECT pg_reload_conf();

Некоторые настройки примерятся только после рестарта кластера. Пример использования pgBadger:

root@pgsql-test-01:~# pgbadger -a 1 /var/log/postgresql/postgresql.log

Общие рекомендации по настройке VACUUM:

autovacuum_max_workers — значение выставляется примерно 10-15 % от общего количества процессорных ядер.

autovacuum_naptime — как часто нужно запускать рабочие процессы автовакуума. Лучше чаще, чем реже, обычно задается 1 секунда.

vacuum_cost_limit — следует учесть, что делится на всех воркеров.

autovacuum_vacuum_scale_factor — лучше уменьшать и делать, например, 1-2-5% на больших таблицах (по умолчанию 0.2 = 20%)

Для очень больших таблиц autovacuum_vacuum_scale_factor лучше устанавливать в 0. И использовать только параметр autovacuum_vacuum_threshold (количество строк в штуках)

Универсальное правило: Мы регулируем vacuum_cost_delay и vacuum_cost_limit. Это интервал сна между обработкой и размером пачки. То есть, всегда нужно отталкиваться от этого. 

Пример настройки для SSD:

ALTER SYSTEM SET vacuum_cost_delay TO '0';
ALTER SYSTEM SET vacuum_cost_page_hit TO '0';
ALTER SYSTEM SET vacuum_cost_page_miss TO '5';
ALTER SYSTEM SET vacuum_cost_page_dirty TO '5';
ALTER SYSTEM SET vacuum_cost_limit TO '200';
ALTER SYSTEM SET autovacuum_max_workers TO '10';
ALTER SYSTEM SET autovacuum_naptime TO '1s';
ALTER SYSTEM SET autovacuum_vacuum_threshold TO '50';
ALTER SYSTEM SET autovacuum_analyze_threshold TO '50';
ALTER SYSTEM SET autovacuum_vacuum_scale_factor TO '0.05';
ALTER SYSTEM SET autovacuum_analyze_scale_factor TO '0.05';
ALTER SYSTEM SET autovacuum_vacuum_cost_delay TO '5ms';
ALTER SYSTEM SET autovacuum_vacuum_cost_limit TO '-1';
SELECT pg_reload_conf();

Просмотр текущих настроек:

SELECT * FROM pg_settings WHERE NAME LIKE '%vacuum%';

Нижеуказанный запрос показывает, какой тип вакуума запущен, его длительность выполнения, на какой таблице он работает в данный момент и какой у него прогресс. 

SELECT
        p.pid,
        now() - a.xact_start AS duration,
        coalesce(wait_event_type ||'.'|| wait_event, 'f') AS waiting,
        CASE 
                WHEN a.query ~ '^autovacuum.*to prevent wraparound' THEN 'wraparound' 
                WHEN a.query ~ '^vacuum' THEN 'user'
                ELSE 'regular'
        END AS mode,
        p.datname AS database,
        p.relid::regclass AS table,
        p.phase,
        pg_size_pretty(p.heap_blks_total * current_setting('block_size')::int) AS table_size,
        pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
        pg_size_pretty(p.heap_blks_scanned * current_setting('block_size')::int) AS scanned,
        pg_size_pretty(p.heap_blks_vacuumed * current_setting('block_size')::int) AS vacuumed,
        round(100.0 * p.heap_blks_scanned / p.heap_blks_total, 1) AS scanned_pct,
        round(100.0 * p.heap_blks_vacuumed / p.heap_blks_total, 1) AS vacuumed_pct,
        p.index_vacuum_count,
        round(100.0 * p.num_dead_tuples / p.max_dead_tuples,1) AS dead_pct
FROM pg_stat_progress_vacuum p
RIGHT JOIN pg_stat_activity a ON a.pid = p.pid
WHERE (a.query ~* '^autovacuum:' OR a.query ~* '^vacuum') AND a.pid <> pg_backend_pid()
ORDER BY now() - a.xact_start DESC;

Настраиваем репликацию между нодами pgsql-test-01 (master) и pgsql-test-02 (slave).

На pgsql-test-01 выполняем:

postgres@postgres=# CREATE USER replica REPLICATION LOGIN CONNECTION LIMIT 2 ENCRYPTED PASSWORD '4UtKbw';
postgres@postgres=# ALTER SYSTEM SET listen_addresses TO '*';
postgres@postgres=# ALTER SYSTEM SET wal_keep_segments TO '32';
postgres@postgres=# ALTER SYSTEM SET track_commit_timestamp TO 'on';

postgres@postgres=# SHOW listen_addresses;
postgres@postgres=# SHOW wal_keep_segments;
postgres@postgres=# SELECT pg_reload_conf();


postgres@pgsql-test-01:~$ vi /etc/postgresql/10/clust_dvdrental/pg_hba.conf
host    replication     replica         10.232.84.232/32        md5

На pgsql-test-02 останавливаем и отключаем кластер main, настраиваем и поднимаем новый кластер:

postgres@pgsql-test-02:~$ mkdir 10/clust_dvdrental
postgres@pgsql-test-02:~$ pg_createcluster -u postgres -p 5433 -d /var/lib/postgresql/10/clust_dvdrental --start-conf=auto -l /var/log/postgresql/postgresql-clust_dvdrental.log 10 clust_dvdrental # 10 - version clust_dvdrental - name
root@pgsql-test-02:~# systemctl daemon-reload
root@pgsql-test-02:~# systemctl start postgresql@10-clust_dvdrental.service
root@pgsql-test-02:~# systemctl stop postgresql@10-main.service
root@pgsql-test-02:~# systemctl disable postgresql@10-main.service

root@pgsql-test-02:~# systemctl stop postgresql@10-clust_dvdrental.service
postgres@pgsql-test-02:~$ cd 10/clust_dvdrental/
postgres@pgsql-test-02:~/10/clust_dvdrental$ rm -rf *
postgres@pgsql-test-02:~/10/clust_dvdrental$ pg_basebackup -P -R -X stream -c fast -h 10.232.84.231 -p 5433 -U replica -v -D /var/lib/postgresql/10/clust_dvdrental
Password: 4UtKbw
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
23687/23687 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/20000F8
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed

Наводим красоту сообразно master-ноде. Также, необходимо добавить возможность создания триггер-файла для быстрого ввода slave в работу (switchover):

postgres@pgsql-test-02:~$ vi /var/lib/postgresql/10/clust_dvdrental/recovery.conf

#Добавляем триггер файл, наличие которого, в случае работы сервера в режиме репликации тут же заставит его превратиться в мастер и выйти из режима READONLY.
trigger_file = '/tmp/to_master'

postgres@pgsql-test-02:~$ vi /etc/postgresql/10/clust_dvdrental/pg_hba.conf
host    replication     replica         10.232.84.231/32        md5

root@pgsql-test-02:~# systemctl start postgresql@10-clust_dvdrental.service

В конкретно этом случае создадим пустую базу и наполним ее содержимым имеющегося бэкапа:

postgres@postgres=# CREATE DATABASE dvdrental;
CREATE DATABASE
Time: 236,087 ms

postgres@pgsql-test-01:~$ pg_restore -p5433 --dbname=dvdrental --create --verbose dvdrental.tar

Настройка резервного копирования начинается с процесса взаимного обмена RSA ключами:

postgres@pgsql-test-01:~$ ssh-keygen -t rsa
postgres@pgsql-test-01:~$ cat .ssh/id_rsa.pub
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQCzo000856VIon08rH+AnFOsbJi4oPVckQCrxNs4+1qDkJ03+cTWcI/u/Gux7OXFJi8jAG4MC5kRAZn3lnd2etEP+STRv7+wkYbVoAXkG/obo6OdO5RtblhRT4DJocGEvbZ2TG43tNawjdmNsDDe5q0gjC+SyS+rl2zF6x+m4jj35RRr5mBqSXGeb6KD4x57IboLYrT+wHeagX9w7dFzrDn3qaH7nr3qPY9u0DoKrPdjM7pw/Krpzr3Us8Vk2XyRe8tSbUnuZJP1GFZkzb7+gw9Xd7ujAi29EAsBSR2hVoGaZdY6Jx811i8f/+RHEYDG9HElGcBYP5QuvlcFbmQPCBB postgres@pgsql-test-01

На сервере pgsql-barman ставим сам barman, копируем pub ключи с pgsql-test-01 и генерируем собственные ключи:

root@pgsql-barman:~# apt install barman
root@pgsql-barman:~# su - barman

barman@pgsql-barman:~# mkdir -p ~/.ssh
barman@pgsql-barman:~# chmod 700 ~/.ssh
barman@pgsql-barman:~# touch ~/.ssh/authorized_keys
barman@pgsql-barman:~# chmod 600 ~/.ssh/authorized_keys


barman@pgsql-barman:~$ ssh-keygen -t rsa
barman@pgsql-barman:~$ cat .ssh/id_rsa.pub
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDVcbFw0DCKIbnZlJ6XkooOJSjeLnPUlN9hdXdh5Q2LL1FEF563ieIJbF1XbgxUfhUVoVpbgQcT79LJAxaqQRecBz5DBeSzQmJ9yGP+9DvgiOHi9t89kOkA1ftESyFih5Q6T2W5V0vxjk4hvCWfZm7HQ/3a9LvK1S9CScqgclVOXRYYX0X+rcqggJGrGtWDflW8Jrkb+W8lfdYY3NgYDpRJLzXYz2+d8EIRma9pDcKDOjsIYeirYzMQVDF4b/FUYQFEfyUblWKtUgYvS8RH5+5pLLCKsL8jPOi8sdzHtSQspORyzXqRh9euFGk3+gymFAutvWCL3RV93YrsUPhNtr53 barman@pgsql-barman

Копируем pub ключи с pgsql-barman на pgsql-test-01 и проверяем наличие связи:

postgres@pgsql-test-01:~$ vi .ssh/authorized_keys
postgres@pgsql-test-01:~$ ssh barman@pgsql-barman

Создаем файлы конфигурации barman. Их у нас четыре штуки. Один общий barman.conf, вспомогательный скрипт barman_post.sh и два варианта при резервном копировании с master и slave.

cat /etc/barman.conf

[barman]

barman_user = barman
configuration_files_directory = /etc/barman.d
barman_home = /var/lib/barman
log_file = /var/log/barman/barman.log
log_level = INFO
compression = gzip
post_backup_script = /var/lib/barman/barman_post.sh
bandwidth_limit = 1000000
parallel_jobs = 4
immediate_checkpoint = true
basebackup_retry_times = 3
basebackup_retry_sleep = 60
last_backup_maximum_age = 1 DAYS
minimum_redundancy = 1
retention_policy = RECOVERY WINDOW OF 4 WEEKS

cat /var/lib/barman/barman_post.sh

#!/bin/bash

host=$(hostname -f)
export MAILTO=mixa@www2.dreamcatcher.ru

file_name=$RANDOM
desc="backup status $BARMAN_STATUS cluster $BARMAN_SERVER on the host $host"

echo "Host                          : $host" > $file_name
echo "Name of the server            : $BARMAN_SERVER" >> $file_name
echo "ID of the backup              : $BARMAN_BACKUP_ID" >> $file_name
echo "Backup destination directory  : $BARMAN_BACKUP_DIR ">> $file_name
echo "ID of the previous backup     : $BARMAN_PREVIOUS_ID" >> $file_name
echo " " >> $file_name
echo "Status of the backup          : $BARMAN_STATUS" >> $file_name
echo "Error message                 : $BARMAN_ERROR " >> $file_name
echo "Version of Barman             : $BARMAN_VERSION" >> $file_name

echo " " >> $file_name
echo "--- description ---" >> $file_name
echo " " >> $file_name

barman show-backup $BARMAN_SERVER $BARMAN_BACKUP_ID >> $file_name

cat $file_name | mailx -s "$desc" $MAILTO

rm $file_name

cat /etc/barman.d/clust_dvdrental_stb.conf

[clust_dvdrental_stb]

description =  "DVDRENTAL from pgsql-test-02 (Streaming-Only)"
conninfo = port=5433 host=pgsql-test-02 user=postgres dbname=dvdrental
streaming_conninfo = host=pgsql-test-02 user=postgres
backup_method = postgres
streaming_archiver = on
slot_name = barman
streaming_archiver = on
streaming_archiver_name = barman_receive_wal
streaming_archiver_batch_size = 50
path_prefix = "/usr/bin"

cat /etc/barman.d/clust_dvdrental.conf

[clust_dvdrental]

description =  "DVDRENTAL from pgsql-test-01 (via SSH)"
ssh_command = ssh postgres@pgsql-test-01
conninfo = port=5433 host=pgsql-test-01 user=postgres dbname=dvdrental
backup_method = rsync
reuse_backup = link
backup_options = concurrent_backup
parallel_jobs = 8
archiver = on
path_prefix = "/usr/bin"

Первый вариант, копирование с master.

Смотрим пути, куда будет складываться архив:

barman@pgsql-barman:~$ barman show-server clust_dvdrental | grep incoming_wals_directory
        incoming_wals_directory: /var/lib/barman/clust_dvdrental/incoming

Вносим изменения в конфигурацию кластера на pgsql-test-01:

postgres@pgsql-test-01:~$ vi /etc/postgresql/10/clust_dvdrental/postgresql.conf

wal_level = replica
archive_mode = on
archive_command = 'rsync -a %p barman@pgsql-barman:/var/lib/barman/clust_dvdrental/incoming/%f'

root@pgsql-test-01:~# systemctl restart postgresql@10-clust_dvdrental.service

Проверяем подключение:

barman@pgsql-barman:~$ barman check clust_dvdrental
Server clust_dvdrental:
        PostgreSQL: OK
        is_superuser: OK
        wal_level: OK
        directories: OK
        retention policy settings: OK
        backup maximum age: FAILED (interval provided: 1 day, latest backup age: No available backups)
        compression settings: OK
        failed backups: OK (there are 0 failed backups)
        minimum redundancy requirements: FAILED (have 0 backups, expected at least 1)
        ssh: OK (PostgreSQL server)
        archive_mode: OK
        archive_command: OK
        continuous archiving: OK
        archiver errors: OK

Может появиться ошибка вида «WAL archive: FAILED (please make sure WAL shipping is setup)«. В моем случае это означало что база пуста, операций никаких не делалось, поэтому нет WAL-файлов. Стоило сымитировать нагрузку, ошибка пропала.

Делаем архив:

barman@pgsql-barman:~$ barman backup clust_dvdrental
Starting backup using rsync-concurrent method for server clust_dvdrental in /var/lib/barman/clust_dvdrental/base/20200520T133413
Backup start at LSN: 0/5000028 (000000010000000000000005, 00000028)
This is the first backup for server clust_dvdrental
WAL segments preceding the current backup have been found:
        000000010000000000000003 from server clust_dvdrental has been removed
Starting backup copy via rsync/SSH for 20200520T133413 (8 jobs)
Copy done (time: 3 seconds)
This is the first backup for server clust_dvdrental
Asking PostgreSQL server to finalize the backup.
Backup size: 38.0 MiB. Actual size on disk: 38.0 MiB (-0.00% deduplication ratio).
Backup end at LSN: 0/5000130 (000000010000000000000005, 00000130)
Backup completed (start time: 2020-05-20 13:34:13.476136, elapsed time: 5 seconds)
Processing xlog segments from file archival for clust_dvdrental
        000000010000000000000004
        000000010000000000000005
        000000010000000000000005.00000028.backup

Просмотр списка архивов:

barman@pgsql-barman:~$ barman list-backup clust_dvdrental
clust_dvdrental 20200520T133413 - Wed May 20 13:34:22 2020 - Size: 38.0 MiB - WAL Size: 0 B

Смотрим конкретный архив:

barman@pgsql-barman:~$ barman show-backup clust_dvdrental 20200521T054128
Backup 20200521T054128:
  Server Name            : clust_dvdrental
  Status                 : DONE
  PostgreSQL Version     : 100012
  PGDATA directory       : /var/lib/postgresql/10/clust_dvdrental

  Base backup information:
    Disk usage           : 38.0 MiB (38.1 MiB with WALs)
    Incremental size     : 38.3 KiB (-99.90%)
    Timeline             : 1
    Begin WAL            : 000000010000000000000009
    End WAL              : 000000010000000000000009
    WAL number           : 1
    WAL compression ratio: 99.84%
    Begin time           : 2020-05-21 05:41:32.321883+00:00
    End time             : 2020-05-21 05:41:37.935335+00:00
    Copy time            : 3 seconds + 2 seconds startup
    Estimated throughput : 12.7 KiB/s (8 jobs)
    Begin Offset         : 40
    End Offset           : 304
    Begin LSN           : 0/9000028
    End LSN             : 0/9000130

  WAL information:
    No of files          : 0
    Disk usage           : 0 B
    Last available       : 000000010000000000000009

  Catalog information:
    Retention Policy     : VALID
    Previous Backup      : 20200520T133601
    Next Backup          : - (this is the latest base backup)

Задаем создание архива по расписанию:

barman@pgsql-barman:~$ crontab -e

30 23 * * * /usr/bin/barman backup clust_dvdrental
* * * * * /usr/bin/barman cron

Имитация катастрофы. Берем и удаляем какую-нибудь таблицу. Обращаю внимание, что после восстановления на master, slave придется пересоздавать (я не знаю, как возобновить синхронизацию):

postgres@pgsql-test-01:~$ psql -p 5433 -d dvdrental
postgres@dvdrental=# \dt
             List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | actor         | table | postgres
 public | address       | table | postgres
 public | category      | table | postgres
 public | city          | table | postgres
 public | country       | table | postgres
 public | customer      | table | postgres
 public | film          | table | postgres
 public | film_actor    | table | postgres
 public | film_category | table | postgres
 public | inventory     | table | postgres
 public | language      | table | postgres
 public | payment       | table | postgres
 public | rental        | table | postgres
 public | staff         | table | postgres
 public | store         | table | postgres
 public | test_table    | table | postgres
(16 rows)

postgres@dvdrental=# drop table test_table;
DROP TABLE
Time: 6,391 ms

Восстановление. Обращайте внимание на хосты, на которых проводятся операции:

postgres@pgsql-test-01:~$ systemctl stop postgresql@10-clust_dvdrental.service

barman@pgsql-barman:~$ barman show-backup clust_dvdrental latest
Backup 20200521T054128:
  Server Name            : clust_dvdrental
  Status                 : DONE
  PostgreSQL Version     : 100012
  PGDATA directory       : /var/lib/postgresql/10/clust_dvdrental

  Base backup information:
    Disk usage           : 38.0 MiB (38.1 MiB with WALs)
    Incremental size     : 38.3 KiB (-99.90%)
    Timeline             : 1
    Begin WAL            : 000000010000000000000009
    End WAL              : 000000010000000000000009
    WAL number           : 1
    WAL compression ratio: 99.84%
    Begin time           : 2020-05-21 05:41:32.321883+00:00
    End time             : 2020-05-21 05:41:37.935335+00:00
    Copy time            : 3 seconds + 2 seconds startup
    Estimated throughput : 12.7 KiB/s (8 jobs)
    Begin Offset         : 40
    End Offset           : 304
    Begin LSN           : 0/9000028
    End LSN             : 0/9000130

  WAL information:
    No of files          : 1
    Disk usage           : 34.3 KiB
    WAL rate             : 2.36/hour
    Compression ratio    : 99.79%
    Last available       : 00000001000000000000000A

  Catalog information:
    Retention Policy     : VALID
    Previous Backup      : 20200520T133601
    Next Backup          : - (this is the latest base backup)

barman@pgsql-barman:~$ barman recover --target-time "2020-05-21 05:41:32.321883+00:00"  --remote-ssh-command "ssh postgres@pgsql-test-01"   clust_dvdrental   20200521T054128   /var/lib/postgresql/10/clust_dvdrental

Starting remote restore for server clust_dvdrental using backup 20200521T054128
Destination directory: /var/lib/postgresql/10/clust_dvdrental
Doing PITR. Recovery target time: '2020-05-21 05:41:32.321883+00:00'
Copying the base backup.
Copying required WAL segments.
Generating recovery.conf
Identify dangerous settings in destination directory.

IMPORTANT
These settings have been modified to prevent data losses

postgresql.conf line 228: archive_command = false

WARNING
You are required to review the following options as potentially dangerous

postgresql.conf line 40: data_directory = '/var/lib/postgresql/10/clust_dvdrental'              # use data in another directory
postgresql.conf line 42: hba_file = '/etc/postgresql/10/clust_dvdrental/pg_hba.conf'    # host-based authentication file
postgresql.conf line 44: ident_file = '/etc/postgresql/10/clust_dvdrental/pg_ident.conf'        # ident configuration file
postgresql.conf line 48: external_pid_file = '/var/run/postgresql/10-clust_dvdrental.pid'                       # write an extra PID file
postgresql.conf line 83: ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
postgresql.conf line 84: ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
postgresql.conf line 656: include_dir = 'conf.d'                        # include files ending in '.conf' from

Your PostgreSQL server has been successfully prepared for recovery!

postgres@pgsql-test-01:~$ systemctl start postgresql@10-clust_dvdrental.service

Переотливаем slave:

root@pgsql-test-02:~# systemctl stop postgresql@10-clust_dvdrental.service
postgres@pgsql-test-02:~$ cd 10/clust_dvdrental/
postgres@pgsql-test-02:~/10/clust_dvdrental$ rm -rf *
postgres@pgsql-test-02:~/10/clust_dvdrental$ pg_basebackup -P -R -X stream -c fast -h 10.232.84.231 -p 5433 -U replica -v -D /var/lib/postgresql/10/clust_dvdrental
Password: 4UtKbw

postgres@pgsql-test-02:~$ systemctl start postgresql@10-clust_dvdrental.service

Второй вариант, копирование со slave:

Настраиваем pgsql-test-02:

postgres@pgsql-test-02:~$ vi /etc/postgresql/10/clust_dvdrental/postgresql.conf

wal_level=replica
max_wal_senders = 10
max_replication_slots = 10
synchronous_standby_names = 'barman_receive_wal'
archive_mode=on
archive_command = 'rsync -a %p barman@pgserver:/var/lib/barman/clust_dvdrental_stb/incoming/%f'
archive_timeout = 3600

postgres@pgsql-test-02:~$ vi /etc/postgresql/10/clust_dvdrental/pg_hba.conf

# Barman
host    all             postgres        10.232.84.235/32        trust
host    replication     postgres        10.232.84.235/32        trust

postgres@pgsql-test-02:~$ systemctl restart postgresql@10-clust_dvdrental.service

Создаем слот:

barman@pgsql-barman:~$ barman receive-wal --create-slot clust_dvdrental_stb
Creating physical replication slot 'barman' on server 'clust_dvdrental_stb'
Replication slot 'barman' created

Проверяем, видим ошибки:

barman@pgsql-barman:~$ barman check clust_dvdrental_stb
Server clust_dvdrental_stb:
        WAL archive: FAILED (please make sure WAL shipping is setup)
        PostgreSQL: OK
        is_superuser: OK
        PostgreSQL streaming: OK
        wal_level: OK
        replication slot: FAILED (slot 'barman' not initialised: is 'receive-wal' running?)
        directories: OK
        retention policy settings: OK
        backup maximum age: FAILED (interval provided: 1 day, latest backup age: No available backups)
        compression settings: OK
        failed backups: OK (there are 0 failed backups)
        minimum redundancy requirements: FAILED (have 0 backups, expected at least 1)
        pg_basebackup: OK
        pg_basebackup compatible: OK
        pg_basebackup supports tablespaces mapping: OK
        pg_receivexlog: OK
        pg_receivexlog compatible: OK
        receive-wal running: FAILED (See the Barman log file for more details)
        archiver errors: OK

Когда вы запускаете barman receive-wal, он может зависнуть. Чтобы правильно выполнить запуск команды в первый раз, необходимо выполнить barman cron, после чего проверяем снова:

barman@pgsql-barman:~$ barman cron
Starting WAL archiving for server clust_dvdrental_stb
barman@pgsql-barman:~$ barman check clust_dvdrental_stb
Server clust_dvdrental_stb:
        PostgreSQL: OK
        is_superuser: OK
        PostgreSQL streaming: OK
        wal_level: OK
        replication slot: OK
        directories: OK
        retention policy settings: OK
        backup maximum age: FAILED (interval provided: 1 day, latest backup age: No available backups)
        compression settings: OK
        failed backups: OK (there are 0 failed backups)
        minimum redundancy requirements: FAILED (have 0 backups, expected at least 1)
        pg_basebackup: OK
        pg_basebackup compatible: OK
        pg_basebackup supports tablespaces mapping: OK
        pg_receivexlog: OK
        pg_receivexlog compatible: OK
        receive-wal running: OK
        archiver errors: OK

Смотрим статус потоковой репликации:

barman@pgsql-barman:~$ barman replication-status clust_dvdrental_stb
Status of streaming clients for server 'clust_dvdrental_stb':
  Current LSN on master: 0/E012800
  Number of streaming clients: 1

  1. Async WAL streamer
     Application name: barman_receive_wal
     Sync stage      : 1/3 1-safe
     Communication   : TCP/IP
     IP Address      : 10.232.84.235 / Port: 45180 / Host: -
     User name       : postgres
     Current state   : streaming (async)
     Replication slot: barman
     WAL sender PID  : 23790
     Started at      : 2020-05-22 08:21:06.675200+00:00
     Sent LSN   : 0/E012800 (diff: 74.0 KiB)
     Write LSN  : 0/E012800 (diff: 74.0 KiB)
     Flush LSN  : 0/E012800 (diff: 74.0 KiB)

Теперь делаем резервную копию:

barman@pgsql-barman:~$ barman backup clust_dvdrental_stb
Starting backup using postgres method for server clust_dvdrental_stb in /var/lib/barman/clust_dvdrental_stb/base/20200522T084935
Backup start at LSN: 0/E110C20
Starting backup copy via pg_basebackup for 20200522T084935
WARNING: pg_basebackup does not copy the PostgreSQL configuration files that reside outside PGDATA. Please manually backup the following files:
        /etc/postgresql/10/clust_dvdrental/postgresql.conf
        /etc/postgresql/10/clust_dvdrental/pg_hba.conf
        /etc/postgresql/10/clust_dvdrental/pg_ident.conf

Copy done (time: less than one second)
Finalising the backup.
This is the first backup for server clust_dvdrental_stb
Backup size: 54.1 MiB
Backup end at LSN: 0/E110C20 (00000002000000000000000E, 00110C20)
Backup completed (start time: 2020-05-22 08:49:35.735188, elapsed time: less than one second)

Проверяем наличие бэкапа:

barman@pgsql-barman:~$ barman list-backup clust_dvdrental_stb
clust_dvdrental_stb 20200522T084935 - Fri May 22 08:49:40 2020 - Size: 54.1 MiB - WAL Size: 0 B