![[ZOIS]](../pics/little.zois.gif){kind=small}
Home Page *
Contact ZOIS *
Technical Notes
ZOIS Technical Note TN-2003-01-15.
Author and Audience
This TN is intended for persons working with the JBoss Enterprise
Java Beans Container[1], the PostgreSQL Data Base
Management System[2] on the Threads distribution of
Linux provided by Red Hat[3]. Some Linux, PostgreSQL
and JBoss skills are assumed. Written by Martin Sullivan[4], ZOIS Limited, Cockermouth.
Abstract
The steps required to connect PostgreSQL to JBoss 2, so that
PostgreSQL can be used as a Database Manager to this Enterprise Java
Beans (EJB) Container are explained. The PostgreSQL is supplied with
the Threads Linux distribution that approximates to Red Hat 7.3.
Introduction
The author recently upgraded a Lap Top computer to Threads Linux 7.3[5] from Red Hat 6.2. Threads Linux, by the way, is a fully free distribution produced by Red Hat. Threads 7.3 approximates to an official Red Hat 7.3 without their support or additional goodness. Prior to the upgrade the computer ran a number of EJB demonstrations, some of which are documented elsewhere on this site[6]. The upgrade necessitated the re-installation of JBoss version 2 and the reconfiguration of it and the Java Server Pages Container, Tomcat, to allow these demonstrations to once more proceed. Again this has been documented elsewhere on this site[7].
During this process it was decided to use the PostgreSQL system contained within the Linux distribution (version 7.1 was provided) and at this point the existing documentation[7] was found to be inadequate. Hopefully this Technical Note will go someway to correcting this.
This note also contains information about the Java Virtual Machine
(JVM) that is required for this work.
Materials and Platform
This work was performed on a two year-old IBM ThinkPad 570 (CPU
Pentium II, speed 360MHz), 200MBytes memory). Any similar Intel based
personal computer should be adequate.
Method
Java Virtual Machine
All the Java work, prior to this upgrade, had been performed on Red
Hat 6.2. With the upgrade to Threads (and effectively Red Hat) 7.3 it
was found that the IBM provided Java Virtual Machine (JVM) at version
1.3 failed. It appeared to catch a signal in a signal handler which
invoked the same signal handler and spiralled fatally out of
control. The solution was to upgrade to IBM's 1.3.1 Java Kit
(IBMJava2-131) which can be obtained from the IBM download site. Once
installed both the regular PATH and CLASSPATH environment variables
should be adjusted suitably.
PostgreSQL Installation
To fully install PostgreSQL for this work, the following RPM Packages have to be installed:
In the lines found below and in other TN[7] the example database called "sullivan" is owned by "sullivan", although it could equally be "scott", or any other user, in both cases. To create a database user log in as the Linux owner of PostgreSQL, postgres; the command createuser can then be run. Loging in as postgres usually requires switching user (su) from root as access is normally directly blocked. Having created a PostgreSQL user, sullivan, the Linux user sullivan will be able to run createdb and establish a test database (usually named after him- or herself).
PostgreSQL's Java Database Connect
One makes connections to PostgreSQL's SQL engine via a Java Database
Connect (JDBC) Application Programing Interface. This interface is
provided by a JDBC 'Driver' which, in the JBoss 2 example
configuration offered on this site is noted as being at
org.postgresql.Driver[7]. The actual
Java Classes that provide this functionality are to be found in a Java
Archive (jar) file called jdbc7.1-1.2.jar. This is found in the Linux
PostgreSQL distribution at /usr/share/pgsql. It was most convenient to
copy this file into the JBoss library directory found at
$JBOSS_HOME/lib/ext so that it could be picked up directly, although,
obviously there are other ways that this could be achieved (such as
manipulation of the CLASSPATH environment variable).
Configuration of PostgreSQL
As distributed, PostgreSQL is configured to use a local UNIX socket for communication between an SQL client (such as psql) and the SQL engine and server, known as the postmaster. Further, this local connection is configured to only allow owners of a particular database to connect to it. In contrast, the JDBC method of connection demands that the connection be over TCP/IP sockets (all be it local ones) and the connection configured in our JBoss example[7] is for a database called "sullivan" owned by "sullivan". JBoss will however have to connected to this database by whatever user starts JBoss. All this with out particular regard to passwords, as found in this URL string:
jdbc:postgresql:sullivan?user=sullivan?password=-not-required- |
This, of course, is rather horrid from a security stand-point but will do for the demonstration on a single machine.
To allow UNIX socket connections the postmaster must be started
with the -i option. PostgreSQL is itself started in this
particular Linux distribution from an /etc/init.d script called
postgresql. The script uses pg_ctl to actually start the PostgreSQL
system and the line that does this must be modified to include the
-o option to pg_clt to include the -i option
for the postmaster. Consult the manual pages for more information about
both the -o option to pg_sql and -i option
of the postmaster. Here is the modified line in question:
su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl -o -i -D $PGDATA -p \ /usr/bin/postmaster start > /dev/null 2>&1" < /dev/null |
Now that PostgreSQL will accept TCP/IP connection it needs to be told to accept any connections without regard to authentication from any and all users on the local machine. This information is kept in the pg_hba.conf file found (in this Linux release) in the /var/lib/pgsql/data directory. Add the following line (which you'll find commented out elsewhere in the file).
host all 127.0.0.1 255.255.255.255 trust |
The line tells PostgreSQL to trust all users connecting from the
local host (127.0.0.1). You will need, unsurprisingly, postgres user
privileges for this and PostgreSQL needs restarting once all these
changes have been made (hint: /etc/init.d/postgresql).
Testing JDBC Connections
Sadly, JBoss (2.2.1 at least) is not very helpful in diagnosing
connection problems. You invariably get a "null pointer" exception
thrown by the "Minerva" component, which is responsible for managing a
pool of JDBC connections. It is therefore prudent to write a small
native JDBC testing program which will produce some slightly more
meaningful exception output. A variety of debugging techniques
(yes, even as crude as System.err.println) can also be
employed. These can be hacked up from any of a zillion JDBC examples
that exist on the web, but you can find one on this site too[8].
Conclusion
The above instructions allow a connection to be established using
Java Database Connect (JDBC) between JBoss (2.2.1) and PostgreSQL 7.2,
all running under the Threads 7.3 Linux distribution (effectively Red
Hat 7.3). The connect has been tested using Stateless Session Beans
(but not, it has to be noted, with anything more complicated, such as
Entity or stateful beans).
References
$Date: 2003/09/22 13:51:12 $