When dropping a PostgreSQL database fails or was completed unexpectedly, it usually means the database is still in use or recovery incomplete. Learn how to safely drop databases and recover from failed drop operations.
The "database dropped" error occurs when PostgreSQL encounters issues while executing a DROP DATABASE command or when a database has been partially dropped during recovery. This can happen due to active connections, permission issues, transaction states, or incomplete recovery from a previous operation. PostgreSQL prevents dropping databases that are currently connected to, ensuring data consistency and preventing accidental data loss.
Connect to the postgres or template1 database and check if there are active connections to your target database:
SELECT * FROM pg_stat_activity WHERE datname = 'your_database';This shows all active sessions connected to the database. If the output is empty, no sessions are connected.
If active connections exist, terminate them using:
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'your_database'
AND pid <> pg_backend_pid();This safely terminates all connections except your current session.
Connect to a different database before dropping. Use the postgres or template1 database:
psql -U postgres -d postgresThen verify your connection:
SELECT current_database();For PostgreSQL 13 and later with active connections, use the FORCE option:
DROP DATABASE your_database WITH (FORCE);For earlier versions or to prevent errors if the database does not exist:
DROP DATABASE IF EXISTS your_database;Or the standard command:
DROP DATABASE your_database;List all databases to confirm the target database no longer exists:
\lOr use the query:
SELECT datname FROM pg_database WHERE datname = 'your_database';An empty result confirms the database was successfully dropped.
For PostgreSQL versions 13 and earlier without FORCE support, manually terminate connections and close all client applications before dropping. If a database is stuck in an invalid state after failed recovery (showing "cannot connect to invalid database"), connect as superuser to the postgres database and use DROP DATABASE to clean up the invalid entry in pg_database catalog. Logical replication slots or subscriptions can prevent database drops even with FORCE; remove these first using DROP SUBSCRIPTION or DROP REPLICATION SLOT. For point-in-time recovery scenarios, ensure WAL archiving and recovery configuration are complete before attempting database drops. The dropdb utility is a convenient wrapper: dropdb --force your_database.
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL
ERROR 42501: must be owner of table
How to fix "must be owner of table" in PostgreSQL
trigger cannot change partition destination
How to fix "Trigger cannot change partition destination" in PostgreSQL
SSL error: certificate does not match host name
SSL error: certificate does not match host name in PostgreSQL
No SSL connection
No SSL connection to PostgreSQL