PostgreSQL says “database is being accessed by other users”? Build a session ledger before force drop

1. Preserve the refusal before trying to clear it

Record the exact SQLSTATE, target database, attempted operation, UTC time, application deployment or restore step, and the connection count PostgreSQL reported. Do not paste a terminate query into the same incident record and call the evidence complete. Once a pool reconnects or a backend disappears, the ownership trail changes.

The Stack Overflow case reviewed for this guide still reported 24 sessions after revoke and terminate attempts. A separate DBA.StackExchange thread shows a script terminating its own connection to the maintenance database. Those are different failure families, but both show why “kill everything and retry” loses the context needed to explain the block.

If the request is part of a restore or environment replacement, preserve the backup and change ticket separately. This ledger does not prove that a backup is restorable, that a database is disposable, or that an application owner approved downtime.

2. Prove which server and database your collector is using

Open a new maintenance session to postgres or another database that is not the target. PostgreSQL’s DROP DATABASE documentation requires that separation, and the same habit prevents your evidence collector from becoming one of the target sessions it is trying to explain.

Capture current_database(), current_user, session_user, pg_backend_pid(), inet_server_addr(), inet_server_port(), server_version, server_version_num and pg_is_in_recovery(). Compare the endpoint with the application and change ticket. If a proxy or managed endpoint prevents you from proving the exact cluster, mark cluster identity unresolved instead of assuming a familiar hostname is enough.

The first query should also calculate current_database() = target_database. If it is true, stop and reconnect through a different maintenance database. If the target is absent from pg_database, stop: a misspelled name, different cluster or already-completed change is a different incident.

3. Open a bounded read-only snapshot

Use BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY for one collection pass. Record the target database OID, owner, datallowconn, connection limit and whether the collector has CONNECT. Also record membership in pg_read_all_stats and the track_activities setting.

PostgreSQL documents that current-query information is captured when first requested in a transaction and then stays consistent through that transaction. That consistency is useful for joining several views, but it means waiting inside the same transaction does not create the fresh comparison you need later. Commit the first read-only pass, then start a new transaction for snapshot two.

Ordinary users can see that other sessions exist and can see general properties, but many columns for sessions owned by other roles can be null. A collector without pg_read_all_stats must label the ledger visibility-incomplete. Nulls are not empty evidence, and they never authorize a state-changing command.

4. Inventory sessions without exporting SQL text

For rows whose datname equals the target, capture pid, leader_pid, usename, application_name, client_addr, client_port, backend_type, state, backend_start, xact_start, query_start, state_change, wait_event_type and wait_event. Sort by backend type, role, application, client and PID so repeated pool members become visible.

Do not export the query column by default. Query text can contain customer data, literals or credentials, and it is not required to establish who owns a connection. The ledger should also mark whether any row is the collector PID; if your collector is connected to the correct maintenance database, that flag should be false for target rows.

Application name and client address are clues, not final identity. A blank application name can still be a real client. A null client address can mean a Unix socket or an internal process. Attribute the row to an application owner only when the role, endpoint, deployment context and timing agree.

5. Check the blocker families that a session list misses

Query pg_prepared_xacts for the target database and record transaction ID, GID, prepared time, owner and database. A prepared transaction is durable two-phase state, not an idle client session. Do not commit or roll it back from this guide; hand the GID to the application or transaction coordinator that owns it.

Query pg_replication_slots for logical slots whose database is the target. Record slot name, plugin, type, temporary flag, active state, active PID, retained transaction identifiers and LSN positions. A slot can be inactive yet still be an owned replication object. Do not drop it merely because active is false.

Query pg_subscription through its target database OID and record only subscription name, owner, enabled state, slot name and publication names. Do not select subconninfo; PostgreSQL warns that it can contain plain-text passwords and revokes normal-user access to that column. A subscription belongs to a replication design, not to a generic cleanup list.

These checks are not optional decoration. PostgreSQL explicitly says FORCE does not clear prepared transactions, active logical replication slots or subscriptions in the target database. If any row appears, stop and obtain the owning system’s plan.

6. Run a fresh second snapshot to expose reconnect races

Commit the first read-only transaction and save its output with a UTC timestamp. After a short interval chosen for your application’s connection-pool cycle, open a new read-only transaction and rerun the same queries. Do not use the cached first transaction as snapshot two.

Compare stable keys rather than row order: session PID plus backend_start, prepared-transaction GID, replication slot name and subscription OID or name. A new PID with the same role, application and client family suggests a reconnecting pool or worker. A provider-owned role that appears intermittently is a managed-service escalation, not evidence that it is safe to race a drop between checks.

If the two snapshots disagree, the correct result is reconnect-race. Preserve both. A zero count during one instant does not establish quiescence when an owner is actively repopulating sessions.

7. Classify the owner branch and stop at the boundary

Application-pool: repeated client backends share a role, application and endpoint, or disappear and return with new PIDs. Send the ledger to the service owner. Admin-tool: the client name or local socket points to pgAdmin, psql, an IDE or migration tool; close it through its owner’s normal workflow. Background-worker: backend_type or application context identifies a worker; escalate to the component owner rather than treating it as an anonymous user.

