Резервирование PostgreSQL

Статья описывает настройку двух серверов PostgreSQL 15 с репликацией для резервированной установки ТехноДок на операционную систему Debian 12.

Принцип работы

Основной сервер PostgreSQL записывает все изменения данных в журнал предзаписи и передаёт его резервному серверу по сети непрерывным потоком. Резервный сервер применяет полученные изменения у себя и остаётся копией основного.

Запись при этом принимает только основной сервер: резервный открыт лишь на чтение, пока его не повысят до основного. Поэтому при отказе основного сервера кто-то должен выполнить повышение, и от того, кто именно, зависит время простоя.

Способ	Когда подходит
Без менеджера кластера	Простой на время реакции администратора допустим. Дополнительных программ ставить не нужно.
Менеджер кластера Patroni	Простой недопустим: резервный сервер повышается автоматически за несколько секунд. Требует хранилища конфигурации и потому большего числа серверов.

Строка соединения в обоих случаях одна и та же: оба сервера перечисляются в ней через запятую, а параметр Target Session Attributes=primary выбирает тот, что открыт на запись. Пример приведён в шаге 4 статьи Пошаговая настройка резервирования.

Машины и сетевые требования

Настройка выполняется на четырёх машинах. Имена хостов и адреса в примерах условные: замените их на свои везде, где они встречаются в командах и файлах конфигурации.

Имя хоста	Адрес	Роль	Порты
`db.primary-server`	192.168.0.101	Основной сервер базы данных	5432, 8008, 2379, 2380
`db.standby-server`	192.168.0.102	Резервный сервер базы данных	5432, 8008
`app.server1`	192.168.0.11	Сервер приложений	2379, 2380
`app.server2`	192.168.0.12	Сервер приложений	2379, 2380

Откройте на каждой машине перечисленные в таблице порты для входящих подключений по TCP. Порт 5432 нужен всегда: по нему серверы приложений подключаются к базе данных, а серверы базы данных передают друг другу журнал предзаписи. Остальные порты нужны только в схеме с Patroni. Порты 2379 и 2380 использует хранилище конфигурации etcd, узлы которого стоят на app.server1, app.server2 и db.primary-server, а порт 8008 — узлы Patroni.

Потоковая репликация без менеджера кластера

При отказе основного сервера ТехноДок не может записывать данные, пока администратор не повысит резервный сервер до основного вручную.

Шаг 1. Установка PostgreSQL

Установите PostgreSQL на основном и резервном сервере базы данных:

sudo apt update && sudo apt install -y postgresql

Проверка. На каждом сервере базы данных команда sudo -u postgres psql -c "SELECT version()" показывает установленную версию PostgreSQL.

Шаг 2. Настройка основного сервера

Подключитесь к базе данных командой sudo -u postgres psql и выполните SQL-команды:

ALTER SYSTEM SET listen_addresses TO '*';
ALTER SYSTEM SET wal_log_hints TO 'on';
SELECT * FROM pg_create_physical_replication_slot('__slot');

Создайте пользователя с правом репликации:

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'replicator_password';

Задайте пароль учётной записи postgres. Под ней ТехноДок подключается к базе данных, а утилита pg_rewind — к другому серверу при возврате его в схему.

ALTER ROLE postgres WITH PASSWORD 'technodoc_password';

Пароль должен совпадать со значением Password в строке соединения из шага 4 статьи Пошаговая настройка резервирования. На резервный сервер пароль переносит pg_basebackup, поэтому задавать его там не нужно.

Добавьте в файл /etc/postgresql/15/main/pg_hba.conf следующие записи:

host all all 192.168.0.11/32 scram-sha-256
host all all 192.168.0.12/32 scram-sha-256
host all all 192.168.0.101/32 scram-sha-256
host all all 192.168.0.102/32 scram-sha-256
host replication all 192.168.0.101/32 scram-sha-256
host replication all 192.168.0.102/32 scram-sha-256

Первые две записи разрешают подключение серверам приложений, вторые две нужны утилите pg_rewind: она подключается к базе данных postgres на другом сервере. Записи host replication разрешают репликацию.

Перезапустите сервер PostgreSQL.

Проверка. Команда sudo -u postgres psql -c "SHOW wal_log_hints" возвращает on, а команда sudo -u postgres psql -c "SELECT slot_name, active FROM pg_replication_slots" показывает слот __slot. Значение f в столбце active на этом шаге ожидаемо: слот станет активным, когда к нему подключится резервный сервер.

Шаг 3. Настройка резервного сервера

