I was involved in a case today where I had to drop a Postgresql database using the command dropdb -U database_user database but it was giving me this error,
dropdb: database removal failed: ERROR: database “Enter Database Name Here” is being accessed by other users.
Intially, I thought it was because i was using ipython to access the database by making some django querries, but even when stopped ipython, the particular error was still persisting.
After a bit of googling, I found out that using select * from pg_stat_activity, i can get the process ids of all the ongoing connections with the database. This was a local database, so the case of other network users accessing the database was out of question.
Next step was to kill -9 all the process ids (procpid) associated with the select * from pg_stat_activity command.
That solved the problem and I could successfully drop the database.