Some clients connect to our postgresql database but leave the connections opened. Is it possible to tell Postgresql to close those connection after a certain amount of inactivity ?
TL;DR
IF you're using a Postgresql version >=
9.2
THEN use the solution I came up withIF you don't want to write any code
THEN use arqnid's solutionIF you don't want to write any code
AND you're using a Postgresql version >=14
THEN use Laurenz Albe's solution
For those who are interested, here is the solution I came up with, inspired from Craig Ringer's comment:
(...) use a cron job to look at when the connection was last active (see pg_stat_activity) and use pg_terminate_backend to kill old ones.(...)
The chosen solution comes down like this:
- A connection is considered inactive if its state is either
idle,idle in transaction,idle in transaction (aborted)ordisabled.- A connection is considered old if its state stayed the same during more than 5 minutes.
rank() function)This is the SQL query run by the 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
pg_terminate_backend. We used a cron like job to periodically invoke pg_terminate_backend. –
Ivonneivor If you are using PostgreSQL >= 9.6 there is an even easier solution. Let's suppose you want to delete all idle connections every 5 minutes, just run the following:
alter system set idle_in_transaction_session_timeout='5min';
In case you don't have access as superuser (example on Azure cloud), try:
SET SESSION idle_in_transaction_session_timeout = '5min';
But this latter will work only for the current session, that most likely is not what you want.
To disable the feature,
alter system set idle_in_transaction_session_timeout=0;
or
SET SESSION idle_in_transaction_session_timeout = 0;
(by the way, 0 is the default value).
If you use alter system, you must reload configuration to start the change and the change is persistent, you won't have to re-run the query anymore if, for example, you will restart the server.
To check the feature status:
show idle_in_transaction_session_timeout;
Connect through a proxy like PgBouncer which will close connections after server_idle_timeout seconds.
server_idl_timeout. See https://mcmap.net/q/56132/-how-to-close-idle-connections-in-postgresql-automatically –
Ivonneivor From PostgreSQL v14 on, you can set the idle_session_timeout parameter to automatically disconnect client sessions that are idle.
If you use AWS with PostgreSQL >= 9.6, you have to do the following:
go to RDS > Parameter groups > Create parameter group Select the version of PSQL that you use, name it 'customParameters' or whatever and add description 'handle idle connections'.
Fortunately it will create a copy of the default AWS group so you only have to tweak the things that you deem not suitable for your use-case.
Now click on the newly created parameter group and search 'idle'.
The default value for 'idle_in_transaction_session_timeout' is set to 24 hours (86400000 milliseconds). Divide this number by 24 to have hours (3600000) and then you have to again divide 3600000 by 4, 6 or 12 depending on whether you want the timeout to be respectively 15, 10 or 5 minutes (or equivalently multiply the number of minutes x 60000, so value 300 000 for 5 minutes).
Last, but not least, change the group:
go to RDS, select your DB and click on 'Modify'.
Now under 'Database options' you will find 'DB parameter group', change it to the newly created group.
You can then decide if you want to apply the modifications immediately (beware of downtime).
I have the problem of denied connections as there are too much clients connected on Postgresql 12 server (but not on similar projects using earlier 9.6 and 10 versions) and Ubuntu 18.
I wonder if those settings
tcp_keepalives_idle
tcp_keepalives_interval
could be more relevant than
idle_in_transaction_session_timeout
idle_in_transaction_session_timeout indeed closes only the idle connections from failed transactions, not the inactive connections whose statements terminate correctly... the documentation reads that these socket-level settings have no impact with Unix-domain sockets but it could work on Ubuntu.
tcp_keepalives_interval=300 tcp_keepalives_interval=30 . If I understand correctly, the server should wait 5 minutes to check if the client is still connected, and then wait 30 further seconds for an ACK from the client... –
Recession tcp_keepalives_interval twice... –
Latterll tcp_keepalives_idle=300 , tcp_keepalives_interval=30, tcp_user_timeout = 300 (on separate lines). The server waits 5 minutes before sending the first ACK request to the client, waitx 30 seconds before retrying if it doesn't reply, and closes the connection slot after 5 minutes. tcp_keepalives_count also allows to specify the number of keepalive queries (default system setting TCP_KEEPCNT if not mentionned). See also this unofficial site : postgresqlco.nf/doc/en/param/tcp_keepalives_count –
Recession Up to PostgreSQL 13, you can use my extension pg_timeout.
Here is a solution if you using docker containers: Run following commands against your database container to find out the number of idle connections you have:
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
To kill idle connections, Run the following commands against your database container:
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
© 2022 - 2024 — McMap. All rights reserved.
pg_stat_activity) and usepg_terminate_backendto kill old ones. Easily expressed in a simple query. I'm not sure ifpg_terminate_backendwas available in the pretty-ancient 8.3, though. – Supenpg_stat_activitytable i have lots ofidleconnections that's runningSELECT 1orSHOW TRANSACTION ISOLATION LEVEL. Then when I run a second load test, the number kept growing, does this mean that the idle connections were not re-used during second test? Can i use the same solution here to fix it? – Family