Остановите сервер PostgreSQL при помощи команды sudo systemctl stop postgresql, где postgresql — имя службы.
Очистите каталог данных PG_DATA.
Выполните команду pg_basebackup -D "PG_DATA" -h ip_master -p port_master -X stream -c fast -S __slot -U replicator -W -R, где:
- pg_basebackup — утилита из поставки PostgreSQL, расположенная в каталоге bin сервера базы данных;
- PG_DATA — каталог данных PostgreSQL, по умолчанию /var/lib/postgresql/15/main;
- ip_master — адрес или имя хоста основного сервера базы данных, в примере это db.primary-server;
- port_master — порт основного сервера базы данных, по умолчанию 5432;
- __slot — имя слота репликации, созданного на основном сервере;
- replicator — учётная запись с правом репликации, созданная на предыдущем шаге. Команда запросит её пароль.
Добавьте в файл /etc/postgresql/15/main/pg_hba.conf те же записи, что и на основном сервере:

host all all 192.168.0.11/32 scram-sha-256
host all all 192.168.0.12/32 scram-sha-256
host all all 192.168.0.101/32 scram-sha-256
host all all 192.168.0.102/32 scram-sha-256
host replication all 192.168.0.101/32 scram-sha-256
host replication all 192.168.0.102/32 scram-sha-256

Запустите сервер PostgreSQL при помощи команды sudo systemctl start postgresql.

Примечание

Слот репликации удерживает журналы предзаписи на основном сервере, пока резервный сервер их не заберёт. Если резервный сервер долго не работает, журналы заполнят диск основного сервера; ограничить их объём позволяет параметр max_slot_wal_keep_size.

Предупреждение

Каталог PG_DATA хранит все базы данных сервера PostgreSQL, а не только базу данных ТехноДок. Очистка каталога удалит их все, а pg_basebackup положит на их место копию основного сервера. Потоковая репликация повторяет сервер целиком, поэтому отводите под резервирование ТехноДок отдельный сервер PostgreSQL.

Проверка репликации

Подключитесь к базе данных на db.primary-server командой sudo -u postgres psql и посмотрите состояние передачи журнала:

SELECT client_addr, state, sync_state FROM pg_stat_replication;

Команда показывает строку с адресом db.standby-server, состоянием streaming и значением async — потоковая репликация асинхронная.

Убедитесь на db.standby-server, что сервер работает в режиме восстановления:

SELECT pg_is_in_recovery();

Команда возвращает t.

Создайте на db.primary-server временную базу данных:

CREATE DATABASE replication_check;

Убедитесь на db.standby-server, что база данных появилась:

SELECT datname FROM pg_database WHERE datname = 'replication_check';

Удалите базу данных на db.primary-server:

DROP DATABASE replication_check;

Результат. Созданная база данных появилась на резервном сервере, а после удаления исчезла и там — репликация работает.

Переключение резервного сервера в режим основного

При отказе db.primary-server подключитесь к базе данных на db.standby-server командой sudo -u postgres psql и выполните команды:

CHECKPOINT;
SELECT pg_promote();
SELECT * FROM pg_create_physical_replication_slot('__slot');

После этого db.standby-server принимает запись, и ТехноДок продолжает работу с ним. Машины меняются ролями: основным сервером до конца работы схемы остаётся db.standby-server, а db.primary-server вернётся в схему резервным.

Возврат прежнего сервера в качестве резервного

Когда отказавший db.primary-server снова доступен, необходимо сделать его резервным. Основным сервером при этом работает db.standby-server, повышенный на предыдущем шаге. Все команды ниже выполняются на db.primary-server.

Остановите сервер PostgreSQL командой sudo systemctl stop postgresql. Если он был остановлен аварийно, сначала запустите его и остановите этой же командой, иначе pg_rewind завершится ошибкой.
Создайте файл паролей /var/lib/postgresql/.pgpass в домашнем каталоге пользователя postgres и добавьте в него строку:

db.standby-server:5432:*:postgres:technodoc_password

где technodoc_password — пароль учётной записи postgres, заданный на шаге 2. Ограничьте доступ к файлу командой sudo chown postgres:postgres /var/lib/postgresql/.pgpass && sudo chmod 600 /var/lib/postgresql/.pgpass: файл с более широкими правами PostgreSQL не читает.

Выполните команду sudo -u postgres pg_rewind --target-pgdata=PG_DATA --source-server="user=postgres port=5432 host=db.standby-server dbname=postgres" -R, где PG_DATA — каталог данных PostgreSQL. Утилита сравнивает db.primary-server с db.standby-server и забирает с него изменения, сделанные после отказа. Пароль утилита читает из файла .pgpass, поэтому его нет ни в истории команд, ни в списке процессов, ни в строке соединения, которую параметр -R записывает в postgresql.auto.conf.
Запустите сервер PostgreSQL командой sudo systemctl start postgresql.

