Stop (long) running SQL query in PostgreSQL when session or requests no longer exist?

To stop the query:

SELECT pg_cancel_backend(procpid);

To kill the database connection:

SELECT pg_terminate_backend(procpid);

To get an overview of the current transactions, to get the proced id's:

SELECT * FROM pg_stat_activity;

http://www.postgresql.org/docs/current/interactive/functions-admin.html

Considering your psql configured to run and connect to current dev database this one liner comes handy in development when testing complicated queries which can hung, just kills whatever runs:

If not configured properly - add flags for user/password/database

psql -c "SELECT procpid FROM pg_stat_activity;" -t | xargs -n1 -I {} psql -c "SELECT pg_cancel_backend({})"