Установка и настройка PgBouncer#

PGBouncer — программа, управляющая пулом соединений PostgreSQL. Любое конечное приложение может подключиться к PGBouncer, как если бы это был непосредственно сервер PostgreSQL. PGBouncer создаст подключение к реальному серверу, либо задействует одно из ранее установленных подключений.

Назначение PGBouncer — минимизировать издержки, связанные с установлением новых подключений к PostgreSQL

pgbouncer

Установка#

Установите пакет pgbouncer из репозитория дистрибутива при помощи пакетного менеджера.

PgBouncer доступен в официальных APT-репозиториях PostgreSQL. Если репозитории PostgreSQL уже добавлены в вашу систему, вы можете установить PgBouncer напрямую с их использованием.

Настройка и запуск#

Предварительная конфигурация PostgreSQL сервера#

  • Убедитесь, что в файле pg_hba.conf указан метод аутентификации scram-sha-256:

host    all             all             all                     scram-sha-256

  • Подключитесь к базе postgres под пользователем postgres:

$ su postgres
$ psql

  • Выполните данный скрипт, который создаст функцию и пользователя, необходимые для аутентификации PgBouncer (измените пароль в поле PASSWORD на более надёжный):

CREATE FUNCTION public.lookup (
   INOUT p_user     name,
   OUT   p_password text
) RETURNS record
   LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog AS
$$SELECT usename, passwd FROM pg_shadow WHERE usename = p_user$$;

CREATE ROLE pgbouncer WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION CONNECTION LIMIT -1 PASSWORD 'pgbouncerpass'; 
REVOKE EXECUTE ON FUNCTION public.lookup(name) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.lookup(name) TO pgbouncer;

  • Выполните команду, которая выведет хеш пароля для пользователя pgbouncer, и сохраните его. Он понадобится при настройке аутентификации pgbouncer.

SELECT passwd FROM pg_shadow WHERE usename = 'pgbouncer';

  • Завершите сессию psql

\q

Конфигурация PgBouncer#

Конфигурация PgBouncer редактируется в файле /etc/pgbouncer/pgbouncer.ini

  • В раздел [databases] нужно добавить адрес и порт, на котором запущен сервер PostgreSQL.

[databases]
* = host=localhost port=5432

  • В разделе [pgbouncer] в значении listen_addr следует указать, на каких адресах будет слушать PgBouncer, а также при необходимости изменить порт (по умолчанию 6432). При задании значения listen_addr = * pgbouncer будет слушать на всех адресах.

listen_addr = *
listen_port = 6432

  • Также в разделе [pgbouncer] следует задать следующие значения:

auth_type - метод аутентификации в postgresql
auth_file - путь до файла с данными для аутентификации
auth_user - пользователь, который будет использоваться для аутентификации в postgresql
auth_dbname - база данных, которая используется для аутентификации
auth_query - запрос, который будет выполнен при аутентификации

max_client_conn - максимальное количество клиентских подключений, которые PgBouncer может обслуживать одновременно
default_pool_size - максимальное количество подключений к базе данных для каждого пула
reserve_pool_size - количество дополнительных подключений, доступных в резервном пуле, которые используются, если пул исчерпан
reserve_pool_timeout - указывает, сколько секунд клиент может ждать подключения из резервного пула
pool_mode - определяет, как PgBouncer управляет соединениями. Для использования PgBouncer с системой Global следует установить режим transaction, чтобы снизить нагрузку на БД
max_prepared_statements - определяет максимальное количество подготовленных SQL-запросов, которые PgBouncer может хранить на одно соединение

  • Пример конфигурации для корректной работы аутентификации с использованием вышеописанной sql-функции:

auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
auth_user = pgbouncer
auth_dbname = postgres
auth_query = SELECT p_user, p_password FROM public.lookup($1)

  • Пример конфигурации для использованием с k8s кластером Global из 10 подов (значения следует задать в зависимости от предположительной нагрузки на БД)

max_client_conn = 5000
default_pool_size = 20
reserve_pool_size = 10
reserve_pool_timeout = 5
pool_mode = transaction
max_prepared_statements = 200

  • Для корректной работы pgbouncer с dbeaver и globalserver следует раскомментировать поле «ignore_startup_parameters» и задать ему следующее значение:

ignore_startup_parameters = extra_float_digits,search_path

  • Также в сервисе pgbouncer (/lib/systemd/system/pgbouncer.service) следует раскомментировать параметр LimitNOFile, отвечающий за максимальное количество открытых файловых дескрипторов, доступных для процесса, и настроить его в зависимости от количества подключений:

[Unit]
Description=connection pooler for PostgreSQL
Documentation=man:pgbouncer(1)
Documentation=https://www.pgbouncer.org/
After=network.target
#Requires=pgbouncer.socket

[Service]
Type=notify
User=postgres
ExecStart=/usr/sbin/pgbouncer /etc/pgbouncer/pgbouncer.ini
ExecReload=/bin/kill -HUP $MAINPID
KillSignal=SIGINT
LimitNOFILE=65535

[Install]
WantedBy=multi-user.target

$ sudo systemctl daemon-reload
$ sudo systemctl restart pgbouncer

Добавление пользователей#

Пользователи добавляются в файл /etc/pgbouncer/userlist.txt

Формат списка пользователей имеет следующий вид:

"<ИМЯ_ПОЛЬЗОВАТЕЛЯ>" "<ПАРОЛЬ/ХЕШ ПАРОЛЯ>"

Для работы аутентификации с вышеописанными настройками, в userlist.txt требуется установить следующие значения:

"pgbouncer" "<scram-sha-256 хеш пароля для пользователя pgbouncer>"

Запуск PgBouncer#

После завершения настройки PgBouncer запустите его через сервис:

$ sudo systemctl enable --now pgbouncer

По умолчанию pgbouncer будет доступен на 6432 порту сервера.