Getting Apache Tomcat 5.5 and PostgreSQL 8.1 to Work Together
(Tutorial) (2/3)
Date: 2006_0109
Installation Environment: Mac OS 10.4
Installation Packages: Apache Tomcat 5.5.12, PostgreSQL 8.1
Contributor: Orton Huang [/oth]
Step 3. Setting up PostgreSQL 8.1
The Apple Developer Connection provided another good website for
installing PostgreSQL for Mac OS 10 here.
I didn't follow all the directions there however but some of this is
repeated from there.
To get more information about PostgreSQL, you should go here.
3.1 Installing and Configuring PostgreSQL 8.1
First of all, you have to install a packaged called readline. You
can install that through fink. If you haven't installed fink, there
are directions here.
After you install fink, you can use that to install readline by
typing:
sudo /sw/bin/fink install readline
Then you can go to PostgreSQL's website here
to download the latest source files from their ftp. I downloaded the
file postgresql-8.1.0.tar.gz
To compile it, you'll need Xcode which you can get at Apple's
Developer Connection here.
I put the source file in /usr/local/src/postgres. You can untar
it with the command:
tar -xzvf postgresql-8.1.0.tar.gz
Next, you have to run the configure script inside the
/usr/local/src/postgres/postgresql-8.1.0 directory. You can run it
with this command:
./configure --with-includes=/sw/include/ --with-libraries=/sw/lib
Then run:
make
make install
After PostgreSQL is installed, you have to add an administrative
user. The username I used was "postgres".
In OS 10.4, open System Preferences->Accounts->New User. Create
a user with:
Name: postgres
Short Name: postgres
Next, make a directory for the database data.
mkdir /usr/local/pgsql/data.
And give ownership to that directory to the new postgres user.
chown postgres /usr/local/pgsql/data
Login as the postgres user for the next few steps. You can do this by
typing:
su -l postgres
Now, the database is ready to be initialized:
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
Already... before we start up the database, we'll set up some
environment variables to make our lives easier. You can permanently
set the variables in your ~/.bash_profile for the postgres
user. You can add these lines to your ~/.bash_profile.
export PATH=$PATH:/usr/local/pgsql/bin
export PGDATA=/usr/local/pgsql/data
If you source that file, "source ~/.bash_profile", then those
commands will be run.
3.2 Running PostgreSQL Server
Now! We're ready to start up the PostgreSQL Database Server. The
following command tells the pg_ctl script to use /usr/local/pgsql/data
as your data directory and "logfile" to contain your logs.
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
You can type "pg_ctl --help" to get usage information.
Let's create a simple test database to make sure things are
working.
createdb testdb
You can access the testdb database using PostgreSQL's
command-line utility psql
psql testdb
There are tutorial files in your /usr/local/pgsql/doc/html
directory that you can use to learn more about SQL and the PostgreSQL server.
3.3 Setting Up Permissions in PostgreSQL
We'll go over how to set up a basic shared server with
password-protected user accounts. You can find more information in the
PostgreSQL documentation here.
Authentication rules for users of the PostgreSQL system are defined in
the file /usr/local/pgsql/data/pg_hba.conf.
We'll add a user and
password:
user: "temp_user"
password: "temp"
to a new database "shared".
First, let's edit the pg_hba.conf file. This is borrowed directly from the Mac
Developer Connection Site again. This configuration file sets the
password authentication method to store the password as MD5 hashes.
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
host all postgres 127.0.0.1 255.255.255.255 md5
host samegroup all 127.0.0.1 255.255.255.255 md5
local all postgres md5
local samegroup all md5
To add a password for the postgres user, type:
~> psql testdb
testdb=# alter user postgres with password 'temp';
testdb=# \q
~> pg_ctl reload
Now, we'll create the "shared" database and the new user "temp_user".
~> psql testdb
testdb=# create database shared;
testdb=# create group shared;
testdb=# create user temp_user with password 'temp';
testdb=# alter group shared add user temp_user;
Now, you can log into the "shared" database with the new user "temp_user";
~> psql shared temp_user
One thing to note is that in the pg_hbal.conf, we gave the "postgres"
user access to all the databases. So he will be able to log into any
of the databases whereas the user "temp_user" will only be able to log
into databases that share the same name the "temp_user" is a member
of.
3.4 Inserting Data Into the Tables in PostgreSQL
Here, we'd like to set up some sample tables that we'll be using and
we'll populate them with some simple data. To learn more about
populating tables in the database, you can go to the PostgreSQL
documentation here.
Log into the "shared" database:
~> psql shared temp_user
Here's a simple table that we can create. This is a table called
"test_table" with four columns: "last_name", "first_name",
"date_of_birth" and a serial id column.
CREATE TABLE test_table (
last_name varchar(30),
first_name varchar(30),
date_of_birth date,
test_id serial
);
You can copy and paste that directly into the psql command-line
utility and it will create the tables.
To insert a few rows, you can do:
testdb=# INSERT INTO test_table VALUES ('Baggins', 'Frodo', '1968-09-22');
testdb=# INSERT INTO test_table VALUES ('Baggins', 'Bilbo', '1890-09-22');
Step 4. Installing the JDBC Driver for PostgreSQL
You can download a copy of the JDBC Driver from http://jdbs.postgresql.org/download.html.
I downloaded postgresql-8.1-404.jdbc3.jar, which was titled "8.1-404
JDBC 3". For Tomcat to access it, it has to be in your
$CATALINE_HOME/common/lib directory, so you should copy it there. Also, it has to be in your
CLASSPATH. To do this, make sure the following line is in your
~/.bash_profile file:
export CATALINA_HOME=/usr/local/apache-tomcat-5.5.12
export CLASSPATH="$CLASSPATH:.:$CATALINA_HOME/common/lib/postgresql-8.1-404.jdbc3.jar"
Where apache-tomcat-5.5.12 should be your Tomcat
installation.
Now, we're ready to start writing some JSP's and Servlets that can
communicate with the database!
|