PostgreSQL DROP DATABASE: Syntax and Active Connections

DROP DATABASE permanently removes a PostgreSQL database and its data. Before running it, confirm the exact database name, make any required backup, and connect to a different database (commonly postgres). The command cannot run inside a transaction block.

Syntax and permissions

DROP DATABASE [IF EXISTS] database_name [WITH (FORCE)];

Only the database owner can drop the database. IF EXISTS suppresses the error when the named database does not exist; it does not bypass ownership, connection, or transaction restrictions.

For example, connect as the database owner to a different database, then drop the target:

psql --username=db_owner --dbname=postgres

DROP DATABASE IF EXISTS test_db;

If you are connected to the target database in psql, switch away first. \c is a psql meta-command, so do not end it with a SQL semicolon:

\c postgres

Then run DROP DATABASE test_db; as a separate SQL command. See PostgreSQL CREATE DATABASE if you need a disposable database to follow the example.

Handle active connections

A database cannot be dropped while sessions are connected to it. Check active sessions from another database:

SELECT pid, usename, application_name, client_addr
FROM pg_stat_activity
WHERE datname = 'test_db'
  AND pid <> pg_backend_pid();

WITH (FORCE) is available starting in PostgreSQL 13. It attempts to terminate connections the command is allowed to terminate:

DROP DATABASE test_db WITH (FORCE);

FORCE can still fail if prepared transactions, active logical replication slots, subscriptions, or sessions that the current user cannot terminate remain. Review and resolve those blockers rather than repeatedly forcing the command. For older server versions without FORCE, disconnect confirmed sessions individually with pg_terminate_backend(pid) only when you have permission and intend to stop those sessions.

PostgreSQL 13 introduced the FORCE option; see the PostgreSQL 13 release notes.

Common errors

• Cannot drop the currently open database: reconnect to postgres or another database, then issue the command.
• Database is being accessed by other users: inspect pg_stat_activity; use WITH (FORCE) only if terminating eligible sessions is acceptable.
• Cannot run inside a transaction block: execute the statement as a standalone command, not inside BEGIN/COMMIT or a migration tool that wraps it in a transaction.
• Permission denied: connect as the database owner or use an account with the required ownership privileges.

See the PostgreSQL documentation for DROP DATABASE and psql meta-commands.