Incident Response

PostgreSQL "database is being accessed by other users": How to Fix It

DROP DATABASE, RENAME or CREATE DATABASE ... TEMPLATE refuses to run while anyone else is connected. Here is how to see who is there, when WITH (FORCE) works and when it does not, and what to watch on replicas, poolers and CDC slots.

· 12 min read · AG Data Team

"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):

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');

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:

-- 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

PostgreSQL Errors Administration Replication
Work with us

Need a database dropped or renamed safely?

We plan and run risky maintenance on production PostgreSQL: dropping, renaming and cloning databases without surprise outages on replicas, poolers or CDC pipelines.

Get in touch More from the blog