Download PostgreSQL 7.2.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.2.3.tar.gz

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.2.3
root:~# chmod 750 /usr/local/pgsql
root:~# exit
logout
joeuser:~$ su - postgres
Password: ***********

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

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 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.2.3
postgres:/usr/local/src/postgresql-7.2.3$ ./configure
postgres:/usr/local/src/postgresql-7.2.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.2.3$ make install
...
Thank you for choosing PostgreSQL, the most advanced open source database engine.

OpenFTS is the module that provides full text search to OpenACS 4.6. We won't be installing it until later, but since it needs a special PostgreSQL module called 'tsearch', we'll install it now.

postgres:/usr/local/src/postgresql-7.2.3$ cd contrib/tsearch
postgres:/usr/local/src/postgresql-7.2.3/contrib/tsearch$ make
postgres:/usr/local/src/postgresql-7.2.3/contrib/tsearch$ make install

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

postgres:/usr/local/src/postgresql-7.2.3/contrib/tsearch$ 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

Next, we'll install plpgsql into our PostgreSQL installation so that we can use stored procedures. We'll also create a database user named joeuser (replace with your own username), so that you'll be able to access the database via AOLserver.

postgres:~$ createlang plpgsql template1
postgres:~$ # Test if we succeeded
postgres:~$ createlang -l template1
Procedural languages
  Name   | Trusted?  
---------+----------
 plpgsql | t        
(1 row)
postgres:~$ createuser joeuser
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

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

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

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)

Here are some links: