CODEWITHSUNDEEP
postgresql
zoran
zoran

Reputation: 1190

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

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.

Upvotes: 0

Views: 462

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246318

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.

Upvotes: 2

Related Questions