Why does Postgres dropdb works but psql DROP DATABASE doesn't?

Question

I am writing a script that will drop PostgreSQL database. I do it like this:

echo "> Prevent connection of new users."
PGPASSWORD=$PG_PASSWORD_IMPORT psql \
    -h $PG_HOST_IMPORT -p $PG_PORT_IMPORT -U $PG_USER_IMPORT -d $PG_DATABASE_IMPORT \ 
    -c "REVOKE CONNECT ON DATABASE $PG_DATABASE_IMPORT FROM PUBLIC, $PG_USER_IMPORT;"

echo "> Terminate existing connections."
PGPASSWORD=$PG_PASSWORD_IMPORT psql \
    -h $PG_HOST_IMPORT -p $PG_PORT_IMPORT -U $PG_USER_IMPORT -d $PG_DATABASE_IMPORT \
    -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND datname = '$PG_DATABASE_IMPORT';"

echo "> Drop '$PG_DATABASE_IMPORT' database."
PGPASSWORD=$PG_PASSWORD_IMPORT dropdb \
    -h $PG_HOST_IMPORT -p $PG_PORT_IMPORT -U $PG_USER_IMPORT  $PG_DATABASE_IMPORT

and it works well.

But when I try to drop database using psql, then I get error ERROR: cannot drop the currently open database.

echo "> Prevent connection of new users."
PGPASSWORD=$PG_PASSWORD_IMPORT psql \
    -h $PG_HOST_IMPORT -p $PG_PORT_IMPORT -U $PG_USER_IMPORT -d $PG_DATABASE_IMPORT \
    -c "REVOKE CONNECT ON DATABASE $PG_DATABASE_IMPORT FROM PUBLIC, $PG_USER_IMPORT;"

echo "> Terminate existing connections."
PGPASSWORD=$PG_PASSWORD_IMPORT psql \
    -h $PG_HOST_IMPORT -p $PG_PORT_IMPORT -U $PG_USER_IMPORT -d $PG_DATABASE_IMPORT \
    -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND datname = '$PG_DATABASE_IMPORT';"

echo "> Drop '$PG_DATABASE_IMPORT' database."
PGPASSWORD=$PG_PASSWORD_IMPORT psql \
    -h $PG_HOST_IMPORT -p $PG_PORT_IMPORT -U $PG_USER_IMPORT -d $PG_DATABASE_IMPORT \
    -c "DROP DATABASE $PG_DATABASE_IMPORT;"

I find it strange, because according to the documentation, dropdb is a wrapper around the SQL command DROP DATABASE.

The issue is that when you open a session with a certain database, Postgres won't let you drop that database. But from the command line, there is no such restriction — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 13, 2019 at 10:12

Laurenz Albe · Accepted Answer · 2019-07-13 10:54:07Z

2

The dropdb documentation says:

--maintenance-db=dbname

Specifies the name of the database to connect to in order to drop the target database. If not specified, the postgres database will be used; if that does not exist (or is the database being dropped), template1 will be used.

dropdb connects to postgres, not to the database about to be dropped, that's why it works in this case.

answered Jul 13, 2019 at 10:54

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why does Postgres dropdb works but psql DROP DATABASE doesn't?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related