Beyond Linux From Scratch: Version 5.0
Prev	Chapter 24. Databases	Next

PostgreSQL-7.3.4

Introduction to PostgreSQL

PostgreSQL is an advanced object-relational database management system (ORDBMS), derived from the Berkeley Postgres database management system.

Package information

Download (HTTP):
Download (FTP): ftp://ftp8.us.postgresql.org/pub/pgsql/source/v7.3.4/postgresql-7.3.4.tar.gz
Download size: 11 MB
Estimated Disk space required: 86 MB
Estimated build time: 1.74 SBU

PostgreSQL dependencies

Optional

readline-4.3, Python-2.3.2, OpenSSL-0.9.7c and Linux-PAM-0.77

Installation of PostgreSQL

Install PostgreSQL with the following commands:

./configure --prefix=/usr &&
make &&
make install

Initialize a database cluster with the following commands:

mkdir -p /var/pgsql/data &&
useradd -d /var/pgsql/data postgres &&
chown postgres /var/pgsql/data &&
su - postgres -c '/usr/bin/initdb -D /var/pgsql/data'

Start the database server with the following command:

su - postgres -c '/usr/bin/postmaster -D /var/pgsql/data > \
        /var/pgsql/data/logfile 2>&1 &'

Now we can create a database and verify the installation:

su - postgres -c '/usr/bin/createdb test'
echo "create table t1 ( name varchar(20), state_province varchar(20) );" \
                | (su - postgres -c '/usr/bin/psql test ')
		
echo "insert into t1 values ('Billy', 'NewYork');" \
                | (su - postgres -c '/usr/bin/psql test ')
echo "insert into t1 values ('Evanidus', 'Quebec');" \
	        | (su - postgres -c '/usr/bin/psql test ')
echo "insert into t1 values ('Jesse', 'Ontario');" \
                | (su - postgres -c '/usr/bin/psql test ')
		
echo "select * from t1;" | (su - postgres -c '/usr/bin/psql test')

Command explanations

useradd -d /var/pgsql/data postgres: Add an unprivileged user to run the database server. Running the server as root is dangerous, and moreover simply will not work.

su - postgres -c '/usr/bin/initdb -D /var/pgsql/data': Initialize the database tablespace. This command may not be executed by root.

su - postgres -c '/usr/bin/postmaster -D /var/pgsql/data > /var/pgsql/data/logfile 2>&1 &': Start the database server. User postgres must execute this command as well.

createdb test, create table t1 , insert into t1 values..., select * from t1: Create a database, add a table to it, insert some rows into the table and select them to verify that the installation is working properly.

Configuring PostgreSQL

Config files

$PGDATA/pg_ident.con, $PGDATA/pg_hba.conf, $PGDATA/postgresql.conf

The PGDATA environment variable is used to distinguish database clusters from one another by setting it to the value of the directory which contains the cluster desired. The three configuration files exist in every PGDATA/ directory. Details on the format of the files and the options that can be set in each can be found in file:///usr/share/doc/postgresql/html/index.html.

Create the boot script with the following:

cat > /etc/rc.d/init.d/postgresql << "EOF"
#!/bin/bash
# Begin $rc_base/init.d/postgresql

# Based on sysklogd script from LFS-3.1 and earlier.
# Rewritten by Gerard Beekmans  - gerard@linuxfromscratch.org

source /etc/sysconfig/rc
source $rc_functions

case "$1" in
        start)
                echo "Starting PostgreSQL daemon..."
		su - postgres -c '/usr/bin/pg_ctl start -W -D /var/pgsql/data \
                               -l /var/pgsql/data/logfile -o "-i" '
                evaluate_retval
                ;;

        stop)
                echo "Stopping PostgreSQL daemon..."
                /usr/bin/pg_ctl stop -m smart -D /var/pgsql/data
		evaluate_retval
                ;;

        restart)
                $0 stop
                sleep 1
                $0 start
                ;;

        status)
                /usr/bin/pg_ctl status -D /var/pgsql/data
                ;;

        *)
                echo "Usage: $0 {start|stop|restart|status}"
                exit 1
                ;;
esac

# End $rc_base/init.d/
EOF
chmod 755 /etc/rc.d/init.d/postgresql

Create the symbolic links to this file in the relevant rc.d directory with the following commands:

cd /etc/rc.d/init.d &&
ln -sf ../init.d/postgresql ../rc0.d/K26postgresql &&
ln -sf ../init.d/postgresql ../rc1.d/K26postgresql &&
ln -sf ../init.d/postgresql ../rc2.d/K26postgresql &&
ln -sf ../init.d/postgresql ../rc3.d/S34postgresql &&
ln -sf ../init.d/postgresql ../rc4.d/S34postgresql &&
ln -sf ../init.d/postgresql ../rc5.d/S34postgresql &&
ln -sf ../init.d/postgresql ../rc6.d/K26postgresql

The PostgreSQL package contains clusterdb, createdb, createlang, createuser, dropdb, droplang, dropuser, ecpg, initdb, initlocation, ipcclean, pg_config, pg_controldata, pg_ctl, pg_dump, pg_dumpall, pg_encoding, pg_id, pg_resetxlog, pg_restore, postgres, postmaster, psql, vacuumdb, libecpg, libpq and various charset modules.