"database is being accessed by other users" means PostgreSQL refused to drop, rename or move a database, or to copy it as a template, because other sessions are connected to it. The DETAIL line tells you how many: other sessions, prepared transactions, or both. The fix is to get everyone out first. How you do that depends on your version and on what is connected.
postgres=# DROP DATABASE appdb;
ERROR: database "appdb" is being accessed by other users
DETAIL: There are 3 other sessions using the database.
Which commands raise it
The same check runs in several places in the server code (dbcommands.c):
DROP DATABASE, unless you addFORCE;ALTER DATABASE ... RENAME TO;ALTER DATABASE ... SET TABLESPACE;CREATE DATABASE ... TEMPLATE x, where the message readssource database "x" is being accessed by other users.
Two related errors look similar but are different. cannot drop the currently open database means you are connected to it. Connect to postgres instead (DROP DATABASE). current database cannot be renamed is the same situation for RENAME.
Step 1: see who is connected
SELECT pid, usename, application_name, client_addr, backend_type,
state, now() - backend_start AS connected_for
FROM pg_stat_activity
WHERE datname = 'appdb'
AND pid <> pg_backend_pid();
Look at application_name and client_addr before you kill anything. The usual suspects are an application pool that was not stopped, a connection pooler holding server connections open, a monitoring agent, a GUI client someone left open over the weekend, and the replication or background workers you forgot about. If the "database" you want to drop is still serving traffic, this query is your last chance to find out.
Step 2: DROP DATABASE WITH (FORCE)
-- PostgreSQL 13+, run from another database (e.g. postgres)
DROP DATABASE appdb WITH (FORCE);
-- the same from the shell
dropdb --force appdb
Since PostgreSQL 13, FORCE tells DROP DATABASE to terminate the connections itself, and dropdb has the matching -f / --force option (PostgreSQL 13 release notes). It terminates background workers and the sessions you could terminate with pg_terminate_backend. If any connections remain, the command fails (DROP DATABASE).
FORCE does not help in three cases. The documentation lists them: prepared transactions, active logical replication slots, and subscriptions in the target database. We cover those below. Also remember that DROP DATABASE cannot run inside a transaction block, so it will fail inside a migration tool that wraps every step in BEGIN/COMMIT.
FORCE does exactly what it says. Sessions are killed mid-transaction and their work is rolled back. Run the pg_stat_activity query first and make sure every connection belongs to something you meant to stop. On a shared server, check the database name twice: there is no undo.
Older versions, or when you want control
On PostgreSQL 12 and older, or when you want to see each step, do it by hand:
-- 1. stop new connections (applies to superusers too)
ALTER DATABASE appdb ALLOW_CONNECTIONS false;
-- 2. disconnect everyone who is still there
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'appdb' AND pid <> pg_backend_pid();
-- 3. drop
DROP DATABASE appdb;
Step 1 matters. Without it, an application with a connection pool reconnects within milliseconds of the terminate, and you get the same error again. ALLOW_CONNECTIONS false means nobody can connect, and that includes superusers (CREATE DATABASE). CONNECTION LIMIT 0 is weaker, because the limit is not enforced against superusers or background workers. If you abort the drop, set ALLOW_CONNECTIONS true again. Otherwise the application stays locked out.
Permissions matter too. pg_terminate_backend works on your own role's sessions, or for members of pg_signal_backend, but only a superuser can terminate a superuser's session (server signaling functions). That is the usual reason FORCE leaves connections behind when the database owner runs it.
Prepared transactions, slots and subscriptions
-- prepared (two-phase) transactions
SELECT gid, owner, database, prepared FROM pg_prepared_xacts;
-- logical replication slots
SELECT slot_name, database, active, active_pid FROM pg_replication_slots
WHERE database = 'appdb';
-- subscriptions that live in that database (pg_subscription is cluster-wide)
SELECT subname, subenabled FROM pg_subscription
WHERE subdbid = (SELECT oid FROM pg_database WHERE datname = 'appdb');
- Prepared transactions come from two-phase commit, typically an XA transaction manager. They are not sessions, so terminating backends does nothing. Finish them with
ROLLBACK PREPARED 'gid'orCOMMIT PREPARED 'gid', as the same user or a superuser (ROLLBACK PREPARED). You must be connected to the database where the transaction was prepared. The server rejects it from anywhere else. - An active logical replication slot gives
database "appdb" is used by an active logical replication slot. Stop the consumer (a subscriber, Debezium, another CDC tool) first. Note that inactive slots of that database are dropped together with it, silently. If a CDC pipeline was only paused, its position is gone after the drop. - A subscription in the database gives
database "appdb" is being used by logical replication subscription. PostgreSQL won't drop it for you because it may hold a slot on another server. RunDROP SUBSCRIPTIONfirst. Our logical replication guide covers the cleanup order.
CREATE DATABASE ... TEMPLATE
CREATE DATABASE test_copy TEMPLATE appdb;
ERROR: source database "appdb" is being accessed by other users
DETAIL: There is 1 other session using the database.
Copying a database requires that no other session is connected to the source. The documentation says this is the main limitation of using any database as a template (CREATE DATABASE). The usual case is cloning a database for tests while the application is still running against it. Disconnect it first, or use pg_dump and restore instead. If the error names template1, something is connected to template1. Find it with the same pg_stat_activity query, or create the database from template0, which is normally marked to refuse connections.
On large and busy systems
On a laptop, this is a ten-second fix. In production, a few more things matter:
- Connection poolers. PgBouncer keeps server connections open and opens new ones on demand. Disable the database in PgBouncer first, then kill its connections, then run the drop or rename (PgBouncer admin console). More in our PgBouncer guide.
- Hot standbys. When a standby replays
DROP DATABASEorALTER DATABASE ... SET TABLESPACE, every session connected to that database on the standby is disconnected immediately, whatevermax_standby_streaming_delayis set to (hot standby). Reporting jobs on replicas die without warning.RENAMEdoes not disconnect standby sessions. - Cloning big databases. The default
CREATE DATABASEstrategy,WAL_LOG, writes every block of the copy to WAL. For a large template, that means a lot of WAL for replicas and the archive to absorb.FILE_COPYwrites far less WAL but forces a checkpoint before and after the copy (CREATE DATABASE). Either way, the source stays locked to new connections until the copy finishes. - Dropping big databases. In our experience, deleting hundreds of gigabytes of files takes noticeable time and I/O on some filesystems, and every physical replica does the same work when it replays the drop. Schedule it as maintenance, not as a quick cleanup at peak.
- Many databases. With a database per tenant, drop and rename run from scripts. Make the script check the exact name, log what it terminated, and refuse to run with FORCE against anything that had active client sessions in the last few minutes.
-- PgBouncer admin console (psql -p 6432 pgbouncer)
DISABLE appdb; -- reject new client connections
KILL appdb; -- drop client and server connections now
-- ... DROP / RENAME in PostgreSQL ...
ENABLE appdb;
Key takeaways
- The error means other sessions or prepared transactions use the database; DETAIL tells you which.
- Look at pg_stat_activity first: make sure you are not about to kill live traffic.
- PostgreSQL 13+: DROP DATABASE ... WITH (FORCE) or dropdb --force.
- Older versions: ALLOW_CONNECTIONS false, pg_terminate_backend, then DROP.
- FORCE cannot clear prepared transactions, active logical slots or subscriptions; inactive slots are dropped silently.
- Disable the database in PgBouncer first, and expect standby sessions to be cut off on DROP.