Initializing the Filesystem

Before you can create a database in a database cluster, the filesystem must first be initialized. There are two ways to initialize the filesystem for use with PostgreSQL; you may either use the initdb application to create an entirely new database cluster (as was used to prepare your initial database system, in Chapter 2), or you may use the initlocation application to prepare a secondary data directory.

A database cluster represents several associated databases. A single instance of the postmaster process can only access one database cluster at a time. Alternatively, through initlocation, you can create databases that are part of an existing cluster that just happen to be stored in another data directory.

The following sections cover these two applications.

Initializing a Database Cluster

Use the initdb program to create and initialize a new database cluster within your filesystem. Again, a database cluster is the framework upon which PostgreSQL databases are created. You should already have one cluster in the data directory which was initialized in Chapter 2.

You may use initdb to initialize a new data directory for a database cluster, and instruct postmaster to start up using that data cluster instead of the default. Alternatively, you may have two postmaster processes running at the same time with different database clusters, provided that they are configured to listen on different ports.

After you use initdb to create a new database cluster, that new cluster's filesystem will be owned by whatever operating system user you were logged in as when issuing the command.


Do not run the initdb program while logged in as the root user! The cluster needs to be created and owned by whichever normal user is going to become the new cluster's database superuser.

You can also use initdb to correct a corrupted template1 database by executing initb with the -t (or - -template) parameter. This will re-generate the template1 database from scratch.

Here is the syntax for initdb:

  initdb [ -D dbdir | - -pgdatadbdir ]
         [ -i sysid | - -sysid sysid ]
         [ -W | - -pwprompt ] 
         [ -E encoding | - -encoding=encoding ]
         [ -L libdir | - -pglib=libdir ]
         [ -n | - -noclean ]
         [ -d | - -debug ]
         [ -t | - -template ]

The following are the valid options for initdb:

[ -D dbdir | - -pgdata=dbdir ]

The directory that you wish to initialize a new database cluster within. If you do not specify a directory name, the command will look at the PGDATA environment variable.

[ -i sysid | - -sysid=sysid ]

The system ID of the database superuser to be created. If unspecified, the ID will be the operating system ID of the system user who runs the initdb program.

[ -W | - -pwprompt ]

Prompt for a password upon connection.

[ -E encoding | - -encoding=encoding ]

The name of the multi-byte encoding type of the template database within this cluster. Whatever type you set here will become the default type for any databases you later create using this cluster. This is only relevant if you have enabled multi-byte encoding in PostgreSQL.

[ -l libdir | - -pglib=libdir ]

The location of the PostgreSQL library files used by initdb when creating a database cluster. It is rarely necessary to use this parameter. The location of the libraries is usually known by the initdb program, and if it isn't known, initdb will prompt you for the location.

[ -t | - -template ]

The template switch, which causes initdb to re-initialize only the template1 database within an already existing database cluster. This can help during PostgreSQL version updates, or if your template1 database ever becomes corrupted, or is lost.

[ -n | - -noclean ]

The noclean switch, which specifies that initdb should not clean up its files in the event that it is unable to complete cluster creation due to an error. This parameter is only useful for debugging purposes.

[ -d | - -debug ]

The debug switch, which causes debugging information from the creation of the catalog tables to be displayed.

If the command completes successfully, initdb will have created a database cluster in the specified data directory; this cluster can then be used by the backend to store its databases.

Example 9-7 initializes a new database cluster in the /usr/local/pgsql/booktown directory:

Example 9-7. Initializing a New Database Cluster

[postgres@booktown ~]$ initdb /usr/local/pgsql/booktown
This database system will be initialized with username "postgres".
This user will own all the data files and must also own the server process.

Creating directory /usr/local/pgsql/booktown
Creating directory /usr/local/pgsql/booktown/base
Creating directory /usr/local/pgsql/booktown/global
Creating directory /usr/local/pgsql/booktown/pg_xlog
Creating template1 database in /usr/local/pgsql/booktown/base/1
DEBUG:  database system was shut down at 2001-08-27 16:51:07 PDT
DEBUG:  CheckPoint record at (0, 8)
DEBUG:  Redo record at (0, 8); Undo record at (0, 8); Shutdown TRUE
DEBUG:  NextTransactionId: 514; NextOid: 16384
DEBUG:  database system is in production state
Creating global relations in /usr/local/pgsql/booktown/global
DEBUG:  database system was shut down at 2001-08-27 16:51:14 PDT
DEBUG:  CheckPoint record at (0, 108)
DEBUG:  Redo record at (0, 108); Undo record at (0, 0); Shutdown TRUE
DEBUG:  NextTransactionId: 514; NextOid: 17199
DEBUG:  database system is in production state
Initializing pg_shadow.
Enabling unlimited row width for system tables.
Creating system views.
Loading pg_description.
Setting lastsysoid.
Vacuuming database.
Copying template1 to template0.

Success. You can now start the database server using:
    /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/booktown
    /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/booktown -l logfile start

Initializing a Secondary Database Location

If you are not interested in creating a new database cluster, but simply wish to store a particular database in a different data directory, use initlocation. The initlocation program creates the directories needed for a secondary database storage area. For more information on how to create a database in a secondary data storage area, refer to the Section called Creating and Removing a Database" later in this chapter. Here is the syntax for initlocation:

  initlocation directory

In this syntax, directory is the path for the new secondary database location. This command should be run as the user which runs the postmaster, so that it will have the necessary rights in the created path.

Example 9-8 demonstrates how to initialize a secondary database storage area in the /usr/local/pgsql/booktown2 directory:

Example 9-8. Initializing a Secondary Database Location

[postgres@booktown ~]$ initlocation /usr/local/pgsql/booktown2