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… okSuccess. 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 exitLess 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 errorsIf 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… okSuccess. 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
