Deleting Databases From CLI in PostgreSQL

 

Before You Begin

If a database is no longer needed, you can delete it. However, you must first connect to PostgreSQL with psql.

To connect to PostgreSQL with psql:

1. Navigate to installdirectory/pgsq-(version).
2. Run the following command:
   
   psql.exe -d clusterdatabasename -U admin -h host -p port
   
   where clusterdatabasename is the name of the cluster database, admin is the name of the administrator account, host is the host, and port is the port.
   
   For example,
   
   psql.exe -d postgres -U administrator -h localhost -p 2732

Steps

You can delete a database using the DROP DATABASE statement. For example, 

DROP DATABASE [IF EXISTS] name;

Where name is the name of the database that you want to delete.

Using [IF EXISTS] prevents an error if you try to delete a database that does not exist.

To delete a database:

Run the following command:

DROP DATABASE [IF EXISTS] name;

where name is the name of the database that you want to delete.

Use IF EXISTS to prevent an error when you delete a database that does not exist. If you try to delete a database that does not exist, PostgreSQL issues a warning.

When using the DROP DATABASE statement, be careful as this permanently deletes catalog entries and the data directory. This action cannot be undone.

Only the database owner can run the DROP DATABASE statement. You also cannot run the DROP DATABASE statement if there are active connections to the database. You must connect to another database (for example, PostgreSQL) to run the DROP DATABASE statement.

PostgreSQL also provides a dropdb utility program that allows you to delete a database using the DROP DATABASE statement.

To delete a database that still has active connections:

1. Locate the target database activities using the following command:
   
   SELECT
   *
FROM
   pg_stat_activity
WHERE
   datname='target_database'; 

2. Terminate the active connections using the following command:
   
   SELECT
   pg_terminate_backend (pg_stat_activity.pid)
FROM
   pg_stat_activity
WHERE
   pg_stat_activity.datname='target_database';

3. Run the following command:
   
   DROP DATABASE target_database

Example 1

The following example shows the PostgreSQL DROP DATABASE statement uses the databases created in the PostgreSQL create database tutorial. If these databases are not available, you can create them using the following commands:

CREATE DATABASE hrdb;
CREATE DATABASE hrdb;

Example 2

The following example shows how to delete the hrdb database database that does not have active connections. In this example, you want to remove the hrdb database using the hrdb owner to connect to a database other than hrdb database (for example, PostgreSQL).

DROP DATABASE hrdb;

Example 3

The following example shows how to delete the testdb1 database that has active connections:

DROP DATABASE testdb1;

However, after the command is complete, PostgreSQL issued the following error:

ERROR: database “testdb1” is being accessed by other users
SQL state: 55006
Detail: There is 1 other session using the database.

To delete the testdb1 database:

1. Run the following statement to determine the current activities on the testdb1 database:
   

   SELECT
   *
FROM
   pg_stat_activity
WHERE
   datname = ‘testdb1’
   
   Since the testdb1 database only has one connection from localhost, you can terminate the connection and delete the database.

2. Terminate the connection to the testdb1 database using the following statement:
   
   SELECT
   pg_terminate_backend (pg_stat_activity.pid)
FROM
   pg_stat_activity
WHERE
   pg_stat_activity.datname = 'testdb1';

3. Run the following command to delete testdb1database:
   
   DROP DATABASE testdb1;