Using PostgreSQL with Tomcat on Linux

This FlashGuideTM covers using Tomcat 4.x or 5.x with PostreSQL on Linux. These instructions are for the current versions of Tomcat 4.1.31 and Tomcat 5.0.28, but should work the same for all previous versions.

Follow Step 1 to install Tomcat 4 or Step 2 to install Tomcat 5.


1. Installing Tomcat 4 on Linux

  1. Download the latest Tomcat binary from the Tomcat 4 section of http://jakarta.apache.org/site/binindex.cgi. Currently, Tomcat 4.1.31 is the latest.
  2. Install Tomcat by unzipping/untaring the download file and placing in the desired directory (I used /usr/local) 
    cd /usr/local
tar zxf ./jakarta-tomcat-4.1.31.tar.gz
  3. Note the location of your Tomcat installation - we will refer to this as $CATALINA_HOME
  4. Optionally, save time on typing by creating a symbolic link like this: 
    ln -s jakarta-tomcat-4.1.31 tomcat4

2. Installing Tomcat 5 on Linux

  1. Download the latest Tomcat binary from the Tomcat 5 section of http://jakarta.apache.org/site/binindex.cgi. Currently, Tomcat 5.0.28 is the latest.
  2. Install Tomcat by unzipping/untaring the download file and placing in the desired directory (I used /usr/local) 
    cd /usr/local
tar zxf ./jakarta-tomcat-5.0.28.tar.gz
  3. Note the location of your Tomcat installation - we will refer to this as $CATALINA_HOME
  4. Optionally, save time on typing by creating a symbolic link like this: 
    ln -s jakarta-tomcat-5.0.28 tomcat5

3. 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.

  1. 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.

  2. Download the latest PostgreSQL source code from http://www.postgresql.org/download/bittorrent. Currently, PostgreSQL 7.4.6 is the latest version.
  3. Unpack the distribution into the desired directory: 
    tar zxf postgresql-7.4.6.tar.gz
  4. 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".

  5. Build PostgreSQL (make sure you are logged in as user "root" for this): 
    gmake
gmake install
  6. Create the "postgres" user: 
    adduser postgres

    Or, on SuSE, you do: 

    useradd postgres
  7. Create the PostreSQL data directory: 
    mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
  8. 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 &
  9. 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.

  10. Make sure the $POSTGRESQL_HOME/bin directory is in your $PATH 
    export PATH=$PATH:$POSTGRESQL_HOME/bin
  11. 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.

  12. 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

4. Configuring connection pooling for PostgreSQL

  1. If you built the PostgreSQL JDBC libraries with the server itself, as described in my previous instructions, then all you need to do is locate the file $POSTGRESQL_HOME/share/java/postgresql.jar and copy it to $CATALINA_HOME/common/lib.
  2. If you did not build the PostgreSQL JDBC libraries with the server itself, then go to http://jdbc.postgresql.org/download.html and download the jar appropriate to your PostgreSQL version and Java version. Once downloaded, copy that jar to your $CATALINA_HOME/common/lib directory.
  1. Edit $CATALINA_HOME/conf/server.xml and add the following connection pool definition. In this example, I'm just putting this block within the default ("ROOT") context within the "localhost" hsot: 
              <Resource auth="Container"
             description="PostgreSQL Test Connection Pool"
             name="jdbc/posgres" type="javax.sql.DataSource"/>
         <ResourceParams name="jdbc/posgres">
             <parameter>
                 <name>driverClassName</name>
                 <value>org.postgresql.Driver</value>
             </parameter>
             <parameter>
                 <name>username</name>
                 <value>postgres</value>
             </parameter>
             <parameter>
                 <name>maxIdle</name>
                 <value>2</value>
             </parameter>
             <parameter>
                 <name>maxActive</name>
                 <value>10</value>
             </parameter>
             <parameter>
                 <name>url</name>
                 <value>jdbc:postgresql://localhost:5432/mydb?autoReconnect=true</value>
             </parameter>
         </ResourceParams>

    Change the "url" parameter if your PostgreSQL server is on a different machine other than the one Tomcat is running on.


5. Testing

  1. Create a test JSP and place it under the appropriate directory in $CATALINA_HOME/webapps. In the previous step, if you put the connection pool definition within the "ROOT" Context, then you'll want to put your JSP in the $CATALINA_HOME/webapps/ROOT directory. Your JSP can be as simple as: 
    <%@ page language="java" 
 import="java.sql.*, javax.sql.DataSource, javax.naming.* " %>

<html>
 <head>
  <title>Test of PostgreSQL connection pooling</title>
 </head>

 <body>
  <br/>
  <center>
   <h3>This is a test of PostgreSQL connection pooling</h3>
  </center>
  <br/>

<%
   Connection conn = null;
   Statement stmt = null;
   ResultSet rs = null;

   try 

      while (rs.next()) 

         out.println("</tr>");
      }
	    
      out.println("</table>");
      conn.close();
   } catch (Exception e)  finally  catch (Exception e) 
      if (stmt != null) try  catch (Exception e) 
      if (conn != null) try  catch (Exception e) 
   }
%>

 </body>
</html>

    The select statement, "select * from TestTable", comes from the FlashGuide "Getting started with PostgreSQL on Linux". Feel free to substitute your own.

  2. Start Tomcat: 
    cd $CATALINA_HOME/bin
./catalina.sh start
  3. Point your browser to http://[hostname]:[port]/[jspname], where "[hostname]" refers to the machine Tomcat is running on, "[port]" refers to the Tomcat HTTP port (8080 is the default), and "[jspname]" refers to the name (and application prefix, if necessary), of your test JSP. In my case, for example, I simply type in: http://localhost:8080/postgres.jsp.

Back to Table of Contents
 
©2002-2005 Galatea Information Strategies. All rights reserved