Postgresql - unable to drop database because of some auto connections to DB
PostgresqlPostgresql 9.2Postgresql 9.5Postgresql Problem Overview
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.
When I use:
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB';
I terminated the connection from that DB, but if I try to drop database after that somehow someone automatically connects to that database and gives this error. What could be doing that? No one uses this database, except me.
Postgresql Solutions
Solution 1 - Postgresql
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;
Solution 2 - Postgresql
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.
Solution 3 - Postgresql
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
Solution 4 - Postgresql
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.
Solution 5 - Postgresql
Simply check what is the connection, where it's coming from. You can see all this in:
SELECT * FROM pg_stat_activity WHERE datname = 'TARGET_DB';
Perhaps it is your connection?
Solution 6 - Postgresql
Update in Postgresql 13
You could just use this command to drop a Database forcefully, thus disconnecting each user/app connected to it.
DROP DATABASE db_name WITH (FORCE)
You could check the manual for more.
> FORCE - > Attempt to terminate all existing connections to the target database. It doesn't terminate if prepared transactions, active logical replication slots or subscriptions are present in the target database.
Solution 7 - Postgresql
GUI solution using pgAdmin 4
First enable show activity on dashboard if you haven't:
File > Preferences > Dashboards > Display > Show Activity > true
Now disable all the processes using the db:
- Click the DB name
- Click Dashboard > Sessions
- Click refresh icon
- Click the delete (x) icon beside each process to end them
You should now be able to delete the db.
Solution 8 - Postgresql
If no potential impact on other services on your machine, simply service postgresql restart
Solution 9 - Postgresql
Solution:
Solution 10 - Postgresql
Simple as that
sudo service postgresql restart
Solution 11 - Postgresql
In macOS try to restart postgresql database through the console using the command:
brew services restart postgresql
Solution 12 - Postgresql
REVOKE CONNECT
will not prevent the connections from the db owner or superuser. So if you don't want anyone to connect the db, follow command may be useful.
alter database pilot allow_connections = off;
Then use:
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'pilot';
Solution 13 - Postgresql
In my case, I am using AWS Redshift (based on Postgres). And it appears there are no other connections to the DB, but I am getting this same error.
ERROR: database "XYZ" is being accessed by other users
In my case, it seems the database cluster is still doing some processing on the database, and while there are no other external/user connections, the database is still internally in use. I found this by running the following:
SELECT * FROM stv_sessions;
So my hack was to write a loop in my code, looking for rows with my database name in it. (of course the loop is not infinite, and is a sleepy loop, etc)
SELECT * FROM stv_sessions where db_name = 'XYZ';
If rows found, proceed to delete each PID, one by one.
SELECT pg_terminate_backend(PUT_PID_HERE);
If no rows found, proceed to drop the database
DROP DATABASE XYZ;
Note: In my case, I am writing Java unit/system tests, where this could be considered acceptable. This is not acceptable for production code.
Here is the complete hack, in Java (ignore my test/utility classes).
int i = 0;
while (i < 10) {
try {
i++;
logStandardOut("First try to delete session PIDs, before dropping the DB");
String getSessionPIDs = String.format("SELECT stv_sessions.process, stv_sessions.* FROM stv_sessions where db_name = '%s'", dbNameToReset);
ResultSet resultSet = databaseConnection.execQuery(getSessionPIDs);
while (resultSet.next()) {
int sessionPID = resultSet.getInt(1);
logStandardOut("killPID: %s", sessionPID);
String killSessionPID = String.format("select pg_terminate_backend(%s)", sessionPID);
try {
databaseConnection.execQuery(killSessionPID);
} catch (DatabaseException dbEx) {
//This is most commonly when a session PID is transient, where it ended between my query and kill lines
logStandardOut("Ignore it, you did your best: %s, %s", dbEx.getMessage(), dbEx.getCause());
}
}
//Drop the DB now
String dropDbSQL = String.format("DROP DATABASE %s", dbNameToReset);
logStandardOut(dropDbSQL);
databaseConnection.execStatement(dropDbSQL);
break;
} catch (MissingDatabaseException ex) {
//ignore, if the DB was not there (to be dropped)
logStandardOut(ex.getMessage());
break;
} catch (Exception ex) {
logStandardOut("Something went wrong, sleeping for a bit: %s, %s", ex.getMessage(), ex.getCause());
sleepMilliSec(1000);
}
}
Solution 14 - Postgresql
In my opinion there are some idle queries running in the backgroud.
- Try showing running queries first
> SELECT pid, age(clock_timestamp(), query_start), usename, query
> FROM pg_stat_activity
> WHERE query != '
- kill idle query ( Check if they are referencing the database in question or you can kill all of them or kill a specific using the pid from the select results )
> SELECT pg_terminate_backend(procpid);
Note: Killing a select query doesnt make any bad impact
Solution 15 - Postgresql
first:
sudo systemctl restart postgresql
then:
drop database DATABASE_NAME;
Solution 16 - Postgresql
While I found the two top-upvoted answers useful on other occasions, today, the simplest way to resolve the issue was to realize that PyCharm might be keeping a session open, and if I clicked Stop
in PyCharm, that might help. With pgAdmin4 open in the browser, I did so, and almost immediately saw the Database sessions stats drop to 0, at which point I was able to drop the database.
Solution 17 - Postgresql
If you encounter this error in IntelliJ make sure you close the connection in all windows by clicking the button shown below
Solution 18 - Postgresql
In my case, I continued to get the error even after using the below command - because another user connection was immediately created after execution.
REVOKE CONNECT ON DATABASE <db_name> FROM public;
What fixed it for me was using inferno's solution above (also below) to prevent connections.
ALTER DATABASE <db_name> allow_connections = off
That allowed me to terminate the process without the process immediately being recreated.
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'TARGET_DB' -- ← change this to your DB AND pid <> pg_backend_pid();
Solution 19 - Postgresql
If you are using docker to run postgresql server, restart the container.
Solution 20 - Postgresql
For me, I just restart postgresql.
systemctl restart postgresql
Solution 21 - Postgresql
This is what worked for us on postgres 12. Using pgadmin, pgbouncer, and multiple client applications.
REVOKE CONNECT ON DATABASE <mydbname> FROM public;
ALTER DATABASE <mydbname> allow_connections = off;
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '<mydbname>';
DROP DATABASE <mydbname>;
Solution 22 - Postgresql
In terminal try this command:
ps -ef | grep postgres
you will see like:
> 501 1445 3645 0 12:05AM 0:00.03 postgres: sasha dbname > [local] idle
The third number (3645) is PID.
You can delete this
sudo kill -9 3645
And after that start your PostgreSQL connection.
Start manually:
pg_ctl -D /usr/local/var/postgres start
Solution 23 - Postgresql
Stop your running application.(in Eclipse) After you try again.
Solution 24 - Postgresql
What you need to be certain is that the service using the DB is not running.
Experienced same issue, running some Java apps, and none of the above options worked, not even restart.
Run a ps aux
kill the main service using the DB.
kill -9 'PID'
of the application- or if the application runs as a service make sure to run the
service stop
cmd for your OS.
After that the default way to drop a table will work flawlessly.
In my example were issues with