Section |
---|
Column |
---|
Preface Info |
---|
| These instructions will help you connect the BellaDati application to a PostgreSQL database. We recommend using database engine PostgreSQL 1012. This database engine is supported now and will be supported 6 months after BellaDati 2. 10 release.
PostgreSQL 12 will be is supported starting from BellaDati 2.10 release. |
|
|
Create and Configure the PostgreSQL Database Warning |
---|
title | Setup the pg_hba.conf file to allow accessing the database via TCP socket. Here is an example: |
---|
|
Code Block |
---|
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. Code Block |
---|
$ createuser -sDRP belladati_dbuser |
Create a database for BellaDati to be used as data warehouse (e.g. belladati_db ) with Unicode collation. Code Block |
---|
CREATE DATABASE belladati_db WITH ENCODING 'UNICODE' TEMPLATE template0; |
Or from the command-line: Code Block |
---|
$ 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.
Warning |
---|
title | 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: Code Block |
---|
CREATE LANGUAGE plpgsql |
Or from the command line (this option is no longer available since PostgreSQL 10. Use command above): Code Block |
---|
$ createlang -U belladati_dbuser plpgsql belladati_db |
Note |
---|
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 |
|
Info |
---|
Remember the database name; it will be used to configure BellaDati's connection to the database in the applications server configuration. |
PostgreSQL configurationWe 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. Warning |
---|
This configuration may change according to real usage needs. Example below shows the minimal configuration for single server installation. |
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. |
| autovacuum | on | Starts the autovacuum subprocess. |
| autovacuum_analyze_scale_factor | 0.05 | Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples. |
| autovacuum_analyze_threshold | 10 | Minimum number of tuple inserts, updates, or deletes prior to analyze. |
| autovacuum_freeze_max_age | 200000000 | Age at which to autovacuum a table to prevent transaction ID wraparound. |
| autovacuum_max_workers | 5 | Sets the maximum number of simultaneously running autovacuum worker processes. |
| autovacuum_multixact_freeze_max_age | 400000000 | Multixact age at which to autovacuum a table to prevent multixact wraparound. |
| autovacuum_naptime | 15s | Time to sleep between autovacuum runs. |
| autovacuum_vacuum_cost_delay | 10ms | Vacuum cost delay in milliseconds, for autovacuum. |
| autovacuum_vacuum_cost_limit | 1000 | Vacuum cost amount available before napping, for autovacuum. |
| autovacuum_vacuum_scale_factor | 0.1 | Number of tuple updates or deletes prior to vacuum as a fraction of reltuples. |
| autovacuum_vacuum_threshold | 25 | Minimum number of tuple updates or deletes prior to vacuum. |
|
Info |
---|
We recommend set the archive_mode parameter to off , using the pg_dump command instead. |
Backup and restoreYou can backup the BellaDati database using the PostgreSQL dump command: Code Block |
---|
pg_dump -U user -F c -v -f "path_to_dump/dump.backup" belladati_db
|
To restore a backup, use the pg_restore command: Code Block |
---|
pg_restore -v -c -O -d belladati_db -U user path_to_dump/dump.backup
|
Next stepsInstalling BellaDati on Linux - continue Installing BellaDati on Linux HIDDEN OLD, step "check that default ports"
Installing BellaDati WAR continue installing the BellaDati.war application on your application server as described in:
|