How to Delete Database in PostgreSQL

In this tutorial, we will see how to delete database in PostgreSQL. Deleting the database will permanently remove all the catalog entries and data directories. This action can not be undone. However, It’s a good practice to delete unused databases and keep the DB server clean.

Let’s see how to do it.

DROP Database Statement

We can delete the database in PostgreSQL using DROP Database statement. The syntax of DROP Database statement is as below:

DROP DATABASE [IF EXIST] database_name;

In the above syntax, IF EXIST is the clause that specifies that do not throw any error if the database which is being deleted does not exist. It will show a notice instead.

database_name is the name of database which you are going to delete.

Example of PostgreSQL database delete is:

DROP DATABASE mydb;

Only database owners and superusers have access to delete the database. Also if there are any active connections to the database, that database can not be deleted directly unless its active connections are closed.

Let’s see how to delete the database that has active connections:

First, check for the activities that are active in the database using pg_stat_activity view.

SELECT *
FROM pg_stat_activity
WHERE datname = '<database_name>';

Then, terminate the active sessions by using following query:

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

Note, If you are running above query in PostgreSQL version below 9, use procpid instead of pid column.

In the last, execute DROP Database statement.

WITH (FORCE)

Postgres version higher than 13 provides WITH (FORCE) option which will allow you to delete the database with active connections.

DROP DATABASE <database_name> WITH (FORCE);

dropdb Utility

There is one more way to delete the database in PostgreSQL: using dropdb utility. This is a command which is identical to DROP DATABASE statement, but it provides more options like deleting the remote database, interactive delete etc.

Syntax:

dropdb <options> <database name>;

Option	Description
-e	Verbose the output.
-f	Attempts to terminate all current connections before dropping the database. It’s same as force delete.
-i	Prompts verification before executing database deletion for interactive deletion.
-V	The console prints the dropdb utility version.
--if-exists	Prints a notice instead of an error if the database does not exist.
--help	Show the help menu.
-h <host>	Specifies the hostname of the remote machine where the database is located
-p <port>	Specifies the TCP port where the remote database server is listening.
-U <username>	Connect as the specified user to the remote database.
-w --no-password	Never issue the password prompt. Useful for batch and script jobs when no user is present.
-W	Force password prompt. Without the option, the server loses the connection attempt if a password is necessary.
--maintenance-db=<database name>	The option specifies the database name connection.