2. Compiling and Installing the best free RDBMS

2.1 Preliminaries

As root, create a user and group (if you haven't done so before) for PostgreSQL. This is the account that PostgreSQL will run as since it will not run as root. Also give the postgres user a password:

groupadd web

useradd -g web postgres -d /usr/local/pgsql

passwd postgres

Download PostgreSQL from the mirror closest to you. The list of mirrors is at http://www.postgresql.org/sites.html. You can download it in one big file ( 8 Mb) or in smaller files (base, support, doc, test). Put it in a temp directory such as /tmp.

Untar with the command:

tar xzvf postgresql-xxxxx.tar.gz (where xxxx is the version number)

Repeat the tar command for each file if you are downloading the multiple-file distribution.

2.2 Optional - How to increase the blocksize in PostgreSQL

This is not required to compile PostgreSQL.

By default PostgreSQL is compiled with a blocksize of 8 Kb. You can compile PostgreSQL to have 16 Kb blocksize instead, which will allow for bigger text and lztext data types. NOTE: If you've built PostgreSQL in that directory before, you need to a) delete the config.cache file (created by autoconf) and b) do a make clean to give it a fresh start.

Here's how you do it:

1. Go into the src/include directory of the PostgreSQL source distribution.
2. Edit the file config.h.in
3. Look for the line"#define BLCKSZ 8192" and change it to"#define BLCKSZ 16384"
4. Save it.

2.3 Compiling and installing

* You will need to run the command ./configure from within the src/ directory of the PostgreSQL distribution. This will create the files with the options that will be used to compile PostgreSQL specifically to your machine (./configure --help will show you all the available options).

* If you are going to access psql (the PostgreSQL's interactive query tool) from within an X terminal (such as xterm, kterm, Eterm,...) and you have the X development libraries installed then do:

./configure --with-x

Otherwise simply do a ./configure

* After it's done fire up a:

make

* The compilation process will take place. Go look at OpenACS and see on what you can help make the toolkit even more butt-kicking. After it's done, you should see a message like this:

All of PostgreSQL is successfully made. Ready to install.

* If you are upgrading your PostgreSQL, follow the instructions on Chapter 5 of the Administrators Guide.

* Create the directories to house PostgreSQL:

mkdir /usr/local/pgsql

chown postgres.web /usr/local/pgsql

* Become the postgres user: su - postgres

* Then install the PG executable files and libraries by running:

make install

* Tell your system how to find the new shared libraries. This can be accomplished in two ways:

Editing the file /etc/profile and adding the lines:

LD_LIBRARY_PATH=/usr/local/pgsql/lib

export LD_LIBRARY_PATH

OR editing the file /etc/ld.so.conf and adding the line:

/usr/local/pgsql/lib

and then running the command /sbin/ldconfig.

* You probably want to install the man and HTML documentation. Type

cd /usr/local/src/pgsql/postgresql-7.0/doc (or wherever you untarred it)

make install

This will install files under /usr/local/pgsql/doc and /usr/local/pgsql/man.

* Setup some environment variables to make your life easier:

You can do this by editing the /etc/profile file and including these lines:

PATH=$PATH:/usr/local/pgsql/bin

MANPATH=$MANPATH:/usr/local/pgsql/man

export MANPATH

* As the user postgres, create the database installation (the working data files). It can be anywhere that is writable by the postgres user (usually /usr/local/pgsql/data):

mkdir /usr/local/pgsql/data

/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

* Start the database server with the command:

/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data

This will start the server in the foreground (CTRL+C to stop it). To make it detach to the background, you can use the -S option, but then you won't see any log messages the server produces. A better way to put the server in the background is :

nohup /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data \

</dev/null >>server.log 2>>1 &

2.4 Getting it to start with every boot

Unfortunately there's no easy answer here. Every system (even GNU/Linux distributions) is a bit different than the other. In Red Hat / Mandrake systems I modify the postgresql startup script that comes with the RPM distribution to fit a compiled version.

In the contrib/linux directory of the PostgreSQL source distribution there's a sample startup script that you can use to include in your /etc/init.d directory.

Look back at the OpenACS documentation site in the next few days because I am putting some sample PostgreSQL startup scripts for different GNU/Linux distributions (Red Hat, Mandrake, Debian).

2.5 Testing

After you've started the database server, try creating a database and connecting to it through psql:

createdb mydb

psql mydb

If you see a prompt after a few messages, you're set ! Congratulations.