Similar to its approach in creating databases, PostgreSQL offers two methods to remove a database permanently from your
system: the DROP DATABASE SQL command, and the
command-line executable. The use of these methods requires the usecreatedb
right to be set in the pg_shadow table for the user initiating the command.
Upon dropping a database, all tables, data, and other objects in that database are destroyed. The system files
associated with the database
are also physically removed
. PostgreSQL will not prompt you to verify the
deletion of the database. This action cannot be undone, nor can it be executed within a
The syntax for the DROP DATABASE SQL command is as follows:
In this syntax,
represents the name of the database to be removed from the system. Note
that no user may be connected to the database that you are trying to remove, or the command will fail. Example 9-12 demonstrates dropping a database called example.
Example 9-12. Using DROP DATABASE
DROP DATABASE example;
The DROP DATABASE server message indicates that the database was successfully
removed, and its associated system files deleted. Other messages you may receive from the command follow:
ERROR: DROP DATABASE: cannot be executed on the currently open database
This message indicates that you are connected to the database you are trying to remove. A database cannot be removed
from the system while you are actively connected to it.
ERROR: DROP DATABASE: database "example" is being accessed by other users
This message indicates that another user is connected to the database you are attempting to remove. You must wait until
all users are disconnected before being able to successfully remove a database.
ERROR: DROP DATABASE: database "example" does not exist
This message indicates that there is no database with the specified
(in this case,
Similar to the
script, there is another command-line wrapper called
that executes the DROP DATABASE SQL command. The only
provides, as compared to the DROP DATABASE command,
is that you execute it from a shell, and you can use the interactive flag to have it prompt you for confirmation.
The syntax for the
script is as follows:
In this syntax,
is the name of the database to be permanently removed from PostgreSQL,
describe each of the options available to the application. Most of these options exist
to describe the PostgreSQL connection options, and to mimic the options described in the Section called Using createdb
earlier in this chapter. The notable exception is the
Here is the complete list of options for
, - -host=
that will be connected to, to drop the database. Defaults to
, or a host defined by the PGHOST environment variable.
, - -port=
Specifies that the database connection is made on port
, rather than the
default port (usually 5432, though it may have been configured differently when PostgreSQL was
compiled, by the
, - -username=
Specifies that the username
is the user who connects to PostgreSQL
(rather than the name of the system user executing
) to drop the database.
- -W, - -password
Accepts no parameters, and causes a password prompt, which happens automatically if the
file is configured not to
the requesting host.
- -i, - -interactive
Accepts no parameters, and causes the user to be prompted to confirm the removal of the database before
actually destroying the data.
- -e, - -echo
Accepts no parameters, and causes the DROP DATABASE statement
sent to PostgreSQL to be displayed to the screen as it is executed by
- -q, - -quiet
Accepts no parameters, and causes no output to be sent to
(though errors will
still be sent to
It is prudent to always execute the
command with the
flag, as it
requires a confirmation before anything is actually removed from PostgreSQL. Example 9-13
demonstrates the removal of a database named example with the
flag, as the manager user.
Example 9-13. Using the dropdb command
[[email protected] ~]$
dropdb -U manager -i example
Database "example" will be permanently deleted.
Are you sure? (y/n)