Unpack PostgreSQL. If you have not downloaded the postgresql tarball to /tmp/postgresql-7.3.4.tar.gz, get it.

[root@yourserver root]# cd /usr/local/src
[root@yourserver src]# tar xzf /tmp/postgresql-7.3.4.tar.gz
[root@yourserver src]# 
cd /usr/local/src
tar xzf /tmp/postgresql-7.3.4.tar.gz

Create the Postgres user.  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. Since nobody will log in directly as that user, we'll leave the password blank.

[root@yourserver src]# groupadd web
[root@yourserver src]# useradd -g web -d /usr/local/pgsql postgres
[root@yourserver src]# mkdir -p /usr/local/pgsql
[root@yourserver src]# chown -R postgres.web /usr/local/pgsql /usr/local/src/postgresql-7.3.4
[root@yourserver src]# chmod 750 /usr/local/pgsql
[root@yourserver src]#
groupadd web
useradd -g web -d /usr/local/pgsql postgres
mkdir -p /usr/local/pgsql
chown -R postgres.web /usr/local/pgsql /usr/local/src/postgresql-7.3.4
chmod 750 /usr/local/pgsql

Set up postgres's environment variables. They are necessary for the executable to find its supporting libraries. For convenience, we'll simply append the necessary lines to the postgres shell config file.

[root@yourserver src]# echo "export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/pgsql/lib" >> ~postgres/.bashrc
[root@yourserver src]# echo "export PATH=$PATH:/usr/local/pgsql/bin" >> ~postgres/.bashrc
echo "export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/pgsql/lib" >> ~postgres/.bashrc
echo "export PATH=$PATH:/usr/local/pgsql/bin" >> ~postgres/.bashrc

Test this by logging in as postgres and checking the paths; you should see /usr/local/pgsql/bin

[root@yourserver src]# su - postgres
[postgres@yourserver pgsql]$ env | grep PATH
LD_LIBRARY_PATH=:/usr/local/pgsql/lib
PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:/usr/bin/X11:/usr/X11R6/bin:/root/bin:/usr/local/pgsql/bin:/usr/local/pgsql/bin
[postgres@yourserver pgsql]$ exit

Compile and install PostgreSQL.  Change to the postgres user and 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.

[root@yourserver src]# su - postgres
[postgres@yourserver pgsql]$ cd /usr/local/src/postgresql-7.3.4
[postgres@yourserver postgresql-7.3.4]$ ./configure
creating cache ./config.cache
checking host system type... i686-pc-linux-gnu
(many lines omitted>
linking ./src/makefiles/Makefile.linux to src/Makefile.port
linking ./src/backend/port/tas/dummy.s to src/backend/port/tas.s
[postgres@yourserver postgresql-7.3.4]$ make all
make -C doc all
make[1]: Entering directory `/usr/local/src/postgresql-7.3.4/doc'
(many lines omitted)
make[1]: Leaving directory `/usr/local/src/postgresql-7.3.4/src'
All of PostgreSQL successfully made. Ready to install.
[postgres@yourserver postgresql-7.3.4]$ make install
make -C doc install
make[1]: Entering directory `/usr/local/src/postgresql-7.3.4/doc'
(many lines omitted)
Thank you for choosing PostgreSQL, the most advanced open source database
engine.
su - postgres
cd /usr/local/src/postgresql-7.3.4
./configure
make all
make install

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

[postgres@yourserver tsearch]$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
(17 lines omitted)
or
    /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
[postgres@yourserver tsearch]$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/data/server.log start
postmaster successfully started
[postgres@yourserver tsearch]$
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/data/server.log start

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

Install Pl/pgSQL. Set up plpgsq and allow your user to have access. Plpgsql is a PL/SQL-like language. We add it to template1, which is the template from which all new databases are created. We can verify that it was created with the createlang command in list mode.

[postgres@yourserver pgsql]$ createlang plpgsql template1
[postgres@yourserver pgsql]$ createlang -l template1
Procedural languages
  Name   | Trusted?
---------+----------
 plpgsql | t
(1 row)

[postgres@yourserver pgsql]$
createlang plpgsql template1
createlang -l template1

Test PostgreSQL (OPTIONAL). Create a database and try some simple commands. The output should be as shown.

[postgres@yourserver pgsql]$ createdb mytestdb
CREATE DATABASE
[postgres@yourserver pgsql]$ 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;
          timestamptz
-------------------------------
 2003-03-07 22:18:29.185413-08
(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@yourserver pgsql]$ dropdb mytestdb
DROP DATABASE
[postgres@yourserver pgsql]$ exit
logout

[root@yourserver src]#

Set PostgreSQL to start on boot. First, we copy the postgresql.txt init script, which automates startup and shutdown, to the distribution-specific init.d directory. Then we verify that it works. Then we automate it by setting up a bunch of symlinks that ensure that, when the operating system changes runlevels, postgresql goes to the appropriate state. Red Hat and Debian and SuSE each work a little differently.

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)

Tune postgres. (OPTIONAL). The default values for PostgreSQL are very conservative; we can safely change some of them and improve performance.