Galatea Information Strategies

Getting started with PostgreSQL on Linux

This FlashGuide^TM will get you started with PostgreSQL 7.4.x on Linux. First, we'll build and install PostgreSQL. Then we'll create a database, add a few objects and execute a few commands.

1. Build PostgreSQL on Linux

There are some binary distributions of PostgreSQL available from the PostgreSQL website, http://www.postgresql.org. Currently, I've seen only RedHat and Fedora RPMs. If you can use them, great. Otherwise, follow these instructions on building PostgreSQL.

Check your prerequisites:
1. You will need GCC installed
2. You will need GNU make installed
3. You will need /usr/ccs/bin and the gcc executables in your $PATH
4. You will need the zlib compression library installed. If you don't have it, you might be able to find an RPM for your Linux distribution from http://rpmfind.net. If not, do the following:
  - Download the latest source from http://www.gzip.org. The current version is 1.2.1.
  - Unpack the distribution into the desired directory:
```
tar zxf zlib-1.2.1.tar.gz
       
```
  - Cd into the distribution directory and type the following:
```
cd zlib-1.2.1
./configure --prefix=/usr --shared
make
make install
       
```
5. Optionally, download the GNU Readline library. You might be able to find an RPM on http://rpmfind.net.
6. If you are building the PostgreSQL JDBC drivers at the same time you build the database server itself, you need to have a Java Development Kit (JDK) installed:
  - Download JDK 1.3 or JDK 1.4 from http://java.sun.com.
  - Install the JDK per the accompanying installation instructions
  - Set the $JAVA_HOME environment variable; ideally, set it in /etc/profile so it is available to all shells, all the time
7. If you are building the PostgreSQL JDBC drivers at the same time you build the database server itself, you need to have Apache Ant installed:
  - Download the latest Ant binary distribution from http://ant.apache.org. At the time of this writing, the latest version is 1.6.2.
  - Unpack the distribution in the desired directory - on my system, I did:
```
cd /usr/local/share
tar zxf apache-ant-1.6.2-bin.tar.gz
ln -s apache-ant-1.6.2 ant
       
```
    The last command creates a symbolic link called "ant" to the distribution directory. It just saves on typing.
  - Make sure you set the $ANT_HOME environment variable to point to the distribution directory (or the symbolic link, if you did what I did above). Make sure $ANT_HOME is set in /etc/profile.
Download the latest PostgreSQL source code from http://www.postgresql.org/download/bittorrent. Currently, PostgreSQL 7.4.6 is the latest version.
Unpack the distribution into the desired directory:
```
tar zxf postgresql-7.4.6.tar.gz
   
```
Cd into the distribution directory and type the following:
```
cd postgresql-7.4.6
./configure --without-readline --with-java
   
```
Even though I had GNU Readline installed on my system, I still had to add the "--without-readline" option. You might have better luck - try just "./configure" first.

You can omit the "--with-java" option if you don't want to build the PostgreSQL JDBC drivers at this time

By default, the PostgreSQL installation directory will be /usr/local/pgsql. To change this, use the "--prefix" option and specify a different directory, like: "--prefix=/export/home/postgresql".
Build PostgreSQL (make sure you are logged in as user "root" for this):
```
gmake
gmake install
   
```

Create the "postgres" user:

adduser postgres

Or, on SuSE, you do:

useradd postgres

Create the PostreSQL data directory:

mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data

As the "postgres" user, initialize the database server and start it:

su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data >logfile 2>&1 &

Recommended, but optional, is to set the $POSTGRESQL_HOME variable in /etc/profile to point to the installation directory:
```
export POSTGRESQL_HOME=/usr/local/pgsql
   
```
Change the location as necessary.
Make sure the $POSTGRESQL_HOME/bin directory is in your $PATH
```
export PATH=$PATH:$POSTGRESQL_HOME/bin
   
```

As root, create a startup script for PostgreSQL called "/etc/rc.d/init.d/postgresql

#! /bin/sh
# PostgreSQL startup/shutdown script

