PostgreSQL: database is being accessed by other users

What it means

Some commands work on a whole database and can’t run while anyone else is connected to it: DROP DATABASE, ALTER DATABASE … RENAME TO, and CREATE DATABASE … TEMPLATE <db> (for the template). PostgreSQL found other sessions in that database, waited about five seconds for them to leave, and gave up:

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

The DETAIL counts the sessions. It may instead mention prepared transactions (PREPARE TRANSACTION that were never committed), which hold the database too. When the database is a template, the message starts source database "shop" is being accessed by other users.

Common causes

An application still connected: the app server, a worker, a cron job, or its connection pool, which keeps connections open while idle.
Your own other window: another psql, an IDE or a database client with a tab still open on that database.
Tests or tooling that drop and recreate a database while a previous run’s connections are still closing.
template1 in use while you CREATE DATABASE, because another session is connected to it.

How to fix it

See who is connected

From a session in another database (such as postgres):

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

application_name and client_addr usually identify the program. Stop it, or point it elsewhere, before you drop or rename.

Drop it anyway, ending the sessions (PostgreSQL 13 and later)

DROP DATABASE shop WITH (FORCE);

It terminates the other sessions first; they get terminating connection due to administrator command. From the shell: dropdb --force shop. It can’t remove prepared transactions, logical replication slots or subscriptions; those need dealing with first.

End the sessions yourself

On older versions, or for RENAME and TEMPLATE, which have no FORCE:

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

Applications reconnect quickly, so to keep them out while you work, stop them first, or block new connections: ALTER DATABASE shop ALLOW_CONNECTIONS false; (as superuser or the owner), then terminate, then run your command.

Don’t connect to the template

For CREATE DATABASE, make sure nothing is connected to template1 (or the template you name). CREATE DATABASE … TEMPLATE template0 avoids the problem, as nobody can connect to template0.

Reproduce it

In a throwaway PostgreSQL 18.6 container (a database of our own to drop), one session connected to shop and ran SELECT pg_sleep(60). From a session in postgres:

\timing on
DROP DATABASE shop;

ERROR:  database "shop" is being accessed by other users
DETAIL:  There is 1 other session using the database.
Time: 5052.507 ms (00:05.053)

It waited five seconds before failing. ALTER DATABASE shop RENAME TO shop_old failed the same way, and CREATE DATABASE shop_copy2 TEMPLATE shop gave source database "shop" is being accessed by other users. DROP DATABASE shop WITH (FORCE) succeeded, and the connected session got:

FATAL:  terminating connection due to administrator command
SSL connection has been closed unexpectedly
connection to server was lost

pg_terminate_backend on that session followed by a plain DROP DATABASE worked as well. With \set VERBOSITY verbose, psql shows the code: ERROR: 55006: database "shop" is being accessed by other users.

In Inlet

Inlet’s Activity monitor lists the server’s sessions, so you can see who is connected to the database, and end a session (Pro). If Inlet itself is connected to that database, its own sessions count too: disconnect, and run the DROP from a connection to another database.

database is being accessed by other users