Debugging stuck queries in postgres (lock)

From: https://stackoverflow.com/questions/26596566/postgres-database-locked-queries-running-forever

You should check for locks:

SELECT l.*,a.*
  FROM pg_locks l
  JOIN pg_stat_activity a USING (pid)
 WHERE NOT granted;

You'll see a list of waiting sessions. And the following:

SELECT l.*,a.*
  FROM pg_locks l
  JOIN pg_stat_activity a USING (pid)
 WHERE granted
   AND (database,relation) IN (SELECT database,relation
                                 FROM pg_locks WHERE NOT granted);

will give you a list of blocking sessions. If you use psql, use expanded output to get column-per-row output, better to view such information.

I advise you to also have a look at this wiki page and this question: Postgresql DROP TABLE doesn't work (though it speaks bout DROP TABLE there, it might help).

In your case, I recommend to identify blocking sessions and try to find out why they're blocking. Most typical case in my experience — somebody forgot to press enter after COMMIT and went out for lunch. If you're sure this will not hurt your system, you can kill blocking session:

SELECT pg_terminate_backend(pid);

benregn/debug_postgres_locks.md