Before everything else

What do you need for this amazing solution !

Hardware

Two Postgresql Servers

The sizing of the server depend on usage, amount of data, budjet of you client and so on.

A witness

The witness must run a linux distribution so you can use

  • Virtual Machine (recommended)
  • Physical Machine (recommended
  • Nas (not recommended but working)
  • Docker container (not recommended but working)
  • Raspberry pi (not recommended but working)

Software

Debian

Here I use Debian but you can use another distribution. for information it's work in production with CentOS (6 & 7) and RedHat (6 & 7).

Postgresql

I use the postgresql.org repository for the Postgres packages and not the package of Postgres include in each distribution.

Repmgr

I use Repmgr for the failover detection and to promote standby.

Install

Add entry in /etc/hosts :

root@allsrv# vi /etc/hosts
  192.168.master.ip    master.srv.sigma.host master repmgr_master
  192.168.standbay.ip  standby.srv.sigma.host standby repmgr_standby
  192.168.witness.ip   witness.srv.sigma.host witness repmgr_witness
  192.168.cluster.ip   cluster_vip.srv.sigma.host cluster_vip

I like to put data in a different disk from the system

root@allsrv# mkdir -p /data/postgresql
root@allsrv# chown -R postgres:postgres /data/postgresql

Install Postgresql engine on the 3 nodes

Get the repository for your distribution: postgresql.org
Exemple for Debian 8:

root@allsrv# echo "deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main " >    /etc/apt/sources.list.d/pgdg.list
root@allsrv# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc |sudo apt-key     add -
root@allsrv# apt-get update

Change the postgresql-common

root@allsrv# apt-get install postgresql-common
root@allsrv# vi /etc/postgresql-common/createcluster.conf

Change as follows:

#create_main_cluster = true
#data_directory = '/var/lib/postgresql/%v/%c'
log_line_prefix = '%%t [%%p-%%l] %%q%%u@%%d '

by

create_main_cluster = false
data_directory = '/data/postgresql/%v/%c'
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '

make directory

root@allsrv# mkdir -p /data/postgresql
root@allsrv# chown postgres:postgres /data/postgresql

Install Postgresql (server, client, contrib)

root@allsrv# apt-get install postgresql-9.5 postgresql-client-9.5 postgresql-contrib-9.5
root@allsrv# apt-get install postgresql-server-dev-9.5
root@allsrv# apt-get install make libxslt1.1 libxslt1-dev libreadline-dev libselinux1-dev libpam0g-dev libedit-dev

find pg_config

root@allsrv# locate pg_config
root@allsrv# /usr/lib/postgresql/9.5/bin/pg_config
root@allsrv# export PATH=$PATH:/usr/lib/postgresql/9.5/bin

root@allsrv# vi /etc/profile.d/postgres
  # modify to suit your environment
  if [ "$(id -un)" = "postgres" ]; then
    export PATH=$PATH:/usr/lib/postgresql/9.5/bin
  fi

Install Repmgr

root@allsrv# wget http://www.repmgr.org/download/repmgr-3.0.2.tar.gz
root@allsrv# tar zxvf repmgr-3.0.2.tar.gz
root@allsrv# cd repmgr-3.0.2
root@allsrv# make USE_PGXS=1 install