Fix PostgreSQL "Database Is Being Accessed by Other Users" in Django

Problem statement

You try to drop or recreate a database and PostgreSQL refuses:

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

It shows up in several Django workflows: manage.py test failing to destroy test_appdb, a staging refresh script running dropdb before pg_restore, reset_db from django-extensions, or renaming a database during a migration. PostgreSQL will not drop a database while any session is connected to it, and something still is: a running Gunicorn worker, a Celery process, a PgBouncer pool, an admin's psql session, or a crashed test run.

1. List the sessions: SELECT pid, usename, application_name, client_addr FROM pg_stat_activity WHERE datname = 'appdb_staging';
2. Stop whatever owns them: app services, Celery, cron jobs, and the pooler's connections to that database.
3. Terminate the rest: SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'appdb_staging' AND pid <> pg_backend_pid();
4. On PostgreSQL 13+, DROP DATABASE appdb_staging WITH (FORCE); does both steps at once.
5. Block new connections during the operation with ALTER DATABASE ... WITH ALLOW_CONNECTIONS false.

Step-by-step solution

1. Find who is connected

Connect to a different database, such as postgres, and list the sessions:

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

application_name and client_addr usually identify the source. Set application_name in Django's database OPTIONS per process type (web, celery, cron) so this list is readable next time.

2. Stop the sources

Terminating sessions while the app runs only works for a moment, because workers reconnect immediately. Stop them first:

sudo systemctl stop myapp-staging myapp-staging-celery
# or with Compose
docker compose stop web worker beat

With PgBouncer in front, the pooler holds server connections even when the app is stopped. Run PAUSE appdb_staging; and KILL appdb_staging; on the PgBouncer admin console, or restart PgBouncer.

3. Block new connections and terminate

ALTER DATABASE appdb_staging WITH ALLOW_CONNECTIONS false;
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'appdb_staging' AND pid <> pg_backend_pid();
DROP DATABASE appdb_staging;

Blocking connections first prevents a restarting worker from slipping in between the terminate and the drop. You need superuser rights or membership in pg_signal_backend to terminate other users' sessions.

4. Use FORCE on PostgreSQL 13 and later

DROP DATABASE appdb_staging WITH (FORCE);

Or from the shell: dropdb --force appdb_staging. PostgreSQL terminates the sessions and drops the database in one step. It still fails if a session cannot be terminated, for example a prepared transaction or a logical replication slot using the database.

5. Fix the test database case

When manage.py test says:

Got an error creating the test database: database "test_appdb" already exists
Type 'yes' if you would like to try deleting the test database 'test_appdb', or 'no' to cancel:

and deleting fails because of other sessions, a previous run left processes behind. In CI, use --noinput and make sure each job has its own database service. Locally, close shells and IDE database panels connected to test_appdb, then run with --keepdb if you want to reuse it.

Explanation

Every PostgreSQL session is bound to one database for its whole life, and its backend process has the database's files open. Dropping the database while sessions exist would remove files from under them, so PostgreSQL refuses. pg_terminate_backend sends SIGTERM to those backends, which roll back and exit. The FORCE option added in PostgreSQL 13 automates exactly that.

Edge cases / notes

• Never on production. The same commands drop production databases without asking. Make scripts check the database name and host explicitly.
• Template databases. CREATE DATABASE ... TEMPLATE appdb fails with a similar message if anyone is connected to the template.
• Replication slots. Logical replication slots tied to the database must be dropped before the database.

Next steps

• Script the restore itself with Back Up and Restore PostgreSQL for Django.
• Refresh staging safely with Anonymize Production Data for Staging.
• For pool management, see Set Up PgBouncer for Django.

FAQ

Is it safe to terminate the sessions?

They roll back any open transactions. On staging or test databases that is fine. On production, terminating app sessions causes errors for users.

Why do sessions come back right after terminating them?

The app or pooler reconnects. Stop those processes first or block connections with ALLOW_CONNECTIONS false.

Does Django keep connections open after tests?

Test runs close their connections at the end. Leftover sessions usually come from a crashed run or another tool.

What version added DROP DATABASE WITH (FORCE)?

PostgreSQL 13. On older versions, terminate the sessions manually before dropping.