Terminating (kill) database connection, specific session

PostgreSQL

Find session ID (pid)

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:

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

Kill all sessions except pg_backend_pid (our own PID)

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

SHOW FULL PROCESSLIST;

KILL 123456;

Some helpful commands:

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

• Terminate (kill) specific session in PostgreSQL database
• How to terminate PostgreSQL database connections