Репликация PostgreSQL

Как-то одна из моих знакомых админов спрашивала за репликацию PostgreSQL 9.4. Вопрос, в общем-то, несложный, но существующие howto и официальные мануалы показались мне не до конца понятными и прозрачными, и я решил написать свой, по-настоящему универсальный стандарт выложить в блог материал из нашей внутренней вики, благо PostgreSQL у нас используется давно и успешно.
За предоставленный материал спасибо Андрею =)

Что мы рассмотрим?

  • настройка доступов
  • репликация
  • резервные копии и восстановление

А теперь обо всём этом подробнее под катом

Настройка доступов

В нашей схеме работы используется связка PgBouncer + PostgreSQL 9.x.
PgBouncer – это мультиплексор соединений к БД, его использование обусловлено тем, что приложения могут плодить сотни коннектов к базе – а в postgresql схема работы такова, что “один коннект == один процесс”: соответственно, кол-во одновременно работающих коннектов(процессов) не сможет превышать кол-во ядер процессора.

PgBouncer позволяет группировать подключения к бд по связке “username/database”, и использовать тем самым одно подключение к БД для разных коннектов приложений. В некоторых случаех это позволяет неплохо сэкономить ресурсы и не дёргать базу лишний раз.

Для контроля доступа/подключения используется так называемый “hba” файл (сокр. от “host-based authentication”).
Почитать подробнее про него и его формат можно в официальной документации, здесь же стоит отметить лишь то, что у нас здесь их используется два: один – для PgBouncer’a, второй – для самого сервера PostgreSQL.

В файле для pgbouncer’a прописаны доступы для хостов приложений, в файле сервера – доступы для самого pgbouncer, ldap-пользователей и настройки, необходимые для работы репликации.
Так же, возможен дополнительный уровень проверки с использованием pg_ident.conf.

В общих чертах, это всё. В конце будет приведен наглядный пример настроек.

Репликация

Прежде, чем я опишу механизм настройки репликации для postgresql, следует сделать небольшую вводную.

PostgreSQL предоставляет такой механизм, как “потоковая репликация” (streaming replication): это механизм работы, основанный на постоянном копировании WAL-логов с master-сервера и применении их на слейве.
Чтобы это работало, нам необходим сервер, на котором установлена та же версия postgresql, что и на мастере (а в идеале – и такое же железо, чтобы, в случае становления слейва – мастером, он смог “потянуть” рабочую нагрузку), и идентичные настройки в конфигах и hba (кроме listen_addresses, понятное дело).

WAL (сокр. от “write-ahead log”) – это бинарный лог, в который postgres пишет диффы и правила, как их применять: это НЕ лог транзакций, в котором описаны SQL-команды для выполнения, и НЕ классический бинарный лог, в котором светятся только сырые данные – его внутреннее содержание отличается (хотя ближе ко второму варианту). Но основная особенность этого лога в том, что он пишется прежде, чем действия реально применяются в базе (отсюда и его название).
Имея сохраненную копию папки с данными postgresq и WAL-логи с того момента, как была сделана копия данных, можно осуществить восстановление PITR (сокр. от “point-in-time-recovery”) – восстановление на любой, заданный момент времени от создания бэкапа до последней метки времени в WAL-логе (об этом позже).

PostgreSQL позволяет себя настроить таким образом, чтобы стягивать WAL-логи в real-time режиме (за это отвечает специальный демон), причем это будет практически “бесплатно”. Делать это можно как с мастера, так и с других реплик (т.н. “каскадная репликация”), в нашем случае мы можем позволить себе делать это напрямую с мастера (в случае с резервным копированием, это разумно – чтобы не было ошибок, связанных с несовпадающими id транзакций на мастере и слейвах).

Для начала, нам необходимо проверить, что в конфиге postgresql.conf имеются следующие настройки:

