Recently I've needed to install the PostgreSQL 8.14 database server on a couple Linux machines for testing. Here is some information and scripts to make it easier for you start and stop the database, since installing from source instead of RPM leaves you without the convenient /etc/init.d boot scripts for Postgres and requires you to start Postgres database with the postmaster command when su'd as the postgres user. A bit of a headache... so I wrote the script shown further below as a convenience when managing Postgres and it may be helpful for those who don't want to read all the docs right away.

After having downloaded and uncompressed (tar -xvzf postgresql-8.1.4.tar.gz), the installation instructions begin with the a short version, to be run from inside the uncompressed source directory. I've modified the short version such that when creating the system postgres user account, no shell is given for the postgres user, then later a shell can be specified when using the su command to run the postmaster (The postmaster command can not be run by root directly).

view plain print about
1./configure
2gmake
3su
4gmake install
5adduser postgres -s /sbin/nologin
6mkdir /usr/local/pgsql/data
7chown postgres /usr/local/pgsql/data
8su -s /bin/sh postgres
9/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
10/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data >
logfile 2>&1 &


You may wish to create a database and assign a specific user:

view plain print about
1createuser cfuser
2createdb mydatabase -U cfuser


You may also wish to connect to Postgres over TCP from another host. To make Postgres listen on a TCP socket (default port 5432), start the postmaster with the -i option. To allow access from other hosts, you'll need to edit the file {postgres_home}/data/pg_hba.conf, for which you can find the documentation here. Several networks can be added to the authorization list such as this example:

view plain print about
1# TYPE DATABASE USER CIDR-ADDRESS METHOD<br/><br/># "local" is for Unix domain socket connections only
2local all all trust
3# IPv4 local connections:
4host all all 127.0.0.1/32 trust
5host all all 10.1.0.0/16 trust
6host all all 192.168.1.0/24 trust


Installing from source means that you must start and stop the database by su'ing to the postgres user and running the postmaster to start it or pg_ctl to stop it. Further, when rebooting the machine, the Postgres database won't start up automatically as it would if it had been installed from the RPM (Postgres server RPM is not available from the download page).

To make my life easier when managing these Postgres databases, I've created the following pgsqld script which can be copied to the {postgres_home}/bin/ directory:

view plain print about
1#!/bin/sh<br/><br/># chkconfig: 345 89 13
2# description: starts the PostgreSQL database server
3# author: Steven Erat (serat -/at/- adobe -/dot/- com)
4# caveat emptor: This script is released without warranty and
5    and is not guaranteed to work in all cases.
6    The user assumes all liability for using this script.<br/><br/>ID=`id -u`<br/><br/>if [ ! $ID -eq 0 ]; then
7    echo "You must be root to start or stop PostgreSQL database server"
8    exit 1
9fi<br/><br/>PGHOME=/usr/local/pgsql
10PGLOG=$PGHOME/logs/pglog<br/><br/>pgstart() {
11    
12    pglisten=`netstat -an | grep PGSQL | cut -d'.' -f4`
13    
14    if [ ! "$pglisten" = "" ]; then
15     echo " PostgreSQL database is already running and listening on port $pglisten "
16     exit 1
17    fi<br/><br/>    echo " Starting the PostgreSQL database server ..."<br/><br/>    su -s /bin/sh postgres -c "$PGHOME/bin/postmaster -D $PGHOME/data -i >
> $PGLOG 2>&1 &"
18    
19    sleep 5
20    
21    pglisten=`netstat -an | grep PGSQL | cut -d'.' -f4`
22    
23    if [ -n $pglisten ]; then
24     echo " PostgreSQL database is listening on port $pglisten "
25    fi
26}<br/><br/>pgstop() {<br/><br/>    
27    pglisten=`netstat -an | grep PGSQL | cut -d'.' -f4`
28    
29    if [ -z "$pglisten" ]; then
30         echo " PostgreSQL isn't running "
31    else
32        echo " Stopping the PostgreSQL database server that is listening on port $pglisten ..."
33    
34        su -s /bin/sh postgres -c "$PGHOME/bin/pg_ctl stop -D $PGHOME/data -m fast >> $PGLOG 2>&1 &"
35        
36        sleep 4
37    
38        pglisten=`netstat -an | grep PGSQL | cut -d'.' -f4`<br/><br/>
39     if [ ! "$pglisten" = "" ]; then
40      echo " !!! PostgreSQL database has NOT been stopped !!!"
41     else
42      echo " PostgreSQL database has been stopped! "
43          fi
44    fi<br/><br/>}<br/><br/>pgstatus() {
45    
46    echo " `su -s /bin/sh postgres -c "$PGHOME/bin/postmaster --version"`"
47    
48    pglisten=`netstat -an | grep PGSQL | cut -d'.' -f4`<br/><br/> if [ -z "$pglisten" ]; then
49     echo " PostgreSQL isn't running "
50 else
51     echo " PostgreSQL database server is running and listening on port $pglisten"
52 fi
53    
54    echo " See $PGLOG for PostgreSQL logging"
55}<br/><br/>ARG=$1<br/><br/>case $ARG in
56    
57    start)
58        pgstart
59    ;;<br/><br/>    stop)
60    
61        pgstop
62    ;;<br/><br/>    restart)
63        
64        pgstop
65        pgstart
66    ;;<br/><br/>    status)
67    
68        pgstatus
69    ;;<br/><br/>    *)    
70    
71        echo " Usage:$0 (start|stop|restart|status)"
72    ;;<br/><br/>esac<br/><br/>exit 0


To use the script, you can make it executable and then run it with the arguments of start, stop, restart, or status (i.e. ./pgsqld start).

To make the database start on a system boot or to add the convenience of controlling the database with the service command (i.e. service pgsqld start), make a symbolic link to the pgsqld script from /etc/init.d, then run chkconfig to add it as a service:

view plain print about
1cd /etc/init.d
2ln -s /usr/local/pgsql/bin/pgsqld pgsqld
3chkconfig --add pgsqld
4chkconfig --list pgsqld
5pgsqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off


Postgres will now start after a reboot, and from any system directory root can use the service command to control the database:

view plain print about
1[root@RHEL4QA1 ~]# service pgsqld start
2 Starting the PostgreSQL database server ...
3 PostgreSQL database is listening on port 5432
4[root@RHEL4QA1 ~]# service pgsqld status
5 postmaster (PostgreSQL) 8.1.4
6 PostgreSQL database server is running and listening on port 5432
7 See /usr/local/pgsql/logs/pglog for PostgreSQL logging
8[root@RHEL4QA1 ~]# service pgsqld stop
9 Stopping the PostgreSQL database server that is listening on port 5432 ...
10 PostgreSQL database has been stopped!
11[root@RHEL4QA1 ~]# service pgsqld status
12 postmaster (PostgreSQL) 8.1.4
13 PostgreSQL isn't running
14 See /usr/local/pgsql/logs/pglog for PostgreSQL logging