Install PostgreSQL 7.1.3

Skip this page if you're not interested in PostgreSQL.

Download the PostgreSQL source

Download PostgreSQL 7.1.3 from the mirror closest to you. The list of mirrors is at http://www.postgresql.org. Download it to /tmp.

As root, unpack it into /usr/local/src

joeuser:~$ su -
Password: ***********
root:~# cd /usr/local/src
root:/usr/local/src# tar xzf /tmp/postgresql-7.1.3.tar.gz

Create the Postgres user

Still 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:

root:~# groupadd web
root:~# useradd -g web -d /usr/local/pgsql postgres 
root:~# passwd postgres

root:~# mkdir -p /usr/local/pgsql
root:~# chown -R postgres.web /usr/local/pgsql /usr/local/src/postgresql-7.1.3
root:~# exit
logout
joeuser:~$ su - postgres
Password: ***********

Set up postgres's environment variables

Edit /usr/local/pgsql/.bash_profile so it looks like this:

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

export PATH LD_LIBRARY_PATH

Logout and login again as postgres. Use the echo command to make sure that /usr/local/pgsql/bin is now in your PATH

postgres:~$ exit
logout
joeuser:~$ su - postgres
Password: ************
postgres:~$ echo $PATH
/usr/local/bin:/usr/bin:/bin: ... :/usr/local/pgsql/bin

Compile and install PostgreSQL

First, we run ./configure to set the compilation options automatically. This is the point at which you can configure PostgreSQL in various ways. For example, if you want to enable Unicode support, add the flags --enable-locale and --enable-multibyte. If you want to see what the other possibilities are, run ./configure --help.

postgres:~$ cd /usr/local/src/postgresql-7.1.3
postgres:/usr/local/src/postgresql-7.1.3$ ./configure
postgres:/usr/local/src/postgresql-7.1.3$ make all

Compilation will take a while (about 10 minutes). Once it's done, you will see the following message:

All of PostgreSQL is successfully made. Ready to install.

Next, we'll install PostgreSQL. If all is successful, you'll see the following “Thank You” message.

postgres:/usr/local/src/postgresql-7.1.3$ make install
...
Thank you for choosing PostgreSQL, the most advanced open source database engine.

Prepare PostgreSQL for OpenFTS

OpenFTS is the module that provides full text search to OpenACS 4.5. We won't be installing it until later, but we'll set up a couple things that are best done right now.

postgres:/usr/local/src/postgresql-7.1.3$ make install-all-headers
postgres:/usr/local/src/postgresql-7.1.3$ cd contrib/intarray
postgres:/usr/local/src/postgresql-7.1.3/contrib/intarray$ make
postgres:/usr/local/src/postgresql-7.1.3/contrib/intarray$ make install

Start PostgreSQL

The initdb command initializes the database. pg_ctl is used to start up PostgreSQL.

postgres:/usr/local/src/postgresql-7.1.3/contrib/intarray$ cd
postgres:~$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
postgres:~$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/data/server.log start
postmaster successfully started

PostgreSQL errors will be logged in /usr/local/pgsql/data/server.log

Set up plpgsql and allow nsadmin access

We have to install plpgsql into our PostgreSQL installation so that we can use stored procedures. Fortunately, it's pretty easy. We'll also create a database user named nsadmin, so that aolserver can access the database. (Don't worry that you don't have a nsadmin user yet - we'll create that in the next chapter.)

postgres:~$ createlang plpgsql template1
postgres:~$ # Test if we succeeded
postgres:~$ createlang -l template1
     Procedural languages
  Name   | Trusted? | Compiler 
---------+----------+----------
 plpgsql | t        | PL/pgSQL
(1 row)
postgres:~$ createuser nsadmin
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) y
CREATE USER

Test PostgreSQL

Create a database and try some simple commands. The output should be as shown.

postgres:~$ createdb mytestdb
CREATE DATABASE
postgres:~$ psql mytestdb
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

mytestdb=# select current_timestamp;
       timestamp        
------------------------
 2001-12-20 14:24:30-05
(1 row)

mytestdb=# create function test1() returns integer as 'begin return 1; end;' language 'plpgsql';
CREATE
mytestdb=# select test1();
 test1 
-------
     1
(1 row)

mytestdb=# \q
postgres:~$ dropdb mytestdb
DROP DATABASE

Getting PostgreSQL to start on boot

