Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
You can use the following queries to monitor the performance of the database or to find and kill queries that cause lock.
The following query returns queries that last longer than 5 minutes.
1 2 3 4 5 6 | SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state FROM pg_stat_activity WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'; |
You can find queries that have ExclusiveLock with the following query.
1 | SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid where mode ='ExclusiveLock'; |
We have found your query with the help of the above queries.
After learning the pid (process id) value of the query, we can stop the query with the help of the following query.
1 | SELECT pg_cancel_backend(__ pid__); |
It may take some time to stop the query completely using the pg_cancel_backend command.
Or you can kill that session directly by using the command below.
1 | SELECT pg_terminate_backend(__ pid__); |
Important Note:The pg_terminate_backend command can cause inconsistency.
