• Превышение лимита max_connections в PostgreSQL при микросервисной архитектуре.
• Высокое потребление памяти каждым новым форком backend-процесса PostgreSQL.
• Ошибки аутентификации Auth failed for user при подключении через пулер соединений.
• Рост задержек на установку TCP/TLS рукопожатий клиентами.

1. Установка PgBouncer

apt-get install -y pgbouncer || dnf install -y pgbouncer

2. Создание функции auth_query в PostgreSQL

Подключитесь к БД под суперпользователем и выполните:

CREATE SCHEMA IF NOT EXISTS pgbouncer;

CREATE OR REPLACE FUNCTION pgbouncer.get_auth(p_usename text)
RETURNS TABLE(usename name, passwd text)
SECURITY DEFINER
AS $$
BEGIN
    RETURN QUERY 
    SELECT s.rolname, s.rolpassword::text 
    FROM pg_authid s 
    WHERE s.rolname = p_usename;
END;
$$ LANGUAGE plpgsql;

REVOKE ALL ON FUNCTION pgbouncer.get_auth(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION pgbouncer.get_auth(text) TO pgbouncer_admin;

3. Конфигурация /etc/pgbouncer/pgbouncer.ini

[databases]
app_production = host=127.0.0.1 port=5432 dbname=app_production
* = host=127.0.0.1 port=5432

[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /run/postgresql/pgbouncer.pid
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
auth_query = SELECT usename, passwd FROM pgbouncer.get_auth($1)
auth_user = pgbouncer_admin

# Режимы пулинга: session, transaction, statement
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 5
reserve_pool_timeout = 5
max_db_connections = 100

4. Создание /etc/pgbouncer/userlist.txt

Файл должен содержать только учетную запись сервисного пользователя auth_user:

"pgbouncer_admin" "SCRAM-SHA-256$4096:5xyz...=="

5. Запуск и проверка статуса

systemctl enable --now pgbouncer
psql -p 6432 -U pgbouncer_admin -d pgbouncer -c "SHOW POOLS;"