Postgres with pgpool

Author: Lokesh Jawane  

Overview

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:

• A primary server runs the active database. This database accepts connections from clients and permits read-write operations.
• One or more standby servers run a copy of the active database. These databases are configured to accept connections from clients and permit read-only operations. If the primary database server fails, the system can fail over to the standby server, which makes the standby server the active primary server. 
  
  
   The best way with the minimal conf & details which I have followed: 
  • https://wiki.postgresql.org/wiki/Streaming_Replication 
  • http://pjkh.com/articles/postgresql-replication-and-load-balancing-with-pgpool2/ 

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.

https://github.com/crevise/playbook/tree/master/ansible-postgresql

Pgpool role run steps in pgpool role README.md

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
[master]
ansible_connection=ssh ansible_ssh_user=<ssh login user>
ansible_ssh_private_key_file=<ssh pri key path>

[slave]
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
thepassword
:wq!

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

#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: Lokesh.Jawane@Crevise.com