Rudeboy AI
Killing long-running PostgreSQL processes

Photo by Chris Liverani on Unsplash

Killing long-running PostgreSQL processes

Rudeboy AI's photo
Rudeboy AI
·

1 min read

Commonly used commands for inspecting processes in PostgreSQL by querying the pg_stat_activity view in the pg_catalog schema.

Find all active processes.

SELECT * FROM pg_stat_activity WHERE state = 'active';

Cancel process by PID

pg_cancel_backend() cancels the running query and keeps the process running.

SELECT pg_cancel_backend(<insert-pid>);

Kill process by PID

pg_terminate_backend() kills the process and, thus, the database connection.

SELECT pg_terminate_backend(<insert-pid>);

Find all active processes running for more than 5 minutes

SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM
  pg_stat_activity
WHERE
  (now() - pg_stat_activity.query_start) > interval '5 minute'
  AND pid <> pg_backend_pid()
  AND state = 'active'
ORDER BY
  duration DESC;

Find and kill all active queries running for more than 5 minutes by ‘name’

Remember the pg_stat_activity-view includes processes that are not connections, such as WAL senders and receivers for replication.

SELECT
  pg_terminate_backend(pid)
FROM
  pg_stat_activity
WHERE
  (now() - pg_stat_activity.query_start) > interval '5 minute'
  AND pid <> pg_backend_pid()
  AND state = 'active'
  AND query like '%name%';
SQLPostgreSQLDatabases