Установка и настройка 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 может хранить на одно соединение

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

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.

Безопасность#

Рекомендуется закрыть основной порт PostgreSQL при использовании PgBouncer.

Для этого измените файл pg_hba.conf, разрешив только локальное подключение:

host    all             all             127.0.0.1/32           scram-sha-256
host    all             all             ::1/128                scram-sha-256

Если требуется прямое подключение к СУБД с определённых IP-адресов, добавьте их дополнительно:

host    all             all             <IP_ADDR>/32    scram-sha-256

где <IP_ADDR> — IP-адрес хоста, которому разрешено прямое подключение к СУБД.

Пример:

host    all             all             192.168.1.12/32        scram-sha-256

При использовании брандмауэра задайте следующие правила для запрета прямого подключения:

ufw allow 6432/tcp
ufw allow from 127.0.0.1 to any port 5432 proto tcp
ufw deny 5432/tcp