PostgreSQL Streaming Replication с автоматическим failover через Patroni: как мы обеспечили 99.99% uptime для тикетинговой системы

SaaS-платформа «ТикетПро» — система управления заявками для сервисных компаний. 1200 организаций, 45 000 пользователей, 8 000 тикетов в сутки. PostgreSQL 16 на одном сервере — 420 GB данных, 3 200 транзакций/сек в пике. Бизнес-требование — SLA 99.99%, то есть не более 4.3 минуты простоя в месяц.

Реальность была далека от этих цифр:

• Три инцидента за квартал — дважды падал диск (raid controller), один раз OOM killer убил PostgreSQL. Суммарный даунтайм — 4 часа 17 минут.
• Обновления — каждый minor update PostgreSQL требовал 10-15 минут простоя. Обновляли в 3 ночи, но клиенты в разных часовых поясах всё равно жаловались.
• Бэкапы — pg_dump раз в сутки, восстановление занимает 2 часа. RPO = 24 часа — неприемлемо для тикетинговой системы.
• Отчёты — тяжёлые аналитические запросы тормозили OLTP-нагрузку. Менеджеры запускали отчёт за месяц, и все пользователи ощущали замедление.

Команда «ТикетПро» обратилась к нам в itfresh.ru с задачей: сделать PostgreSQL отказоустойчивым без переезда на managed-решения (данные должны оставаться на их серверах по требованию клиентов).

Мы спроектировали кластер из трёх нод PostgreSQL с автоматическим failover:

• pg-node1 (primary) — 16 vCPU, 64 GB RAM, NVMe SSD 1 TB
• pg-node2 (sync standby) — идентичная конфигурация, синхронная реплика
• pg-node3 (async standby) — 8 vCPU, 32 GB RAM, для отчётов и бэкапов
• etcd-кластер — 3 ноды (размещены на тех же серверах + отдельная VM) для DCS (Distributed Configuration Store)
• PgBouncer — на каждой application-ноде, подключение через Patroni REST API

Почему именно Patroni, а не repmgr или stolon? Patroni — стандарт де-факто для PostgreSQL HA. Активное сообщество, используется в Zalando (авторы), GitLab, Citus. Repmgr не умеет автоматический failover без доработок, stolon заброшен.

Топология репликации:

# Синхронная репликация: primary → sync standby (pg-node2)
# Гарантия: транзакция подтверждается только после записи WAL на обе ноды
# RPO = 0 (zero data loss)

# Асинхронная репликация: primary → async standby (pg-node3)
# Задержка: обычно < 100 мс, при пиковой нагрузке до 1-2 сек
# Используется для read-only запросов и бэкапов

pg-node1 (primary)
    ├── pg-node2 (sync standby)  ← failover target
    └── pg-node3 (async standby) ← reporting + backups

Прежде чем настраивать Patroni, важно понимать, что происходит под капотом. Streaming replication в PostgreSQL передаёт WAL (Write-Ahead Log) записи с primary на standby в реальном времени.

Конфигурация primary-сервера:

# postgresql.conf на primary (pg-node1)

# Репликация
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
synchronous_commit = on
synchronous_standby_names = 'FIRST 1 (pg_node2, pg_node3)'

# WAL
wal_keep_size = 4GB
archive_mode = on
archive_command = 'pgbackrest --stanza=ticketpro archive-push %p'

# Мониторинг
track_commit_timestamp = on
log_replication_commands = on

Настройка аутентификации для репликации:

# pg_hba.conf — разрешаем подключение реплик
# TYPE  DATABASE        USER           ADDRESS           METHOD
host    replication     replicator     10.10.1.0/24      scram-sha-256
host    all             all            10.10.1.0/24      scram-sha-256

Создание пользователя для репликации и replication slot:

-- На primary
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'strong_password_here';

-- Создаём physical replication slot (предотвращает удаление WAL до получения репликой)
SELECT pg_create_physical_replication_slot('pg_node2_slot');
SELECT pg_create_physical_replication_slot('pg_node3_slot');

Инициализация standby через pg_basebackup:

# На standby-сервере (pg-node2)
# Останавливаем PostgreSQL если запущен
sudo systemctl stop postgresql

