Close connections

PostgreSQL has a very useful state table called pg_stat_activity. This table is similar to the “SHOW PROCESSLIST” found in MySQL, but has the clear advantage of being queryable and provides more precise information than MySQL.

Here is an extract:

 12407 | postgres | 51572 | 10 | postgres | | 127.0.0.1 | | 44244 | 2017-07-26 07:41:44.454929+00 | | 2017-07-26 12:48:44.474222+00 | 2017-07-26 12:48:44.474335+00 | | | idle | | | SELECT CASE WHEN pg_is_in_recovery = 'false' THEN 0 ELSE COALESCE(ROUND(EXTRACT(epoch FROM now() - pg_last_xact_replay_timestamp())), 0) END AS seconds FROM pg_is_in_recovery()<br> 349593 | db02 | 51573 | 10 | postgres | | 127.0.0.1 | | 44245 | 2017-07-26 07:41:46.305319+00 | | 2017-07-26 12:48:46.336722+00 | 2017-07-26 12:48:46.33685+00 | | | idle | | | SELECT xact_commit,xact_rollback FROM pg_stat_database WHERE datname=$1;<br> 24816 | db01 | 51575 | 10 | postgres | | 127.0.0.1 | | 44246 | 2017-07-26 07:41:46.588503+00 |

In some cases, it can be useful to cut all connections to a specific database (for instance mass dormant connections). Here is a simple SQL query to run on your PostgreSQL (9.2 and above) to close all connections:

SELECT
pg_terminate_backend(pid) FROM
pg_stat_activity
WHERE pid <> pg_backend_pid() AND datname = 'target_database';

This query will issue a close order on all connections of the target_database database except the current connection.

If you only want to kill the idle queries, you can use the following variant:

SELECT
pg_terminate_backend(pid) FROM
pg_stat_activity
WHERE pid <> pg_backend_pid() AND datname = 'target_database' AND state = 'idle';