Настройка репликации базы данных Master-Slave для веб-приложения

Настройка репликации базы данных Master-Slave для веб-приложения

Репликация решает две задачи: отказоустойчивость (failover) и масштабирование чтения (read scaling). Рассматриваем PostgreSQL streaming replication — стандартный и надёжный подход.

Архитектура

App servers
    │
    ├── writes ──▶ Primary (master)
    │                    │
    │              WAL stream
    │                    │
    └── reads  ──▶ Replica 1 (hot standby)
                   Replica 2 (hot standby)

Hot standby — реплика принимает SELECT-запросы. Cold standby — только для failover, без чтения.

Настройка Primary

postgresql.conf на основном сервере:

# Replication
wal_level = replica          # минимум для streaming replication
max_wal_senders = 5          # максимум одновременных репликационных соединений
wal_keep_size = 1GB          # сколько WAL держать на диске для отстающей реплики
max_replication_slots = 5    # физические слоты репликации

# Performance
synchronous_commit = on      # on = синхронная запись WAL, off = быстрее, риск потери 1 транзакции

pg_hba.conf на primary — разрешаем подключение репликационного пользователя:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     replicator      10.0.1.0/24             scram-sha-256

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

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'strong_password';

Настройка Replica (PostgreSQL 12+)

На реплике запускаем pg_basebackup для начальной синхронизации:

# На сервере реплики, PostgreSQL остановлен
pg_basebackup \
    -h 10.0.1.10 \         # IP primary
    -U replicator \
    -D /var/lib/postgresql/14/main \
    -P \                   # прогресс
    -Xs \                  # включить WAL stream
    -R                     # создать standby.signal и записать primary_conninfo

Флаг -R создаёт standby.signal и добавляет в postgresql.auto.conf:

primary_conninfo = 'host=10.0.1.10 port=5432 user=replicator password=strong_password application_name=replica1'
primary_slot_name = 'replica1_slot'

postgresql.conf на реплике:

hot_standby = on              # принимать SELECT на реплике
hot_standby_feedback = on     # реплика сообщает primary о своих транзакциях (предотвращает vacuum race)
max_standby_streaming_delay = 30s

Репликационные слоты

Слот гарантирует, что primary не удалит WAL-сегменты, пока реплика их не получила:

-- На primary
SELECT pg_create_physical_replication_slot('replica1_slot');

-- Проверка статуса
SELECT slot_name, active, restart_lsn, confirmed_flush_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag_bytes
FROM pg_replication_slots;

Опасность слотов: если реплика надолго отключится, primary будет накапливать WAL-файлы. Контролируем:

# postgresql.conf на primary
max_slot_wal_keep_size = 10GB   # PostgreSQL 13+: максимум WAL для слота

Мониторинг репликационного лага

-- На primary: статус всех реплик
SELECT
    application_name,
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replay_lag_bytes,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication;

-- На реплике: текущий лаг
SELECT
    now() - pg_last_xact_replay_timestamp() AS replication_lag_seconds,
    pg_is_in_recovery() AS is_replica;

Настройка приложения

На уровне приложения роутим запросы: write → primary, read → replica.

Для Node.js + Sequelize:

const sequelize = new Sequelize({
  dialect: 'postgres',
  replication: {
    read: [
      { host: '10.0.1.11', username: 'app', password: process.env.DB_PASS, database: 'mydb' },
      { host: '10.0.1.12', username: 'app', password: process.env.DB_PASS, database: 'mydb' },
    ],
    write: {
      host: '10.0.1.10', username: 'app', password: process.env.DB_PASS, database: 'mydb',
    },
  },
  pool: { max: 10, idle: 10000 },
});

Для Python (SQLAlchemy) — через custom routing или библиотеку sqlalchemy-rwconn.

PgBouncer как прокси

Рекомендуется ставить PgBouncer перед каждым сервером БД:

App → PgBouncer (primary:6432) → PostgreSQL Primary :5432
App → PgBouncer (replica:6432) → PostgreSQL Replica :5432

PgBouncer уменьшает количество реальных соединений к PostgreSQL — важно при сотнях воркеров приложения.

Автоматический failover

Patroni — стандарт для автоматического failover PostgreSQL в production:

# /etc/patroni/config.yml (фрагмент)
scope: postgres-cluster
name: node1

restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.0.1.10:8008

etcd:
  hosts: 10.0.1.20:2379,10.0.1.21:2379,10.0.1.22:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576   # 1MB
  pg_hba:
    - host replication replicator 10.0.1.0/24 scram-sha-256

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 10.0.1.10:5432
  data_dir: /var/lib/postgresql/14/main
  parameters:
    max_connections: 200
    wal_level: replica
    max_wal_senders: 5

Patroni использует etcd (или Consul, ZooKeeper) как distributed lock. При отказе primary — автоматически промоутирует реплику с наименьшим лагом.

Сроки

Ручная настройка streaming replication (primary + 1–2 реплики, без failover): 1 день. Установка и настройка Patroni с etcd и HAProxy для автоматического failover: 2–3 дня.