lib/tasks/force_drop.rake

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

If you just copy‑pasted that error into a search engine, you’re probably trying to tear down a test database in CI, reset a staging clone, or nuke a local development database. You ran psql and it threw that in your face. I’ve hit this a dozen times—and 9 out of 10 it’s not a stray psql window. It’s your connection pooler (PgBouncer, your ORM’s internal pool) holding idle connections long after the owning application shut down. The rest of the time it’s a leftover monitoring agent, a replication slot, or a forgotten tab in tmux.

Here’s exactly what’s going on, how to see who’s still connected, and the deterministic pattern that kills them so you can drop the database cleanly.

TL;DR — the one‑liner and the pre‑13 fallback

If you’re on Postgres 13 or later:

DROP DATABASE IF EXISTS mydb WITH (FORCE);

That one line terminates every normal backend connected to mydb and drops it immediately. No REVOKE CONNECT, no hand‑rolled pg_terminate_backend loop. Just done.

If you’re on Postgres 12 or earlier (or a managed service that blocks FORCE), use this fallback pattern:

REVOKE CONNECT ON DATABASE mydb FROM PUBLIC;
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'mydb' AND pid <> pg_backend_pid();
DROP DATABASE IF EXISTS mydb;

Those three statements are the atomic unit I’ve used for years. They stop new connections from arriving, boot everyone currently connected, and then drop the database.

Why this happens

Postgres’ DROP DATABASE requires zero other connections to the target database. It’s a safety guarantee: you can’t rip out a database while someone is using it without risking corruption. The docs state it plainly: “if anyone else is connected to the target database, this command will fail.”

In practice, the culprits are rarely humans. Tools like PgBouncer, or the built‑in connection pools in application frameworks (Rails, Django, HikariCP), maintain persistent TCP sockets to Postgres even when your application code is idle. If you restart an application server, the pooler may hold those connections open for minutes depending on its timeout settings. Postgres doesn’t care that nobody is running a query; it just sees a backend with state = 'idle' and datname = 'mydb'. That’s enough to block DROP DATABASE. The same thing happens if a replication slot is still referencing the database (in that case even pg_terminate_backend won’t help until you drop the slot).

So before you ever reach for a force drop, you should first look at exactly who’s holding the door open.

Step 1: See who’s actually connected

Before you start killing backend processes, inspect who or what is holding the connection. This prevents you from accidentally terminating a long‑running, critical migration or an analytical query that has been running for hours.

Run this from a different database (like postgres) or from a superuser session on the same cluster:

SELECT pid, usename, application_name, state, query, datname
FROM pg_stat_activity
WHERE datname = 'mydb'
ORDER BY pid;

Real output looks like this:

  pid  | usename  | application_name | state  |         query          | datname
-------+----------+------------------+--------+------------------------+---------
 20398 | app_user | my_app           | idle   |                        | mydb
 20401 | app_user | my_app           | active | SELECT * FROM users;   | mydb
 20944 | postgres | psql             | active | DROP DATABASE mydb;    | mydb   -- yourself
(3 rows)

Look for idle connections from your pooler, monitoring tools, or a forgotten psql window. If there’s a replication slot keeping a walsender attached, you’ll see application_name like walreceiver and state = 'active', but those need a different treatment (drop the slot on the primary).

Always look before you kill. Terminating the wrong session (e.g. a long‑running migration on another database) is a great way to create new outages while fixing a test database problem.

Step 2: Stop new connections from arriving

Even after you terminate existing backends, their client might have retry logic that instantly reconnects. The fix is a preemptive REVOKE CONNECT:

REVOKE CONNECT ON DATABASE mydb FROM PUBLIC;

This blocks any new connection attempt to mydb from non‑superusers. (If you have explicit grants to specific roles, you’ll need to revoke those individually or use PUBLIC as a broad catch‑all—most test and staging databases only use the default PUBLIC grant.)

This REVOKE also means you can safely loop through pg_terminate_backend without worrying that a pooler is going to race you and reconnect before the DROP.

Note: If your application connects as a superuser or the database owner, the PUBLIC revoke might not block them. In those cases you may need to explicitly revoke CONNECT from the specific role.

Step 3: Terminate existing backends safely

Now kick out everyone who is currently connected—except yourself:

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

pg_terminate_backend(pid) sends a SIGTERM to the backend process. That gives it a chance to roll back any current transaction and disconnect cleanly. Crucially, it disconnects the session; after the backend receives SIGTERM and exits, the client will see that the connection is closed. That’s different from pg_cancel_backend(pid), which sends SIGINT and only cancels the currently running query—the session stays alive. For the purpose of dropping the database, cancellation is useless; you need termination.

If you ever see the error pg_terminate_backend() returned false, it means you don’t have permission to terminate that backend (normally you need the same role or be superuser). On managed services like RDS you need the rds_superuser role, which sometimes requires a little extra fiddling—just something to keep in mind.

Step 4: Drop it

After revoking and terminating, it’s finally time:

