PostgreSQL 12 High Availability Cookbook - Third Edition

There's more...

If a connected application encounters a bug and goes haywire, it might be convenient to disconnect several clients simultaneously. PostgreSQL lets us run query results through functions, so we could kill all connections that were idle in the transaction for at least 2 minutes by running this query as a superuser:

SELECT pg_terminate_backend(pid) 
  FROM pg_stat_activity 
 WHERE now() - state_change > INTERVAL '2 minutes' 
   AND state = 'idle in transaction';

The pg_stat_activity view offers a lot of characteristics to differentiate target queries. We could terminate only connections from a specific IP address or those that connected to the database over a week ago. There is a lot of opportunity here to maintain a highly available ...