- This topic has 1 reply, 1 voice, and was last updated 5 years, 8 months ago by
.
-
Posts
-
-
Sometimes when you try to delete / drop a PostgreSQL database, it doesn’t work. To fix this, you have to drop the database from the command prompt using this command:
dropdb –username=xxx –password=yyy zzz
Where xxx is the username of an account with permission to drop the database, yyy is the password for that user account and zzz is the name of the database you are trying to drop.
You will then be asked to re-enter your password and the database will then drop.
If this doesn’t work, restart the postgresql-x64-9.1 – PostgreSQL Server 9.1 service from the services.msc mmc console, then immediately run the command to drop the database again (before doing anything else).
If this doesn’t work, you will get an error explaining why you can’t drop the database. Typically this is because there’s an active connection to the database.
-
❗ 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.
-
- You must be logged in to reply to this topic.