postgres session timeout - ghdrako/doc_snipets GitHub Wiki
- https://stackoverflow.com/questions/12391174/how-to-close-idle-connections-in-postgresql-automatically
- https://postgresqlco.nf/doc/en/param/idle_session_timeout/
- https://www.depesz.com/2021/01/12/waiting-for-postgresql-14-add-idle_session_timeout/
- https://www.enterprisedb.com/postgres-tutorials/how-monitor-postgresql-connections
- https://medium.com/little-programming-joys/finding-and-killing-long-running-queries-on-postgres-7c4f0449e86d
- https://postgresqlco.nf/doc/en/param/max_connections/
- From PostgreSQL v14 on, you can set the [idle_session_timeout(https://www.postgresql.org/docs/14/runtime-config-client.html#GUC-IDLE-SESSION-TIMEOUT) parameter to automatically disconnect client sessions that are idle.
SELECT * FROM pg_settings WHERE name = 'idle_session_timeout' # zamyka sesjie bez tranzakcji idle po czasie np 10000 (10s)
SELECT * FROM pg_settings WHERE name = 'idle_in_transaction_session_timeout' # zabija sesje w tranzakcji ktora sie nie zakonczyla
-
Up to PostgreSQL 13, you can use extension pg_timeout. https://api.pgxn.org/src/pg_timeout/pg_timeout-1.0.0/README.md This extension has been validated with PostgresSQL 9.5, 9.6, 10, 11 and 12
-
use a cron job to look at when the connection was last active (see
pg_stat_activity
) and usepg_terminate_backend
to kill old ones
- A connection is considered inactive if its state is either idle, idle in transaction, idle in transaction (aborted) or disabled.
- A connection is considered old if its state stayed the same during more than 5 minutes.
If the pg_cancel_backend
command does not terminate the query, we can escalate to a more forceful termination by executing the following command:
SELECT pg_terminate_backend(<PID>);
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
idle_in_transaction_session_timeout
This will only close connections that opened a transaction and failed to close (commit or rollback) it within the given timeout (as the name "idle_in_transaction_session_timeout" suggests). It won't close connections that are just "idle"
alter system set idle_in_transaction_session_timeout='5min';
To disable the feature,
alter system set idle_in_transaction_session_timeout=0;
or
SET SESSION idle_in_transaction_session_timeout = 0;
show idle_in_transaction_session_timeout;