Cancel vs Terminate PostgreSQL Backends Explained

calendar Apr 14, 2026 / · 4 min read · postgresql administration database performance pg_stat_activity  ·
Share on: linkedin copy

Cancel vs Terminate PostgreSQL Backends: What Every DBA Should Know

PostgreSQL gives you two tools for dealing with problem backends: pg_cancel_backend() and pg_terminate_backend(). They sound similar but behave very differently. Using the wrong one can disrupt users unnecessarily, or leave a problematic connection in place when you needed it gone entirely.

This guide explains both functions, when to use each, and how to identify the right PIDs to target.

Sample Code

Cancel a running query (leaves the connection open):

1SELECT pg_cancel_backend(pid);

Terminate a backend entirely (closes the connection):

1SELECT pg_terminate_backend(pid);

Find PIDs to target using pg_stat_activity:

 1SELECT
 2    pid,
 3    usename,
 4    application_name,
 5    client_addr,
 6    state,
 7    wait_event_type,
 8    wait_event,
 9    now() - query_start AS query_duration,
10    left(query, 100)    AS query_preview
11FROM
12    pg_stat_activity
13WHERE
14    pid <> pg_backend_pid()
15    AND state <> 'idle'
16ORDER BY
17    query_duration DESC NULLS LAST;

Cancel or terminate multiple sessions at once:

 1-- Cancel all queries running longer than 5 minutes
 2SELECT pg_cancel_backend(pid)
 3FROM pg_stat_activity
 4WHERE state = 'active'
 5  AND query_duration > interval '5 minutes'
 6  AND pid <> pg_backend_pid();
 7
 8-- Terminate all connections from a specific application
 9SELECT pg_terminate_backend(pid)
10FROM pg_stat_activity
11WHERE application_name = 'bad_app'
12  AND pid <> pg_backend_pid();

Notes: Both functions take a process ID (PID) as their argument and return true if the signal was sent successfully, or false if the backend no longer exists. Returns false rather than an error when the PID is gone.

Code Breakdown

  • pg_cancel_backend(pid) — Sends a SIGINT signal to the target backend. This cancels the current query but leaves the database connection open. The client receives a cancellation error and can immediately run another query.
  • pg_terminate_backend(pid) — Sends a SIGTERM signal to the target backend. This terminates the entire connection. The client loses its connection and must reconnect.
  • pg_backend_pid() — Returns the PID of your own session. The pid <> pg_backend_pid() condition prevents you from accidentally canceling or terminating your own connection.
  • pg_stat_activity — The system view that lists all active backends. Used here to find PIDs worth targeting and to filter by state, duration, user, or application.
  • state <> 'idle' — Focuses on backends that are actually doing something. Idle connections are not running queries, so canceling them has no effect.
  • now() - query_start — Calculates how long the current query has been running. Useful for finding long-running queries.
  • left(query, 100) — Truncates the query text to 100 characters for readable output. Full query text is in the query column.

Key Points

  • pg_cancel_backend() is the gentler option. Use it first. If the query is stuck in a wait state (not actively running), it may not respond to a cancel signal.
  • pg_terminate_backend() always works for clearing a connection but forces the client to reconnect. Use it for idle-in-transaction sessions, sessions holding locks, or connections that did not respond to a cancel.
  • Both functions return false (not an error) if the PID no longer exists by the time the signal is sent. This is safe — it just means the backend already finished.

Insights and Explanations

When cancel does not work:

pg_cancel_backend() sends a cancel signal, but the backend only checks for it at certain points. If a query is stuck waiting on a lock — shown as state = 'active' with wait_event_type = 'Lock' in pg_stat_activity — the cancel signal will not take effect until the lock is acquired. In that case, you need to resolve the lock conflict or use pg_terminate_backend().

Idle in transaction:

A session in state idle in transaction has started a transaction but is not currently running a query. It may be holding locks that block other sessions. A cancel signal does nothing here because there is no active query to cancel. Use pg_terminate_backend() to clear these sessions:

1SELECT pg_terminate_backend(pid)
2FROM pg_stat_activity
3WHERE state = 'idle in transaction'
4  AND now() - state_change > interval '10 minutes';

The difference in what the client sees:

  • After pg_cancel_backend(): the client gets ERROR: canceling statement due to user request. The connection stays alive.
  • After pg_terminate_backend(): the client gets FATAL: terminating connection due to administrator command. The connection drops.

Additional Considerations

  • Permissions: Superusers can cancel or terminate any backend. Regular users can only cancel or terminate their own backends. To let a non-superuser manage other sessions, grant the pg_signal_backend role (PostgreSQL 14+).
  • Replication connections: Terminating a replication backend will briefly disconnect a standby. It will reconnect automatically, but replication lag may spike.
  • Autovacuum workers: You can cancel autovacuum workers with pg_cancel_backend(). PostgreSQL will restart the autovacuum process. Only do this if the autovacuum is actively blocking other work.

References

Posts in this series