Monitoring and Diagnosis - PostgreSQL 9 Administration - page 193

Killing a specific session
Sometimes the only way to let the system as a whole continue is by terminating some
offending database sessions.
Getting ready
Again, this is a superuser-only capability, so log in as a superuser.
How to do it...
Once you have figured out the backend you need to kill, use the function named
pg_terminate_backend(processid) to actually kill it.
How it works...
When a backend executes the pg_terminate_backend(processid) function, it sends
a signal SIGQUIT to the backend given as an argument, after checking that the process
identified by the argument processid actually is a PostgreSQL backend.
The backend receiving this signal stops whatever it is doing, and terminates it in a
controlled way.
The client using that backend loses the connection to database. Depending on how it is
written, it may silently reconnect or it may show an error to the user.
There's more...
Killing the session may not always be what you really want, so consider other options as well.
Try to cancel the query first
You may want to try a milder version pg_cancel_backend(processid) first.
The difference between these two is that pg_cancel_backend() just cancels the current
query, whereas pg_terminate_backend() really kills the backend.
If the backend won't terminate
If pg_terminate_backend(processid ) won't kill the backend, and you really need to
reset the database state to make it continue processing requests, then you have yet another
option—sending SIGKILL to the offending backend.