CODEWITHSUNDEEP
databasepostgresql
josernestodavila
josernestodavila

Reputation: 397

Drop a database being accessed by another users?

I'm trying to drop a database from PgAdmin 3 and I get this error message:

ERROR: can't delete current database
SQL state: 55006

how can I force the delete/fix this error, of this database?

Upvotes: 16

Views: 37001

Answers (7)

Vanna
Vanna

Reputation: 2322

This post by Leeladharan Achar was helpful for me in working with this error.

It essentially boils down to:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'target_db'
  AND pid <> pg_backend_pid();

DROP DATABASE target_db;

Upvotes: 19

Walu.Q
Walu.Q

Reputation: 11

The easiest and perhaps the neatest fix is to go to services and stop the PostgreSQL server and then start it, and then run the drop database yourdbname; command again. That should disconnect any sessions and allow you to drop the current database.

Upvotes: 0

Ashnur
Ashnur

Reputation: 366

If you want to use the pgAdmin4 interface, you have to first delete/drop the db, then, before waiting for the error, you have to immediatelly disconnect from the same database, then it gets deleted without problem.

Upvotes: 2

klaxon
klaxon

Reputation: 415

Simplest fix for this is restart the postgresql. After that You can get rid of database!

Upvotes: 8

Maninder
Maninder

Reputation: 1919

The best method to drop user is below mentioned

Like i have a user name is "X" and it have access permission to database name : "Test" . And now we are created connection with "Test" database

If we try: drop user "X" this will be definality show below mentioned error:

ERROR: user "X" cannot be dropped because the user has a privilege on some object SQL State=55006

First of all connection should not be created with "Test" db.because it currently use so we are not able to delete the user. create connection with any of database except eg "Test" Now again try drop user test It should be worked fine on my side,let me know if you are facing issue on your side

Upvotes: 0

user80168
user80168

Reputation:

Instead of creating new database he can simply connect to postgres database, which is created by default in all new PostgreSQL installations. And even if it is not there - template1 should be always there.

Upvotes: 1

danieltalsky
danieltalsky

Reputation: 7940

Quick fix in PgAdmin: just create another empty database. Select it. Delete the first one. Voila.

You can also connect to the command line without selecting a specific database, and drop your database.

The problem here is not that other users are connected to the database, but that you are.

Upvotes: 16

Related Questions