|
 |
|
| |
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
dropdb
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.
|
Warning
|
|
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
permanent
deletion of the database. This action cannot be undone, nor can it be executed within a
transaction block.
|
The syntax for the DROP DATABASE SQL command is as follows:
DROP DATABASE
dbname
In this syntax,
dbname
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
template1=#
DROP DATABASE example;
DROP DATABASE
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
dbname
(in this case,
example).
Similar to the
createdb
script, there is another command-line wrapper called
dropdb
that executes the DROP DATABASE SQL command. The only
functionality that
dropdb
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
dropdb
script is as follows:
dropdb [
options
]
dbname
In this syntax,
dbname
is the name of the database to be permanently removed from PostgreSQL,
and
options
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
-i
, or
- -interactive
,
flag.
Here is the complete list of options for
dropdb
:
- -h
HOSTNAME
, - -host=
HOSTNAME
-
The
HOSTNAME
that will be connected to, to drop the database. Defaults to
localhost
, or a host defined by the PGHOST environment variable.
- -p
PORT
, - -port=
PORT
-
Specifies that the database connection is made on port
PORT
, rather than the
default port (usually 5432, though it may have been configured differently when PostgreSQL was
compiled, by the
- -with-pgport
flag).
- -U
USERNAME
, - -username=
USERNAME
-
Specifies that the username
USERNAME
is the user who connects to PostgreSQL
(rather than the name of the system user executing
dropdb
) to drop the database.
- -W, - -password
-
Accepts no parameters, and causes a password prompt, which happens automatically if the
pg_hba.conf
file is configured not to
trust
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
dropdb
.
- -q, - -quiet
-
Accepts no parameters, and causes no output to be sent to
stdout
(though errors will
still be sent to
stderr
).
It is prudent to always execute the
dropdb
command with the
-i
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
-i
interactive
flag, as the manager user.
Example 9-13. Using the dropdb command
[jworsley@booktown ~]$
dropdb -U manager -i example
Database "example" will be permanently deleted.
Are you sure? (y/n)
y
DROP DATABASE
|
|
|
