PostgreSQL: The definitive guide
Prev	Chapter 2. Getting Started	Next

2.6. Creating and Removing a Database

PostgreSQL installs two default template databases. Upon creation, a new database is cloned from one of these templates. They are template0, and template1. Of these, you may only connect to template1. This is because the template0 database exists as an empty template, while template1 may be modified to include commonly used languages, functions, and even database objects, such as tables, views, or sequences. Neither of the template databases may be removed from the system.

The following sections cover creating and removing databases from PostgreSQL.

2.6.1. Creating a Database

PostgreSQL provides two methods for creating a new database: the CREATE DATABASE SQL command, and the createdb command-line executable. To use either of these methods requires that you have the necessary rights. You do not have to be a PostgreSQL superuser to create a database. You do however have to have either the usecreatedb right set in the pg_shadow table or rolcreatedb right set in the pg_roles table.

In 8.1 and above, users and groups are no longer distinct entities. They all work through a singular mechanism known as roles. You should focus on thinking about a user or groups role versus users and groups independently.

If you are unsure if your user has the the right to create databases you can check via a query to the pg_roles table.

If you are unsure of whether or not this right has been granted to your user, check through a query to the pg_user view (which in turn queries the pg_shadow table; only superusers may query the pg_shadow directly). The usecreatedb column in the pg_shadow table contains a boolean value, which reflects if this right has been granted. Example 2-24 illustrates an example query to the pg_user view to check for usecreatedb rights for the guest user.

Example 2-24. Checking usecreatedb rights

template1=> SELECT rolcreatedb FROM pg_roles WHERE rolname='jd';
 rolcreatedb
-------------
 f
(1 row)

2.6.1.1. Using CREATE DATABASE

The syntax for the CREATE DATABASE SQL command is as follows:

  CREATE DATABASE dbname
         [ WITH [ OWNER [=] dbowner ]
                [ TEMPLATE = template ]
                [ ENCODING = encoding ]
                [ TABLESPACE [=] tablespace ]
                [ CONNECTION LIMIT [=] connlimit ] ]

In this syntax, dbname is the name of the new database to be created. All database names must begin with an alphabetical character, and are limited to 31 characters in length. PostgreSQL allows any number of databases to be created in a given data directory (assuming there is available disk space).

By appending the optional WITH keyword, up to three more optional attributes may be specified:

OWNER = dbowner

Sets the owner of the database to be created. The owner should be the rolename of the user you wish to own the database. If unspecified the user executing the CREATE DATABASE statement will be used.

TEMPLATE = template

The template identifier refers to a database to "clone" in creating the new database. Any database objects within that database will be duplicated in the creation of the database dbname.

If unspecified, PostgreSQL will implicitly choose template1 as the database to duplicate objects from. If you wish for a completely fresh database to be created, you may specify template0 to avoid copying the objects with which you may have populated template1.

ENCODING = encoding

The encoding value can be either a string constant describing the encoding type (e.g., SQL_ASCII, LATIN1, etc), or its equivalent PostgreSQL numeric constant. The available PostgreSQL multibyte encoding formats, and their numeric constant values, are listed in Appendix A.

If the ENCODING keyword is unspecified, PostgreSQL will create a database using its default encoding. This is usually SQL_ASCII, though it may have been set to a different default during the initial configuration of PostgreSQL (see Chapter 2 for more on default encoding).

TABLESPACE = tablespace

This is the name of a tablespace you have created. For more information on tablespaces see Section 2.5.2. If unspecified the default will be used.

CONNECTION LIMIT = connlimit

The maximum number of connections allowed to this specific database. If unspecified the max_connections default will be used.

You must connect to a database prior to issuing the CREATE DATABASE command. If you have not yet created a database, you may "bootstrap" your way into creating one through the use of the default template1 database. By connecting to this database, you may create new databases which can then be connected to directly.

Once a database is created, the creator automatically becomes it's owner, or DBA (database administrator). This user will own each object within the database, and therefore be able to grant rights on those objects to other users. Be sure to create your databases with the user that you'll use to actively maintain the database with.

Example 2-25 demonstrates connecting to the template1 database as the Book Town managerial user named manager, and creating Book Town's example database, booktown. This example uses psql, but the same SQL syntax will work with any valid PostgreSQL client.

Example 2-25. Creating a database

[jd@jd pgsqldev]$ bin/psql template1
Welcome to psql 8.1devel, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=# CREATE DATABASE booktown;
CREATE DATABASE

