Postgresql - unable to drop database because of some auto connections to DB

Question

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

User · Answer

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

User · Answer

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

User · Answer

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  lt  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

User · Answer

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

User · Answer

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

User · Answer

Simple as that  sudo service postgresql restart

User · Answer

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

User · Answer

If no potential impact on other services on your machine  simply service postgresql restart

User · Answer

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

User · Answer

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      lt IDLE gt   AND query NOT ILIKE   pg stat activity    ORDER BY query start desc      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

User · Answer

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

User · Answer

In macOS try to restart postgresql database through the console using the command   brew services restart postgresql

User · Answer

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  lt  gt  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

User · Answer

Stop your running application  in Eclipse  After you try again

User · Answer

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

User · Answer

Solution  1  Shut down Pg server 2  It will disconnect all active connection 3  Restart Pg Server 4  Try your command

User · Answer

GUI solution using pgAdmin 4 First enable show activity on dashboard if you haven t  File  gt  Preferences  gt  Dashboards  gt  Display  gt  Show Activity  gt  true  Now disable all the processes using the db   Click the DB name Click Dashboard  gt  Sessions Click refresh icon Click the delete  x  icon beside each process to end them  You should now be able to delete the db

[postgresql] Postgresql - unable to drop database because of some auto connections to DB

Examples related to postgresql

Examples related to postgresql-9.2

Examples related to postgresql-9.5