wal_level = archive (или выше); необходимо для того, чтобы WAL-логи содержали информацию, необходимую для работы streaming replication
hot_standby = on; позволяет обрабатывать запросы на чтение в процессе восстановления
max_wal_senders = “number_of_replicas + 1”; учитываются в общем кол-ве соединений к серверу max_connections; так же, можно задать max_replication_slots
wal_keep_segments = 1000; количество файлов WAL в директории pg_xlog/, которое будет храниться в папке с данными (по умолчанию, 16mb/файл);
оно должно быть достаточным для того, чтобы обеспечить реплике успевание их стягивать при потоковой репликации;
в противном случае, вы рискуете получить ситуацию, когда мастер удалит файлы журналов, которые ещё не были скопированы на реплику, и репликация сломается;
если у вас активная запись в БД, и за время “лага репликации” вы успеваете выбрать это количество – вам стоит его увеличить.

Если вы хотите дополнительно копировать WAL-логи в другое хранилище – вам понадобятся:

archive_mode = on;
archive_command = ‘test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f’ ; # здесь может быть любая ваша команда или даже скрипт, который обеспечит копирование файлов, например rsync

Далее, создаём в базе пользователя с определенной ролью REPLICATION, и прописываем в hba-файле сервера для него доступ.
Все действия производятся на pgsql-master.server из-под пользователя “postgres”:

[cc lang=”bash”]# md5 пароля формируется следующим образом: “md5” + $( echo -n “” | md5sum – )
# в psql выполняем:
postgres=$ CREATE ROLE repusr;
postgres=$ ALTER ROLE repusr WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN REPLICATION NOBYPASSRLS PASSWORD ‘md540e8137e4ef276ecbcbe3f1b2eb5c343’;

# добавляем в /etc/postgresql/9.6/main/pg_hba.conf следующую строчку (в данном случае, 192.168.0.11/32 – адрес pgsql-slave.server):
# помните: если в вашем hba-файле имеются запретительные правила, то порядок следования строк приобретает значение – анализ идёт сверху вниз, и если вы попадёте под запретительное правило – до вашего разрешения вы уже не доберетесь
host replication repusr 192.168.0.11/32 md5 # pgsql-slave.server

# чтобы применились новые правила из pg_hba.conf, выполним команду (нужны sudo-привилегии):
root@pgsql-master ~$: sudo systemctl reload postgresql
[/cc]

После этого необходимо сходить на pgsql-slave.server, и сделать т.н. “pg_basebackup” – команду, которая автоматизирует за нас следующие действия:

  • создание checkpoint’a (когда сервер флашит все изменения на диск и создает контрольную точку);
  • выполнение “pg_start_backup()” (в этот момент серверу сообщается о начале резервного копирования; создается checkpoint, метки начала, а все изменения в базе на это время будут писаться в WAL);
  • запуск копирования основной директории с данными на резервный сервер с помощью rsync/cp или любыми другими способами;
  • создание файла восстановления recovery.conf ;
  • выполнение “pg_stop_backup()” (сообщает серверу о том, что резервное копирование завершено, и можно продолжить работу в штатном режиме);

Функционал “pg_basebackup” стал доступен начиная с версии 9.2, до этих пор процедура бэкапов и настройки репликации происходили по описанной выше схеме (подробнее здесь).
Итак. На сервере pgsql-slave.server выполняем (подразумевается, что основная директория с данными располагается в /db/9.6/main ):

