Chapter 1. About this Book

This book describes how to setup an openCRX database instance for PostgreSQL.

Chapter 2. Prerequisites

As a first step you must download the following software packages:

• Download openCRX for PostgreSQL from here (e.g. opencrx-1.6.0-core.postgresql-8.zip or opencrx-1.6.0-core.postgresql-8.tar.gz). The distribution contains the PostgreSQL scripts required to install the openCRX database.

• Download PostgreSQL source code from here or PostgreSQL server binary from here.

• Download PgAdmin3 from here (optional, only required if you prefer a GUI for admin tasks).

• You can find the PostgreSQL JDBC driver inside the binary distribution or you can build it when you compile the PostgreSQL source. Alternatively, you can download it from here.

Please ensure that you install the correct JDBC driver (i.e. matching JDK, PostgreSQL version, etc.) and one JDBC driver only! Ignoring this wisdom leads to problems as the connection to the database will fail.

As a next step you must install or compile PostgreSQL. The chapter Server Administration of the PostgreSQL documentation explains in detail how to install the database.

This document assumes that you use the pgAdmin3 or the psql terminal for database administration. The JDBC driver is required for the application server installation.

Chapter 3. Upgrading from previous versions

If you already have PostgreSQL for openCRX installed, upgrade the database as explained below. You can then skip the rest of this document.

The openCRX distributions provide an SQL script of the form upgrade-from-<version from>-to-<version to>.sql. E.g. If you have installed openCRX 1.5.0 and you want to upgrade to version 1.6.0 you have to run the script upgrade-from-1.5.0-to-1.6.0.sql on your database instance.

Chapter 4. Create the database

In order to connect to a PostgreSQL server from a remote pgAdmin3 instance you have to properly configure PostgreSQL server.

Let the PostgreSQL server accept connections from inside your network (suppose 192.168.1.0/24) by postgres and system users. We suppose the connections will be clear (not ssl) and protected by a password authentication mechanism. To do this, add the following lines at the end af the file <postgres home dir>/data/pg_hba.conf

#TYPE  DATABASE    USER        IP-ADDRESS         IP-MASK       METHOD
local   all        all                                          password 
host    crx-CRX    system      192.168.1.0        255.255.255.0 password 
host    all        postgres    192.168.1.0        255.255.255.0 password

Remove the line with ident sameuser if it exists in pg_hba.conf: host all all 127.0.0.1 255.255.255.255 ident sameuser

If the PostgreSQL server is running, stop it before you continue

postgres$pg_ctl stop

Next, PostgreSQL server has to be configured to accept more than one connection through a socket. To do this, log-in to the system with the PostgreSQL server user account (usually postgres on a linux system) and start the the PostgreSQL server with the following options:

postgres$pg_ctl -o -i -l postmaster.log start

Now you can connect to the default PostgreSQL server database (template1) with the postgres user (DB Administrator) .

Hint: if you prefer creating the database with psql terminal instead of using pgAdmin3, skip the next few steps and continue with Create the database with psql terminal.

In the next dialogs you create the user system (must be a database superuser!).

pgAdmin3 allows you to create and manage databases. Before you can install the openCRX database schemas you must install a new database for openCRX. To do this start the pgAdmin3. The startup screen looks as shown in Figure 4-4. Right-click over the database item and select New Object -> New Database in order to create a new database instance.

You should now see the first page of the instance creation wizard as shown in Figure 4-5. Choose the template Blank. Remember to set up the system user as owner.

Set the encoding of this new database to UNICODE, otherwise you will not be able to use openCRX with full UTF-8 support.

Chapter 5. Create the database with psql terminal

Skip this chapter if you used pgAdmin3 to create your database and continue with Install Database Schema.

Create a PostgreSQL user named system. Set the password of the user to manager. Allow the user to create databases.

su - postgres              # change to youre postgresql Account. 
createuser -P system       # Create the user system and ask for a passwort

Create the database crx-CRX with the following commands:

su - postgres
createdb -h localhost -E utf8 -U system crx-CRX

Set the encoding of this new database to UNICODE by really using the option -E utf8 in the command createdb above. Otherwise you will not be able to use openCRX with full UTF-8 support.

Chapter 6. Install Database Schema

After creating the database instance and the SQL User you are ready to install the openCRX database schema.

Connect to the crx-CRX database as system user.

Load and exec the file dbcreate-tables.sql with pgAdmin3 query (the sql icon on the top).

Similarly, load and exec the file dbcreate-indexs.sql .

If you prefer using the psql terminal you can install the database schema as follows:

psql -U system crx-CRX < dbcreate-tables.sql
psql -U system crx-CRX < dbcreate-indexes.sql

Chapter 7. Next Steps

If you have completed successfully the database installation you are ready to use the openCRX database. The application server installation guides explain how to connect the application server to the openCRX database instance.