Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

DROP DATABASE fails because it is the database you are connected to

Errorpq: cannot drop the currently open database

Short answer

PostgreSQL never drops the database your own session is connected to. Connect to postgres or another database and run DROP DATABASE there, or call the dropdb tool. If other sessions are still connected, DROP DATABASE name WITH (FORCE) disconnects them first.

  • Applies to PostgreSQL 16 and 17, Go 1.24 with lib/pq or pgx
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Ask the server which database your session is in. If it is the one you want to drop, no option of the command will help.

    psql "$DATABASE_URL" -c 'select current_database()'
  2. 2Match what you see, then jump to its fix

    1. 1pq: cannot drop the currently open database, and current_database() equals the name you are droppingThe connection that runs DROP DATABASE is connected to the targetFix
    2. 2The drop fails after you moved to postgres, because someone else is connectedOther sessions are still connected to the target databaseFix
    3. 3The drop still fails with WITH (FORCE) while no ordinary sessions are leftFORCE does not work because of prepared transactions or replicationFix

What does the error look like?

  • pq: cannot drop the currently open database

Cause 1 of 3

The connection that runs DROP DATABASE is connected to the target

1Check

In Go the usual cause is a *sql.DB opened with the target in the DSN, then used to drop that same database.

psql "$DATABASE_URL" -c 'select current_database()'

2Fix

Open a second connection to the postgres database (or any other one) and drop from there. The command line equivalent is dropdb, which wraps the same command and connects to the postgres database by default (or template1 if needed).

// Go: connect to the maintenance database, not to the target
admin, err := sql.Open("postgres", "postgres://user:pass@localhost:5432/postgres?sslmode=disable")
if err != nil { log.Fatal(err) }
defer admin.Close()
_, err = admin.Exec(`DROP DATABASE IF EXISTS mydb`)

# shell
dropdb --if-exists mydb

3Verify

The database is gone from the list.

psql postgres -c '\l'

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 3

Other sessions are still connected to the target database

1Check

List who is connected to the target. App servers, pools and an open psql tab all count.

psql postgres -c "select pid, usename, application_name from pg_stat_activity where datname = 'mydb'"

2Fix

Stop those clients, or use the FORCE option, which tries to terminate the existing connections before the drop. It only terminates sessions your role has permission to terminate with pg_terminate_backend.

DROP DATABASE mydb WITH (FORCE);

3Verify

The statement completes with no error.

psql postgres -c '\l'

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 3

FORCE does not work because of prepared transactions or replication

1Check

FORCE does not terminate anything if prepared transactions, active logical replication slots or subscriptions exist in the target database.

psql mydb -c 'select gid from pg_prepared_xacts'
psql mydb -c 'select slot_name from pg_replication_slots where database = current_database()'

2Fix

Finish or roll back the prepared transactions, and drop the replication slots or subscriptions, then repeat the drop.

-- run as a superuser or the owner, using the gid shown above
ROLLBACK PREPARED 'the-gid';

3Verify

DROP DATABASE now completes.

psql postgres -c 'DROP DATABASE mydb WITH (FORCE)'

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Keep a small admin connection string that points at the postgres database, and use it only for create and drop.
  • In test setups, close every pool for the target before dropping it.
  • Reserve WITH (FORCE) for test and throwaway databases, never for one in use.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.