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:
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
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
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 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_ssh_private_key_file=<ssh pri key path>
[slave]
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
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=
Note: site.yml is master playbook file
Your master slave are up & running now! :)
Contact me at: Lokesh.Jawane@Crevise.com
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"
ReplyDeleteGreat Article Cloud Computing Projects
DeleteNetworking 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
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.
ReplyDeleteThanks 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
ReplyDelete