PostgreSQL: 'ERROR: database "shopdb" is being accessed by other users' (55006)

ERROR: database "shopdb" is being accessed by other users with DETAIL: There is 1 other session using the database. — PostgreSQL will not drop a database while another session is connected to it. On PostgreSQL 13 or later, DROP DATABASE shopdb WITH (FORCE); ends those sessions and drops it in one statement.

On an older server, or when you want to see who is connected first, find the sessions in pg_stat_activity, end them with pg_terminate_backend, then drop. Run either one from another database, such as postgres, never from inside the database you are dropping.

The error

$ psql -U postgres -c 'DROP DATABASE shopdb;'
ERROR:  database "shopdb" is being accessed by other users
DETAIL:  There is 1 other session using the database.

With -v VERBOSITY=verbose psql also printed the SQLSTATE, 55006, and LOCATION: dropdb, dbcommands.c:1791. Nothing was running in the other session: it was idle.

Why it happens

Every session is bound to one database for its whole life, and dropping a database removes it for good. PostgreSQL refuses rather than pull it from under a connected client, and the DETAIL line counts the clients still attached. An idle connection counts as much as a busy one, which is why the error surprises people: the culprit is usually a psql window left open, a database tool's browser tree, or your own application's connection pool keeping connections open between requests.

The fix

$ psql -U postgres -c 'DROP DATABASE shopdb WITH (FORCE);'
DROP DATABASE
$ psql -U postgres -c "SELECT count(*) AS sessions_left FROM pg_stat_activity WHERE datname = 'shopdb';"
 sessions_left
---------------
             0
(1 row)

WITH (FORCE) terminates the other sessions itself. Run from inside shopdb, it still failed, with ERROR: cannot drop the currently open database, so connect to another database first. The two-step version, which also shows you who was connected:

$ psql -U postgres -c "SELECT pid, usename, application_name, client_addr, state FROM pg_stat_activity WHERE datname = 'shopdb';"
 pid | usename  | application_name | client_addr | state
-----+----------+------------------+-------------+-------
 110 | postgres | psql             |             | idle
(1 row)
$ psql -U postgres -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'shopdb' AND pid <> pg_backend_pid();"
 pg_terminate_backend
----------------------
 t
(1 row)
$ psql -U postgres -c 'DROP DATABASE shopdb;'
DROP DATABASE

If the session belongs to your own application, stop the application before the drop rather than ending its connections under it. EF Core's EnsureDeleted() did not hit this error in the test: with Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3 its command log showed DROP DATABASE efshop WITH (FORCE);, and the idle psql session went with the database.

How it was reproduced

A postgres:18 container (PostgreSQL 18.4, Debian build) on Docker 29.9.2 under Windows 11. The other session was a psql reading from an idle pipe, sleep 600 | psql -U postgres -d shopdb, started with docker exec -d; pg_stat_activity showed it as idle. Each DROP ran from a second psql connected to the default postgres database. The EF Core check was a .NET 10 console app (SDK 10.0.402, Microsoft.EntityFrameworkCore 10.0.4). The names fix6-pg18, shopdb and efshop are this test's own, and the container was removed afterwards.

Frequently asked

How do I drop a PostgreSQL database that is being accessed by other users?

On PostgreSQL 13 or later, run DROP DATABASE name WITH (FORCE) while connected to another database such as postgres; it ends the other sessions and drops the database. On older versions, end the sessions with pg_terminate_backend first, then run DROP DATABASE.

How do I see who is connected to a PostgreSQL database?

Query pg_stat_activity, for example SELECT pid, usename, application_name, client_addr, state FROM pg_stat_activity WHERE datname = 'shopdb'. application_name shows the client's name when it sets one, such as psql, and state shows whether the session is idle or running a query.

Why does DROP DATABASE WITH (FORCE) say cannot drop the currently open database?

You ran it while connected to that same database. Connect to another one, for example with psql -d postgres, and run it from there; FORCE ends the other sessions, not your own.

More decoded errors in the Fixes category. Once the database is gone, an app that still names it fails with FATAL: database does not exist.