DROP DATABASE fails because other sessions are connected
ERROR: database "mydb" is being accessed by other usersShort 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.
Fix it fast
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';"2Match what you see, then jump to its fix
- 1
Rows come back from pg_stat_activity and the server is a recent versionOrdinary sessions are open and FORCE can end themFix - 2
pg_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
pg_terminate_backend returns an error about permissions, or falseYour role is not allowed to end those sessionsFix
- 1
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 -lConfirmed by postgresql.org
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
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
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
Databases
DROP DATABASE fails because it is the database you are connected to
pq: cannot drop the currently open database
PostgreSQL never drops the database your own session is connected to.
3 causes, each with a fixDatabases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fix