После выполнения указанных действий db.primary-server работает резервным сервером и повторяет изменения с db.standby-server. Схема снова резервирована, но роли машин остались обратными их именам.

Примечание

При серьёзных повреждениях данных pg_rewind завершается ошибкой. В этом случае настройте db.primary-server заново, как описано в разделе Настройка резервного сервера, и укажите источником данных db.standby-server.

Автоматическое переключение при помощи Patroni

Patroni — менеджер кластера PostgreSQL: он сам следит за серверами, повышает резервный сервер до основного при отказе основного и возвращает восстановленный сервер в схему как резервный. Простой сокращается до нескольких секунд, а действий администратора при отказе не требуется.

Два сервера не могут договориться между собой, кто из них основной: сервер не отличает отказ соседа от обрыва связи с ним, и оба могут начать принимать запись. Поэтому Patroni хранит состояние кластера во внешнем хранилище конфигурации — обычно это etcd, а решение принимает большинство его узлов. Узлов хранилища нужно нечётное количество, минимум три, но отдельных машин под них не требуется: три узла etcd размещаются на серверах приложений и одном из серверов базы данных.

Строку соединения менять не нужно: оба сервера перечисляются в ней так же, как при ручной настройке, а параметр Target Session Attributes=primary выбирает сервер, открытый на запись, — после переключения драйвер находит новый основной сервер сам.

Полное описание возможностей Patroni есть в его документации.

Предупреждение

Patroni сам создаёт кластер PostgreSQL и управляет его запуском, поэтому настраивайте его на серверах, где базы данных ТехноДок ещё нет. Если вы уже настроили потоковую репликацию по разделу выше, данные с обоих серверов будут потеряны: Patroni создаёт кластер заново.

Шаг 1. Хранилище конфигурации etcd

Шаг выполняется на трёх машинах: app.server1, app.server2 и db.primary-server. Отдельные машины под хранилище не нужны — узлы etcd работают рядом с уже установленными службами и почти не расходуют ресурсы.

Установите etcd на каждой из трёх машин:

sudo apt update && sudo apt install -y etcd-server etcd-client

Задайте настройки узла в файле /etc/default/etcd. На app.server1:

ETCD_NAME=etcd1
ETCD_DATA_DIR=/var/lib/etcd/default
ETCD_LISTEN_PEER_URLS=http://192.168.0.11:2380
ETCD_LISTEN_CLIENT_URLS=http://192.168.0.11:2379,http://127.0.0.1:2379
ETCD_INITIAL_ADVERTISE_PEER_URLS=http://192.168.0.11:2380
ETCD_ADVERTISE_CLIENT_URLS=http://192.168.0.11:2379
ETCD_INITIAL_CLUSTER=etcd1=http://192.168.0.11:2380,etcd2=http://192.168.0.12:2380,etcd3=http://192.168.0.101:2380
ETCD_INITIAL_CLUSTER_TOKEN=technodoc
ETCD_INITIAL_CLUSTER_STATE=new

На app.server2 задайте ETCD_NAME=etcd2 и адрес 192.168.0.12, на db.primary-server — ETCD_NAME=etcd3 и адрес 192.168.0.101. Значение ETCD_INITIAL_CLUSTER одинаково на всех трёх машинах.

Запустите службу и включите её автозапуск:

sudo systemctl enable --now etcd

Проверка. Команда etcdctl --endpoints=http://192.168.0.11:2379,http://192.168.0.12:2379,http://192.168.0.101:2379 endpoint health сообщает is healthy по каждому адресу, а etcdctl --endpoints=http://192.168.0.11:2379 member list показывает все три узла в состоянии started.

Шаг 2. Установка PostgreSQL и Patroni

Шаг выполняется на обеих машинах базы данных.

Установите PostgreSQL и Patroni:

sudo apt update && sudo apt install -y postgresql patroni

Остановите штатный кластер PostgreSQL и уберите его из автозапуска: запуском базы данных дальше управляет Patroni.

sudo systemctl disable --now postgresql

Шаг 3. Настройка кластера

Создайте файл /etc/patroni/technodoc.yml. На db.primary-server:

scope: technodoc
name: db.primary-server

restapi:
  listen: 192.168.0.101:8008
  connect_address: 192.168.0.101:8008

etcd3:
  hosts:
  - 192.168.0.11:2379
  - 192.168.0.12:2379
  - 192.168.0.101:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      parameters:
        wal_log_hints: 'on'

  initdb:
  - encoding: UTF8
  - data-checksums

  pg_hba:
  - host replication replicator 192.168.0.101/32 scram-sha-256
  - host replication replicator 192.168.0.102/32 scram-sha-256
  - host all all 192.168.0.11/32 scram-sha-256
  - host all all 192.168.0.12/32 scram-sha-256
  - host all all 192.168.0.101/32 scram-sha-256
  - host all all 192.168.0.102/32 scram-sha-256
  - local all all peer

