4 Setting Up a PostgreSQL Server for Data Mart

Instructions

This guide covers how you can setup PostgreSQL for use with your OpenClinica Data Mart. This is necessary if you would like to send the Data Mart data to an external database (Option 2 in the Customer Configuration Worksheet).

Setting up your PostgreSQL database involves the following steps:

1. Install a PostgreSQL server.
2. Create a role in PostgreSQL that OpenClinica can send the Data Mart with.
3. Create a DB for the Data Mart data to be loaded into.
4. Enable access in PostgreSQL so that you OpenClinica application server can communicate with PostgreSQL.

Step 1: Install a PostgreSQL Server.

If your server is running Linux please follow the instructions on our installation guide for Linux section “Postgres Install” located here.

If your server is running Windows please follow the instructions on our installation guide for Windows section “Install PostgreSQL” located here.

Step 2: Create a role.

If your server for PostgreSQL is running Linux, run the following command to create a role. This command will create a role with the username of “datamart” and a password of “datamart.”

/opt/PostgreSQL/8.4/bin/psql -U postgres -c “CREATE ROLE datamart LOGIN ENCRYPTED PASSWORD ‘datamart’ NOINHERIT NOCREATEDB NOCREATEROLE;”

If your server for PostgreSQL is running Windows please open up PGAdminIII (which is installed when you install PostgreSQL). In the PGAdminIII interface execute the following query on the “postgres” database. This will create a role with the username of “datamart” and a password of “datamart.”

CREATE ROLE datamart LOGIN ENCRYPTED PASSWORD ‘datamart’ NOINHERIT NOCREATEDB NOCREATEROLE;

Step 3: Create a DB.

If your server for PostgreSQL is running Linux, run the following command to create a DB. This command will create a DB with the name of “datamart” and owned by the role “datamart.”

/opt/PostgreSQL/8.4/bin/psql -U postgres -c “CREATE DATABASE  datamart WITH ENCODING=’UTF8′ OWNER=datamart;”

If your server for PostgreSQL is running Windows, please open up PGAdminIII. In the PGAdminIII interface execute the following query on the “postgres” DB. This will create a DB with the name of “datamart” and owned by the role “datamart.”

CREATE DATABASE  datamart WITH ENCODING=’UTF8′ OWNER=datamart;

Step 4: Allow access to the DB from your application server.

By default, PostgreSQL will not allow the connection. Please follow this guide to enable access for the application server.

If you followed the instructions in steps 1-4, the entry for pg_hba.conf should look like the following. Replace $IP with the IP of your application server.

host datamart datamart $IP/32 md5

If more then one Data Mart DB is needed:

Most customers will have more then one Data Mart DB since they have more then one OpenClinica instance with Data Mart activated on each instance. In this scenario you have two options for setting up the additional Data Mart databases.

1. Use a separate DB server for each Data Mart DB
• For this scenario you can re-run steps 1-4 on the other server.
2. Use the same DB server for all Data Mart DBs
• For this scenario, re-run step 3 “Create a DB” while replacing the DB name with a unique name. e.g.:” datamart_test”
• Additionally please complete step 4 “Allow access to the DB from your application server” for this new DB name. For example, if you used “datamart_test” as your new DB name an example entry for pg_hba.conf will look like the following:

host datamart_test datamart $IP/32 md5

What your OpenClinica Enterprise support team needs to configure this Data Mart option:

As described in you Customer Configuration Worksheet we will need the following information to complete the configuration of your Data Mart DB(s).

• Server IPHostname
• The IPHostname of the PostgreSQL DB server you configured.
• Database Name
• Using the examples provided in this guide this would be “datamart” and “datamart_test” for a two instance installation.
• Username
• Using the examples provided in this guide the username would be “datamart” for both Data Mart DB(s)
• Password
• Using the examples provided in this guide the password would be “datamart”. This should be changed for security purposes.

The following is a depiction of the relevant section in teh Customer Configuration Worksheet. Based on this guide you would provide us the following information for your two instances. Replace $IP with the IPHostname of your database server.