PostgreSQL

Postgres "Database Is Being Accessed by Other Users" on Drop

PostgreSQL refuses to drop a database that has any active connections to it, as a safety measure — the error is expected behavior, not a bug, and the fix is either closing those connections yourself or asking Postgres to do it for you.

The Problem

Trying to drop a database fails with:

ERROR: database "myapp_dev" is being accessed by other users
DETAIL: There is 1 other session using the database.

Even if you're confident you've closed your own connection to that database, something else — often an editor, a background service, or a connection pool — is still holding a session open.

Why It Happens

PostgreSQL won't drop a database while any session has an active connection to it, since doing so could corrupt data or leave connected clients in an undefined state. Common sources of lingering connections:

  • A database GUI tool (like pgAdmin, DBeaver, or a similar client) still has a connection open in the background, even if you're not actively viewing that database's tab
  • An application server or background worker maintains a connection pool that's still connected, even if the specific request that used it has finished
  • A separate terminal or script session left connected via psql without you remembering it's still open
  • Connection pooling middleware (like PgBouncer) keeping idle connections alive in its own pool, independent of whether your application is actively using them at this moment

The Fix

First, identify exactly what's connected to the database you're trying to drop:

SELECT pid, usename, application_name, client_addr, state
FROM pg_stat_activity
WHERE datname = 'myapp_dev';

This shows every active session, including the connecting user, application name (if the client sets one), and connection state — often making it immediately obvious what's still holding a connection open.

If you can identify and close the offending client normally (closing the GUI tool's connection tab, stopping the background service), that's the cleanest fix, since it lets that application close its connection gracefully rather than being forcibly terminated.

If you need to force-close connections immediately — common when the source is unclear or hard to reach quickly — terminate them directly from another session connected to a different database:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'myapp_dev' AND pid <> pg_backend_pid();

The pid <> pg_backend_pid() condition excludes your own current session from being terminated, which matters if you happen to be connected to the same database you're clearing connections from (though for actually dropping the database, you'd typically be connected to a different one, like postgres).

After terminating the connections, the drop should succeed immediately, assuming nothing reconnects in the brief window between terminating and dropping:

DROP DATABASE myapp_dev;

If a persistent connection pool or service keeps reconnecting automatically right after being terminated (a common issue with actively running application servers), stop that service entirely first, rather than repeatedly terminating connections it immediately re-establishes.

Still Not Working?

If you're on PostgreSQL 13 or newer, there's a more direct option that combines termination and dropping into a single step, forcing the drop regardless of active connections:

DROP DATABASE myapp_dev WITH (FORCE);

This is convenient for development environments where you don't need to carefully identify what's connected first, but use it cautiously in any environment where forcibly terminating active sessions could cause a client application to behave unexpectedly, rather than as a routine habit for databases other people might be actively using.

If connections keep reappearing immediately even after you've stopped every application you're aware of, check whether a monitoring or backup tool is periodically connecting to every database on the server as part of its own routine health checks — these connections can be brief but frequent enough to consistently collide with your drop attempt if your timing happens to overlap with theirs. Reviewing the application_name column in the earlier pg_stat_activity query often reveals a recognizable tool name (like a backup agent or monitoring dashboard) rather than an actual application you'd expect to be connected.

Finally, if this is a recurring annoyance in a development workflow (frequently dropping and recreating a local database), consider whether you actually need to drop the database at all versus simply truncating its tables or resetting via a migration tool's built-in reset command, which typically handles connection management internally and avoids running into this error in the first place.

You might also like