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);