Comment fermer automatiquement les connexions inactives dans PostgreSQL?

Question

Certains clients se connectent à notre base de données PostgreSQL mais laissent les connexions ouvertes. Est-il possible de demander à PostgreSQL de fermer ces connexions après un certain laps de temps d'inactivité?

TL;DR

SI vous utilisez une version de PostgreSQL >= 9.2
ALORS utilisez la solution que j'ai trouvée

SI vous ne voulez pas écrire de code
ALORS utilisez la solution d'arqnid

SI vous ne voulez pas écrire de code
ET que vous utilisez une version de PostgreSQL >= 14
ALORS utilisez la solution de Laurenz Albe

Answer 1

Pour ceux qui sont intéressés, voici la solution que j'ai trouvée, inspirée du commentaire de Craig Ringer:

(...) utiliser une tâche cron pour regarder quand la connexion était la dernière active (voir pg_stat_activity) et utiliser pg_terminate_backend pour tuer les anciennes.(...)

La solution choisie se résume ainsi:

• Tout d'abord, nous mettons à jour vers Postgresql 9.2.

• Ensuite, nous planifions un thread pour s'exécuter toutes les secondes.

• Lorsque le thread s'exécute, il recherche toutes les anciennes connexions inactives.

  • Une connexion est considérée inactive si son état est soit idle, idle in transaction, idle in transaction (aborted) ou disabled.
  • Une connexion est considérée ancienne si son état est resté le même pendant plus de 5 minutes.

• Il y a d'autres threads qui font la même chose que ci-dessus. Cependant, ces threads se connectent à 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. (fonction rank())

Voici 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
        -- Exclure la connexion appartenant au thread (ie pas de kill automatique)
        pid <> pg_backend_pid( )
    AND
        -- Exclure les connexions d'applications connues
        application_name !~ '(?:psql)|(?:pgAdmin.+)'
    AND
        -- Inclure les connexions à la même base de données à laquelle le thread est connecté
        datname = current_database() 
    AND
        -- Inclure les connexions utilisant le même nom d'utilisateur de thread
        usename = current_user 
    AND
        -- Inclure uniquement les connexions inactives
        state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') 
    AND
        -- Inclure les connexions anciennes (trouvées avec le champ state_change)
        current_timestamp - state_change > interval '5 minutes' 
)
SELECT
    pg_terminate_backend(pid)
FROM
    inactive_connections 
WHERE
    rank > 1 -- Laisser une connexion pour chaque application connectée à la base de données

Answer 2

Si vous utilisez PostgreSQL >= 9.6, il existe une solution encore plus facile. Supposons que vous voulez supprimer toutes les connexions inactives toutes les 5 minutes, il vous suffit d'exécuter la commande suivante :

alter system set idle_in_transaction_session_timeout='5min';

S'il vous est impossible d'accéder en tant que superutilisateur (par exemple sur le cloud Azure), essayez :

SET SESSION idle_in_transaction_session_timeout = '5min';

Cependant, cette dernière ne fonctionnera que pour la session en cours, ce qui n'est probablement pas ce que vous voulez.

Pour désactiver la fonctionnalité,

alter system set idle_in_transaction_session_timeout=0;

ou

SET SESSION idle_in_transaction_session_timeout = 0;

(à propos, 0 est la valeur par défaut).

Si vous utilisez alter system, vous devez recharger la configuration pour démarrer le changement et le changement est persistant, vous n'aurez pas à exécuter à nouveau la requête si, par exemple, vous redémarrez le serveur.

Pour vérifier l'état de la fonctionnalité :

show idle_in_transaction_session_timeout;

Answer 3

Connectez-vous via un proxy tel que PgBouncer qui fermera les connexions après server_idle_timeout secondes.

Answer 4

À partir de PostgreSQL v14, vous pouvez définir le paramètre idle_session_timeout pour déconnecter automatiquement les sessions client qui sont inactives.

Answer 5

Si vous utilisez AWS avec PostgreSQL >= 9.6, vous devez faire ce qui suit:

Créer un groupe de paramètres personnalisé

Allez sur RDS > Groupes de paramètres > Créer un groupe de paramètres Sélectionnez la version de PSQL que vous utilisez, nommez-le 'paramètres personnalisés' ou autre et ajoutez la description 'gérer les connexions inactives'.

Changer la valeur de idle_in_transaction_session_timeout

Heureusement, cela créera une copie du groupe AWS par défaut, donc vous n'aurez qu'à ajuster les choses que vous estimez mal adaptées à votre cas d'utilisation.

Cliquez maintenant sur le groupe de paramètres nouvellement créé et cherchez 'idle'.
La valeur par défaut de 'idle_in_transaction_session_timeout' est définie à 24 heures (86400000 millisecondes). Divisez ce nombre par 24 pour obtenir les heures (3600000) puis divisez de nouveau 3600000 par 4, 6 ou 12 en fonction que vous souhaitiez respectivement un délai d'expiration de 15, 10 ou 5 minutes (ou multipliez le nombre de minutes x 60000, donc la valeur 300 000 pour 5 minutes).

Attribuer le groupe

Enfin, mais non le moindre, changez le groupe:

Allez sur RDS, sélectionnez votre base de données et cliquez sur 'Modifier'.

Maintenant sous 'Options de base de données', vous trouverez 'Groupe de paramètres DB', changez-le pour le groupe nouvellement créé.

Vous pouvez ensuite choisir si vous souhaitez appliquer les modifications immédiatement (attention à l'arrêt).