How to drop a PostgreSQL database if there are active connections to it

Question

I need to write a script that will drop a PostgreSQL database  There may be a lot of connections to it  but the script should ignore that   The standard DROP DATABASE db name query doesn t work when there are open connections    How can I solve the problem

User · Answer

In my case i had to execute a command to drop all connections including my active administrator connection

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = current_database()

which terminated all connections and show me a fatal ''error'' message :

FATAL: terminating connection due to administrator command SQL state: 57P01

After that it was possible to drop the database

User · Answer

In Linux command Prompt  I would first stop all postgresql processes that are running  by tying this command    sudo  etc init d postgresql restart  type the command     bg to check if other postgresql processes are still running   then followed by dropdb dbname to drop the database  sudo  etc init d postgresql restart bg dropdb dbname   This works for me on linux command prompt

User · Answer

I just restart the service in Ubuntu to disconnect connected clients   sudo service postgresql stop sudo service postgresql start  psql DROP DATABASE DB NAME

User · Answer

Depending on your version of postgresql you might run into a bug  that makes pg stat activity to omit active connections from dropped users  These connections are also not shown inside pgAdminIII    If you are doing automatic testing  in which you also create users  this might be a probable scenario    In this case you need to revert to queries like     SELECT pg terminate backend procpid    FROM pg stat get activity NULL  integer    WHERE datid  SELECT oid from pg database where datname    your database      NOTE  In 9 2  you ll have change procpid to pid

User · Answer

PostgreSQL 13 introduced FORCE option   DROP DATABASE DROP DATABASE drops a database     Also  if anyone else is connected to the target database  this command will fail unless you use the FORCE option described below  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   DROP DATABASE db name WITH  FORCE

User · Answer

Here s my hack     D    Make sure no one can connect to this database except you  sudo -u postgres  usr pgsql-9 4 bin psql -c  UPDATE pg database SET datallowconn false WHERE datname   lt DATABASE NAME gt        Drop all existing connections except for yours  sudo -u postgres  usr pgsql-9 4 bin psql -c  SELECT pg terminate backend pg stat activity pid  FROM pg stat activity WHERE pg stat activity datname     lt DATABASE NAME gt   AND pid  lt  gt  pg backend pid        Drop database   D sudo -u postgres  usr pgsql-9 4 bin psql -c  DROP DATABASE  lt DATABASE NAME gt      I put this answer because include a command  above  to block new connections and because any attempt with the command     REVOKE CONNECT ON DATABASE  lt DATABASE NAME gt  FROM PUBLIC   lt USERS ETC gt         do not works to block new connections   Thanks to  araqnid  GoatWalker    D  https   stackoverflow com a 3185413 3223785

User · Answer

PostgreSQL 9 2 and above   SELECT pg terminate backend pid FROM pg stat activity WHERE datname    YOUR DATABASE NAME HERE

User · Answer

On Windows  You can open the Task Manager - gt  Services  find the  quot postgresql quot  process  and restart it  When service will running again the database can be removed

User · Answer

This will drop existing connections except for yours   Query pg stat activity and get the pid values you want to kill  then issue SELECT pg terminate backend pid int  to them   PostgreSQL 9 2 and above   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  lt  gt  pg backend pid      PostgreSQL 9 1 and below   SELECT pg terminate backend pg stat activity procpid  FROM pg stat activity WHERE pg stat activity datname    TARGET DB  --   change this to your DB   AND procpid  lt  gt  pg backend pid      Once you disconnect everyone you will have to disconnect and issue the DROP DATABASE command from a connection from another database aka not the one your trying to drop   Note the renaming of the procpid column to pid  See this mailing list thread

User · Answer

I noticed that postgres 9 2 now calls the column pid rather than procpid   I tend to call it from the shell      usr bin env bash   kill all connections to the postgres server if   -n   1      then   where  where pg stat activity datname     1     echo  killing all connections to database   1   else   echo  killing all connections to database  fi  cat  lt  lt -EOF   psql -U postgres -d postgres  SELECT pg terminate backend pg stat activity pid  FROM pg stat activity   where  EOF   Hope that is helpful  Thanks to  JustBob for the sql

User · Answer

You could kill all connections before dropping the database using the pg terminate backend int  function    You can get all running backends using the system view pg stat activity   I m not entirely sure  but the following would probably kill all sessions   select pg terminate backend procpid  from pg stat activity where datname    doomed database    Of course you may not be connected yourself to that database

User · Answer

Nothing worked for me except  I loggined using pgAdmin4 and on the Dashboard I disconnected all connections except pgAdmin4 and then was able to rename by right lick on the database and properties and typed new name

User · Answer

In PostgreSQL 9 2 and above  to disconnect everything except your session from the database you are connected to   SELECT pg terminate backend pg stat activity pid  FROM pg stat activity WHERE datname   current database     AND pid  lt  gt  pg backend pid      In older versions it s the same  just change pid to procpid  To disconnect from a different database just change current database   to the name of the database you want to disconnect users from   You may want to REVOKE the CONNECT right from users of the database before disconnecting users  otherwise users will just keep on reconnecting and you ll never get the chance to drop the DB  See this comment and the question it s associated with  How do I detach all other users from the database   If you just want to disconnect idle users  see this question

[postgresql] How to drop a PostgreSQL database if there are active connections to it?

Examples related to postgresql