Prepared-transaction: pg_prepared_xacts has a target row; the transaction coordinator owns the decision. Replication-object: a logical slot or subscription names the target; the replication owner must decide its lifecycle. Managed-service: a provider or superuser role is visible but the customer role cannot act; open a provider case with both snapshots.

Visibility-incomplete: the collector lacks pg_read_all_stats, relevant fields are redacted, the server endpoint is ambiguous, or a required catalog query is denied. This branch always stops. Ask an authorized database administrator or provider to rerun the same ledger; do not infer absence from a permission failure.

8. Save a redacted evidence bundle, not a cleanup script

Keep the incident time, target name and OID, maintenance database, endpoint and version, collector role and PID, pg_read_all_stats result, track_activities result, both snapshots, classification, unresolved fields and the owner or team responsible for the next decision. Redact client addresses when the support route does not need them.

Do not include full SQL text, passwords, connection strings or subconninfo. Hash the saved bundle if it will move between teams, and record who collected it. The artifact should let another authorized operator distinguish “no visible sessions” from “no blockers” and “collector could not see the blockers.”

The exit test is deliberately strict: every visible session and cluster object has an owner; both snapshots are stable; the collector had sufficient visibility; the target, endpoint and maintenance database are proven; backup and downtime decisions live in their own approved change. Until then, the result is evidence-needed—not permission to terminate, revoke, restart, rename or drop.

9. Run the inline read-only worksheet twice

Set target_db to the exact database name and run this worksheet from postgres or another maintenance database. The script is 2,365 UTF-8 bytes with SHA-256 e787aced94fd049e0e6335d61a51bca6ef0d659f88264e0e2153276f273fc196. It opens a repeatable-read, read-only transaction and selects identity, database, session, prepared-transaction, logical-slot and subscription evidence without selecting raw query text or subscription connection strings.

```sql \set ON_ERROR_STOP on \pset pager off \if :{?target_db} \else \echo 'Set target_db before running, for example: \\set target_db my_database' \quit 2 \endif BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY; SELECT clock_timestamp() AT TIME ZONE 'UTC' AS captured_at_utc, current_database() AS maintenance_database, :'target_db' AS target_database, current_database() = :'target_db' AS collector_is_on_target, current_user, session_user, pg_backend_pid() AS collector_pid, inet_server_addr()::text AS server_address, inet_server_port() AS server_port, current_setting('server_version') AS server_version, current_setting('server_version_num') AS server_version_num, pg_is_in_recovery() AS server_in_recovery, current_setting('track_activities') AS track_activities, pg_has_role(current_user, 'pg_read_all_stats', 'member') AS has_pg_read_all_stats; SELECT d.oid AS target_oid, d.datname AS target_database, pg_get_userbyid(d.datdba) AS database_owner, d.datallowconn AS connections_allowed, d.datconnlimit AS connection_limit, has_database_privilege(current_user, d.oid, 'CONNECT') AS collector_has_connect FROM pg_database AS d WHERE d.datname = :'target_db'; SELECT a.pid, a.leader_pid, a.usename, a.application_name, a.client_addr::text AS client_address, a.client_port, a.backend_type, a.state, a.backend_start, a.xact_start, a.query_start, a.state_change, a.wait_event_type, a.wait_event FROM pg_stat_activity AS a WHERE a.datname = :'target_db' ORDER BY a.backend_type, a.usename, a.application_name, a.client_addr, a.pid; SELECT x.transaction, x.gid, x.prepared, x.owner, x.database FROM pg_prepared_xacts AS x WHERE x.database = :'target_db' ORDER BY x.prepared, x.gid; SELECT s.slot_name, s.plugin, s.slot_type, s.database, s.temporary, s.active, s.active_pid, s.xmin, s.catalog_xmin, s.restart_lsn, s.confirmed_flush_lsn FROM pg_replication_slots AS s WHERE s.database = :'target_db' ORDER BY s.slot_name; SELECT s.oid AS subscription_oid, s.subname AS subscription_name, pg_get_userbyid(s.subowner) AS subscription_owner, s.subenabled AS enabled, s.subslotname AS slot_name, s.subpublications AS publications FROM pg_subscription AS s JOIN pg_database AS d ON d.oid = s.subdbid WHERE d.datname = :'target_db' ORDER BY s.subname; COMMIT; ```

The same bytes passed a static audit for the read-only transaction boundary, target parameter use, all five catalog families, absence of raw query text and subconninfo, and absence of state-changing SQL statements. That audit proves only what the text contains; it is not a live PostgreSQL integration test.

Before operational use, an authorized administrator should run the worksheet in a non-production cluster with the same major version and managed-service restrictions, inspect every result set, save the script hash with snapshot one, commit, wait one pool cycle, and run the exact same bytes again as snapshot two. A denied catalog, null field or version mismatch remains an unresolved boundary; do not remove the failed check to make the ledger look complete.