Post

Terminating (kill) database connection, specific session

Posted Updated
Preview Image
By Lam Ngoc Khuong
views 1 min read

PostgreSQL

Find session ID (pid)

1
2
3
4

SELECT *
FROM pg_stat_activity
WHERE datname = 'dbname' # postgres database
AND usename = 'apiuser'  # postgres username

Kill session

  • pg_cancel_backend(pid): Terminate a query but keep the connection alive
  • pg_terminate_backend(pid): Terminate a query and kill the connection

Kill session by pid:

1
2
3

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid = '123456';

Kill all sessions except pg_backend_pid (our own PID)

1
2
3
4
5

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND datname = 'dbname' # postgres database
AND usename = 'apiuser'  # postgres username

MySQL

1
2
3

SHOW FULL PROCESSLIST;

KILL 123456;

Some helpful commands:

1
2
3

SELECT concat('KILL ',id,';') FROM information_schema.processlist WHERE user = 'root';

SELECT concat('KILL ',id,';') FROM information_schema.processlist WHERE user = 'root' INTO outfile '/tmp/out.txt';

References

Database
mysql postgres
This post is licensed under CC BY 4.0 by the author.