Kill a stuck PostgreSQL query from your iPhone
A runaway query is eating the server and you are away from your desk. HeapDeck finds it in Activity, shows what it runs and for how long, and stops it after one confirmation.
Updated · Vladimir Chemeris, the developer of HeapDeck
Cancel or terminate
PostgreSQL gives you two ways to stop a session’s work, and they do different things:
SELECT pg_cancel_backend(12345); -- stop the running query, keep the session
SELECT pg_terminate_backend(12345); -- end the session, roll back its transaction
Cancel interrupts the statement a session is running. The client gets an error for that statement, and the connection and any open transaction stay as they were. It is the gentle option, and it stops most runaway SELECT and UPDATE statements.
Terminate ends the backend process. PostgreSQL rolls back its open transaction and releases its locks, and the application sees its connection drop. Use it when cancel can’t help.
Cases where cancel does nothing
- The session is
idle in transaction. The session runs no statement, so cancel has nothing to stop, while its transaction holds locks and can keep vacuum from cleaning up. Terminate releases them. More in what idle in transaction means. - The query sits in code that doesn’t check for interrupts, for example deep in an extension’s C code or in a network call. PostgreSQL delivers both signals, and the backend acts on them at its next check. Terminate is the stronger request.
- Your role lacks the right. Without superuser, you can signal sessions of your own role or, as a member of
pg_signal_backend, of other non-superuser roles. Otherwise PostgreSQL answers with a permission error.
Don’t reach for kill -9 on the server. PostgreSQL treats a backend killed that way as a crash: it ends every session and runs crash recovery before it accepts connections again.
Finding the query to stop
SELECT pid, usename, state,
now() - query_start AS running_for,
left(query, 60) AS query
FROM pg_stat_activity
WHERE state <> 'idle' AND pid <> pg_backend_pid()
ORDER BY running_for DESC NULLS LAST;
On the phone, the Activity screen gives you this list: waiting sessions first, then the rest by duration. The Longest query tile on Health shows the oldest running statement and turns red at 5 minutes; tap it to open Activity. Filters narrow the list to idle in transaction or to sessions older than 10 seconds.
Stopping it from HeapDeck
Swipe the session’s row left for Cancel and Terminate, or open the session and use the buttons there. The confirmation shows the pid, its state, user@app, how long it has run, the database and the connection, with a PROD badge if you marked the connection as production.
- Cancel sends
pg_cancel_backend(pid). - Terminate sends
pg_terminate_backend(pid)after a red warning that the whole session ends. On a Production connection you type the pid to confirm.
Before a cancel goes out, HeapDeck reads the session again. If its statement has finished or the pid now belongs to another backend, HeapDeck sends nothing. HeapDeck reports the result in plain words: the statement got the cancel request, the session ended, the session was gone before the request arrived, or PostgreSQL refused, with its own message word for word.
If the query you want to stop blocks others, Locks shows the whole chain and how many sessions you free by stopping its root. If your role can’t signal a session, HeapDeck says so and gives you the command to hand to a DBA.
Free and Pro
Seeing the sessions, their SQL and what they wait for is free for one saved server. Cancel, terminate and any number of servers are part of HeapDeck Pro, a one-time purchase. HeapDeck’s own session has no actions.
Stop it from happening again
statement_timeoutcaps how long a single statement may run for a role or a database.idle_in_transaction_session_timeoutends sessions that sit in an open transaction too long.idle_session_timeout(PostgreSQL 14 and later) closes connections that stay idle outside a transaction.
Questions
What is the difference between pg_cancel_backend and pg_terminate_backend?
pg_cancel_backend stops the running statement and keeps the session and its transaction open. pg_terminate_backend ends the session and rolls back its transaction, which releases its locks.
Why is pg_terminate_backend not working?
Three common reasons: the backend sits in code that doesn't check for interrupts, such as a network call or an extension, and acts on the signal later; your role lacks the right and PostgreSQL returned a permission error; or you signalled the wrong pid, since a waiting session isn't the one holding the lock. Look for the root with pg_blocking_pids().
Can I kill a Postgres query from my phone?
Yes. In HeapDeck, swipe the session's row in Activity for Cancel or Terminate, or stop the root of a chain in Locks. Both actions are part of HeapDeck Pro.