Between the Software and the Database

September 23, 2003

You have downloaded and installed the PostgreSQL software but what should you do next? Setting up an environment properly to run any software is paramount to flawless use and a happy experience.

This article is one in a series of articles that I have been writing on getting the PostgreSQL database up and running. It looks at the step in-between loading the software and creating a true database that you can use. While there are some issues that are raised in this article, I still believe that this database environment has merit and should draw your consideration for many reasons. While I am not an expert on this database, the issues I have should make you question and research the solutions to them. I know I will and will keep you posted to my progress.

Where is the Data Going to Live

This to me seems to be a flaky part of the runtime environment, but for some reason PostgreSQL needs to know that you have set aside an area on disk for a database to live. What strikes me as unusual, not to mention it will also cause performance problems, is that this is a single area on disk. Let me state that again, ONE directory. This 'is not going to be a huge problem for those disk farms where you can logically group disks into one logical directory and have many different paths to data. However, for the small shop that is working with small discrete drives, or those old dinosaur DBAs who like to manually move their data files around, it may cause a problem. Now I did do a quick search of the net and noticed that there are ways to move things around a bit through the use of symbolic links, but that is for another time.

This data area is given the name of a database cluster by Postgres and in reality is a group of databases that you will create later, to occupy the same area on disk and a single instance of the PostgreSQL software. The area of disk for database consumption is called the data area. After you go through the steps of creating this database cluster, you will have a single database called template1, which you will in turn use to create additional databases in this cluster. The sequence of events for creating the database cluster and data area is shown in Listing 1. The steps are quite easy. First login as root and create the directory where you wish the data area to be and then change the ownership of the directory structure. Secondly, issue the initdb command with the -D option to show where the data directory will be. There are a few more options to the creation of the database cluster that you can research and see if you need by issuing the initdb command with the -help option. You can look at this output in Listing 2. Of notice is the -U and -W option. These allow you to specify the superuser name of the database cluster along with a password. Listing 3 shows the output for this type of configuration. The only difference is a prompt for the new supperuser password. I personally like this option because it gives an additional level of security for access to the database.

After the initdb script runs, I would encourage you to look into the data directory and see what has been created. There are a few directories and a few configuration files. If anything, look at the postgresql.conf configuration file. There are many options for tuning the instance and you should at least get an idea of what there is so as you encounter problems during runtime you will have some notion of what you might be able to tweak.

Listing 1
Create a database cluster and data area

bash-2.05$ su - root
Password:
[root@koopgate root]# mkdir /u04/pgsql
[root@koopgate root]# mkdir /u04/pgsql/data
[root@koopgate root]# chown -R postgres /u04/pgsql

[root@koopgate root]# su - postgres
[postgres@koopgate postgres]$ initdb -D /u04/pgsql/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale en_US.
This locale setting will prevent the use of indexes for pattern matching
operations.  If that is a concern, rerun initdb with the collation order
set to "C".  For more information see the Administrator's Guide.

Fixing permissions on existing directory /u04/pgsql/data... ok
creating directory /u04/pgsql/data/base... ok
creating directory /u04/pgsql/data/global... ok
creating directory /u04/pgsql/data/pg_xlog... ok
creating directory /u04/pgsql/data/pg_clog... ok
creating template1 database in /u04/pgsql/data/base/1... ok
creating configuration files... ok
initializing pg_shadow... ok
enabling unlimited row size for system tables... ok
initializing pg_depend... ok
creating system views... ok
loading pg_description... ok
creating conversions... ok
setting privileges on built-in objects... ok
vacuuming database template1... ok
copying template1 to template0... ok

Success. You can now start the database server using:

    /usr/local/pgsql/bin/postmaster -D /u04/pgsql/data
or
    /usr/local/pgsql/bin/pg_ctl -D /u04/pgsql/data -l logfile start

Listing 2
Options to the initdb command

$ initdb --help
initdb initializes a PostgreSQL database cluster.

Usage:
  initdb [OPTION]... [DATADIR]

Options:
 [-D, --pgdata=]DATADIR     location for this database cluster
  -E, --encoding=ENCODING   set default encoding for new databases
  --locale=LOCALE           initialize database cluster with given locale
  --lc-collate, --lc-ctype, --lc-messages=LOCALE
  --lc-monetary, --lc-numeric, --lc-time=LOCALE
                            initialize database cluster with given locale
                            in the respective category (default taken from
                            environment)
  --no-locale               equivalent to --locale=C
  -U, --username=NAME       database superuser name
  -W, --pwprompt            prompt for a password for the new superuser
  --help                    show this help, then exit
  --version                 output version information, then exit

Less commonly used options:
  -d, --debug               generate lots of debugging output
  -L DIRECTORY              where to find the input files
  -n, --noclean             do not clean up after errors

If the data directory is not specified, the environment variable PGDATA is used.

Listing 3
Creation of database cluster with superuser and password option

bash-2.05$ su - root
Password:
[root@koopgate root]# mkdir /u04/pgsql
[root@koopgate root]# mkdir /u04/pgsql/data
[root@koopgate root]# chown -R postgres /u04/pgsql

[root@koopgate root]# su - postgres
[postgres@koopgate postgres]$ initdb -D /u03/pgsql/data -U mysuperuser -W
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale en_US.
This locale setting will prevent the use of indexes for pattern matching
operations.  If that is a concern, rerun initdb with the collation order
set to "C".  For more information see the Administrator's Guide.

Fixing permissions on existing directory /u03/pgsql/data... ok
creating directory /u03/pgsql/data/base... ok
creating directory /u03/pgsql/data/global... ok
creating directory /u03/pgsql/data/pg_xlog... ok
creating directory /u03/pgsql/data/pg_clog... ok
creating template1 database in /u03/pgsql/data/base/1... ok
creating configuration files... ok
initializing pg_shadow... ok
Enter new superuser password:
Enter it again:
setting password... ok
enabling unlimited row size for system tables... ok
initializing pg_depend... ok
creating system views... ok
loading pg_description... ok
creating conversions... ok
setting privileges on built-in objects... ok
vacuuming database template1... ok
copying template1 to template0... ok

Success. You can now start the database server using:

    /usr/local/pgsql/bin/postmaster -D /u03/pgsql/data
or
    /usr/local/pgsql/bin/pg_ctl -D /u03/pgsql/data -l logfile start







The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers