Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com
Answertopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Removing a Database

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.

Using DROP DATABASE

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).

Using dropdb

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
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire