PostgreSQL Drop PostgreSQL database through command line

Question

I m trying to drop my database and create a new one through the command line   I log in using psql -U username and then do a  connect template1  followed by a DROP DATABASE databasename    I get the error     database databasename is being accessed by other users   I shut down Apache and tried this again but I m still getting this error  Am I doing something wrong

User · Accepted Answer

You can run the dropdb command from the command line:

dropdb 'database name'

Note that you have to be a superuser or the database owner to be able to drop it.

You can also check the pg_stat_activity view to see what type of activity is currently taking place against your database, including all idle processes.

SELECT * FROM pg_stat_activity WHERE datname='database name';

Note that from PostgreSQL v13 on, you can disconnect the users automatically with

DROP DATABASE dbname FORCE;

or

dropdb -f dbname

User · Answer

When it says users are connected  what does the query  select   from pg stat activity   say   Are the other users besides yourself now connected   If so  you might have to edit your pg hba conf file to reject connections from other users  or shut down whatever app is accessing the pg database to be able to drop it   I have this problem on occasion in production   Set pg hba conf to have a two lines like this   local   all         all                               ident host    all         all         127 0 0 1 32          reject   and tell pgsql to reload or restart  i e  either sudo  etc init d postgresql reload or pg ctl reload  and now the only way to connect to your machine is via local sockets   I m assuming you re on linux   If not this may need to be tweaked to something other than local   ident on that first line  to something like host     yourusername   Now you should be able to do   psql postgres drop database mydatabase

User · Answer

Try this  Note there s no database specified - it just runs  on the server   psql -U postgres -c  drop database databasename    If that doesn t work  I have seen a problem with postgres holding onto orphaned prepared statements  To clean them up  do this   SELECT   FROM pg prepared xacts    then for every id you see  run this   ROLLBACK PREPARED   lt id gt

User · Answer

This worked for me   select pg terminate backend pid  from pg stat activity where datname  YourDatabase     for postgresql earlier than 9 2 replace pid with procpid  DROP DATABASE  YourDatabase     http   blog gahooa com 2010 11 03 how-to-force-drop-a-postgresql-database-by-killing-off-connection-processes

[database] PostgreSQL: Drop PostgreSQL database through command line

Examples related to database

Examples related to postgresql