Notes about the database of Cream 2.x

1. How to install a PostgreSQL from scratch.

Suppose to have a SLC4 machine without any postgres DBMS previously installed. Download and run the following rmps. 

yum install  postgresql-libs-7.4.17-1.RHEL4.1
yum install postgresql-server-7.4.17-1.RHEL4.1
yum install postgresql-jdbc-8.1.407-2jpp

Once the operations are completed you can check if everything is fine: 

rpm -qa | grep postgres
postgresql-server-7.4.17-1.RHEL4.1
postgresql-7.4.17-1.RHEL4.1
postgresql-libs-7.4.17-1.RHEL4.1
postgresql-jdbc-8.1.407-2jpp

2. How to configure the postgres DBMS.

As postgres user edit the postgres configuration files as described.

su postgres

  • vi /var/lib/pgsql/data/postgresql.conf
Set the following property:
tcpip_socket = true
  • vi /var/lib/pgsql/data/pg_hba.conf
Set the following properties, provided that you are installing the dB in a machine identified by the <host_ip> ip: 
# IPv4-style local connections:
host	all	all	127.0.0.1	255.255.255.255	trust
host    creamdb cream   <host_ip>       255.255.255.255 password
If necessary, to make the postgres db visible from external hosts add the following line.
For making the db reachable, for instance, from client hosts with 193.206.x.x IP 
# local	all	all	ident	sameuser
host creamdb cream  193.206.0.0 255.255.0.0 password

3. Start postgres service as root user. 

/etc/init.d/postgresql restart

Check the service status as follows. 

/etc/init.d/postgresql status
postmaster (pid 4946 4945 4943) is running...

3. Then it is necessary to create a creamdb instance for Cream.

Suppose you need also to create a db user named cream.

You may use the a psql client to connect to the just installed dB as described below. 

 
psql -U postgres -h localhost -d template1

CREATE USER cream WITH PASSWORD 'cream';
DROP DATABASE creamdb;
CREATE DATABASE creamdb
	WITH OWNER = cream
	ENCODING = 'SQL_ASCII'
	TEMPLATE = template1;

4. Now you can populate the creamdb database using populate_creamdb.sql as SQL script file. 

psql -U cream -h localhost -d creamdb -f populate_creamdb.sql

5. You should have now cream db schema ready to be used.

6. There is no compile-time, but only a run-time, dependency to postgress JDBC jar.

So it is sufficient to have the JDBC3 postgresql-jdbc3.jar archive in the classpath of Tomcat in which Cream is running. You can find the jar typing: 

 
locate postgresql-jdbc3.jar
/usr/share/java/postgresql-jdbc3.jar