[cc lang=”bash”]
root@pgsql-slave ~$: sudo systemctl stop postgresql
root@pgsql-slave ~$: sudo rm -rf /db/9.6/main/*
root@pgsql-slave ~$: su – postgres
postgres@pgsql-slave ~$: pg_basebackup -P -R -X stream -c fast -h pgsql-master.server -U repusr -D /db/9.6/main
postgres@pgsql-slave ~$: echo -e “recovery_target_timeline = ‘latest’\n# recovery_min_apply_delay = 10min \ntrigger_file = ‘/tmp/psql.trigger'” >> /db/9.6/main/recovery.conf
root@pgsql-slave ~$: systemctl start postgresql
[/cc]

Параметр “recovery_min_apply_delay = XX” позволяет настроить применение изменений из WAL-логов с задержкой на XX минут (часов, дней). Это быавет полезно, если вы любите дропать у себя таблички =)
Параметр “trigger_file” позволит нашей реплике быстро стать мастером в случае необходимости: для этого надо всего лишь создать этот файл – и postgresql перестанет стримить WAL-логи с мастера, сам станет мастером и откроется на запись.

Проверить, что наша репликация работает, можно следующим образом:

[cc lang=”bash”]
— (on slave – “t”, on master – “f”) SELECT pg_is_in_recovery();
postgres=$ SELECT pg_is_in_recovery();
# pg_is_in_recovery
# ——————-
# t
# (1 row)

— (on slave, т.н. “лаг репликации”) SELECT now()-pg_last_xact_replay_timestamp();
— если вы выставляли параметр “recovery_min_apply_delay”, то оно будет ~равно этому значению
postgres=$ SELECT now()-pg_last_xact_replay_timestamp();
# ?column?
# —————–
# 00:00:00.021257
# (1 row)

— (on master) SELECT * FROM pg_stat_replication;
postgres=$ \x
# Expanded display is on.
postgres=$ SELECT * FROM pg_stat_replication;
# -[ RECORD 1 ]—-+——————————
# pid | 507
# usesysid | 27581
# usename | repusr
# application_name | walreceiver
# client_addr | 192.168.0.11
# client_hostname |
# client_port | 54340
# backend_start | 2017-06-16 20:34:43.342+03
# backend_xmin |
# state | streaming
# sent_location | 3/9FABB8E0
# write_location | 3/9FABB8E0
# flush_location | 3/9FABB8E0
# replay_location | 3/9FABB8A8
# sync_priority | 0
# sync_state | async[/cc]

Итог: у нас есть рабочая реплика. Таким способом можно цеплять любое (разумное) количество реплик к мастеру, не трогая при этом сам мастер – надо лишь следить, чтобы мы не выходили за рамки max_wal_senders.

Если понадобится из неё сделать мастера – достаточно создать trigger_file: в этот момент slave станет new_master’ом и позволит влить в себя пачку данных.
При этом, файл recovery.conf переименуется в recovery.done, а trigger_file будет удален автоматически, как только слейв почувствует себя мастером.

ВАЖНО: стоит следить – чтобы не был запущен прежний “old_master”, в противном случае вы рискуете получить жёсткий рассинхрон.
Процедура возврата old_master -> master будет состоять из шагов:

  • удалить папку с данными на old_master;
  • сделать old_master репликой к new_master (создать пользователя, выполнить pg_basebackup);
  • остановить new_master (бывший слейв), и переименовать обратно recovery.done > recovery.conf;
  • запустить postgresql на old_master;

Если всё прошло без ошибок – таким образом мы получим картину, которая была до создания trigger_file; “удобно”, конечно – но что поделать 😉

Резервное копирование и восстановление

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

  • использовать классические SQL-дампы нужных таблиц или баз данных;
  • использовать периодическое резервное копирование средствами pg_dump/pg_dumpall;
  • использовать резервное копирование на основе базовых копий и архивов WAL.

Первый вариант полезен для проверки консистентности данных (дамп не сделается, если нарушена связность в бд), но нам не подходит.
Второй вариант также не подходит: выполнение pg_dump не блокирует мастера, но создает довольно высокую нагрузку на дисковую подсистему и, главное, не позволяет выполнить Point-In-Time Recovery (PITR).
Поэтому мы будем использовать третий подход.

По своей сущности, он ничем не отличается от процедуры настройки реплики, описанной выше: бэкап с возможностью восстановления на момент во времени == pg_basebackup + пачка WAL-логов, начиная с момента создания бэкапа.
Это можно делать с использованием самописных скриптов, или с использованием утилиты barman (http://www.pgbarman.org/).
Barman – это всего лишь python-обвязка над всеми теми же действиями и функциями postgresql, но оформленными в довольно удобный инструмент.

Итак. Для приготовления нам понадобятся:

  • сервер с достаточным количеством диска, на котором будет работать barman, 1шт;
  • сервер для проверки наших резервных копий, 1шт;
  • новые пользователи в базе с нужными привилегиями, 2шт;
  • новые записи в pg_hba.conf для этих пользователей, по вкусу.

На сервер с barman’ом устанавливаем:

  • postgresql-client
  • postgresql-client-common
  • postgresql-client-9.6 (версия должна совпадать с той, что стоит на нашем сервере БД)
  • barman (версия 2+)

На сервер для проверок резервных копий устанавливаем:

  • postgresql-9.6
  • postgresql-common
  • postgresql-client-common
  • postgresql-client-9.6 (версия должна совпадать с той, что стоит на нашем сервере БД)
  • barman (версия 2+)
  • postgresql-contrib-9.6
  • python-psycopg2

Далее.
На master-базе (которую мы хотим бэкапить) создадим новых пользователей, и добавим их в pg_hba.conf.
Для корректной работы barman нам понадобятся 2 пользователя: “barman” (должен быть суперюзером) и “streaming_barman” (пользователь для стриминга WAL-логов, достаточно привилегий REPLICATION):

[cc lang=”bash”]
# добавим новых пользоавтелей в pg_hba.conf на сервере:
host postgres barman 192.168.0.12/32 md5 # pgsql-barman.server
host replication streaming_barman 192.168.0.12/32 md5 # pgsql-barman.server

# из-под пользователя “postgres”, в psql-консоли:
# как сделать правильный MD5-hash написано выше
postgres=$ BEGIN;
postgres=$ CREATE ROLE barman;
postgres=$ CREATE ROLE streaming_barman;
postgres=$ ALTER ROLE barman WITH SUPERUSER NOCREATEDB NOCREATEROLE LOGIN PASSWORD ‘md567e47ea958fcd33af11cb8fb8d3ecad1’;
postgres=$ ALTER ROLE streaming_barman WITH LOGIN REPLICATION PASSWORD ‘md5ce13788c9e6d33c997a5723aecb742a6’;
postgres=$ COMMIT;
postgres=$
postgres=$ SELECT pg_reload_conf();
[/cc]

Если мы всё сделали правильно, то теперь barman должен уметь ходить в нашу БД. Самое время настроить его для создания бэкапов.
Идём на сервер pgsql-barman.server и настраиваем конфигурационную секцию для нашего сервера. Напомню, что мы хотим схему “pg_basebackup + streaming WAL”.
[cc lang=”bash”]
#
# создаём файл следующего содержания в /etc/barman.d/pgsql-master.conf из-под пользоавтеля “root” (в этой директории уже есть подходящие шаблоны конфигов, в т.ч. для streaming replication)
# обратите внимание: название секции в конфигурационном файле и название самого файла совпадают – это идентификатор, по которому barman будет работать с вашим сервером.
#
#192.168.0.10 – это адрес нашего сервера с PostgreSQL
root@pgsql-barman ~$: cat /etc/barman.d/pgsql-master.conf
; Barman, Backup and Recovery Manager for PostgreSQL
; http://www.pgbarman.org/ – http://www.2ndQuadrant.com/
;
; Template configuration file for a server using
; only streaming replication protocol
;

[pgsql-master]
description = “pgsql-master.server”
conninfo = host=192.168.0.10 user=barman dbname=postgres
streaming_conninfo = host=192.168.0.10 user=streaming_barman
backup_method = postgres
streaming_archiver = on
slot_name = barman
retention_policy = RECOVERY WINDOW OF 7 DAYS
minimum_redundancy = 5
[/cc]

[cc lang=”bash”]
#
# создаем в домашней директории пользователя “barman” файлик .pgpass, кот. будет содержать информацию по подключениям к нашим серверам
# BE AWARE: лучше всего, чтобы этот файл должен быть с правами “только на чтение”, а сам сервер pgsql-barman.server должен быть надёжно защищен
# т.к. в этом файле пароли хранятся в открытом виде, а созданный нами в базе пользователь “barman” имееть привилегии SUPERUSER
# формат этого файла следующий: сервер:порт:база_данных:имя_пользователя:пароль
# допускается использование шаблона ‘*’
#
barman@pgsql-barman ~$: cat .pgpass
192.168.0.10:5432:postgres:barman:PASSWORDbarman
192.168.0.10:5432:*:streaming_barman:PASSWORDstreaming_barman

barman@pgsql-barman ~$: ls -la ~/.pgpass
-r——– 1 barman barman 329 Jun 14 10:27 /var/lib/barman/.pgpass
[/cc]
[cc lang=”bash”]
#
# проверяем, что созданные нами пользователи имеют все необходимые привилегии и доступы (выполняем команды из-под пользователя “barman”):
#
barman@pgsql-barman ~$: psql -c ‘SELECT version()’ -U barman -h 192.168.0.10 postgres
# version
# —————————————————————————————————————–
# PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
# (1 row)

barman@pgsql-barman ~$: psql -U streaming_barman -h 192.168.0.10 -c “IDENTIFY_SYSTEM” replication=1
# systemid | timeline | xlogpos | dbname
# ———————+———-+———–+——–
# 6429565456649844276 | 1 | 8/C9E37B8 |
# (1 row)
[/cc]
[cc lang=”bash”]
#
# создадим слот для репликации, и запустим стриминг WAL-логов
#
barman@pgsql-barman ~$: barman receive-wal –create-slot pgsql-master
barman@pgsql-barman ~$: barman receive-wal pgsql-master
[/cc]
[cc lang=”bash”]
#
# теперь нам осталось проверить статус сервера, и мы сможем запустить бэкап
#
barman@pgsql-barman ~$: barman check pgsql-master
# Server pgsql-master:
# PostgreSQL: OK
# superuser: OK
# PostgreSQL streaming: OK
# wal_level: OK
# replication slot: OK
# directories: OK
# retention policy settings: OK
# backup maximum age: OK (no last_backup_maximum_age provided)
# compression settings: OK
# failed backups: OK (there are 0 failed backups)
# minimum redundancy requirements: FAILED (have 1 backups, expected at least 5)
# 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 backup pgsql-master
# Starting backup using postgres method for server pgsql-master in /var/lib/barman/pgsql-master/base/20170620T153138
# Backup start at xlog location: 3/B3D45640 (0000000100000003000000B3, 00D45640)
# Copying files.
# Copy done.
# Finalising the backup.
# WARNING: pg_basebackup does not copy the PostgreSQL configuration files that reside outside PGDATA. Please manually backup the following files:
# /etc/postgresql/9.6/main/postgresql.conf
# /etc/postgresql/9.6/main/pg_hba.conf
# /etc/postgresql/9.6/main/pg_ident.conf
# Backup size: 187.7 MiB
# Backup end at xlog location: 3/B5000000 (0000000100000003000000B4, 00000000)
# Backup completed

barman@pgsql-barman ~$: barman list-backup pgsql-master
# pgsql-master 20170620T153138 – Tue Jun 20 15:31:38 2017 – Size: 203.7 MiB – WAL Size: 0 B

barman@pgsql-barman ~$: barman show-backup pgsql-master 20170620T153138
# Backup 20170620T153138:
# Server Name : pgsql-master
# Status : DONE
# PostgreSQL Version : 90603
# PGDATA directory : /db/9.6/main
# Base backup information:
# Disk usage : 187.7 MiB (203.7 MiB with WALs)
# Incremental size : 187.7 MiB (-0.00%)
# Timeline : 1
# Begin WAL : 0000000100000003000000B4
# End WAL : 0000000100000003000000B4
# WAL number : 1
# Begin time : 2017-06-20 12:35:02+00:00
# End time : 2017-06-20 15:31:38.593898+03:00
# Begin Offset : 11299552
# End Offset : 0
# Begin XLOG : 3/B4AC6AE0
# End XLOG : 3/B5000000
# WAL information:
# No of files : 0
# Disk usage : 0 B
# Last available : 0000000100000003000000B4
# Catalog information:
# Retention Policy : VALID
# Previous Backup : 20170619T172911
# Next Backup : – (this is the latest base backup)
[/cc]

Вот мы и сделали бэкап, cool. Можем поставить эту команду в crontab, и делать бэкапы по расписанию. Вот толку от них будет немного, если мы не сможем из них восстановиться.

Чтобы этого избежать, мы будем регулярно восстанавливаться из нашего бэкапа на отдельном сервере. Делать это можно тоже по крону.
Только предварительно надо выполнить следующие действия:

  • создать ssh-ключ пользвателю “barman”, чтобы он мог ходить на сервер для восстановления без пароля;
  • добавить публичную часть ключа в .ssh/authorized_keys пользователю “postgres” на сервере для восстановления;
  • добавить пользователю “postgres” привилегии для старта/стопа/проверки статуса postgresql.

[cc lang=”bash”]
#
# на сервере pgsql-barman.server, из-под пользователя “barman”
#
barman@pgsql-barman ~$: ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/barman/.ssh/id_rsa):
Created directory ‘/home/barman/.ssh’.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/barman/.ssh/id_rsa.
Your public key has been saved in /home/barman/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:npqoTQYiASIKYeL1EZeDkBIQy0MG/lTXg3ngsSliAgE barman@pgsql-barman
The key’s randomart image is:
+—[RSA 2048]—-+
|EO.oo+o=* |
|/o..o.==++ |
|+*.+ o +o . |
| .* . . |
|o .. S |
|.. . . . |
| o o |
| + . o |
| ..o o |
+—-[SHA256]—–+

barman@pgsql-barman ~$: cat .ssh/id_rsa.pub
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDEXvb9WLCIS9Lkxb9MENOXl1Uvv+nqpIkvdx38zp7/h68maLCqDFXMfXciR8j1pS7xbtOgvjzxfAxZ4MAiqRfDq/PNwjiI3Bi+ZDrwRkUeQ4GBuwl99uVTnaa6qkTBZa4BKMZNnYhuAB8ZhhJ/r7TqwN+duch7cxt1Vc9O2zwJJE85doS3DZAO+cjoXuONgDLuF9p4w1A4NIhG9KxAgkLCwvjrvowhTB6zv8PJk7UKEUtgVSdpRDmS5g9OhSyzXZ7nrfHM96qheJgB0jSGOTMVo3UTpZ7orqEqR40QcHiMsiTEocRhzEiphPh82/3leOBtMhhvoJ3gT3j+XEalOOz9 barman@pgsql-barman
[/cc]
[cc lang=”bash”]
#
# на сервере pgsql-verify.server, из-под пользователя “postgres”
# так же, сразу добавим необходимые sudo-привилегии этому пользователю в sudoers (из-под пользователя “root”)
#
postgres@pgsql-verify ~$: install -m 700 -d ~/.ssh && echo -n ‘ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDEXvb9WLCIS9Lkxb9MENOXl1Uvv+nqpIkvdx38zp7/h68maLCqDFXMfXciR8j1pS7xbtOgvjzxfAxZ4MAiqRfDq/PNwjiI3Bi+ZDrwRkUeQ4GBuwl99uVTnaa6qkTBZa4BKMZNnYhuAB8ZhhJ/r7TqwN+duch7cxt1Vc9O2zwJJE85doS3DZAO+cjoXuONgDLuF9p4w1A4NIhG9KxAgkLCwvjrvowhTB6zv8PJk7UKEUtgVSdpRDmS5g9OhSyzXZ7nrfHM96qheJgB0jSGOTMVo3UTpZ7orqEqR40QcHiMsiTEocRhzEiphPh82/3leOBtMhhvoJ3gT3j+XEalOOz9 barman@pgsql-barman’ > .ssh/authorized_keys && chmod 600 ~/.ssh/authorized_keys
#
root@pgsql-verify ~$: echo ‘%postgres ALL=(ALL) NOPASSWD: /usr/sbin/service postgresql stop ,/usr/sbin/service postgresql start’ > /etc/sudoers.d/barman
[/cc]
[cc lang=”bash”]
#
# проверяем. пробуем зайти по ssh: barman@pgsql-barman.server -> postgres@pgsql-verify.server (должно пускать без проблем)
#
barman@pgsql-barman ~$: ssh postgres@pgsql-verify.server
[/cc]
ВАЖНО: на вашем сервере для проверок уже должны быть конфигурационные файлы, поскольку barman не копирует их при создании резервной копии базы – он об этом явно пишет в своём выводе:
[cc]
WARNING: pg_basebackup does not copy the PostgreSQL configuration files that reside outside PGDATA. Please manually backup the following files:
/etc/postgresql/9.6/main/postgresql.conf
/etc/postgresql/9.6/main/pg_hba.conf
/etc/postgresql/9.6/main/pg_ident.conf
[/cc]
Лучше всего скопировать их с вашего pgsql-master.server (только не забудьте подправить параметр ‘listen_addresses’ и вписать в него адрес сервера pgsql-verify.server)

Теперь у нас есть всё, что необходимо для восстановления бэкапа на сервере для проверок.
Само восстановление представляет из себя следующую последовательность действий:

  • остановить postgresql на сервере;
  • удалить все данные из PGDATA;
  • запустить barman recover;
  • запустить postgresql на сервере;
  • проверить, что сервер поднялся и готов обрабатывать запросы.

Команда восстановления на заданный Point-In-Time выглядит так:

[cc lang=”bash”]barman recover –target-time “2017-06-19 16:08:00” –remote-ssh-command “ssh postgres@pgsql-verify.server” pgsql-master latest /db/9.6/main[/cc]

параметры означают следующее:
[cc]
–target-time | желаемый Point-In-Time, формат: date +’%Y-%m-%d %H:%M:%S’
–remote-ssh-comand | команда для доступа на сервер, в нашем примере: “ssh postgres@pgsql-verify.server”
| получается из вывода `barman list-backup`, в нашем примере: pgsql-master latest
| путь к директории, в которую следует восстанавливать бэкап, в нашем примере: /db/9.6/
[/cc]

Поскольку мы настроили доступ по ssh для пользователя barman, мы можем всё это сделать удаленно. Выглядит это примерно так:

[cc lang=”bash”]
/usr/bin/ssh -o ConnectTimeout=4 -o PubkeyAuthentication=yes -o PasswordAuthentication=no postgres@pgsql-verify.server ‘/usr/bin/sudo /usr/sbin/service postgresql stop’
/usr/bin/ssh -o ConnectTimeout=4 -o PubkeyAuthentication=yes -o PasswordAuthentication=no postgres@pgsql-verify.server ‘/usr/sbin/service postgresql status’
/usr/bin/ssh -o ConnectTimeout=4 -o PubkeyAuthentication=yes -o PasswordAuthentication=no postgres@pgsql-verify.server ‘/bin/rm -rf /db/9.6/main/*’
/usr/bin/barman recover –target-time “2017-06-19 16:08:00” –remote-ssh-command “ssh postgres@pgsql-verify.server” pgsql-master latest /db/9.6/main
/usr/bin/ssh -o ConnectTimeout=4 -o PubkeyAuthentication=yes -o PasswordAuthentication=no postgres@pgsql-verify.server ‘/usr/bin/sudo /usr/sbin/service postgresql start’
/usr/bin/ssh -o ConnectTimeout=4 -o PubkeyAuthentication=yes -o PasswordAuthentication=no postgres@pgsql-verify.server ‘/usr/sbin/service postgresql status'[/cc]

NB: в случае, когда вы восстанавливаете бэкап с timestamp’ом, сильно отличным от текущего – может потребоваться после завершения восстановления выполнить на сервере в psql из-под пользователя “postgres” команду
[cc lang=”bash”]SELECT pg_xlog_replay_resume();[/cc]

Это всё.
Можно это дело обернуть в скрипт и так же запускать по крону, после того как бэкап снимается.

3 Comments

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

  2. Только благодаря этому автору я смог “побороть” проблемы с настройкой barman. Опыт работы с Postgresql небольшой, поэтому статья, благодаря “внятности” в порядке изложения материала, мне послужила инструкцией. Спасибо!

Leave a Reply