</>
halfcoder

Гайды

Настройка pgBouncer для пула соединений к PostgreSQL

Режимы session и transaction, pgbouncer.ini, userlist, мониторинг SHOW POOLS и типичные ошибки с prepared statements.

~12 мин чтения

Базы данныхPostgreSQLПул соединений

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

pgBouncer — лёгкий посредник между приложением и PostgreSQL: множество клиентских подключений мультиплексируется в небольшое число серверных сессий к БД. Это снижает расход памяти на процессы, нагрузку на планировщик ОС и риск исчерпания max_connections.

Зачем нужен пул

Каждое подключение к PostgreSQL — отдельный backend-процесс (порядка 2–5 МБ RAM и накладные расходы). Веб-приложения с короткими запросами и большим числом коннектов быстро упираются в лимиты и деградацию.

Плюсы pgBouncer: меньше нагрузки на Postgres, предсказуемый верхний предел активных сессий, смена только хоста/порта в приложении, опционально server_lifetime для периодического обновления серверных сессий.

Установка (Ubuntu / Debian, PGDG)

bash
sudo apt update
sudo apt install -y curl ca-certificates
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /usr/share/keyrings/postgresql-archive-keyring.gpg
echo "deb [signed-by=/usr/share/keyrings/postgresql-archive-keyring.gpg] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt update
sudo apt install -y pgbouncer

Основной конфиг: /etc/pgbouncer/pgbouncer.ini, пользователи: /etc/pgbouncer/userlist.txt.

Секция [databases]

ini
[databases]
mydb = host=192.168.1.100 port=5432 dbname=mydb user=myappuser
; шаблон для любых имён БД на одном хосте:
; * = host=192.168.1.100 port=5432

user можно опустить — тогда подставится пользователь клиентского подключения (если политика безопасности позволяет).

Секция [pgbouncer] (пример)

ini
[pgbouncer]
listen_addr = *
listen_port = 6432

auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 5

max_client_conn = 200
server_idle_timeout = 60
server_lifetime = 3600
query_timeout = 0

server_reset_query = DISCARD ALL
  • default_pool_size — лимит активных подключений к Postgres на пару database + user; обычно заметно меньше max_connections в самой БД (часто в разы).
  • server_reset_query = DISCARD ALL — сбрасывает сессионное состояние при возврате соединения в пул (рекомендуется для transaction).

Для auth_type предпочтительно SCRAM вместо устаревшего md5, если клиенты и записи в userlist совместимы.

Режимы пула

РежимПоведениеСовместимость
sessionСерверная сессия закреплена за клиентом на всё время клиентского подключенияМаксимальная: LISTEN/NOTIFY, временные таблицы, prepared statements
transactionСоединение к БД занято только на время транзакцииНесовместимо с prepared statements (без донастройки), LISTEN, долгоживущими temp tables и т.п.
statementВозврат в пул после одного statementОчень редкий сценарий, нужен автокоммит

Практика: для типичного HTTP API — transaction, если стек приложения не требует сессионных фич. Иначе — session.

userlist.txt

Формат:

text
"myappuser" "SCRAM-SHA-256$4096:..."

Пароли должны соответствовать тому, что ожидает выбранный auth_type. В pg_hba.conf Postgres разрешите хост pgBouncer:

conf
host    all    myappuser    <IP_pgbouncer>/32    scram-sha-256

Запуск и проверка

bash
sudo pgbouncer -v -C /etc/pgbouncer/pgbouncer.ini
sudo systemctl start pgbouncer
sudo systemctl enable pgbouncer

Подключение через пул:

bash
psql -h 127.0.0.1 -p 6432 -U myappuser mydb

Админ-консоль

Виртуальная БД pgbouncer:

bash
psql -p 6432 -U pgbouncer_admin pgbouncer

Полезные команды:

  • SHOW POOLS; — cl_active, cl_waiting, sv_active, sv_idle, maxwait. Постоянный **cl_waiting > 0** → увеличивайте пул (и следите, чтобы суммарно не упереться в max_connections` Postgres).
  • SHOW STATS;, SHOW CLIENTS;, SHOW SERVERS;, SHOW CONFIG;
  • RELOAD; — перечитать ini без остановки.
  • PAUSE / RESUME / SHUTDOWN.

Частые проблемы

  1. Ошибка аутентификации — userlist, auth_type, запись в pg_hba.conf на стороне Postgres.
  2. prepared statement ... does not exist — классика transaction + prepared statements. Варианты: pool_mode = session, отключить prepared на клиенте (например JDBC prepareThreshold=0), или настройки pgBouncer под prepared (зависит от версии и клиента).
  3. Рост cl_waiting — увеличить default_pool_size / per-db pool_size, затем RELOAD.
  4. LISTEN / NOTIFY — только session.
  5. Не стартует — journalctl -u pgbouncer -f, синтаксис pgbouncer.ini.

Быстрый чек-лист

  1. Начните с session, оцените нагрузку; при необходимости переходите на transaction.
  2. Подберите default_pool_size по SHOW POOLS и CPU/RAM Postgres.
  3. Держите server_reset_query = DISCARD ALL.
  4. Вынесите метрики пула в мониторинг (Prometheus exporter для pgBouncer или парсинг SHOW).

Итог

pgBouncer — стандартный слой для контроля числа подключений к PostgreSQL. Критично выбрать pool_mode под возможности приложения и не забыть про сброс состояния сессии при возврате соединения в пул.