Preface

These instructions will help you connect the BellaDati application to a PostgreSQL database.

We recommend using database engine PostgreSQL 16. This database engine is supported now.

PostgreSQL 16 is supported starting from BellaDati 2.10.14 release.

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


  1. 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

  2. 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
  3. Ensure that the user has permissions to connect to the database, and to create and write to tables in the database.


  4. Step 4 can be skipped for PostgreSQL 10 and above

    Check if your database has the plpgsql language. If not, you can define it using command below(template0 has plpgsql installed so belladati_db database inherits it:

    CREATE LANGUAGE plpgsql

    Or from the command line (this option is no longer available since PostgreSQL 10. Use command above):

    $ 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 the applications server configuration.

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.

This configuration may change according to real usage needs. 

Examples below show the minimal configuration for single server installation (4GB) and  recommended configuration for single server installation (16GB).

Name

Value

Description

Note

max_connections

50

Sets the maximum number of concurrent connections.


shared_buffers

512MB

Sets the number of shared memory buffers used by the server.

min 1/4 RAM, 1/2 RAM recommended

temp_buffers

2MB

Sets the maximum number of temporary buffers used by each session.

per session

work_mem

16MB

Sets the maximum memory to be used for query workspaces.

min 16MB, RAM / 128 recommended

maintenance_work_mem

64MB

Sets the maximum memory to be used for maintenance operations.

min 64MB, RAM / 32 recommeneded

max_stack_depth

2MB

Sets the maximum stack depth, in kilobytes.


wal_buffers

512kB

Sets the number of disk-page buffers in shared memory for WAL.


effective_cache_size

1024MB

Sets the planner's assumption about the size of the disk cache.

RAM / 2 recommended

log_statement

none

Sets the type of statements logged.


autovacuumonStarts the autovacuum subprocess.
autovacuum_analyze_scale_factor0.05Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples.
autovacuum_analyze_threshold10Minimum number of tuple inserts, updates, or deletes prior to analyze.
autovacuum_freeze_max_age200000000Age at which to autovacuum a table to prevent transaction ID wraparound.
autovacuum_max_workers5Sets the maximum number of simultaneously running autovacuum worker processes.
autovacuum_multixact_freeze_max_age400000000Multixact age at which to autovacuum a table to prevent multixact wraparound.
autovacuum_naptime15sTime to sleep between autovacuum runs.
autovacuum_vacuum_cost_delay10msVacuum cost delay in milliseconds, for autovacuum.
autovacuum_vacuum_cost_limit1000Vacuum cost amount available before napping, for autovacuum.
autovacuum_vacuum_scale_factor0.1Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.
autovacuum_vacuum_threshold25Minimum number of tuple updates or deletes prior to vacuum.

This example specifies parameters when you run the BellaDati application and database on a single server with recommended 16GB memory. 8GB are allocated for the BellaDati application server running on Java (BellaDati java heapspace  -Xmx  8192 MB). Assuming that the OS takes 2 GB , then that leaves 6 GB for the postgres database. This value should be worked with when calculating the parameter settings in postgresql.conf .

Name

Value

Description

Note

max_connections

210

Sets the maximum number of concurrent connections.

Maximum Pool size (default 200) +10

shared_buffers

2048MB

Sets the number of shared memory buffers used by the server.

15 - 40 % RAM recommended

temp_buffers

2MB

Sets the maximum number of temporary buffers used by each session.

per session

work_mem

10MB

Sets the maximum memory to be used for query workspaces.

RAM/max_connection/3(count of possible operation)

maintenance_work_mem

307MB

Sets the maximum memory to be used for maintenance operations.

RAM x 0.05

max_stack_depth

2MB

Sets the maximum stack depth, in kilobytes.


wal_buffers

512kB

Sets the number of disk-page buffers in shared memory for WAL.


effective_cache_size

4,096MB

Sets the planner's assumption about the size of the disk cache.

(Total RAM - java heapspace)  / 2 recommended

log_statement

none

Sets the type of statements logged.


autovacuumonStarts the autovacuum subprocess.
autovacuum_analyze_scale_factor0.05Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples.
autovacuum_analyze_threshold10Minimum number of tuple inserts, updates, or deletes prior to analyze.
autovacuum_freeze_max_age200000000Age at which to autovacuum a table to prevent transaction ID wraparound.
autovacuum_max_workers5Sets the maximum number of simultaneously running autovacuum worker processes.
autovacuum_multixact_freeze_max_age400000000Multixact age at which to autovacuum a table to prevent multixact wraparound.
autovacuum_naptime15sTime to sleep between autovacuum runs.
autovacuum_vacuum_cost_delay10msVacuum cost delay in milliseconds, for autovacuum.
autovacuum_vacuum_cost_limit1000Vacuum cost amount available before napping, for autovacuum.
autovacuum_vacuum_scale_factor0.1Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.
autovacuum_vacuum_threshold25Minimum number of tuple updates or deletes prior to vacuum.

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

Installing BellaDati on Linux - continue  Installing BellaDati on Linux, step "check that default ports"


Installing BellaDati WAR

continue installing the BellaDati.war application on your application server as described in:


  • No labels