Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

DROP DATABASE fails because other sessions are connected

ErrorERROR: database "mydb" is being accessed by other users

Short answer

PostgreSQL refuses DROP DATABASE while anyone else is connected to it. On a current server, connect to another database and run DROP DATABASE name WITH (FORCE), which terminates the other sessions for you. If FORCE is not available, terminate the sessions with pg_terminate_backend first, and stop apps that reconnect.

  • Applies to PostgreSQL 16, PostgreSQL 17, psql
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Connect to a different database (postgres works) and list who is attached to the target. The usename, application_name and client_addr columns show what to stop.

    psql -d postgres -c "SELECT pid, usename, application_name, client_addr, state
    FROM pg_stat_activity WHERE datname = 'mydb';"
  2. 2Match what you see, then jump to its fix

    1. 1Rows come back from pg_stat_activity and the server is a recent versionOrdinary sessions are open and FORCE can end themFix
    2. 2pg_terminate_backend returns true, but a new session appears and the drop still failsAn app or pooler reconnects between your kill and your dropFix
    3. 3pg_terminate_backend returns an error about permissions, or falseYour role is not allowed to end those sessionsFix

What does the error look like?

  • ERROR: database "mydb" is being accessed by other users

Cause 1 of 3

Ordinary sessions are open and FORCE can end them

1Check

The manual says DROP DATABASE cannot run while connected to the target, and fails if anyone else is connected unless the FORCE option is used. FORCE tries to terminate all existing connections, covering those the current user may terminate with pg_terminate_backend.

psql -d postgres -c "SHOW server_version;"

2Fix

Run the command from another database. It cannot be run inside a transaction block.

DROP DATABASE IF EXISTS mydb WITH (FORCE);

3Verify

The database is gone from the list.

psql -d postgres -l

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 3

An app or pooler reconnects between your kill and your drop

1Check

pg_terminate_backend sends a signal to a session. Something that retries connections can be back before the next statement. Look at application_name and client_addr from the first step to find it.

SELECT pid, application_name, client_addr, backend_start
FROM pg_stat_activity WHERE datname = 'mydb';

2Fix

Stop the app or pooler first. Or forbid new connections to the database, terminate the remaining sessions, and drop it right after. ALLOW_CONNECTIONS false means no one can connect.

ALTER DATABASE mydb WITH ALLOW_CONNECTIONS false;
SELECT pg_terminate_backend(pid)
  FROM pg_stat_activity
 WHERE datname = 'mydb' AND pid <> pg_backend_pid();
DROP DATABASE mydb;

3Verify

pg_stat_activity shows no rows for the database before the drop.

psql -d postgres -c "SELECT count(*) FROM pg_stat_activity WHERE datname = 'mydb';"

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 3

Your role is not allowed to end those sessions

1Check

By default only superusers may use pg_terminate_backend. Others may end sessions of roles they are a member of, or if they have the privileges of pg_signal_backend. Only superusers can end superuser sessions.

psql -d postgres -c "\du"

2Fix

Ask a superuser to run the drop, or grant the signalling role to the user who runs it.

GRANT pg_signal_backend TO ops_role;

3Verify

The terminate query from the first fix now returns true for each session.

SELECT pg_terminate_backend(12345);

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Script tear-down in this order: stop the app, DROP DATABASE WITH (FORCE), recreate.
  • Give every application a distinct application_name so you can tell sessions apart.
  • Keep a separate maintenance role for drops instead of reusing the app role.

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.