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.
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)
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).
Warning
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.
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.
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 called 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, consisting of 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 called 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
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
