3. Installing Memoria Database

Attention

The “Installing Memoria Database” chapter contains information only needed for local Cerebro installation (see: “Local (On-premises) Deployment”).

All Cerebro client applications connect to a central database. The database stores tasks and links between them, user information (including password hashes) and hashes of all attached files (files themselves are stored elsewhere, in decentralized storages, and are handled by Cargador service).

The central database is named Memoria. It runs on the PostgreSQL DBMS. Memoria consists of interconnected tables, accessed through stored procedures. Also, Memoria has an additional plugin for PostgreSQL to handle Gantt charts, user permissions and licence management – malosol.dll/so.

Cerebro users don’t have direct access to data tables. Access is provided by the client application, which calls a stored procedure. The procedure verifies user’s access rights and returns data accordingly. Thus, data is prevented from unauthorized access of low-level DBMS access utilities.

Also, stored procedures handle server-side “Business Logic”, modify data tables according to users’ queries, form statistics reports, etc.

3.1. Installing PostgreSQL DBMS

The PostgreSQL project (http://www.postgresql.org) is a free software, distributed under liberal open source license. This DBMS is very stable and reliable, and has rich functionality.

Originally PostgreSQL was developed on Unix-based systems, therefore we recommend to instal it onto a Unix system. It runs under Windows and MacOS, too, but may have some minor issues in performance and stability.

Currently Cerebro supports PostgreSQL ver.9.0.

In order to install PostgreSQL, first, download the distribution pack from http://www.postgresql.org (alternatively - from: http://www.enterprisedb.com/products-services-training/pgdownload) or use your Linux package installation tool. Then follow the setup wizard included into the distribution pack.

Note

There were some issues during the installation on Windows OS. The installer, for some reason, cannot finish the final step - initialize a new database. If you come across this issue, you can complete the installation manually, following the chapter: “Initializing Database Cluster”.

3.1.1. Initializing Database Cluster

PostgreSQL cluster initialization is basically creation of an initial file structure to store DB files in. Unix-based systems usually save the DB in /var/lib/postgres. The Windows installer asks for a destination folder during the process of installation.

We recommend to select UTF8 option as server encoding.

Manual DB initialization is done by launching initdb utility (from postgresql package) with, for example, the following parameters:

initdb --username=postgres --pwprompt -E UTF8 /mnt/raid/db

This command creates a primary database in /mnt/raid/db folder with postgres as administrator’s account. On creation you are asked to set a password for the new DB administrator.

Warning

After the directory is created, make sure that Postgres user account (usually it is called postgres), has a read/write permission in this directory. For Unix it is recommended to assign the user to be the owner of the directory.

3.1.2. PostgreSQL authentication

Memoria DB uses native PostgreSQL methods for use authentication. For DB there are several ways to see “who is who”:

  • trust – user is not verified. This method must be restricted for obvious security reasons. That’s why the installer configures server to be accessible with trust method only from the 127.0.0.1 address.
  • ident – user authenticates in DB with the current system account, no password required. This method is used for initial PostgreSQL configuration in Unix-based systems.
  • md5 – user inputs the username and MD5 hash of the password. This is the basic method for remote connections to the server, this method should be used for all user connections to Memoria.

Note

The ident and trust authentication methods restricted for 127.0.0.1 or local unix-sockets are recommended for saving backups and other administrative purposes.

3.1.3. Launching and Configuring a Database Server

The following command is used to launch a PostgreSQL server, regardless of the OS:

postgres –D <path to DB>

An actual command will be different from this one, mainly, in the -D parameter, which must contain the actual path to DB file structure. In this structure top level directory contains the PostgreSQL configuration files. Two following files should be edited to configure Memoria:

  • postgresql.conf – the main configuration file;
  • pg_hba.conf – connection security policy settings.

The following parameter should be set in postgresql.conf:

listen_addresses = '*' # listen all addresses

Also usrvar class name must be added to the custom_variable_classes parameter in the CUSTOMIZED OPTIONS section (in the end of the file):

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

custom_variable_classes = 'usrvar'

A line like this is by default in pg_hba.conf:

Host   all   all   127.0.0.1/32  ident

It’s up to you whether to delete it or to leave for administrative purposes. Also, you should add there a line like this:

Host   all   all   0.0.0.0/0    md5

, i.e., allow connections from any addresses using MD5 authentification (see “PostgreSQL authentication”).

3.1.4. Checking Connection

The most popular GUI tool for PostgreSQL administration is the pgAdmin utility. It is an independent software product, not affiliated with the PostgreSQL project. It can be downloaded from http://www.pgadmin.org.

After installing pgAdmin use it to connect to the database in order to check the server settings:

  • Launch pgAdmin;
  • click File/Add server menu item and enter your PostgreSQL server connection settings;
  • connect to the server.

You may also use psql, a default console client:

psql [-h server_host] [--username=<user>] -d template1

, where template1 is a system database.

3.2. Installing Malosol Plugin

Malosol, a DB plugin, handles special functions of licensing, actual user permission calculations and interconnected task parameters’ calculations.

The plugin is delivered as libmalosol dynamic library, within the service-tools pack (available in Download/Server components section on https://cerebrohq.com).

Malosol is installed by unpacking libmalosol executable file (according to DB server OS) to a particular folder (for example, for Linux it might be /cerebro/malosol/libmalosol90-64.so.1.0.0).

Then, in order to connect it to the database, it is necessary to specify path to its executable file in the MALOSOL variable in the config.py script. This should be done during database deployment (see “Deploying Memoria”).Default value of this variable is $libdir/libmalosol90.so, where $libdir means the path to the PostgreSQL libraries folder (for example, in Linux it is /usr/local/pgsql/lib).

Note

You may use a symlink instead of renaming and moving the Malosol library file to $libdir.

3.3. Deploying Memoria

The Memoria database is deployed by cerebro.db.update/internal/init.py script from service-tools

Note

This script requires Python (http://www.python.org) version 2.х or higher (Linux systems usually have Python by default). Make sure that the Python directory is added to the system path. Run the following command in the console to check it (or to find out the current Python version):

python --version

3.3.1. Editing config.py

Before starting Memoriadeployment you should configure parameters in the config.py file. A sample file is located in cerebro.db.update/config.py.example.

Copy this configuration file to a specific directory before editing:

  • for Unix systems – /etc/cerebro.db.update/config.py;
  • for Windows systems – %APPDATA%/cerebro.db.update/config.py, where APPDATA is a system variable, the path like: C:\Users\<имя пользователя>\AppData\Roaming.

Note

This config.py configuration should only be done once, on initial DB deployment. Later on, Memoria will use existing configuration file.

The config.py.example file contains descriptive notes for each parameter, and by default it looks like this:

##################################

DB_NAME="memoria"

# leave HOST empty for localhost or unix-socket
HOST=""
PORT="5432"

##################################
## Authentication
# There are two main way to authenticate in Postgres: 
#  * 'Indent' (or less secure 'Trust' modification) - no password is needed, access grants only from local connection
#      It is installation default on UNIX.
#  * 'md5' - user login and password are needed. General way to login in Postgres from anywhere
#      Windows installer asks you for 'postgres' admin account
#
# When Indent auth is used, only user name is needed. Update will start throught 'sudo' with 'DB_USER' credentials


DB_USER="postgres"
# For Ident auth-method set DB_PASS empty
DB_PASS=""

##################################
# Setup libmalosol plug-in path. You may copy it to postgres/lib folder elsewere.
#   Windows: Besides plug-in path, you have to change filename this to 'libmalosol90.dll'
MALOSOL="$libdir/libmalosol90.so"


##################################
# Fill it if pg-tools could not be found through PATH env-var
#   Windows: use forward '/' slash in path
# PG_DIR="/usr/local/pgsql-9.0/bin"
# PG_DIR="C:/Program Files/postgreSQL/bin"
PG_DIR=""

If the installation procedure meets the following conditions:

you may leave the default config.py parameters unchanged. Otherwise, if any of the conditions are not met, you have to change the settings accordingly.

Warning

A mistake is often made while updating Memoria under Unix systems – access rights for postgres user areinsufficient to read the /etc/cerebro.db.update folder, where config.py is stored. In this case you’ll see the following error:

ImportError: No module named config

3.3.2. Initializing the Database

As mentioned above, a Python script cerebro.db.update/internal/init.py must be executed to deploy a new database. You must do the following to launch the script correctly:

  • unzip service-tools to a temporary location on a disk;

  • copy the command file: init.<cmd|sh> (according to the OS on the server) from the cerebro.db.update/internal subfolder to its parent folder cerebro.db.update;

  • execute the following command in the console:

    init.<cmd|sh> <memoria-x.y>
    

    , i.e., pass it the name of the folder with pre-archived Memoria image as a parameter.

Warning

The init.<cmd|sh> command is used only for initial new Memoria database creation. Use the update.<cmd|sh> command to update an existing database.

If initialized successfully, Memoria displays a log in the console that ends with “Done!”. Otherwise, you should read error messages in the console and try to resolve the issues (in most cases they are caused by unsufficient access rights to some objects). You may try to re-initialize the database iteratively, until all issues are resolved.

3.4. Database Backup

Even if you installed the database on a RAID storage, you should back it up from time to time, preferrably on a separate physical storage.

You can use our backup script as a template:

#!/bin/bash

BAK_PATH=/media/sdb1
VER=`date +'%F_%H.%M'`
outGlob=$BAK_PATH/$VER.global.bak
outLoc=$BAK_PATH/$VER.memoria.bak
sudo -u postgres -- pg_dumpall -U sa -g -f "$outGlob"

if [ $? != 0 ];then exit $?;fi;

sudo -u postgres -- pg_dump -U sa -f "$outLoc" -F c memoria

if [ $? != 0 ];then exit $?;fi;

find $BAK_PATH -type f \! -newermt '1 month ago' -exec rm {} \;

The script creates sets of independent backups with the date of creation included in the filename. Backups are deleted in 1 month after creation.

The script requires local database authentication without password (see settings of pg_hba.conf in “Launching and Configuring a Database Server”).

This script should be launched from time to time. For example, in Linux, corresponding settings in /etc/crontab can look like this:

0   */4  *   *   *      root    /usr/local/bin/memo_back

(the script launches every four hours).

Note

In Windows you have to modify the script and use the Task Scheduler to start the script.

3.5. Version Information

The database contents versions are useful to know for making decisions about upgrading the DB. This information is available in cur_state table. It may be obtained via the following query using pgAdmin for example:

select * from cur_state;

As a result you will get the following data columns:

  • schema_version – the current version of data structure.
  • script_timestamp – the edition of the stored procedures.
  • api_version – the Cerebro client application version. Note that newer client applications may require to upgrade a database for proper functioning.