Перейти к основному содержанию
`Ctrl`+`K`

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

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

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

## Установка

Установите пакет `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

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

    ufw allow from <IP_ADDR> to any port 5432 proto tcp