In MySQL you can run “show processlist;” or “show full processlist;” to get a list of all of the queries that are running on the system and how long they have been running. If you have a runaway query, you can get the process id and kill it from within MySQL.
kill [process id];
Postgres works in a similar fashion. One way to show the running queries in Postgres is with the pg_stat_activity function. You can do a “select * from pg_stat_activity;” and see a lot of information about the connections and queries that are running. There is a pid column that denotes the process id. Instead of using the kill command which sysadmins are already familiar with from working in a *nix command line, Postgres uses a function called pg_terminate_backend. You can run the below command once you have the pid of the query/connection you want to kill.
The advantage Postgres gets by using a function is that you can easily expand what pids get killed based on a where clause against pg_stat_activity. For instance, if I wanted to kill all connections to the database ‘testdb’, I could run the below command.
select pg_terminate_backend(pid) from pg_stat_activity where datname = 'testdb';
You can alter the above query to utilize whatever where clause you’d like to match. I can see a lot of value in matching on the below list for various reasons:
- Database names
- Application names
- Duration (now minus query start)
- The waiting flag
- Query State
- Contents within the query column
The method Postgres uses isn’t as intuitive as what I would expect as a sysadmin or coming from MySQL, but I can see a lot of value to the way it is implemented and think it could be more efficiently used to manage runaway queries than what I am familiar with.