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.

Database connection can be terminated for several reasons, as follows:

• The maximum number of connections is exceeded in some cases where connections are misconfigured, such as when keeping IDLE connections open for a very long time, or when some sessions are opened but in a stale state. 
• Dropping the database, one can not drop a database if someone is connected to it. This is sometimes necessary in testing scenarios.
• Very slow queries: Very slow queries can have a cascading effect on streaming replication as well as other transactions. 

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 can also be done via the kill Linux command. However, the kill command may be dangerous if one kills the PostgreSQL server process instead of the connection process.

When combining the pg_stat_activity view and the pg_terminate_backend function, one can achieve greater flexibility. For example, in streaming replication, one can detect queries that spend 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.

Also, PostgreSQL allows for terminating the SQL statement due to timeouts. For example, in several cases, long running statements can have very bad side effects such as blocking other statements. Statement timeout can be set globally in postgresql.conf or can be set to each session as follows:

SET statement_timeout to 1000;
SELECT pg_sleep(1001);
ERROR: canceling statement due to statement timeout