Stopping Running Queries in PostgreSQL
pg_stat_activity
, then cancel them gracefully with pg_cancel_backend(pid)
or terminate them forcefully with pg_terminate_backend(pid)
.Occasionally, a query in PostgreSQL may run for too long, consume excessive resources, or become stuck. When this happens, you need a way to stop it without restarting the entire database. The process involves two main steps: finding the Process ID (PID) of the runaway query and then using a command to cancel or terminate it.
1. Find the Query’s Process ID (PID)
You can view all currently running activities by querying the pg_stat_activity
view. To see only the active queries, you can filter by the state
column.
sql code snippet start
SELECT pid, query_start, now() - query_start AS duration, query, state
FROM pg_stat_activity
WHERE state = 'active';
sql code snippet end
This command will list all active queries, showing their process ID (pid
), when they started, their total duration
, and the query text itself. Identify the pid
of the query you wish to stop.
2. Cancel the Query (Graceful Method)
The safest way to stop a query is to cancel it using pg_cancel_backend(pid)
. This function sends a SIGINT
signal to the backend process, which attempts to stop the specific query gracefully while keeping the database connection alive. The user’s session is preserved, and they can run other queries.
sql code snippet start
SELECT pg_cancel_backend(<pid of the process>);
sql code snippet end
This should always be your first choice.
3. Terminate the Backend (Forceful Method)
If pg_cancel_backend()
doesn’t work or the process is stuck in a state where it cannot be canceled (e.g., idle in a transaction), you can use the more forceful pg_terminate_backend(pid)
. This function sends a SIGTERM
signal, which will terminate the entire backend process and close the associated connection.
sql code snippet start
SELECT pg_terminate_backend(<pid of the process>);
sql code snippet end
Use this command with caution, as it abruptly ends the entire session, which might cause the client application to see a dropped connection and could lead to transaction rollbacks.