Comment fermer les connexions inactives dans PostgreSQL automatiquement?
certains clients se connectent à notre base de données postgresql mais laissent les connexions ouvertes. Est-il possible de dire à Postgresql de fermer cette connexion après une certaine période d'inactivité ?
TL;DR
si vous utilisez une version Postgresql >=
9.2
ENSUITE, utilisez la solution que j'ai trouvési vous ne voulez pas écrire de code
ENSUITE, utilisez arqnid la solution
3 réponses
pour ceux qui sont intéressés, voici la solution que j'ai trouvée, inspirée de Craig Ringercommentaire:
(...) utilisez une tâche cron pour regarder quand la connexion a été activée pour la dernière fois (voir pg_stat_activity) et utilisez pg_terminate_backend pour tuer les anciennes.(...)
La solution choisie vient vers le bas comme ceci:
- tout d'abord, nous passons à Postgresql 9.2.
- puis, nous programmons un fil pour exécuter à chaque seconde.
- Lorsque le thread s'exécute, il recherche toutes les anciennes connexions inactives.
- Une connexion est considérée comme inactif si c'est état
idle
,idle in transaction
,idle in transaction (aborted)
oudisabled
. - Une connexion est considérée comme vieux si son dernier état n'a pas changé depuis plus de 5 minutes.
- Une connexion est considérée comme inactif si c'est état
- il y a d'autres threads qui font la même chose que surtout. Cependant, ces fils se connecter à la base de données avec un utilisateur différent.
- nous laissons au moins une connexion ouverte pour toute application connectée à notre base de données. (
rank()
function)
ceci est la requête SQL exécutée par le thread:
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
si vous utilisez PostgreSQL >= 9.6 il y a une solution encore plus facile. Supposons que vous voulez supprimer toutes les connexions inactives toutes les 5 minutes, exécutez juste ce qui suit:
alter system set idle_in_transaction_session_timeout='5min';
Dans le cas où vous n'avez pas accès en tant que super-utilisateur (exemple sur Azure cloud), à essayer:
SET SESSION idle_in_transaction_session_timeout = '5min';
Mais ce dernier ne fonctionne que pour la session en cours, qui n'est probablement pas ce que vous voulez.
désactiver la fonctionnalité,
alter system set idle_in_transaction_session_timeout=0;
ou
SET SESSION idle_in_transaction_session_timeout = 0;
(au passage, 0 est la valeur par défaut).
Si vous utilisez alter system
, vous devez recharger la configuration pour démarrer la modification et la modification est persistante, vous n'aurez plus à relancer la requête si, par exemple, vous redémarrez le serveur.
Pour vérifier la fonctionnalité de l'état:
show idle_in_transaction_session_timeout;