Tutorial - Install and Use a PostgreSQL Database

Install PostgreSQL and Setup a Database

The below steps provide a simple walkthrough to install PostgreSQL and configure a database. For more details you can consult the official documentation: https://www.postgresql.org/docs/9.3/static/tutorial-createdb.html.

Install the following packages:

$ sudo apt-get install postgresql-client-9.5 postgresql-9.5

Start PostgreSQL server
```
$ sudo /etc/init.d/postgresql start
```

Launch PostgreSQL prompt

$ sudo -u postgres psql

This should start a prompt as follows:
postgres=#

If it’s a new installation of PostgreSQL, set a password for postgres user
```
postgres=# ALTER USER postgres PASSWORD 'newPassword';
```

Create a user for the RealOpInsight database and set a password

postgres=# CREATE USER realopinsight;
postgres=# ALTER USER realopinsight WITH ENCRYPTED PASSWORD '<password>';

Create a database

postgres=# CREATE DATABASE realopinsight;

Granting privileges on database to the user

postgres=# GRANT ALL PRIVILEGES ON DATABASE realopinsight TO realopinsight ;

Enable PostgreSQL as Database Backend

Log into RealOpInsight as administrator.
Select the menu Database Backend and fill in the displayed database setting form (see screenshot below).
Select PostgreSQL as Database Type.
On the field Database Name set the name of the database to use in PostgreSQL.
If it’s a *new database, i.e. not yet used by RealOpInsight, check the field Empty Database to let RealOpInsight to initialize the database.
On the property Server Address set the IP address or the hostname of PostgreSQL server.
On the property Server Port set the port on which PostgreSQL is listening (it’s 5432 by default).
On the property Username set the username to use to connect to the database (see installation steps above).
On the property Password set the password for the database user (see installation steps above).
Finally click on Save, a message will be displayed to indicate whether the changes have succeeded or not.

Warning: Don’t disconnect from RealOpInsight if you have errors on this page, otherwise you may no longer be able to log in (see alternative settings with environment variables below). If you want to abandon changes, please rollback to your previous successful configuration before leaving.

Configuration Environment Variables

All database configuration settings descrived below can be overrriden by the following environment variables:

REALOPINSIGHT_DB_TYPE

Set the database type:

For SQLite set it to 0
For PostgreSQL set it to 1.

REALOPINSIGHT_DB_SERVER_ADDR

Set the hostname or the IP address of host on which the database server is running. Only applicable for PostgreSQL.

REALOPINSIGHT_DB_SERVER_PORT

Set the port of the database server. Only applicable for PostgreSQL.

REALOPINSIGHT_DB_NAME

Set the name of the database to use. Only applicable for PostgreSQL.

REALOPINSIGHT_DB_USER

Set the user to use to connect to the database. Only applicable for PostgreSQL.

REALOPINSIGHT_DB_PASSWORD

Set the password of the database user. Only applicable for PostgreSQL.