Setup BellaDati with PostgreSQL

These instructions will help you connect BellaDati to a PostgreSQL 8.4+ database. We are recommending the version 9.2+. It is intended for BellaDati WAR installations.

Before You Begin

Are You Migrating BellaDati to Another Server?

If you are migrating BellaDati to another server, create an export of your data as an XML backup. You will then be able to transfer data from your old database to your new database.

On this page:

Create and Configure the PostgreSQL Database

Create a database user which BellaDati will connect as (e.g. belladati_dbuser). Remember this database user name, as it will be used to configure BellaDati's connection to this database in subsequent steps. Do not forget to configure the pg_hba.conf file, which controls the database access.
```
$ createuser -SDRP belladati_dbuser
```
Create a database for BellaDati to be used as data warehouse (e.g. belladati_db) with Unicode collation.
```
CREATE DATABASE belladati_db WITH ENCODING 'UNICODE';
```
Or from the command-line:
```
$ createdb -E UNICODE -O belladati_dbuser belladati_db
```
Ensure that the user has permissions to connect to the database, and to create and write to tables in the database.
Check if your database has the plpgsql language, if not, you can define it using this command:
```
CREATE LANGUAGE plpgsql
```
Or from the command-line:
```
$ createlang -U belladati_dbuser plpgsql belladati_db
```

When using default PostgreSQL configuration file pg_hba.conf, you have to execute this command by connecting via TCP socket:
createlang -h 127.0.0.1 -U belladati_dbuser plpgsql belladati_db

Remember this database name, as it will be used to configure BellaDati's connection to this database in subsequent steps.

Copy the PostgreSQL JDBC Driver to Your Application Server

Skip this step if you are using BellaDati virtual applicance or BellaDati Standalone distribution. BellaDati virtual appliance includes the PostgreSQL JDBC driver.

Download the PostgreSQL JDBC driver from http://jdbc.postgresql.org/download.html. (Obtain version 9.0 or later of the JDBC 4 driver.)
Add the PostgreSQL JDBC driver jar to the lib/ directory of your application server.

PostgreSQL configuration

We are strongly recommending to perform the following PostgreSQL configuration changes in $POSTGRES_HOME/data/postgresql.conf. This example specifies parameters when you are running BellaDati and database on a single server with 4GB memory. 2GB of the are allocated for BellaDati application server running on JAVA (the -Xmx parameter). The rest should be available for operating system and database server.

Name	Value	Note
max_connections	50
shared_buffers	512MB	min 1/4 RAM
temp_buffers	2MB	per session
work_mem	10MB	per client connection
maintenance_work_mem	64MB
max_stack_depth	2MB
wal_buffers	512kB
effective_cache_size	512MB
log_statement	none

We are recommending set the archive_mode parameter to off and using of user defined pg_dump instead.

Backup and restore

You can backup the BellaDati's database using the PostgreSQL dump command:

pg_dump -U user -F c -v -f "path_to_dump/dump.backup" belladati_db

The restore is made by pg_restore command:

pg_restore -v -c -O -d belladati_db -U user path_to_dump/dump.backup

Next steps

Continue configuring BellaDati WAR for your application server as described on:

Page tree