Репликация PostgreSQL, Hot Standby и PgBouncer

ОБЯЗАТЕЛЬНО

Инженеру

Раздел 8.11, шаг 6 из 12. Дальше — PostgreSQL в Docker.

---

Зачем репликация

Задача	Механизм
Отказоустойчивость	Standby + автоматический failover (Patroni — шаг 9)
Масштабирование чтения	Hot Standby, read replicas
Миграция / upgrade	Logical replication на новую major-версию
Интеграция, CDC	Logical replication, pgoutput

---

Streaming replication (физическая)

Primary пишет WAL; standby получает WAL по сети и проигрывает изменения byte-level. Standby — бинарная копия той же major-версии и layout.

Настройка primary (упрощённо):

wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD '…';

pg_hba.conf — разрешить replication с IP standby.

На standby — pg_basebackup:

pg_basebackup -h primary-host -D /var/lib/postgresql/data -U replicator -Fp -Xs -P -R

Флаг -R создаёт standby.signal и primary_conninfo (PostgreSQL 12+).

Проверка:

-- на primary
SELECT client_addr, state, sent_lsn, replay_lsn
FROM pg_stat_replication;

Replication lag — разница sent_lsn и replay_lsn; мониторьте в Prometheus/Grafana (шаг 11).

---

Hot Standby — чтение с реплики

Standby в режиме hot standby принимает read-only запросы, пока apply WAL продолжается.

hot_standby = on
max_standby_streaming_delay = 30s

Конфликты: д long query на replica может блокировать apply WAL (canceling statement due to conflict with recovery). Решения — routing только "быстрых" запросов на replica, hot_standby_feedback = on (trade-off с bloat на primary).

Приложение:

primary → все INSERT/UPDATE/DELETE
replica → SELECT отчёты, дашборды

ORM и connection string должны явно разделять write/read endpoints.

---

Logical replication

Logical — репликация изменений таблиц (INSERT/UPDATE/DELETE) в формате logical decoding. Позволяет:

• реплицировать подмножество таблиц;
• разные версии Postgres (с ограничениями);
• fan-out в несколько подписчиков.

Primary:

wal_level = logical
max_replication_slots = 4

CREATE PUBLICATION app_pub FOR TABLE orders, order_items;

-- на subscriber
CREATE SUBSCRIPTION app_sub
  CONNECTION 'host=primary dbname=app user=…'
  PUBLICATION app_pub;

Ограничения — нет DDL, sequence реплицируются отдельно, initial sync может быть тяжёлым.

---

Failover

Ручной promote standby:

pg_ctl promote -D /var/lib/postgresql/data

В production — Patroni, repmgr, операторы Kubernetes (шаг 9) с consensus (etcd, DCS) для автоматического выбора нового primary и обновления VIP/DNS.

Split-brain

Два узла, считающие себя primary, приводят к порче данных. Нужен quorum и fencing старого primary.

---

PgBouncer — пулинг соединений

Каждый PostgreSQL backend — отдельный процесс. При 500 микросервисных pod × 10 conn = 5000 backend — крах.

PgBouncer — лёгкий прокси между приложением и Postgres.

Режим	Поведение	Когда
Session pooling	Соединение клиента = один backend на всю сессию	Temp tables, prepared statements, LISTEN
Transaction pooling	Backend возвращается в пул после COMMIT	Default для stateless API
Statement pooling	После каждого statement	Редко, ломает транзакции

Пример pgbouncer.ini:

[databases]
app = host=127.0.0.1 port=5432 dbname=app

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 50
max_client_conn = 1000

Приложение подключается к 6432, Postgres видит ~50 backend.

Ограничения transaction mode:

• нельзя полагаться на session-level SET, temp tables, advisory locks между запросами;
• prepared statements — используйте DEALLOCATE ALL или disable prepare в драйвере.

Managed-альтернативы — RDS Proxy, PgBouncer sidecar в Kubernetes.

---

Сравнение с другими СУБД

Общая теория репликации и кластеров — 3.08 итоги. PostgreSQL streaming ≈ MySQL replication, logical ≈ частично Debezium/CDC.

---

Практика

1. Lab — primary + standby в Docker Compose, pg_basebackup, проверка pg_stat_replication. Для одного инстанса Postgres с volume и healthcheck начните с стека №3.
2. Направьте read-only отчёт на standby, primary под нагрузкой записи.
3. PgBouncer transaction mode + pgbench -c 200 через порт 6432 vs прямой 5432.

---

Связанные материалы

• HA-кластеры Patroni
• Kubernetes
• Docker Compose — готовые стеки
• Бэкапы и PITR
• Администрирование в облаке

---