PostgreSQL создаёт отдельный процесс на каждое подключение, а приложение часто открывает сотни коротких сессий. PgBouncer держит небольшой пул реальных соединений и мультиплексирует между ними клиентов, убирая накладные расходы на логины. Разберём установку, режимы пула, SCRAM, мониторинг и ошибки.

Зачем пул соединений

Один бэкенд-процесс PostgreSQL занимает несколько мегабайт. При 500 соединениях сервер может уйти в своп. PgBouncer работает в одном процессе, тратит килобайты на клиента и отдаёт базе столько соединений, сколько нужно.

  • снижение нагрузки на PostgreSQL и предсказуемая память;
  • быстрое переиспользование уже установленных соединений;
  • защита от штормов подключений после деплоя;
  • единая точка для мониторинга и планового переключения.

Пул не ускоряет запросы и не заменяет индексы: он убирает расходы на соединения и не даёт уронить базу.

Установка и базовая конфигурация

sudo apt update
sudo apt install pgbouncer
pgbouncer -V
sudo systemctl status pgbouncer

Основной файл — /etc/pgbouncer/pgbouncer.ini, список пользователей — /etc/pgbouncer/userlist.txt. В Debian служба работает от пользователя postgres, поэтому права на файлы важны. Минимальный рабочий конфиг выглядит так:

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

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600
query_wait_timeout = 30
admin_users = admin
stats_users = monitor

default_pool_size задаётся на пару «пользователь + база», а не на весь PgBouncer. Если у вас пять баз и три роли, суммарное число серверных соединений может оказаться в разы больше 25. Считайте общий бюджет заранее.

Режимы пула

session — самое безопасное поведение: серверное соединение закреплено за клиентом до отключения. Подходит для приложений с сессионным состоянием, но даёт мало выигрыша.

transaction — соединение возвращается в пул после COMMIT или ROLLBACK. Стандартный выбор для веб-приложений и ORM. Клиент не должен рассчитывать, что следующая транзакция попадёт в тот же бэкенд.

statement — соединение освобождается после каждого запроса, многооператорные транзакции запрещены. Режим нишевый: простые прокси-сценарии.

SHOW POOLS;
SHOW DATABASES;
SHOW CONFIG;
SHOW VERSION;

В режиме transaction ломаются SET без LOCAL, LISTEN, сессионные advisory-локи, курсоры WITH HOLD и временные таблицы, живущие дольше транзакции. Это следствие мультиплексирования: следующая транзакция может уйти на другое соединение.

Аутентификация SCRAM

С PostgreSQL 14 пароли по умолчанию хранятся как SCRAM-верификаторы. PgBouncer умеет проверять клиентов по такому верификатору, но есть нюанс: верификатор нельзя использовать для входа на сервер, если он не совпадает с серверным, клиентская аутентификация идёт не по SCRAM или в определении базы задан принудительный пользователь.

sudo -u postgres psql -Atc "SELECT usename, passwd FROM pg_shadow WHERE usename='app'"
sudo -u postgres psql -Atc "SELECT format('\"%s\" \"%s\"', usename, passwd) FROM pg_shadow WHERE usename IN ('app','admin')"

Строки из второй команды добавьте в userlist.txt. Файл должен быть доступен только владельцу: chmod 600 и chown postgres:postgres. Если схема аутентификации сложнее, используйте auth_query с SECURITY DEFINER-функцией, чтобы PgBouncer не ходил в pg_authid под суперпользователем.

CREATE FUNCTION public.pgbouncer_get_auth(p_usename text)
RETURNS TABLE(usename name, passwd text)
LANGUAGE sql SECURITY DEFINER AS $$
    SELECT p.usename, p.passwd FROM pg_shadow p WHERE p.usename = p_usename;
$$;

Создайте роль pgbouncer_auth с LOGIN и паролем и добавьте её в userlist.txt. В конфиге укажите auth_user = pgbouncer_auth и auth_query = SELECT * FROM public.pgbouncer_get_auth($1). Функция должна существовать в каждой базе, к которой подключаются клиенты, либо используйте auth_dbname.

