❗ I didn’t realise that I’d already encountered this problem, so I worked out how to solve it and wrote this article. I just noticed that I’d already wrote an article, but I’ll share what I’d written up because it looks at the problem from a different perspective, shares more info on why the database can’t be deleted and also a different solution.
Here’s how to delete a PostgreSQL database, to get around an annoying message telling you that you cannot drop / delete the database.
First the problem: You open PG Admin, right click the database you want to delete and click drop / delete database. It then gives you an error message saying that the database is in use and there is an active connection. You check for running processes and all you can see is possibly an autovacuum, which you kill and try again. You still cannot delete the database.
You also try deleting the PostgreSQL database using drop database databaseName; which doesn’t work.
The solution: Rather ingeniously PostgreSQL has a clever interface that specifies that you must connect to the database to do things to it, but when you click delete / drop, it doesn’t work because you’re connected to it. Brilliant.
To get around this, create another database (if you don’t already have another database other than the one you want to delete), then restart the PostgreSQL service. Load up PG Admin and WITHOUT CLICKING ON THE DATABASE YOU CANNOT DELETE, click on the database you just created and run the SQL Query interface. Run drop database databaseName; and the database should now be deleted.