Kill an Idle PostgreSQL Connection with pg_terminate_backend

A client left a connection open and idle, and it holds a slot or blocks a maintenance task.

docker run -d --name pgterm -e POSTGRES_PASSWORD=pw postgres:17-alpine > /dev/null
until [ "$(docker logs pgterm 2>&1 | grep -c 'ready to accept connections')" -ge 2 ]; do sleep 1; done
( echo 'select 1;'; sleep 60 ) | docker exec -i -e PGAPPNAME=stale pgterm psql -U postgres -q > client.log 2>&1 &
sleep 3
docker exec pgterm psql -U postgres -tAc "select application_name, state from pg_stat_activity where application_name = 'stale'"
docker exec pgterm psql -U postgres -tAc "select pg_terminate_backend(pid) from pg_stat_activity where state = 'idle' and application_name = 'stale' and state_change < now() - interval '2 seconds'"
sleep 1
docker exec pgterm psql -U postgres -tAc "select count(*) from pg_stat_activity where application_name = 'stale'"
docker rm -f pgterm > /dev/null

stale|idle
t
0

pg_stat_activity lists every backend with its pid, state and state_change, so one query finds the idle ones that have not changed for a while. pg_terminate_backend(pid) returns t when the signal was sent and closes the connection. The session named stale showed idle and, after the call, the count of sessions with that name was 0. pg_cancel_backend cancels the current query and keeps the session. See Server Signaling Functions.

Terminating a session rolls back its open transaction, so check idle in transaction sessions before you end them. You need to be a superuser, or a member of pg_signal_backend, or the same role. The client gets a fatal error on its next use and an application without reconnect logic then fails. This run used PostgreSQL 17.