PostgreSQL "database is being accessed by other users": Why Drop Database Is Blocked?

Safely terminating active sessions before DROP DATABASE, ALLOW_CONNECTIONS isolation, prepared transactions, and replication slot blockers.

Reviewed by:Senior Database Architect & SRE (dataiotech Labs)

Tested Versions: PostgreSQL 13 / 14 / 15 / 16 / 17

First 60 Seconds Emergency Protocol:

The error string is a symptom, not the root cause. Do not restart the server blindly. Run the diagnostic queries below to pinpoint the actual failure mode.

▸Symptoms

plaintext

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

▸Resolution Sequence

1Disallow Incoming Connections (PG 13+):

sql

ALTER DATABASE test_db WITH ALLOW_CONNECTIONS false;

1Terminate Active Sessions:

sql

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

1Inspect Invisible Blockers:

•Prepared transactions: SELECT gid, database FROM pg_prepared_xacts; (ROLLBACK PREPARED 'gid';)
•Replication slots: SELECT slot_name FROM pg_replication_slots WHERE database = 'test_db'; (SELECT pg_drop_replication_slot('...');)

1Drop Database:

sql

DROP DATABASE test_db;

Other Database Error Triage Playbooks

Other battle-tested diagnostic guides for high-severity database errors.

All Errors (20)

POSTGRESQL5 min

PostgreSQL too many clients already: 6-Step Resolution

Three root causes of FATAL: sorry, too many clients already, single diagnostic query to identify the cause, and permanent mitigation via max_connections and PgBouncer.

POSTGRESQL4 min

PostgreSQL password authentication failed: Root Cause and Fix

Four root causes of psql password authentication failed: incorrect password, md5/SCRAM mismatch, pg_hba.conf rule ordering, and port/cluster confusion. Step-by-step triage.

POSTGRESQL4 min

PostgreSQL connection refused: 8-Step Definitive Triage

Difference between connection refused, timeout, and no pg_hba entry; triage for listen_addresses, port conflicts, firewalls, and Docker container mappings.