Download postgresql.txt to /tmp. Then follow the instructions specific to your distribution:

  • Debian:

    postgres:~$ su -
    Password: ***********
    root:~# cp /tmp/postgresql.txt /etc/init.d/postgresql
    root:~# chown root.root /etc/init.d/postgresql
    root:~# chmod 700 /etc/init.d/postgresql

    Test the script

    root:~# /etc/init.d/postgresql stop
    Stopping PostgreSQL: ok

    If PostgreSQL successfully stopped, then use the following command to make sure that the script is run appropriately at boot and shutdown.

    root:~# update-rc.d postgresql defaults
     Adding system startup for /etc/init.d/postgresql ...
       /etc/rc0.d/K20postgresql -> ../init.d/postgresql
       /etc/rc1.d/K20postgresql -> ../init.d/postgresql
       /etc/rc6.d/K20postgresql -> ../init.d/postgresql
       /etc/rc2.d/S20postgresql -> ../init.d/postgresql
       /etc/rc3.d/S20postgresql -> ../init.d/postgresql
       /etc/rc4.d/S20postgresql -> ../init.d/postgresql
       /etc/rc5.d/S20postgresql -> ../init.d/postgresql
    root:~# /etc/init.d/postgresql start
    Starting PostgreSQL: ok
    root:~# exit
    postgres:~$ exit
  • Red Hat:

    postgres:~$ su -
    Password: ***********
    root:~# cp /tmp/postgresql.txt /etc/rc.d/init.d/postgresql
    root:~# chown root.root /etc/rc.d/init.d/postgresql
    root:~# chmod 700 /etc/rc.d/init.d/postgresql

    Test the script

    root:~# /etc/rc.d/init.d/postgresql stop
    Stopping PostgreSQL: ok

    If PostgreSQL successfully stopped, then use the following command to make sure that the script is run appropriately at boot and shutdown.

    root:~# chkconfig --add postgresql
    root:~# chkconfig --list postgresql
    ; You should see:
    postgresql        0:off   1:off   2:off   3:on    4:on    5:on    6:off
    root:~# /etc/rc.d/init.d/postgresql start
    Starting PostgreSQL: ok
    root:~# exit
    postgres:~$ exit
  • SuSE:

    postgres:~$ su -
    Password: ***********
    root:~# cp /tmp/postgresql.txt /etc/rc.d/init.d/postgresql
    root:~# chown root.root /etc/rc.d/init.d/postgresql
    root:~# chmod 700 /etc/rc.d/init.d/postgresql

    Test the script

    root:~# /etc/rc.d/init.d/postgresql stop
    Stopping PostgreSQL: ok

    If PostgreSQL successfully stopped, then use the following command to make sure that the script is run appropriately at boot and shutdown.

    root:~# cd /etc/rc.d/init.d
    root:/etc/rc.d/init.d# ln -s /etc/rc.d/init.d/postgresql K20postgresql
    root:/etc/rc.d/init.d# ln -s /etc/rc.d/init.d/postgresql S20postgresql  
    root:/etc/rc.d/init.d# cp K20postgresql rc2.d
    root:/etc/rc.d/init.d# cp S20postgresql rc2.d
    root:/etc/rc.d/init.d# cp K20postgresql rc3.d
    root:/etc/rc.d/init.d# cp S20postgresql rc3.d
    root:/etc/rc.d/init.d# cp K20postgresql rc4.d
    root:/etc/rc.d/init.d# cp S20postgresql rc4.d 
    root:/etc/rc.d/init.d# cp K20postgresql rc5.d
    root:/etc/rc.d/init.d# cp S20postgresql rc5.d
    root:/etc/rc.d/init.d# rm K20postgresql
    root:/etc/rc.d/init.d# rm S20postgresql

    Test configuration

    root:/etc/rc.d/init.d # cd
    root:~ # /etc/rc.d/init.d/rc2.d/S20postgresql start
    Starting PostgreSQL: ok
    root:~ # exit

From now on, PostgreSQL should start automatically each time you boot up and it should shutdown gracefully each time you shut down. (Note: Debian defaults to starting all services on runlevels 2-5. Red Hat defaults to starting services on 3-5. So, on Red Hat, PostgreSQL won't start on runlevel 2 unless you alter the above commands a little. This usually isn't a problem as Red Hat defaults to runlevel 3)

Learn more about PostgreSQL

Here are some links:

($Id: postgres.html,v 1.4 2002/03/03 06:29:41 rmello Exp $)