Install Jetspeed with PostgreSQL

Since my graduation design request PostgreSQL, I have to give up MySql. PostgreSQL is quit different from MySql, as most extension commands of MySql, like SHOW TABLES , does not work in Postgre, and the structure of Postgre is entirely unfamiliar for me.

Although the installation of Jetspeed's DB is so simple that just three steps:
1 create a database
2 grant the privilege
3 download the JDBC
which still took me almost two hours to accomplish it, for the account and network configuration is some bit complex.

First of all, write a test java file, which is of great help, the following code (replace 'testdb') should be included in the file:

Class.forName("org.postgresql.Driver");
Connection db = DriverManager.getConnection("jdbc:postgresql://localhost:5432/jetspeed", "jetspeed", "jetspeed");

Compile it, JDBC file was not necessary during compilation phase, but it should be appended to the classpath before executing the test program.

Second, install PostgreSQL if it was not installed before. Source installation would be a better choice, and here is the link of PostgreSQL official site. Install via apt would be the most convenient way, I like apt and ubuntu :) .

Third, initialize a DB cluster(apt installation has already initialize one), like the following command:

$ initdb -D /usr/local/pgsql/data

Find pg_hba.conf and postgresql.conf in the directory of DB cluster. Login the cli environment of PostgreSQL (notice: there are two databases already exist, template0 and template1):

$ postmaster -S -D /usr/local/pgsql/data  #start the db server background
$ psql template0

Fourth, assure that the TCP/IP connection was available. Modify postgresql.conf as below:

listen_addresses='*'
port=5432
tcpip_socket=true

Fifth, modify pg_hba.conf (refer to this):

local all    password
host all all 127.0.0.1/32 password

Caution: Start a new line at the end of file. And restart the server after the configuration completed.

Sixth, create a user and create the database, then grant the control privilege to the user. There're several ways to do it, just like this one:

$ createuser -d -P  # create a user with the privilege of database create and a prompt for the password of the new userd
... # input the user info,like username is 'jetspeed' and password is 'jetspeed'
$ createdb jetspeed -U jetspeed -W  # create a new db and the creator is 'jetspeed'
# if error reported, maybe pg_hba.conf was incorrected configured.

Seventh, run the JDBC test. If the test succeed, there would be no problem of Jetspeed installation.