Install PostgreSQL

Please note: this is legacy documentation. Please check out https://docs.miarec.com/all/ for the most up-to-date documentation and user guides. 

This guide provides instructions on how to install PostgreSQL for MiaRec.

Preparing The System

Update system default applications:

yum update

Configure your YUM repository

Postgres is included in the default repository of CentOS / Red Hat / Fedora, but its version is not up to date.

Locate and edit your distributions .repo file, located:

  • On Fedora: /etc/yum.repos.d/fedora.repo and /etc/yum.repos.d/fedora-updates.repo, [fedora] sections
  • On CentOS: /etc/yum.repos.d/CentOS-Base.repo, [base] and [updates] sections
  • On Red Hat: /etc/yum/pluginconf.d/rhnplugin.conf [main] section

To the section(s) identified above, you need to append a line (otherwise dependencies might resolve to the PostgreSQL supplied by the base repository):

exclude=postgresql*

For example, on Centos 6 the .repo file should be:

[base]
name=CentOS-$releasever - Base
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os&infra=$infra
#baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
exclude=postgresql*

#released updates
[updates]
name=CentOS-$releasever - Updates
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=updates&infra=$infra
#baseurl=http://mirror.centos.org/centos/$releasever/updates/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
exclude=postgresql*

Install PGDG RPM file

A PGDG file is available for each distribution/architecture/database version combination. Browse http://yum.postgresql.org and find your correct RPM. For example, to install PostgreSQL 9.4 on CentOS 6 64-bit:

Centos 6:

yum localinstall http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-2.noarch.rpm

Centos 7:

yum localinstall http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-2.noarch.rpm

Install PostgreSQL database server and its extensions

yum install postgresql94-server postgresql94-contrib

Initialize data directory

After installing the packages, a database needs to be initialized and configured.

service postgresql-9.4 initdb

If the previous command did not work, try directly calling the setup binary, located in:

/usr/pgsql-9.4/bin/postgresql94-setup initdb

Configure start at boot up

chkconfig postgresql-9.4 on

Start PostgreSQL database service

service postgresql-9.4 start

Create MiaRec user and database

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

First, connect/login as root:

sudo -u postgres psql postgres

This will open psql command-line interface.

Create user (replace 'password' with something more secure):

CREATE USER miarec PASSWORD 'password';

Create 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

  • /var/lib/pgsql/9.4/data/pg_hba.conf
  • /var/lib/pgsql/9.4/data/postgresql.conf

Edit pg_hba.conf

vi /var/lib/pgsql/9.4/data/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

Edit postgresql.conf (optional)

vi /var/lib/pgsql/9.4/data/postgresql.conf

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

listen_addresses = 'localhost'

to

listen_addresses = '*'

Restart PostgreSQL

service postgresql-9.4 restart