The returned message CREATE DATABASE indicates that the database was created successfully. Other server messages returned may include the following:

ERROR: CREATE DATABASE: permission denied: This message indicates that the user attempting to create the database does not have the rights to create a database. This right (or lack thereof ) is indicated by the usecreatedb column in the pg_shadow table, described earlier in this chapter. See Chapter 10 for more information on enabling this right.
ERROR: CREATE DATABASE: database "booktown" already exists: This message indicates that a database with the specified dbname (in this example, booktown) already exists. You may not have two databases with the same name (even in different physical locations on the filesystem).

If another user is accessing a database that you wish to use as a template, you must wait until the user is no longer accessing it in order to do so.

2.6.1.2. Using createdb

PostgreSQL also provides a command-line wrapper to the CREATE DATABASE command, in an application called createdb. The only alternate functionality to createdb over its SQL counterpart is that it may be run directly from the command line, and it allows a comment to be added into the database, all in one command. In SQL, this would require at least two statements: the CREATE DATABASE statement, and a COMMENT statement.

The syntax for the createdb application is as follows:

  Usage:
  createdb [OPTION]... [DBNAME] [DESCRIPTION]

Options:
  -D, --tablespace=TABLESPACE  default tablespace for the database
  -E, --encoding=ENCODING      encoding for the database
  -O, --owner=OWNER            database user to own the new database
  -T, --template=TEMPLATE      template database to copy
  -e, --echo                   show the commands being sent to the server
  -q, --quiet                  don't write any messages
  --help                       show this help, then exit
  --version                    output version information, then exit

Connection options:
  -h, --host=HOSTNAME          database server host or socket directory
  -p, --port=PORT              database server port
  -U, --username=USERNAME      user name to connect as
  -W, --password               prompt for password

By default, a database with the same name as the current user is created.

In this syntax, dbname is the name of the database to be created, options consists of any of the listed optional flags, and description is the comment to be added with an implicit COMMENT command (see Section 4.1.4" for more on database comments).

The options arguments may be provided either as single-dashed flags, each followed by a space and an argument (e.g., -D PATH), or the GNU-style, double-dashed counterpart, each followed by an equals sign (=) and an argument, if necessary (e.g., - -location=PATH). Single-dashed flags will always consist of a single letter, while double-dashed flags will be more verbose, consisting of an entire word.

The following are the options available to createdb:

-D, - -tablespace=tablespace: Equivalent to the TABLESPACE keyword used with the CREATE DATABASE command.
-E,- -encoding=ENCODING: Equivalent to the ENCODING keyword used with the CREATE DATABASE command.
-O,- -owner=OWNER: Equivalent to the OWNER keyword used with the CREATE DATABASE command.
-T TEMPLATE , - -template=TEMPLATE: Equivalent to the TEMPLATE keyword used with the CREATE DATABASE command.
-e,- -echo: Cause PostgreSQL to echo the commands sent to the server to STDOUT.
-h HOSTNAME , - -host=HOSTNAME: The HOSTNAME that will be connected to, to create the database. Defaults to localhost, or the 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 createdb) to create 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.

The success and failure messages for createdb are identical to those created by CREATE DATABASE, though you may also receive connection errors similar to those received from psql if invalid host or user information is provided. See Section 2.6.1.1" earlier in this chapter for more information on these messages.

Example 2-26 shows the use of the createdb application, creating the new database example as the manager user.

Example 2-26. Using the createdb application

         [jworsley@booktown ~]$ createdb -U manager example
         CREATE DATABASE

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

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.

2.6.2.1. 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 2-27 demonstrates dropping a database called example.

Example 2-27. 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).

2.6.2.2. 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 Section 2.6.1.2" earlier in this chapter. The notable exception is the -i, or - -interactive, flag.

Here is the complete list of options for dropdb:

-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.
-i, - -interactive: Accepts no parameters, and causes the user to be prompted to confirm the removal of the database before actually destroying the data. You should always use this, just in case, remember once the drop is done, if you made a mistake you are restoring from backup.
-q, - -quiet: Accepts no parameters, and causes no output to be sent to stdout (though errors will still be sent to stderr).
-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.

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 2-28 demonstrates the removal of a database named example with the -i interactive flag, as the manager user.

Example 2-28. 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

Prev	Home	Next
Initializing the Filesystem	Up	Configuration of PostgreSQL