How to kill a query (or connection) in PostgreSQL
Two functions, one escalation path: pg_cancel_backend(pid) cancels the running query and leaves the connection alive; pg_terminate_backend(pid) closes the whole connection. Start with cancel.
-- 1. Trova il pid (vedi la guida "show running queries" per di più):
SELECT pid, state, now() - query_start AS runtime, left(query, 100)
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY query_start;
-- 2. Annulla la query (gentile — la sessione resta connessa):
SELECT pg_cancel_backend(12345);
-- 3. Se non basta, chiudi la connessione:
SELECT pg_terminate_backend(12345);Which one, when
| Situation | Use |
|---|---|
| A query is slow / blocking others, the session is otherwise fine | pg_cancel_backend |
Session stuck idle in transaction holding locks (nothing running to cancel) | pg_terminate_backend — cancel does nothing here: there is no query to cancel, it's the open transaction that hurts |
| Runaway session reconnecting and re-issuing the query | Terminate, then fix the client — killing it in a loop is not a strategy |
Both return true if the signal was sent (not a guarantee the query died instantly — cancellation is checked at safe points, so a query stuck in an uninterruptible kernel call can take a moment). The victim receives SQLSTATE 57014 ("canceling statement due to user request") or, when terminated, its client sees the connection drop.
Bulk cleanup, done carefully
-- Termina tutte le sessioni "idle in transaction" da più di 10 minuti:
SELECT pid, pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
AND xact_start < now() - interval '10 minutes';Run the SELECT without the pg_terminate_backend call first and read the list — then, and only then, add the kill. For a permanent fix, set idle_in_transaction_session_timeout instead of running this by hand at every incident.
What never to do
Never kill -9 a PostgreSQL backend process from the shell. A SIGKILLed backend cannot clean up its shared memory state, so the postmaster must assume corruption and restarts the entire cluster, disconnecting everyone and replaying WAL. What looked like killing one query becomes a full outage. A plain kill <pid> (SIGTERM) is equivalent to pg_terminate_backend and is acceptable when you can't get a connection — but the SQL functions are always preferable because they can't hit the wrong process.
Permissions: you can always cancel/terminate your own sessions; for other users' sessions you need membership in pg_signal_backend (or superuser).