postgresql:
  listen: 192.168.0.101:5432
  connect_address: 192.168.0.101:5432
  data_dir: /var/lib/postgresql/15/patroni
  bin_dir: /usr/lib/postgresql/15/bin
  authentication:
    replication:
      username: replicator
      password: replicator_password
    superuser:
      username: postgres
      password: technodoc_password
  parameters:
    unix_socket_directories: /var/run/postgresql

На db.standby-server создайте такой же файл, изменив в нём имя узла и адреса:

name: db.standby-server

restapi:
  listen: 192.168.0.102:8008
  connect_address: 192.168.0.102:8008

postgresql:
  listen: 192.168.0.102:5432
  connect_address: 192.168.0.102:5432

Остальные строки повторите без изменений, включая секции etcd3 и bootstrap. Секция bootstrap применяется один раз, при создании кластера, но задаётся на обеих машинах. Записи из секции pg_hba Patroni добавляет на оба сервера сам, поэтому править файл pg_hba.conf вручную не нужно.

Создайте каталог данных:

sudo mkdir -p /var/lib/postgresql/15/patroni
sudo chown postgres:postgres /var/lib/postgresql/15/patroni
sudo chmod 700 /var/lib/postgresql/15/patroni

Создайте службу systemd в файле /etc/systemd/system/patroni.service. Файл одинаков на обеих машинах:

[Unit]
Description=Patroni
After=network.target

[Service]
Type=simple
User=postgres
Group=postgres
ExecStart=/usr/bin/patroni /etc/patroni/technodoc.yml
Restart=on-failure
RestartSec=5
TimeoutSec=30

[Install]
WantedBy=multi-user.target

Запустите Patroni сначала на db.primary-server и дождитесь, пока он создаст кластер, а затем на db.standby-server:

sudo systemctl daemon-reload
sudo systemctl enable --now patroni

Проверка. Команда sudo patronictl -c /etc/patroni/technodoc.yml list показывает оба узла: один в роли Leader, второй — Replica, оба в состоянии running и с нулевым отставанием.

+ Cluster: technodoc --------------+---------+---------+----+-----------+
| Member            | Host         | Role    | State   | TL | Lag in MB |
+-------------------+--------------+---------+---------+----+-----------+
| db.primary-server | 192.168.0.101| Leader  | running |  1 |           |
| db.standby-server | 192.168.0.102| Replica | running |  1 |         0 |
+-------------------+--------------+---------+---------+----+-----------+

Проверка переключения

Остановите Patroni на узле с ролью Leader командой sudo systemctl stop patroni.
Выполните sudo patronictl -c /etc/patroni/technodoc.yml list на втором узле.

Ожидаемый результат. Роль Leader переходит ко второму узлу за несколько секунд, работа в браузере продолжается: драйвер PostgreSQL находит сервер, открытый на запись, по строке соединения. Прежний узел показан в состоянии stopped.

Запустите Patroni обратно командой sudo systemctl start patroni.

Ожидаемый результат. Узел возвращается в кластер как Replica с нулевым отставанием. Выполнять pg_rewind вручную не нужно — Patroni делает это сам.

Что стоит знать при работе с Patroni

Управляйте базой данных только через Patroni. Команды systemctl start postgresql и pg_ctl в обход менеджера кластера приводят к тому, что запись принимают оба сервера одновременно.
Плановое переключение выполняется командой sudo patronictl -c /etc/patroni/technodoc.yml switchover: она переносит роль основного сервера на выбранный узел, дождавшись, пока тот догонит журнал.
Кластер продолжает работать, пока доступно большинство узлов хранилища — два из трёх. Отказ машины, на которой стоит и etcd, и Patroni, оставляет кворум, а одновременный отказ двух узлов etcd останавливает переключение: оставшийся узел не может подтвердить, что он единственный.

Принцип работы​

Машины и сетевые требования​

Потоковая репликация без менеджера кластера​

Шаг 1. Установка PostgreSQL​

Шаг 2. Настройка основного сервера​

Шаг 3. Настройка резервного сервера​

Проверка репликации​

Переключение резервного сервера в режим основного​

Возврат прежнего сервера в качестве резервного​

Автоматическое переключение при помощи Patroni​

Шаг 1. Хранилище конфигурации etcd​

Шаг 2. Установка PostgreSQL и Patroni​

Шаг 3. Настройка кластера​

Проверка переключения​

Что стоит знать при работе с Patroni​