Репликация и разделение нагрузки в PostgreSQL: запись на Primary, чтение с Replica

Apr 13, 2025

Репликация и разделение нагрузки в PostgreSQL: запись на Primary, чтение с Replica В высоконагруженных системах важно эффективно масштабировать базу данных. Одним из подходов является разделение нагрузки: все записывающие операции (INSERT, UPDATE, DELETE, DDL) направляются на Primary, а читающие запросы (SELECT) — на реплику. PostgreSQL предоставляет встроенные возможности для стриминговой репликации, а такие инструменты как PgBouncer или Odyssey позволяют управлять маршрутизацией запросов.

Преимущества подхода Масштабирование чтения — реплики разгружают основной сервер, позволяя обрабатывать больше SELECT-запросов.

Повышение отказоустойчивости — в случае сбоя Primary можно быстро переключиться на реплику.

Изоляция нагрузок — операции SELECT не мешают INSERT/UPDATE и наоборот.

Архитектура Primary: главный сервер PostgreSQL, принимающий записи.

Replica: один или несколько реплицированных серверов, только для чтения.

PgBouncer: пулер соединений, маршрутизирующий запросы в зависимости от типа.

Репликация PostgreSQL: кратко о настройке В postgresql.conf на Primary:

wal_level = replica
max_wal_senders = 10
wal_keep_size = 512MB
hot_standby = on
На Replica:
pg_basebackup -h primary_host -D /var/lib/postgresql/data -U replicator -Fp -Xs -P -R

Это создаст standby.signal и пропишет primary_conninfo — всё, что нужно для стриминговой репликации.

Разделение нагрузки: SELECT → Replica, остальные → Primary Вариант 1: PgBouncer + два пула Можно создать два инстанса PgBouncer:

Один — для записи (коннект к Primary)

Второй — для чтения (коннект к Replica)

Пример pgbouncer.ini (один инстанс):

write_db = host=primary-db port=5432 dbname=mydb
read_db = host=replica-db port=5432 dbname=mydb

[pgbouncer]

listen_port = 6432
listen_addr = 0.0.0.0
pool_mode = transaction

На уровне приложения:

write_db — используется для всех модифицирующих запросов

read_db — только для SELECT

Вариант 2: Одно соединение, автоматическое разделение (Odyssey или Pgpool-II) Odyssey поддерживает маршрутизацию на уровне SQL-команд:

route:
  user: app_user
  rules:
    - query: "SELECT"
      target: replica
    - query: "*"
      target:

Odyssey может определить тип запроса по началу SQL и направить его на нужный хост.

Что важно учесть Репликация асинхронная. Чтения с Replica могут немного отставать от Primary (задержка репликации).

Консистентность: после записи нельзя сразу читать с реплики, если нужна строгая консистентность. Возможные решения:

Чтение с Primary по идентификатору сессии

Использование pg_stat_replication для контроля задержки

Функции типа now(), random() на реплике могут возвращать отличающиеся значения

Как работать с этим на уровне приложения В приложении стоит внедрить уровень маршрутизации:

def db_execute(query):
    if query.strip().upper().startswith("SELECT"):
        use_connection("read_db")   # replica
    else:
        use_connection("write_db")  # primary

Для Django, SQLAlchemy и других ORM существуют плагины и middleware, позволяющие автоматически разделять SELECT и остальные запросы.

Вывод Реализация стриминговой репликации PostgreSQL с маршрутизацией SELECT-запросов на реплику — это один из самых простых и эффективных способов масштабирования чтений без изменения архитектуры. PgBouncer предоставляет лёгкое решение с ручной маршрутизацией, а Odyssey — более автоматизированный подход. При грамотной настройке можно добиться высокой производительности и отказоустойчивости с минимальными усилиями.

Ivan Goncharov