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

J'ai le problème de connexions refusées car il y a trop de clients connectés sur le serveur Postgresql 12 (mais pas sur des projets similaires utilisant les versions 9.6 et 10 précédentes) et Ubuntu 18.

Je me demande si ces paramètres

tcp_keepalives_idle 
tcp_keepalives_interval 

pourraient être plus pertinents que

idle_in_transaction_session_timeout

idle_in_transaction_session_timeout ferme en effet uniquement les connexions inactives provenant de transactions échouées, pas les connexions inactives dont les requêtes se terminent correctement... la documentation indique que ces paramètres au niveau du socket n'ont pas d'impact avec les sockets de domaine Unix mais cela pourrait fonctionner sur Ubuntu.

Answer 2

Jusqu'à PostgreSQL 13, vous pouvez utiliser mon extension pg_timeout.

Answer 3

Voici une solution si vous utilisez des conteneurs Docker : Exécutez les commandes suivantes contre votre conteneur de base de données pour savoir le nombre de connexions inactives que vous avez :

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

Pour tuer les connexions inactives, exécutez les commandes suivantes contre votre conteneur de base de données :

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