How to 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?

What happens if you just run the `dropdb databasename` command from the command line? — Dylan Markow, Aug 16 '11 at 05:02
Use `psql -U -c "drop database protodb"` (without database name) — user, May 23 '14 at 12:36
This will restart postgres and disconnect everyone: sudo service postgresql restart Then do a: dropdb -h localhost -p 5432 -U "youruser" "testdb" Notice the "" to make sure special characters go in without a hitch. — unom, Jun 26 '14 at 06:11
1. `>psql -U postgres -h localhost` 2. `>drop database database;` — Nuriddin Kudratov, Nov 17 '21 at 22:43

score 568 · Accepted Answer · edited Nov 22 '21 at 18:33

568

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

edited Nov 22 '21 at 18:33

Robin

2,278
3
26
46

answered Aug 16 '11 at 05:11

csano

13,266
2
28
45

3

I'm using `dropuser` command to remove also the user. – pl1nk Sep 05 '12 at 15:26
6

Not helpful answer as for version 9. The error about opened connections appears still. – Pavel Vlasov Apr 17 '14 at 09:25
4

This will restart postgres and disconnect everyone: sudo service postgresql restart Then do a: dropdb -h localhost -p 5432 -U "youruser" "testdb" Notice the "" to make sure special characters go in without a hitch. – unom Jun 26 '14 at 06:10
2

using the postgres user: sudo -u postgres dropdb 'database name' – leszek.hanusz Jan 24 '15 at 12:06
`\l` to see all the databases you have. – Sandip Subedi Jun 04 '19 at 20:39
creating a connection from a different database than the one removing and executing this: `DROP DATABASE dbname WITH (FORCE); ` totally solves the problem!!! – DanielV Apr 13 '23 at 11:58

score 131 · Answer 2 · edited Aug 22 '18 at 09:40

131

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/

edited Aug 22 '18 at 09:40

Brugolo

4,685
3
22
14

answered Feb 22 '12 at 08:40

Eugene

1,435
1
10
4

12

Had to change it a bit to work: `select pg_terminate_backend(pid) from pg_stat_activity where datname='YourDatabase';` – mrt Aug 16 '13 at 09:47
`column "procpid" does not exist` for amazon RDS postgres 9.6 instance – fIwJlxSzApHEZIl May 09 '18 at 13:10
Hm. Ran this, but it just reconnected immediately, after saying, "SSL connection closed unexpectedly [...] attempting to reset: Succeeded." Annnd, I'm back. – mlissner Nov 12 '18 at 19:03

Bohemian · Answer 3 · 2011-08-16T06:17:52.720

83

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>';

edited Aug 16 '11 at 06:17

answered Aug 16 '11 at 05:04

Bohemian

412,405
93
575
722

Sorry I am new to databases, so this is probably a stupid question, but where do I type this out? Before logging into the database right? And I should replace databasename with the name of my database right? – iman453 Aug 16 '11 at 05:11
@iman453: You'd run that straight from your shell/command-line. – mu is too short Aug 16 '11 at 05:26
2

There's no such things as "just on the server" for postgresql. You must connect to a database. In this case you'll be connecting to the postgres database which is pretty much there just for cases like this. And a good point on prepared transactions, but in that case you should get an error message saying that's the issue. – Scott Marlowe Aug 17 '11 at 03:11
@Scott - no so. you can run "server" level commands *without* connecting to a specific database, such as drop/create database commands, as in the example in my answer. – Bohemian Aug 17 '11 at 04:15
1

Sorry Bohemian, but you are the one who's wrong. Here's pg_stat_activity while running createdb from the command line: postgres=# select * from pg_stat_activity ; 11564 | postgres | 22223 | 16384 | smarlowe | CREATE DATABASE test; | f | 2011-08-19 16:18:26.918933-06 | 2011-08-19 16:18:26.918933-06 | 2011-08-19 16:18:26.916578-06 | | -1 Note that this happens while running createdb from the command line in another terminal. That first field is the db that my createdb script was connected to – Scott Marlowe Aug 19 '11 at 22:19
For further proof, drop your postgres database and try the createdb script command. It'll then fail. – Scott Marlowe Aug 19 '11 at 22:22
Interesting. So if you don't specify a database, you still get connected to the default "postgres" database. OK - thanks for the info. I didn't realise that. Nice research btw +1 – Bohemian Aug 19 '11 at 22:30
yeah, the command line utils like createdb and createuser hit the maintenance db which by default is postgres. In older versions it was template1 but this often got in the way of createdb etc, so that was changed and the postgres db was added to handle this job. – Scott Marlowe Aug 24 '11 at 02:21
1

Why isn't this the top, #1 answer? – Henley Oct 31 '16 at 14:22
since my user isn't "postgres", I had to also add: `--dbname=postgres` to my command. – Taytay Aug 02 '17 at 22:24
1

note this does not work for Amazon RDS. It boots you from the `psql` connection and performs an automatic reset and then you log back in and you're back to where you started. – fIwJlxSzApHEZIl May 09 '18 at 13:53
This is the only answer that worked for me – sambecker Jan 27 '20 at 16:48
Variant of this answer when your PostgreSQL user `postgres` is "peer authenticated" from system user `postgres`, as is the default under Ubuntu: `sudo -u postgres psql -U postgres -c "drop database databasename"` – tanius Sep 15 '20 at 13:57

score 19 · Answer 4 · answered Aug 17 '11 at 03:10

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;

How to drop PostgreSQL database through command line

4 Answers4

Linked

Related