It is important to terminate some database connections and processes in the case of dead locks, in case the maximum number of connections is exceeded, as well as for maintenance purposes, such as dropping the database as one cannot drop the database if someone is connected to it.

PostgreSQL provides the pg_terminate_backend(pid) and pg_cancel_backend(pid) functions, while pg_cancel_backend only cancels the current query and pg_terminate_backend kills the entire connection. The following query terminates all connections to the current database except the session connection:

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

The pg_cancel_backend function cancels the current query, but the application may run the same query again. The pg_terminate_backend function terminates the connection, and this also can be done via the kill Linux command. However, the kill command may be dangerous if one kills the postgres server process instead on the connection process.

When combining pg_stat_activity and pg_terminate_backend, one can achieve greater flexibility. For example, in streaming replication, one can detect queries that take a lot of time on the slaves and thus cause a replication lag. Also, if a certain application is not configured properly and has a big number of idle connections, one can kill them to free memory and allow other clients to connect to the database.

Finally, if one is not able to drop a certain database because clients try to connect to it, one can execute the following query, which disallows users from connecting to the database and then kills the connections:

UPDATE pg_database set datallowconn = 'false' WHERE datname = 'database to drop';
--- now kill the connections