Monitoring and Diagnosis - PostgreSQL 9 Administration - page 194

This can be done only from the command line, as root or user postgres on the same host the
database is running by executing the following:
kill -9 <backendpid>
which kills that backend immediately without giving it a chance to clean up, therefore forcing
the postmaster to also kill all other backends and to restart the whole cluster.
Therefore, it actually does not matter which of the PostgreSQL backends you kill.
But beware that in case you have set the parameter synchronous_commit to off , you may
end up losing some supposedly committed transactions if you kill -9 a backend.
So kill -9 is the last resort thing to be done, only if nothing else helps, and not on a
regular basis.
Use statement timeout to clean up queries which take too long
Often you know that you don't have any use for queries running more than x times. Maybe
your web frontend just refuses to wait for more than 10 seconds for a query to complete and
returns some default answer to users if it takes longer, abandoning the query.
In such a case, it is a good idea to set statement_timeout = 15 sec either in
postgresql.conf or as a per user or per database setting, so that queries running
too long don't consume precious resources and make others' queries fail as well.
The queries terminated by statement timeout show up in log as follows:
hannu=# set statement_timeout = '3 s';
SET
hannu=# select wait(10);
ERROR: canceling statement due to statement timeout
They used to show up as a more confusing "query canceled due to user request" on the older
version of PostgreSQL.
Killing "Idle in transaction" queries
Sometimes, people start a transaction, run some queries, and then just leave without ending
the transaction. This can leave some system resources in a state where some housekeeping
processes can't be run or they may even have done something more serious, such as locking
a table, thereby causing immediate denial of service for other users needing that table.
You can use the following query to kill all backends that have an open transaction but have
been doing nothing for the last 10 minutes:
select pg_terminate_backend(procpid)
from pg_stat_activity
where current_query = '<IDLE> in transaction'
and current_timestamp - query_start > '10 min';