PostgreSQL-7.3.2

Introduction to PostgreSQL7.3.2

Download location (HTTP):       http://www.ca.postgresql.org/ftpsite/v7.3.2/postgresql-7.3.2.tar.gz
Download location (FTP):        ftp://ftp8.us.postgresql.org/pub/pgsql/source/v7.3.2/postgresql-7.3.2.tar.gz
Version used:                   7.3.2
Package size:                   11 MB
Estimated Disk space required:  68 MB

The PostgreSQL package contains the PostgreSQL RDBMS, client utilities and language bindings.

Note: The C++ and ODBC bindings have been removed from the main source distribution. They can be found at the PostgreSQL Projects Web Site at: http://gborg.postgresql.org.

PostgreSQL depends on:
readline-4.3

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/tutorial.html

postgres init.d script

Create the postgres, boot script with the following :

cat > /etc/rc.d/init.d/postgres << "EOF"

#!/bin/bash
# Begin $rc_base/init.d/postgres

# Based on sysklogd script from LFS-3.1 and earlier.
# Rewritten by Gerard Beekmans  - [email protected]

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/postgres

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/postgres ../rc0.d/K26postgres &&
ln -sf ../init.d/postgres ../rc1.d/K26postgres &&
ln -sf ../init.d/postgres ../rc2.d/K26postgres &&
ln -sf ../init.d/postgres ../rc3.d/S34postgres &&
ln -sf ../init.d/postgres ../rc4.d/S34postgres &&
ln -sf ../init.d/postgres ../rc5.d/S34postgres &&
ln -sf ../init.d/postgres ../rc6.d/K26postgres

Contents

The PostgreSQL RDBMS contains createdb, createlang, createuser, dropdb, droplang, dropuser, ecpg, initdb, initlocation, ipcclean, pg_config, pg_ctl, pg_dump, pg_dumpall, pg_passwd, pg_restore, pgaccess, pgtclsh, pgtksh, postgres, postmaster, psql and vacuumdb.

Description

createdb

createdb creates a new PostgreSQL database.

createlang

createlang defines a new PostgreSQL procedural language.

createuser

createuser defines a new PostgreSQL user account.

dropdb

dropdb removes a PostgreSQL database.

droplang

droplang removes a PostgreSQL procedural language.

dropuser

dropuser removes a PostgreSQL user account.

ecpg

ecpg is the embedded SQL preprocessor.

initdb

initdb create a new database cluster.

initlocation

initlocation creates a secondary database storage area.

ipcclean

ipcclean removes share memory and semaphores left over by an aborted database server.

pg_config

pg_config retrieves PostgreSQL version information.

pg_ctl

pg_ctl controls stopping and starting the database server.

pg_dump

pg_dump dumps database data and metadata into scripts which are used to recreate the database.

pg_dumpall

pg_dumpall recursively calls pg_dump for each database in a cluster.

pg_passwd

pg_passwd is a secondary password file utility, for controlling access to databases.

pg_restore

pg_restore creates databases from dump files created by pg_dump.

pgaccess

pgaccess is a graphical client for PostgreSQL, written in Tk/tcl.

pgtclsh

pgtclsh is a tcl shell for PostgreSQL.

pgtksh

pgtksh is a wish style shell for PostgreSQL.

postgres

postgres is a single user database server, generally used for debugging.

postmaster

postmaster is the multi-user database daemon.

psql

psql is a console based database shell.

vacuumdb

vacuumdb compacts databases and generates statistics for the query analyzer.