Мониторинг через консоль

Административная консоль — обычное подключение к базе pgbouncer. Пароль берётся из userlist.txt, доступ определяется admin_users.

psql -h 127.0.0.1 -p 6432 -U admin pgbouncer -c "SHOW POOLS;"
psql -h 127.0.0.1 -p 6432 -U admin pgbouncer -c "SHOW STATS;"
psql -h 127.0.0.1 -p 6432 -U admin pgbouncer -c "SHOW CLIENTS;"

В SHOW POOLS смотрите cl_active, cl_waiting, sv_active и maxwait. Растущий cl_waiting означает, что пул мал или запросы слишком долгие. В SHOW STATS полезны avg_query_time и total_query_count, а SHOW SERVERS показывает открытые соединения к PostgreSQL.

psql -h 127.0.0.1 -p 6432 -U admin pgbouncer -c "SHOW SERVERS;" | head -20
ss -tnp | grep 6432

Для метрик подойдёт pgbouncer_exporter или скрипт с SHOW POOLS. Алерты: cl_waiting > 0 и maxwait больше секунды.

Типичные грабли

  • приложение делает SET search_path и ждёт, что настройка сохранится — используйте SET LOCAL внутри транзакции;
  • ORM держит серверные prepared statements, а max_prepared_statements равен нулю;
  • миграции и pg_dump идут через пул и мешают друг другу — выполняйте их прямым подключением;
  • default_pool_size выставлен наугад, без учёта числа баз и ролей;
  • userlist.txt с открытыми паролями лежит с правами 644.

Для protocol-level prepared statements включите max_prepared_statements = 200. Параметр работает с версии 1.21 и убирает ошибки «prepared statement does not exist». Клиенты, которые готовят запросы на уровне SQL через PREPARE, всё равно требуют режима session.

Плавные перезапуски и отказоустойчивость

PgBouncer не переключается на другой сервер сам, но даёт инструменты для управляемого переключения. PAUSE отключает серверные соединения, дожидаясь завершения транзакций, после чего можно менять DNS или бэкенд.

psql -h 127.0.0.1 -p 6432 -U admin pgbouncer -c "PAUSE appdb;"
psql -h 127.0.0.1 -p 6432 -U admin pgbouncer -c "RESUME appdb;"
psql -h 127.0.0.1 -p 6432 -U admin pgbouncer -c "RECONNECT;"
sudo systemctl reload pgbouncer

RELOAD перечитывает конфиг, RECONNECT переоткрывает соединения. После смены TLS-сертификатов сделайте RECONNECT, иначе старые соединения останутся на прежнем. Проверка пула: pg_isready -h 127.0.0.1 -p 6432 -U app -d appdb.

Чек-лист

  • режим пула соответствует приложению, сессионные функции не используются в режиме transaction;
  • размер пула посчитан от CPU и памяти PostgreSQL, а не «на глаз»;
  • userlist.txt закрыт правами 600, пароли не хранятся в открытом виде без нужды;
  • max_prepared_statements включён, если драйвер использует prepared statements;
  • настроены алерты на cl_waiting и maxwait;
  • миграции, pg_dump и обслуживание идут мимо пула;
  • процедура PAUSE/RESUME проверена на стенде.

Итог

PgBouncer — полезный слой между приложением и PostgreSQL. Он снижает число соединений, сглаживает пики и даёт понятную картину нагрузки. Начните с режима transaction, посчитайте бюджет соединений, настройте SCRAM и мониторинг, а миграции и резервное копирование оставьте на прямое подключение. Если приложению нужно сессионное состояние, выберите режим session или перепишите проблемные места: компромисс дешевле нестабильной базы.

Насколько публикация полезна?

Нажмите на звезду, чтобы оценить!

Средняя оценка 0 / 5. Количество оценок: 0

Оценок пока нет. Поставьте оценку первым.

Поделиться: Telegram ВКонтакте