Wednesday, 2 November 2016

Postgres with pgpool

Author: Lokesh Jawane  
PostgreSQL, or Postgres, offers various ways to archive and replicate the primary database for backup, high-availability, and load balancing scenarios. In Hot Standby mode, the system employs two or more servers:

Minimal configuration to setup master slave postgres replication
Below configuration on the both the servers:

wal_level: hot_standby
archive_mode: on
archive_command: 'cd .'
max_wal_senders: 5
wal_keep_segments: 32
hot_standby: on

Take base backup from the primary server

pg_basebackup -h -D -P -U replication --xlog-method=stream

Recovery File
This file required to continue the replication from basebackup. The last line in the file, trigger_file, is one of the most interesting parts of the entire configuration. If you create a file at that location on your slave machine, your slave will reconfigure itself to act as a master.

This will break your current replication, especially if the master server is still running; however this is recommended especially if your master server goes down. This will allow the slave to begin accepting writes. You can then fix the master server and turn that into the slave.

standby_mode = 'on'
primary_conninfo = 'host=master_IP port=5432 user=replication password=password'
trigger_file = '/tmp/postgresql.trigger.5432'

Automation for postgresql master slave with pgpool
Pgpool setup automation has been written in Ansible which currently support ubuntu 14.04 & 16.04 with postgres 9.3 & 9.5 version, from below link can copy the playbook code.

Pgpool role run steps in pgpool role

How to set up?
  • This script setup the master & slave server with single nodes. Currently works for ubuntu14.04 OS version.
  • Replication Configuration Parameters are set in ansible template to create configuration file.

wal_level: hot_standby
archive_mode: on
archive_command: 'cd .'
max_wal_senders: 5
wal_keep_segments: 32
hot_standby: on

 Configuration & Setup
  • Dependencies: All Dependencies are covered in playbook itself.
  • Setup configuration & Steps to run playbook

#Add master slave server ip's or hostname to playbook hosts file
vim hosts
ansible_connection=ssh ansible_ssh_user=<ssh login user>
ansible_ssh_private_key_file=<ssh pri key path>

ansible_connection=ssh ansible_ssh_user=<ssh login user>
ansible_ssh_private_key_file=<ssh pri key path>

 Create encrypted vars file using below steps

#create vault password file
vim .vault_pass.txt

#Create vars.yml file
vim roles/master/vars/vars.yml
repl_password: <replication user password>

#envrypt vars file
ansible-vault encrypt roles/master/vars/vars.yml --vault-password-file ./.vault_pass.txt

Note: there is one more file to vault encrypted file : "roles/common/vars/spoilers.yml" default password is "machine". so decrypt it and reencrypt it with your own password.

How to run playbook

ansible-playbook site.yml -i hosts  -e "postgres_version=9.5 bind_interface=" --vault-password-file ./.vault_pass.txt --sudo

 Note: site.yml is master playbook file

Your master slave are up & running now! :)

Contact me at:


  1. Existing without the answers to the difficulties you’ve sorted out through this guide is a critical case, as well as the kind which could have badly affected my entire career if I had not discovered your website "Devops Training in Bangalore"

    1. Great Article Cloud Computing Projects

      Networking Projects

      Final Year Projects for CSE

      JavaScript Training in Chennai

      JavaScript Training in Chennai

      The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

  2. Does your organization want large-scale migration, or can it survive on shifting solely a small a part of your assets to the cloud? Maybe, a subsidiary may survive with out having to be moved to the cloud.This is great blog. If you want to know more about this visit here AWS Cloud Certified.

  3. Thanks For sharing Your information The information shared Is Very Valuable Please Keep Updating Us Time Just went On reading Thae article Python Online Training DataScience Online Training AWS Online Training Hadoop Online Training


Amazon EKS - Kubernetes on AWS

By Komal Devgaonkar Amazon Elastic Container Service for Kubernetes (Amazon EKS), which is highly available and scalable AWS service....