Install PostgreSQL

This guide provides instructions how to install PostgreSQL for MiaRec.

Preparing The System

Update system default applications:

sudo apt-get update

Configure PostgreSQL Apt repository

These instructions are based on http://www.postgresql.org/download/linux/ubuntu/

Postgres is included into default repository of Ubuntu LTS, but its version is not up to date.

Create the fileĀ /etc/apt/sources.list.d/pgdg.list and add a line for the repository:

deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main

Import the repository signing key, and update the package lists:

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
sudo apt-key add -
sudo apt-get update

Then install the required version.

sudo apt-get install postgresql-9.4 postgresql-contrib-9.4

Create MiaRec user and database

The default database name and database user areĀ postgres. Switch to postgres user to perform the following operations.

In this example we create user 'miarec' and database 'miarecdb'.

First, start psql command-line interface as postgres user:

sudo -u postgres psql postgres

Create user for MiaRec application:

CREATE USER miarec PASSWORD 'password';

Create MiaRec database:

CREATE DATABASE miarecdb WITH ENCODING 'UNICODE' LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0;
ALTER DATABASE miarecdb OWNER TO miarec;

Connect to "miarecdb" database:

\c miarecdb;

Install uuid-ossp and hstore extensions into "miarecdb" database:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "hstore";

Enter \q to exit from psql command-line interface:

\q

PostgreSQL Configuration

The postgresql server is using two main configuration files

  • /etc/postgresql/9.4/main/pg_hba.conf
  • /etc/postgresql/9.4/main/postgresql.conf

pg_hba.conf

Change authentication method from ident to md5 for localhost connections.

Change:

host    all   all     127.0.0.1/32       ident

To:

host    all   all     127.0.0.1/32        md5

When other MiaRec components are deployed on dedicated servers, then you need to add their ip-addresses to trust group. For example:

host    all   all     192.168.0.10/32    md5      # allow access from 192.168.0.10
host    all   all     192.168.1.1/24     md5      # allow access from network 192.168.1.1/24

postgresql.conf

If other MiaRec components (like recorder and web portal) are deployed on dedicated servers, then you need to configure postgres to accept network connections. Change:

listen_addresses = 'localhost'

to

listen_addresses = '*'

Restart PostgreSQL

service postgresql restart