Closed. This question is off-topic. It is not currently accepting answers. Want to improve this question? Update the question so it's on-topic for Stack Overflow. Closed 9 years ago. Improve this 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?
dropdb databasename
command from the command line?
psql -U <user> -c "drop database protodb"
(without database name)
drop database <dataabase_name>;
Don't forget the comma.
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 WITH (FORCE);
or
dropdb -f dbname
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";
select pg_terminate_backend(pid) from pg_stat_activity where datname='YourDatabase';
column "procpid" does not exist
for amazon RDS postgres 9.6 instance
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 '<id>';
psql
connection and performs an automatic reset and then you log back in and you're back to where you started.
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;
Success story sharing
dropuser
command to remove also the user.\l
to see all the databases you have.