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.

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, but you must have the usecreatedb right set in the pg_shadow 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 9-9 illustrates an example query to the pg_user view to check for usecreatedb rights for the guest user.

Example 9-9. Checking usecreatedb rights

template1=>  SELECT usecreatedb FROM pg_user WHERE usename='guest';
 usecreatedb
-------------
 f
(1 row)

Using CREATE DATABASE

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

CREATE DATABASE dbname
       [ WITH [ LOCATION = 'dbpath' ]
              [ TEMPLATE = template ]
              [ ENCODING = encoding ] ]

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:

LOCATION = 'dbpath'

The dbpath value describes an environment variable, initialized in the shell environment of the user which runs the PostgreSQL backend. For example, you might put the following line in /home/postgres/.bash_profile :

export PGDATA2="/usr/local/pgsql/data2"

Thus, enabling the use of PGDATA2 as a variable (once PostgreSQL has been restarted with the environment variable set in memory), and a valid value for dbpath. This is a general security precaution to prevent users from writing to an inappropriate location in the filesystem.

If the LOCATION keyword is omitted, PostgreSQL will create the database in the default data directory (e.g., /usr/local/pgsql/data).

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

Note

The value of dbpath passed to the LOCATION keyword must be set to the name of an environment variable. This variable may not literally describe a system path (e.g., /usr/local/pgsql/data2) unless the CPPFLAGS=-DALLOW_ABSOLUTE_DBPATHS argument was passed to the gmake command when PostgreSQL was originally compiled and installed.

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 9-10 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 9-10. Creating a database

[jworsley@booktown ~]$ psql -U manager template1
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash 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).

Note

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.

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:

createdb [ options ] dbname [ description ]

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 the section titled Documenting a Database 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 (usually an entire word).

The following are the options available to createdb:

-D PATH, - -location=PATH: Equivalent to the LOCATION keyword used with the CREATE DATABASE command. PATH should be the environment variable (set for the user running the PostgreSQL backend) which is set to the system path where the new database files were created.
-T TEMPLATE, - -template=TEMPLATE: Equivalent to the TEMPLATE keyword used with the CREATE DATABASE command. TEMPLATE should be the identifier describing the database (e.g., template0) to use as the basis from which to create the new database, duplicating all objects.
-E ENCODING, - -encoding=ENCODING: Equivalent to the ENCODING keyword used with the CREATE DATABASE command. ENCODING describes a valid encoding string constant, as explained in Appendix A. A numeric constant cannot be passed through createdb, even though one can be passed through CREATE DATABASE. This is because ENCODING is always passed to createdb as a string constant.
-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.
-e, - -echo: Accepts no parameters, and causes the CREATE DATABASE statement sent to PostgreSQL to be displayed to the screen as it is executed by createdb.
-q, - -quiet: Accepts no parameters, and causes no output to be sent to stdout (though errors will still be sent to stderr).

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 the section titled Using CREATE DATABASE earlier in this chapter for more information on these messages.

Example 9-11 shows the use of the createdb application, creating the new database example as the manager user, in the directory described by the PGDATA2 variable. Notice that both forms of options may be simultaneously supplied (single-dashed, and GNU-style).

Example 9-11. Using the createdb application

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

Removing a Database

Note

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

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 titled 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