Terminating and canceling user sessions

Database administrators often need to kill server processes for various reasons. For example, in certain scenarios, very slow queries running on the slave or master, which are configured using streaming replication, can break the replication.

Getting ready

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.

How to do 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...