PostgreSQL Drop Database: How to Remove a DB

To drop (delete) a PostgreSQL database, you use the DROP DATABASE SQL command or the dropdb command-line utility. You must be the database owner or a superuser, and no other connections can be active on the database you are removing. This article explains the syntax, prerequisites, common pitfalls, and safe alternatives.

What Does DROP DATABASE Do?

DROP DATABASE permanently deletes the database and all its objects: tables, indexes, views, functions, and every row of data stored in them. The operation cannot be undone unless you have a backup. After the command succeeds, the database name becomes available for a new database, and any files associated with the database are removed from the server’s data directory.

In PostgreSQL, a database is a logical container for schemas, which in turn hold tables and other objects. Dropping the database removes the entire container. It does not remove the PostgreSQL user (role) that owns it, nor does it affect other databases on the same server.

Prerequisites and Permissions

Before you can drop a database, you must meet two conditions:

• Ownership or superuser: You must be the owner of the database or a superuser. The default superuser is often postgres. If you are neither, the command fails with a permission denied error.
• No active connections: PostgreSQL refuses to drop a database if any client is currently connected to it. This includes your own connection if you are issuing the command from within that database.

To check who is connected, you can query the pg_stat_activity view:

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

If there are connections, you can terminate them using pg_terminate_backend(pid) for each PID, or you can use the FORCE option available in PostgreSQL 13 and later.

Using the DROP DATABASE Command

The basic syntax is:

DROP DATABASE database_name;

You cannot execute this while connected to the database you want to drop. Connect to a different database (e.g., postgres) first:

psql -U postgres -c "DROP DATABASE mydb;"

If you are already in psql, you can switch to another database with \connect postgres and then run the command.

To force termination of existing connections (PostgreSQL 13+):

DROP DATABASE mydb WITH (FORCE);

This will disconnect all sessions from the database and then drop it. Use with caution—any unsaved work in those sessions is lost.

Using the dropdb Utility

The dropdb command-line tool is a wrapper around DROP DATABASE and is convenient for scripting. Its basic usage:

dropdb -U postgres mydb

Common options include:

• -h host – server host
• -p port – server port
• -f – force drop, equivalent to WITH (FORCE) (PostgreSQL 13+)
• -i – prompt for confirmation before dropping

For example, to force-drop a database on a remote server with confirmation:

dropdb -h db.example.com -U admin -f -i mydb

Common Errors and How to Fix Them

Here are the typical errors you might encounter and their solutions:

• “database is being accessed by other users” – There are active connections. Terminate them with pg_terminate_backend or use WITH (FORCE).
• “permission denied to drop database” – You are not the owner or a superuser. Ask the administrator to grant you the necessary privileges or have them run the command.
• “database does not exist” – The name is misspelled or the database was already dropped. Use \l in psql to list databases.
• “cannot drop the currently open database” – You are connected to the database you are trying to drop. Connect to another database first.

Safety: Backup Before You Drop

Because DROP DATABASE is irreversible, always create a backup first. Use pg_dump to create a logical backup of the database:

pg_dump -U postgres -Fc mydb > mydb.dump

This creates a compressed custom-format dump that can be restored later with pg_restore. Alternatively, you can use a plain SQL dump with -Fp and restore with psql. If you have a file-system-level backup (e.g., taking a snapshot of the data directory), that also works, but logical dumps are more portable.

After dropping, you cannot recover the data unless you have a backup. Test your backup by restoring it to a different database name before you rely on it.

Alternatives: TRUNCATE and DROP SCHEMA

Sometimes you do not need to drop the entire database—just its contents. Here are two safer alternatives:

• TRUNCATE: Removes all rows from a table but keeps the table structure. Use TRUNCATE TABLE table_name; or TRUNCATE TABLE table1, table2; to clear multiple tables. This is faster than DELETE without a WHERE clause and does not scan every row. However, it cannot be used on tables referenced by foreign keys unless you also truncate the referencing tables.
• DROP SCHEMA: If you want to remove all objects within a schema but keep the database, use DROP SCHEMA schema_name CASCADE; This will drop all tables, views, functions, etc., in that schema. The CASCADE option also drops any objects that depend on those objects.

These alternatives are useful when you want to reset a database’s data without losing the database itself, which is often faster and less disruptive.

Frequently Asked Questions

Can I drop a database while connected to it?

No. PostgreSQL will not let you drop the database you are currently connected to. You must connect to another database (e.g., postgres) before running DROP DATABASE. This is a safety measure to prevent accidental deletion.

What is the difference between DROP DATABASE and DELETE DATABASE?

There is no DELETE DATABASE command in PostgreSQL. DROP DATABASE is the only way to remove an entire database. DELETE is used to remove rows from a table, not a database.

How do I drop a database if I forgot its name?

Run \l in psql to list all databases. You can also query SELECT datname FROM pg_database; to see the names.

Will dropping a database free up disk space immediately?

Yes, the disk space used by the database is released back to the operating system after a successful drop. However, if you are using tablespaces on separate disks, the space is freed on those disks. The server may not immediately report the freed space if the filesystem caches it, but it is available for reuse.

Can I drop multiple databases at once?

No, PostgreSQL does not support dropping multiple databases in a single command. You must run DROP DATABASE for each one separately, or use a script loop. For example, in bash: for db in db1 db2 db3; do dropdb $db; done.

What happens to the roles (users) that owned the dropped database?

Roles are cluster-wide objects and are not dropped when a database is dropped. The role remains, and it can own other databases. If you want to remove the role as well, you must do that separately with DROP ROLE.

Related

• Ryobi Dual Charger Not Charging Both Batteries? Fix It
• Evaluate AI Coding Tools for Engineering Teams
• Domain Name Tips: How to Choose a Good One

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2