Postgres: force drop a database that is being accessed by other users

You are trying to drop an old database and Postgres gives you this:

ERROR:  database "analytics_old" is being accessed by other users
DETAIL:  There are 2 other sessions using the database.

That error is literal. PostgreSQL will not drop a database while backend processes are connected to it.

On PostgreSQL 13 or newer, the blunt tool is:

DROP DATABASE analytics_old WITH (FORCE);

Run that while connected to some other database, usually postgres:

psql -d postgres

Do not connect to analytics_old and then try to drop analytics_old. PostgreSQL will quite reasonably refuse.

For PostgreSQL 12 and older, you have to do the old dance: stop new connections, terminate existing sessions with pg_terminate_backend(), then run DROP DATABASE.

One strong opinion before the commands: do not force-drop a production database just because the command exists. I have seen this used during a “cleanup” window where the two connected sessions were not harmless stragglers; one was a reporting job holding an open transaction, the other was an app pool pointed at the wrong database after a deploy. FORCE would have made the symptom go away and created a much messier incident.

First, find out who is connected

Start with pg_stat_activity. Always.

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    wait_event_type,
    wait_event,
    query_start,
    left(query, 160) AS query
FROM pg_stat_activity
WHERE datname = 'analytics_old'
  AND pid <> pg_backend_pid()
ORDER BY query_start NULLS LAST;

Typical output looks like this:

  pid  | usename   | application_name | client_addr | state  | query_start              | query
-------+-----------+------------------+-------------+--------+--------------------------+-----------------------------
 21044 | app_user  | api              | 10.0.4.12   | idle   | 2026-07-10 01:19:44+00   | COMMIT
 21091 | reporter  | metabase         | 10.0.4.18   | active | 2026-07-10 01:22:03+00   | SELECT count(*) FROM events

“Being accessed” does not mean someone is hammering the database with an expensive query. An idle psql prompt counts. So does an application connection pool, a BI tool, a monitoring check, a stuck transaction, or a client that immediately reconnects every time you kill it.

If those sessions belong to production applications, stop and coordinate. Terminated sessions get errors. Open transactions are rolled back. Your database may be old, renamed, or “unused” according to a ticket, but the server only knows that something is connected.

PostgreSQL 13 and newer: DROP DATABASE ... WITH (FORCE)

PostgreSQL 13 added the force option:

DROP DATABASE analytics_old WITH (FORCE);

If it works, you get the normal confirmation:

DROP DATABASE

FORCE asks PostgreSQL to terminate backends connected to the target database as part of the drop operation. That is the main improvement over the old manual approach: it avoids the annoying race where you kill sessions, an application pooler reconnects half a second later, and your DROP DATABASE fails anyway.

It is not a magic override for everything.

You still need the right privileges. In practice, that means you must be allowed to drop the database and you must be allowed to terminate the connected backends. Usually that means superuser, or an appropriate role such as pg_signal_backend. The usual restriction still applies: non-superusers cannot signal superuser-owned backends.

FORCE also does not clean up every kind of blocker. Prepared transactions can still stop you. Logical replication slots can still matter. Subscriptions can still get in the way. Check the catalogs if the drop refuses to proceed:

SELECT *
FROM pg_prepared_xacts
WHERE database = 'analytics_old';

SELECT *
FROM pg_replication_slots
WHERE database = 'analytics_old';

SELECT *
FROM pg_subscription;

The shell wrapper is available too:

dropdb --force analytics_old

Same warning: run it against the right cluster, as the right user, and not from muscle memory on a Friday afternoon.

PostgreSQL 12 and older: terminate sessions yourself

Before PostgreSQL 13, the common version of this was:

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

DROP DATABASE analytics_old;

That often works on a quiet system. It fails on systems with stubborn clients.

The race looks like this:

SELECT pg_terminate_backend(...)
-- pooler reconnects here
DROP DATABASE analytics_old;
ERROR: database "analytics_old" is being accessed by other users

For PostgreSQL 12 and older, use the safer sequence:

REVOKE CONNECT ON DATABASE analytics_old FROM PUBLIC;

ALTER DATABASE analytics_old WITH ALLOW_CONNECTIONS false;

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

DROP DATABASE analytics_old;

Run all of that while connected to another database, commonly postgres.

The REVOKE CONNECT ON DATABASE ... FROM PUBLIC line removes the default broad ability to connect. It does not remove explicit CONNECT privileges granted to specific roles. The ALLOW_CONNECTIONS false setting is the heavier door slam: it prevents new connections to that database, which is what you want right before dropping it.

Then pg_terminate_backend() clears the sessions that are already inside.

If the manual termination does not clear everything, inspect again:

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    wait_event_type,
    wait_event,
    query_start,
    left(query, 160) AS query
FROM pg_stat_activity
WHERE datname = 'analytics_old'
ORDER BY query_start NULLS LAST;

If the same application keeps coming back, do not keep mashing pg_terminate_backend() and pretending that is administration. Disable the app, fix the pooler config, remove the service entry, or block the role. The database is doing exactly what you asked it to do: accepting connections from something still configured to use it.

A practical order of operations

For a non-production scratch database on PostgreSQL 13 or newer, I am fine with this:

psql -d postgres

SELECT pid, usename, application_name, client_addr, state, left(query, 120)
FROM pg_stat_activity
WHERE datname = 'analytics_old';

DROP DATABASE analytics_old WITH (FORCE);

For anything that smells like production, I want a little more ceremony:

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    query_start,
    left(query, 160) AS query
FROM pg_stat_activity
WHERE datname = 'analytics_old'
ORDER BY query_start NULLS LAST;

Then verify the less obvious blockers:

SELECT *
FROM pg_prepared_xacts
WHERE database = 'analytics_old';

SELECT *
FROM pg_replication_slots
WHERE database = 'analytics_old';

SELECT *
FROM pg_subscription;

Then stop the application or pooler that owns those connections. After that, use DROP DATABASE ... WITH (FORCE) if you are on PostgreSQL 13+, or the manual revoke/disable/terminate/drop sequence if you are on an older release.

The important part is not the syntax. The important part is knowing whether you are killing a forgotten idle session or cutting the floor out from under a live workload. PostgreSQL gives you the tools for both; it does not know which one you meant.