"database is being accessed by other users" error when dropping a PostgreSQL database - ApsaraDB RDS

Symptom

When you run DROP DATABASE on an ApsaraDB RDS for PostgreSQL instance, the operation fails with the following error:

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

The database name and session count may vary.

Cause

PostgreSQL does not allow you to drop a database while active connections exist. This error occurs when other sessions, such as application connections, monitoring tools, or idle client sessions, remain connected to the target database.

Solution

Terminate all connections to the target database, then drop it.

Note

Before you modify instance configurations or data, create snapshots or enable RDS log backup. If you shared sensitive information such as usernames and passwords in Alibaba Cloud Management Console, change the credentials promptly.

Step 1: Connect to a different database

Connect to the postgres database or any other database on the same instance. You cannot drop a database while connected to it.

Step 2: Identify active connections

Run the following query to list all active connections to the target database:

SELECT pid, usename, application_name, client_addr, state
FROM pg_stat_activity
WHERE datname = '<database_name>';

Replace <database_name> with the name of the database you want to drop.

Step 3: Terminate the connections

Run the following statement to terminate all connections to the target database:

SELECT pg_terminate_backend(pid)
FROM (SELECT pid FROM pg_stat_activity WHERE datname = '<database_name>') a;

Replace <database_name> with the name of the database you want to drop.

The pg_terminate_backend function sends a termination signal to each backend process connected to the specified database. It returns true for each process that received the signal.

Step 4: Drop the database

After all connections are terminated, drop the database:

DROP DATABASE <database_name>;

Alternative: use the FORCE option (PostgreSQL 13+)

If your ApsaraDB RDS for PostgreSQL instance runs PostgreSQL 13 or later, you can use the FORCE option to terminate connections and drop the database in a single command:

DROP DATABASE <database_name> WITH (FORCE);

Note

The FORCE option does not work if the database has prepared transactions, active logical replication slots, or subscriptions. Resolve those conditions first, then retry the command.

Applicable scope

ApsaraDB RDS for PostgreSQL