Setting up the PostgreSQL Database

These instructions will help you connect BellaDati to a PostgreSQL 8.4+ database. We recommend using version 9.2 or higher. This document is intended for BellaDati WAR installations.

Before You Begin

Migrating BellaDati to a Different Server

If you are migrating BellaDati to a different 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

Setup the pg_hba.conf file to allow accessing the database via TCP socket. Here is an example:

host belladati_db belladati_dbuser 0.0.0.0/0 md5

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' TEMPLATE template0;

Or from the command-line:

$ createdb -E UNICODE -O belladati_dbuser belladati_db -T template0

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 the 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 the database name; it will be used to configure BellaDati's connection to the database in subsequent steps.

PostgreSQL configuration

We strongly recommend performing the following PostgreSQL configuration changes in $POSTGRES_HOME/data/postgresql.conf. This example specifies parameters when you run the BellaDati application and database on a single server with 4GB memory. 2GB are allocated for the BellaDati application server running on Java (using the -Xmx parameter). The rest should be available for the 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 recommend set the archive_mode parameter to off, using the pg_dump command instead.

Backup and restore

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

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

To restore a backup, use the pg_restore command:

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

Next steps

Continue installing the BellaDati WAR on your application server as described in:

Page tree