Как автоматически закрыть незанятые соединения в PostgreSQL?

Некоторые клиенты подключаются к нашей базе данных postgresql, но оставляют соединения открытыми. Можно ли сказать Postgresql закрыть эти соединения после определенного периода бездействия?

TL; DR

Если вы используете версию Postgresql>= 9.2
ТО используйте решение, которое я придумал

Если вы не хотите писать код
ТОГДА используйте решение arqnid

Источник

8 ответов

Решение

Для тех, кто заинтересован, вот решение, которое я придумал, вдохновленное комментарием Craig Ringer:

(...) используйте задание cron, чтобы посмотреть, когда соединение было активным в последний раз (см. pg_stat_activity), и используйте pg_terminate_backend, чтобы убить старые.(...)

Выбранное решение сводится к следующему:

  • Во-первых, мы обновляем до Postgresql 9.2.
  • Затем мы планируем запуск потока каждую секунду.
  • Когда поток запускается, он ищет любые старые неактивные соединения.
    • Соединение считается неактивным, если его состояние idle, idle in transaction, idle in transaction (aborted) или же disabled,
    • Соединение считается старым, если его состояние оставалось неизменным в течение более 5 минут.
  • Есть дополнительные темы, которые делают то же самое, что и выше. Однако эти потоки подключаются к базе данных с другим пользователем.
  • Мы оставляем хотя бы одно соединение открытым для любого приложения, подключенного к нашей базе данных.(rank() функция)

Это SQL-запрос, выполняемый потоком:

WITH inactive_connections AS (
    SELECT
        pid,
        rank() over (partition by client_addr order by backend_start ASC) as rank
    FROM 
        pg_stat_activity
    WHERE
        -- Exclude the thread owned connection (ie no auto-kill)
        pid <> pg_backend_pid( )
    AND
        -- Exclude known applications connections
        application_name !~ '(?:psql)|(?:pgAdmin.+)'
    AND
        -- Include connections to the same database the thread is connected to
        datname = current_database() 
    AND
        -- Include connections using the same thread username connection
        usename = current_user 
    AND
        -- Include inactive connections only
        state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') 
    AND
        -- Include old connections (found with the state_change field)
        current_timestamp - state_change > interval '5 minutes' 
)
SELECT
    pg_terminate_backend(pid)
FROM
    inactive_connections 
WHERE
    rank > 1 -- Leave one connection for each application connected to the database
Источник

Если вы используете PostgreSQL >= 9.6, есть еще более простое решение. Предположим, вы хотите удалять все бездействующие соединения каждые 5 минут, просто выполните следующее:

alter system set idle_in_transaction_session_timeout='5min';

Если у вас нет прав суперпользователя (например, в облаке Azure), попробуйте:

SET SESSION idle_in_transaction_session_timeout = '5min';

Но этот последний будет работать только для текущей сессии, что, скорее всего, не то, что вы хотите.

Чтобы отключить эту функцию,

alter system set idle_in_transaction_session_timeout=0;

или же

SET SESSION idle_in_transaction_session_timeout = 0;

(кстати, 0 - это значение по умолчанию).

Если вы используете alter systemвы должны перезагрузить конфигурацию, чтобы начать изменение, и изменение будет постоянным, вам больше не придется повторять запрос, если, например, вы перезапустите сервер.

Чтобы проверить состояние функции:

show idle_in_transaction_session_timeout;
Источник

Соединитесь через прокси как PgBouncer, который закроет соединения после server_idle_timeout секунд.

Источник

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

Источник

Если вы используете AWS с PostgreSQL >= 9.6, вам необходимо сделать следующее:

Создать группу специальных параметров

перейдите в RDS > Группы параметров> Создать группу параметров. Выберите версию PSQL, которую вы используете, назовите ее "customParameters" или как-нибудь иначе и добавьте описание "обрабатывать незанятые соединения".

Измените значение idle_in_transaction_session_timeout

К счастью, он создаст копию группы AWS по умолчанию, поэтому вам нужно будет настроить только те вещи, которые вы считаете не подходящими для вашего варианта использования.

Теперь щелкните по вновь созданной группе параметров и выполните поиск "простаивает".
Значение по умолчанию для idle_in_transaction_session_timeout установлено на 24 часа (86400000 миллисекунд). Разделите это число на 24, чтобы получить часы (3600000), а затем вам нужно снова разделить 3600000 на 4, 6 или 12 в зависимости от того, хотите ли вы, чтобы тайм-аут составлял соответственно 15, 10 или 5 минут (или, что эквивалентно, умножьте количество минут x 60000, значит значение 300 000 за 5 минут).

Назначьте группу

И последнее, но не менее важное: смените группу:

перейдите в RDS, выберите свою БД и нажмите "Изменить".

Теперь в разделе "Параметры базы данных" вы найдете "Группа параметров БД", измените ее на вновь созданную группу.

Затем вы можете решить, хотите ли вы применить изменения немедленно (остерегайтесь простоев).

Источник

У меня проблема с отказом в подключении, поскольку к серверу Postgresql 12 подключено слишком много клиентов (но не в аналогичных проектах, использующих более ранние версии 9.6 и 10) и Ubuntu 18.

Интересно, эти настройки

tcp_keepalives_idle 
tcp_keepalives_interval

может быть более актуальным, чем

idle_in_transaction_session_timeout

idle_in_transaction_session_timeout действительно закрывает только неактивные соединения из неудачных транзакций, а не неактивные соединения, чьи операторы завершаются правильно... в документации говорится, что эти настройки уровня сокета не влияют на сокеты домена Unix, но могут работать в Ubuntu.

Источник

До PostgreSQL 13 вы можете использовать мое расширение pg_timeout.

Источник

Вот решение, если вы используете Docker-контейнеры: выполните следующие команды для вашего контейнера базы данных, чтобы узнать количество имеющихся у вас простаивающих соединений:

      echo "SELECT datname, count(*) FROM pg_stat_activity WHERE state = 'idle' GROUP BY datname;" >> query.sql
cat ./query.sql | docker exec -i $name_of_db_container psql -U $pg_user

Чтобы завершить простаивающие соединения, выполните следующие команды для контейнера базы данных:

      echo "select pg_terminate_backend(pid) from pg_stat_activity where state = 'idle';" >> query.sql
cat ./query.sql | docker exec -i $name_of_db_container psql -U $pg_user
Источник
Другие вопросы по тегам