Postgresql - unable to drop database because of some auto connections to DB
Whenever I try to drop database I get:
ERROR: database "pilot" is being accessed by other users
DETAIL: There is 1 other session using the database.
First You need to revoke
REVOKE CONNECT ON DATABASE TARGET_DB FROM public;
Then use:
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB';
It will surely work.
I found a solution for this problem try to run this command in terminal
ps -ef | grep postgres
kill process by this command
sudo kill -9 PID
You can prevent future connections:
REVOKE CONNECT ON DATABASE thedb FROM public;
(and possibly other users/roles; see \l+
in psql
)
You can then terminate all connections to this db except your own:
SELECT pid, pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = current_database() AND pid <> pg_backend_pid();
On older versions pid
was called procpid
so you'll have to deal with that.
Since you've revoked CONNECT
rights, whatever was trying to auto-connect should no longer be able to do so.
You'll now be able to drop the DB.
This won't work if you're using superuser connections for normal operations, but if you're doing that you need to fix that problem first.
After you're done dropping the database, if you create the database again, you can execute below command to restore the access
GRANT CONNECT ON DATABASE thedb TO public;
It means another user is accessing the database. Simply restart PostgreSQL. This command will do the trick
root@kalilinux:~#sudo service postgresql restart
Then try dropping the database:
postgres=# drop database test_database;
This will do the trick.