Databases - Practical PostgreSQL - Creating a Database

Databases - Practical PostgreSQL
Previous Page	Home	Next Page

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

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

Databases - Practical PostgreSQL
Previous Page	Home	Next Page