DROP DATABASE IF EXISTS mydb;

IF EXISTS makes the whole pattern safe to run repeatedly in scripts (idempotent). If the database was already dropped by a previous pipeline run, this statement just returns a notice and moves on.

That’s the classic three‑step dance. With Postgres 13+ you can skip it:

DROP DATABASE IF EXISTS mydb WITH (FORCE);

Behind the scenes, WITH (FORCE) does the REVOKE CONNECT and pg_terminate_backend magic atomically, then executes the DROP. It doesn’t nuke prepared transactions, connections that are active as replication clients (like a walsender), or databases that are currently being used as a template for another active connection. I’ve only hit those edge cases a couple of times in CI, but when you do, you’ll need to drop the slot/subscription first or handle the prepared transaction. For 99% of test and staging databases, FORCE is all you need.

The one‑liner for CI and scripts

If you’re scripting a database teardown and want a single block that works on any Postgres version, here’s a copy‑pasteable psql snippet. Run it connected to the postgres database or any other database that isn’t the one you’re dropping:

DO $$
DECLARE
    r RECORD;
BEGIN
    -- Block new connections to the target DB
    EXECUTE 'REVOKE CONNECT ON DATABASE mydb FROM PUBLIC';

    -- Terminate every backend except myself
    FOR r IN
        SELECT pid FROM pg_stat_activity
        WHERE datname = 'mydb' AND pid <> pg_backend_pid()
    LOOP
        PERFORM pg_terminate_backend(r.pid);
    END LOOP;

    -- Now drop it
    EXECUTE 'DROP DATABASE IF EXISTS mydb';
END $$;

If you prefer a shell‑script approach, the same logic can be expressed as a bash one‑liner (use psql -d postgres to avoid connecting to the target database):

DB_NAME="mydb"
psql -d postgres -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '$DB_NAME' AND pid <> pg_backend_pid();" || true
psql -d postgres -c "DROP DATABASE IF EXISTS $DB_NAME WITH (FORCE);"

Rails and Django migration‑safe version

When you’re running rails db:drop or python manage.py flush/reset_db in CI, those commands also need an empty (or at least reachable) database. They’ll hang on the same lock if there are lingering connections. The raw‑SQL escape hatch is to run the force‑drop before the framework’s command. For Rails, you could add a small setup task:

# lib/tasks/force_drop.rake
namespace :db do
  desc 'Force-drop the development/test database when connections are stuck'
  task force_drop: :environment do
    config = ActiveRecord::Base.connection_db_config.configuration_hash
    dbname = config[:database]
    # Connect to the 'postgres' maintenance database instead
    ActiveRecord::Base.establish_connection(config.merge(database: 'postgres'))
    ActiveRecord::Base.connection.execute("DROP DATABASE IF EXISTS #{dbname} WITH (FORCE)")
  end
end

For Django, a similar raw psycopg2 call against the postgres database before manage.py flush does the trick. The key is to get outside the target database first, because you can’t drop the DB you’re currently connected to.

Common gotchas

• Superuser only on managed Postgres. On RDS, rds_superuser can execute REVOKE CONNECT and pg_terminate_backend on all non‑superuser backends, but you might hit a permissions wall if a backend is owned by a different rds_superuser‑class role. If you’re stuck, check who owns the connection with SELECT usename FROM pg_stat_activity WHERE pid = ...; and terminate with a role that matches.

• Replication slots. A physical or logical replication slot that isn’t actively consumed can still hold a walsender process that looks like a regular backend in pg_stat_activity. Even WITH (FORCE) won’t kill it. Drop the slot first (SELECT pg_drop_replication_slot('slot_name')), then the database will drop.

• Prepared transactions. If someone left a two‑phase commit hanging (PREPARE TRANSACTION 'foo'), the database considers it an active connection in the middle of a transaction. You’ll need to ROLLBACK PREPARED 'foo' or COMMIT PREPARED 'foo' before the drop succeeds. This is extremely rare in test environments, but it’s burned me once.

• Database used as a template. If the target database is currently being used as a template for another active connection, WITH (FORCE) will fail. You’ll have to disconnect that other session first or stop the operation that’s cloning the database.

When not to force‑drop

This is important enough to say again: force‑dropping a database by killing backends is a tool for throwaway environments, not for “I’ve got a locked table in prod and need to kill sessions to unlock it.” If you need to interrupt a runaway query on a live system, use pg_cancel_backend to stop the query without booting the session. For long‑running tables that nobody should be using, REVOKE CONNECT and let the connections naturally close. Kicking out production users with pg_terminate_backend can cause partial failures that are worse than whatever timeout you were trying to avoid—imagine a multi‑step financial transaction where step 2 committed but step 3 never ran because you killed the session.

The error database is being accessed by other users is Postgres politely refusing to let you shoot yourself in the foot. Once you understand who’s connected and when it’s safe to kill them, the three‑step pattern is boring and reliable—exactly what you want in a CI pipeline or automated script.