<\/p>\n
You can use the following queries to monitor the performance of the database or to find and kill queries that cause lock.<\/p>\n
The following query returns queries that last longer than 5 minutes.<\/p>\n
SELECT\u00a0 pid,\u00a0 \r\nnow() - pg_stat_activity.query_start AS duration,\r\n\u00a0 query,\u00a0\r\nstate\u00a0 \r\nFROM pg_stat_activity\r\nWHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';<\/pre>\n<\/p>\n
You can find queries that have ExclusiveLock with the following query.<\/p>\n
SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid where mode ='ExclusiveLock';<\/pre>\n<\/p>\n
We have found your query with the help of the above queries.<\/p>\n
After learning the pid (process id) value of the query, we can stop the query with the help of the following query.<\/p>\n
SELECT pg_cancel_backend(__ pid__);<\/pre>\n<\/p>\n
It may take some time to stop the query completely using the pg_cancel_backend command.<\/p>\n
Or you can kill that session directly by using the command below.<\/p>\n
SELECT pg_terminate_backend(__ pid__);<\/pre>\nImportant Note:<\/strong>The pg_terminate_backend command can cause inconsistency.<\/p>\n\n
<\/div>\n