case "$1" in
  start)
    pid=`pidof postmaster`
    if [ -n "$pid" ] 
    then
      echo "PostgreSQL is already running\n"
    else
      rm -f /tmp/.s.PGSQL.* > /dev/null
      echo "Starting PostgreSQL ... from $POSTGRESQL_HOME"
      su -l postgres -c '$POSTGRESQL_HOME/bin/postmaster -i -S -D$POSTGRESQL_HOME/data'
      sleep 1
      pid=`pidof postmaster`
      if [ -n "$pid" ] 
      then
         echo "PostgreSQL is running"
      else
         echo "PostgreSQL failed to start"
      fi
    fi
  ;;

  stop)
   echo "Stopping PostgreSQL ..."
   killproc postmaster
   sleep 2
   pid=`pidof postmaster`
   if [ -n "$pid" ] 
   then
      echo "PostgreSQL failed to stop"
   else
      echo "PostgreSQL is stopped"
   fi
  ;;

  status)
   status postmaster
  ;;

  restart)
   $0 stop
   $0 start
  ;;

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

exit 0

This script assumes that the $POSTGRESQL_HOME variable is set. If this is not the case on your system, set is within the script.

Link the postgresql startup script in the appropriate rc directory:

cd /etc/rc.d/rc3.d
ln -s ../init.d/postgresql S79postgresql
ln -s ../init.d/postgresql K79postgresql

2. Quick start

$POSTGRESQL_HOME refers to the installation directory for PostgreSQL - you may or may not have that environment variable actually defined.
Login in to your system as user "postgres"
Create a user account for the user that will own the test database. You do not have to do this if you want to perform these operations as the default, "postgres" user.
```
$POSTGRESQL_HOME/bin/createuser testuser
  
```
Change "testuser" to the name of system user you wish to add to this server. You will be prompted with a few questions about the type of permissions you want to grant this user.
Create a database for the new user
```
$POSTGRESQL_HOME/bin/createdb testdb testuser
  
```
If you omit the user name, the database will be owned by the "postgres" user.
If you specified a user other than "postgres", login now to that account.

Create a simple table:

$POSTGRESQL_HOME/bin/psql testdb
Welcome to psql 7.4.6, 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
 
mydb=> create table TestTable (
mydb(> testint int not null,
mydb(> teststr varchar(50) not null
mydb(> );
CREATE TABLE
mydb=> \q

Note the "\q" used to quit the PostgreSQL shell.

If you omit the database name to the psql command, it will assume a default database name of the user name. So, if you are logged in as user "testuser" and you type "$POSTGRESQL_HOME/bin/psql", then PostgreSQL will attempt to connect you with the "testuser" database.

Now let's list the objects in our database:

$POSTGRESQL_HOME/bin/psql mydb
mydb=> \d
         List of relations
 Schema |   Name    | Type  | Owner 
--------+-----------+-------+-------
 public | testtable | table | lajos
(1 row)
 
mydb=> \q

If we had indexes or other objects, these would show up as well with the "\d" command.

Since often it is more convenient to put SQL commands into a file and execute them, create a test file called "psqltest.sql" containing the following:

insert into TestTable values (1, 'aaa');
insert into TestTable values (2, 'bbb');
insert into TestTable values (3, 'ccc');
insert into TestTable values (4, 'ddd');

Run the SQL commands in the script file using the "\i" command in psql:

$POSTGRESQL_HOME/bin/psql testdb
mydb=> \i psqltest.sql
mydb=> \q

Alternatively, and even more practical, is to run the SQL commands in the script file using the "-f" flag of the psql command:
```
$POSTGRESQL_HOME/bin/psql mydb -f psqltest.sql
  
```

Now we can select the contents of our table:

$POSTGRESQL_HOME/bin/psql mydb
mydb=> select * from TestTable;
 testint | teststr 
---------+---------
       1 | aaa
       2 | bbb
       3 | ccc
       4 | ddd
(4 rows)
 
mydb=> \q

Since stored procedures are one of the cool features of PostgreSQL, let's create one that will return the "teststr" column of our table given an integer:

$POSTGRESQL_HOME/bin/psql mydb
mydb=> CREATE FUNCTION TestProc (integer) RETURNS text as
mydb=> 'select teststr from TestTable where testint = $1'
mydb=> LANGUAGE 'sql';
CREATE FUNCTION
mydb=> \q

Let's test the procedure:

$POSTGRESQL_HOME/bin/psql mydb
mydb=> SELECT TestProc(1);
 testproc 
----------
 aaa
(1 row)
mydb=> \q

Back to Table of Contents