# Очищаем data directory
sudo rm -rf /var/lib/postgresql/16/main/*

# Клонируем primary
sudo -u postgres pg_basebackup \
  --host=10.10.1.1 \
  --port=5432 \
  --username=replicator \
  --pgdata=/var/lib/postgresql/16/main \
  --wal-method=stream \
  --checkpoint=fast \
  --write-recovery-conf \
  --slot=pg_node2_slot \
  --progress \
  --verbose

# pg_basebackup автоматически создаёт standby.signal
# и добавляет в postgresql.auto.conf:
#   primary_conninfo = 'host=10.10.1.1 port=5432 user=replicator ...'
#   primary_slot_name = 'pg_node2_slot'

sudo systemctl start postgresql

После запуска standby начинает получать WAL-записи в реальном времени. Проверяем состояние репликации на primary:

-- Мониторинг репликации на primary
SELECT
    client_addr,
    state,
    sync_state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication;

-- Результат:
-- client_addr  | state     | sync_state | replay_lag_bytes | replay_lag
-- 10.10.1.2    | streaming | sync       | 0                | 00:00:00.001
-- 10.10.1.3    | streaming | async      | 16384            | 00:00:00.089

Ручная репликация работает, но failover при ней — ручной процесс: нужно промотировать standby, переключить приложения, убедиться что старый primary не пишет. Patroni автоматизирует всё это.

Установка и конфигурация Patroni:

# Устанавливаем на каждой ноде
sudo apt install -y patroni

# Или через pip (если нужна свежая версия)
pip3 install patroni[etcd3]

Конфигурация Patroni для pg-node1:

# /etc/patroni/config.yml (pg-node1)
scope: ticketpro-cluster
name: pg-node1
namespace: /postgresql-clusters/

restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.10.1.1:8008
  authentication:
    username: patroni
    password: patroni_api_secret

etcd3:
  hosts:
    - 10.10.1.1:2379
    - 10.10.1.2:2379
    - 10.10.1.4:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576  # 1 MB
    synchronous_mode: true
    synchronous_mode_strict: false
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        wal_level: replica
        hot_standby: 'on'
        max_wal_senders: 10
        max_replication_slots: 10
        wal_keep_size: 4GB
        synchronous_commit: 'on'
        max_connections: 500
        shared_buffers: 16GB
        effective_cache_size: 48GB
        work_mem: 64MB
        maintenance_work_mem: 2GB
        wal_buffers: 64MB
        checkpoint_completion_target: 0.9
        random_page_cost: 1.1
        effective_io_concurrency: 200
        min_wal_size: 2GB
        max_wal_size: 8GB
        archive_mode: 'on'
        archive_command: 'pgbackrest --stanza=ticketpro archive-push %p'

  initdb:
    - encoding: UTF8
    - data-checksums
    - locale: en_US.UTF-8

  pg_hba:
    - host replication replicator 10.10.1.0/24 scram-sha-256
    - host all all 10.10.1.0/24 scram-sha-256
    - host all all 10.20.0.0/16 scram-sha-256

  users:
    admin:
      password: admin_secret
      options:
        - createrole
        - createdb
    replicator:
      password: replicator_secret
      options:
        - replication

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 10.10.1.1:5432
  data_dir: /var/lib/postgresql/16/main
  bin_dir: /usr/lib/postgresql/16/bin
  config_dir: /var/lib/postgresql/16/main
  authentication:
    superuser:
      username: postgres
      password: postgres_secret
    replication:
      username: replicator
      password: replicator_secret
    rewind:
      username: rewind_user
      password: rewind_secret
  callbacks:
    on_start: /etc/patroni/callbacks/on_start.sh
    on_stop: /etc/patroni/callbacks/on_stop.sh
    on_role_change: /etc/patroni/callbacks/on_role_change.sh

watchdog:
  mode: required
  device: /dev/watchdog
  safety_margin: 5

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false

Ключевые параметры:

• synchronous_mode: true — Patroni управляет синхронной репликацией, автоматически выбирая sync standby
• maximum_lag_on_failover — standby с отставанием более 1 MB не будет промотирован
• use_pg_rewind: true — позволяет бывшему primary автоматически вернуться как standby без полного pg_basebackup
• watchdog — hardware watchdog перезагрузит сервер, если Patroni зависнет (защита от split-brain)

Patroni использует etcd как DCS (Distributed Configuration Store) для leader election. Без etcd Patroni не знает, кто primary. Поэтому etcd-кластер должен быть надёжнее, чем сам PostgreSQL.

# Установка etcd на каждой ноде
sudo apt install -y etcd

# /etc/default/etcd на etcd-node1 (10.10.1.1)
ETCD_NAME="etcd-node1"
ETCD_DATA_DIR="/var/lib/etcd/default"
ETCD_LISTEN_PEER_URLS="http://10.10.1.1:2380"
ETCD_LISTEN_CLIENT_URLS="http://10.10.1.1:2379,http://127.0.0.1:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.10.1.1:2380"
ETCD_ADVERTISE_CLIENT_URLS="http://10.10.1.1:2379"
ETCD_INITIAL_CLUSTER="etcd-node1=http://10.10.1.1:2380,etcd-node2=http://10.10.1.2:2380,etcd-node3=http://10.10.1.4:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="ticketpro-etcd-cluster"

# Heartbeat и election timeouts
ETCD_HEARTBEAT_INTERVAL=1000
ETCD_ELECTION_TIMEOUT=5000

Проверяем здоровье etcd-кластера:

# Статус кластера
etcdctl endpoint status --cluster -w table
# +-------------------+------------------+---------+---------+-----------+
# |     ENDPOINT      |        ID        | VERSION | DB SIZE | IS LEADER |
# +-------------------+------------------+---------+---------+-----------+
# | 10.10.1.1:2379    | 8e9e05c52164694d |  3.5.12 |   45 kB |      true |
# | 10.10.1.2:2379    | a]4a77e1c1f2a23  |  3.5.12 |   45 kB |     false |
# | 10.10.1.4:2379    | 72b6840e5cd34a1  |  3.5.12 |   45 kB |     false |
# +-------------------+------------------+---------+---------+-----------+

# Проверяем Patroni leader key
etcdctl get /postgresql-clusters/ticketpro-cluster/leader
# pg-node1

Важно: etcd-кластер из 3 нод выдерживает потерю одной ноды. Если потеряны 2 — кластер теряет кворум, и Patroni не сможет выполнить failover. Для продакшена мы рекомендуем 5 нод etcd, но для «ТикетПро» трёх хватило.

Приложение «ТикетПро» — 12 инстансов backend на Python (Django). Каждый инстанс открывает до 40 соединений к PostgreSQL. 12 × 40 = 480 соединений — на грани max_connections=500. А PostgreSQL плохо работает при большом числе соединений: каждое — отдельный процесс с форком.

PgBouncer решает две задачи: пулинг соединений и маршрутизация на текущий primary.

# /etc/pgbouncer/pgbouncer.ini
[databases]
# Patroni отдаёт информацию о текущем primary через REST API
# Мы используем callback-скрипт для обновления конфига
ticketpro = host=10.10.1.1 port=5432 dbname=ticketpro
ticketpro_ro = host=10.10.1.3 port=5432 dbname=ticketpro

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# Transaction pooling — оптимально для веб-приложений
pool_mode = transaction

# Размеры пулов
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 10
reserve_pool_timeout = 3
max_client_conn = 1000
max_db_connections = 100

# Таймауты
server_idle_timeout = 300
server_lifetime = 3600
client_idle_timeout = 0
query_timeout = 300
client_login_timeout = 15

# Логирование
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60

Для автоматического переключения PgBouncer на новый primary после failover мы написали callback-скрипт, который Patroni вызывает при смене роли:

#!/bin/bash
# /etc/patroni/callbacks/on_role_change.sh
# Patroni вызывает с аргументами: action role name

ACTION=$1
ROLE=$2
CLUSTER=$3

logger -t patroni-callback "Role change: action=$ACTION role=$ROLE cluster=$CLUSTER"

# Отправляем уведомление в Telegram
curl -s -X POST "https://api.telegram.org/bot${TG_BOT_TOKEN}/sendMessage" \
  -d chat_id="${TG_CHAT_ID}" \
  -d text="⚠️ PostgreSQL role change: $(hostname) is now ${ROLE}" \
  -d parse_mode=HTML

# Обновляем конфиг Consul для service discovery
if [ "$ROLE" == "master" ] || [ "$ROLE" == "primary" ]; then
    consul kv put postgresql/primary "$(hostname -I | awk '{print $1}')"
fi

Для Django-приложения переключение на PgBouncer потребовало одну строчку:

# settings.py — переключение с прямого подключения на PgBouncer
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'HOST': '127.0.0.1',      # локальный PgBouncer
        'PORT': '6432',            # порт PgBouncer
        'NAME': 'ticketpro',
        'USER': 'app_user',
        'PASSWORD': os.environ['DB_PASSWORD'],
        'OPTIONS': {
            'options': '-c statement_timeout=30000',
        },
        'CONN_MAX_AGE': 0,  # Важно: при transaction pooling CONN_MAX_AGE=0
    },
    'readonly': {
        'ENGINE': 'django.db.backends.postgresql',
        'HOST': '127.0.0.1',
        'PORT': '6432',
        'NAME': 'ticketpro_ro',   # async standby для отчётов
        'USER': 'app_user',
        'PASSWORD': os.environ['DB_PASSWORD'],
        'CONN_MAX_AGE': 0,
    }
}

Результат: количество соединений к PostgreSQL снизилось с 480 до 50. Время отклика уменьшилось на 15% за счёт переиспользования подготовленных соединений.

Отказоустойчивость, которую не тестировали — это иллюзия. Мы провели серию тестов:

Тест 1: Planned switchover (плановое переключение).

# Текущее состояние кластера
patronictlticketpro-cluster list
# +----------+-----------+---------+----------+----+-----------+
# | Member   | Host      | Role    | State    | TL | Lag in MB |
# +----------+-----------+---------+----------+----+-----------+
# | pg-node1 | 10.10.1.1 | Leader  | running  |  5 |           |
# | pg-node2 | 10.10.1.2 | Sync    | running  |  5 |         0 |
# | pg-node3 | 10.10.1.3 | Replica | running  |  5 |         0 |
# +----------+-----------+---------+----------+----+-----------+

# Выполняем switchover
patronictlticketpro-cluster switchover --master pg-node1 --candidate pg-node2 --force
# Successfully switched over to "pg-node2"

# Проверяем — pg-node2 стал primary
patronictlticketpro-cluster list
# +----------+-----------+---------+----------+----+-----------+
# | Member   | Host      | Role    | State    | TL | Lag in MB |
# +----------+-----------+---------+----------+----+-----------+
# | pg-node1 | 10.10.1.1 | Sync    | running  |  6 |         0 |
# | pg-node2 | 10.10.1.2 | Leader  | running  |  6 |           |
# | pg-node3 | 10.10.1.3 | Replica | running  |  6 |         0 |
# +----------+-----------+---------+----------+----+-----------+

Время switchover: 3-5 секунд. Приложения получили кратковременные ошибки соединения (1-2 запроса), PgBouncer переподключился автоматически.

Тест 2: Unplanned failover (аварийное переключение).

# Имитируем аварию — убиваем PostgreSQL на primary
sudo systemctl stop postgresql  # или kill -9 на процесс

# В логах Patroni на pg-node2:
# 2026-04-01 14:23:15 WARNING: Request failed: GET http://10.10.1.1:8008/patroni
# 2026-04-01 14:23:25 WARNING: Request failed: GET http://10.10.1.1:8008/patroni
# 2026-04-01 14:23:35 INFO: Lock owner: pg-node1; I am pg-node2
# 2026-04-01 14:23:35 INFO: pg-node1 is not reachable, checking leader status
# 2026-04-01 14:23:45 INFO: promoted self to leader by acquiring session lock
# 2026-04-01 14:23:45 INFO: cleared rewind state after becoming the leader

# Время failover: ~30 секунд (3 × loop_wait=10)
# Потеря данных: 0 (синхронная репликация)

Тест 3: Network partition.

# Изолируем primary от etcd (сеть между pg-node1 и etcd отключена)
sudo iptables -A OUTPUT -p tcp --dport 2379 -j DROP

# Patroni на pg-node1 теряет leader lock:
# 2026-04-01 15:10:25 WARNING: Lost leader lock
# 2026-04-01 15:10:25 INFO: Demoting self (graceful)
# PostgreSQL на pg-node1 переходит в read-only

# pg-node2 получает leader lock и промотируется
# Split-brain предотвращён: watchdog на pg-node1 гарантирует demote

Все три теста прошли успешно. Мы задокументировали runbook для каждого сценария и обучили команду «ТикетПро».

Мы настроили мониторинг через postgres_exporter + Prometheus + Grafana:

# Prometheus alerts для PostgreSQL replication
groups:
  - name: postgresql_replication
    rules:
      - alert: PostgreSQLReplicationLag
        expr: pg_replication_lag_seconds > 5
        for: 2m
        labels:
          severity: warning
        annotations:
          summary: "Replication lag on {{ $labels.instance }}: {{ $value }}s"

      - alert: PostgreSQLReplicationSlotInactive
        expr: pg_replication_slots_active == 0
        for: 5m
        labels:
          severity: critical
        annotations:
          summary: "Replication slot inactive on {{ $labels.instance }}"

      - alert: PostgreSQLReplicationSlotsWALRetained
        expr: pg_replication_slots_pg_wal_lsn_diff > 1073741824
        for: 10m
        labels:
          severity: warning
        annotations:
          summary: "Replication slot retaining >1GB WAL on {{ $labels.instance }}"

      - alert: PatroniClusterUnhealthy
        expr: patroni_cluster_running_members < 3
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "Patroni cluster has only {{ $value }} running members"

Полезные запросы для мониторинга:

-- Текущее состояние репликации (на primary)
SELECT
    application_name,
    client_addr,
    state,
    sync_state,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_lag,
    replay_lag AS replay_lag_time
FROM pg_stat_replication;

-- Состояние replication slots
SELECT
    slot_name,
    active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

-- На standby: насколько мы отстаём
SELECT
    now() - pg_last_xact_replay_timestamp() AS replication_delay;

Результаты за 3 месяца эксплуатации:

SLA 99.99% достигнут. Единственный инцидент за квартал — switchover для обновления PostgreSQL 16.2 → 16.3, который занял 4 секунды и не был воспринят пользователями как простой. Если вам нужна отказоустойчивая PostgreSQL — обращайтесь к нам в itfresh.ru.