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:
psql.exe -d clusterdatabasename -U admin -h host -p port
psql.exe -d postgres -U administrator -h localhost -p 2732
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:
SELECT
*
FROM
pg_stat_activity
WHERE
datname='target_database';
SELECT
pg_terminate_backend (pg_stat_activity.pid)
FROM
pg_stat_activity
WHERE
pg_stat_activity.datname='target_database';
DROP DATABASE target_database
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;
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;
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:
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.
SELECT
pg_terminate_backend (pg_stat_activity.pid)
FROM
pg_stat_activity
WHERE
pg_stat_activity.datname = 'testdb1';
DROP DATABASE testdb1;
Still have questions? We can help. Submit a case to Technical Support.