Sometimes a long running query in Postgres can get stuck and you need to kill it. This recipe outlines the steps to do so. A few notes:
- This recipe does not require console access to the server.
- You need a way to run a postgresql command. I used Navicat. psql or a Rails console work, too.
- Tested on Postgresql 9.2 on heroku postgres.
And here are the instructions:
- Get a list of all currently active connections on the Postgres server. Run the query below on the server:
SELECT * FROM pg_stat_activity ORDER BY client_addr ASC, query_start ASC
- Each row in the results table corresponds to a postgres process. Find the row for the process you want to kill by looking at the ‘current_query’ column.
- Record the process id. You can find it in the ‘procpid’ column from the row that contains the query you want to kill.
- Kill the process by running the following query (replace ‘
’ with the process id you found in step 3): `SELECT pg_cancel_backend( )`
- Reload the query from step 1 to confirm that the process is gone.