Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Replication with RepMgr and PgBouncer in Postgresql

Introduction. PostreSQL, or postgres, is a popular database management system that can organize and manage the data associated with websites or applications.Replication is a means of copying database information to a second system in order to create high availability and redundancy
repmgr 3.0:-
repmgr is an open-source tool suite to  monitor replication, and perform administrative tasks such as failover or manual switchover operations.
PgBouncer is connection pooler for PostgreSQL databases and acts as gateway for clients to connect to the databases.pgBouncer allows for backend database configuration changes by just editing its configuration file and reloading the process.

Related image

host1: first database host running on
host2: second database host running on 192.168.1101
bouncer-host: host running pgbouncer on

1.Install dependencies.
2.Configure ssh access between db nodes.
3.Configure databases.
4.Configure PostgreSQL replication.
5.Configure replication manager.
6.Clone slave.
7.Configure pgBouncer.

1) Install Dependences host1, host2, bouncer-host)
The default ubuntu 14.04 installation does not include PostgreSQL 9.4 so we will need to add the repository:
Create the file /etc/apt/sources.list.d/pgdg.list, and add a line for the repository
deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main
Import the repository signing key, and update the package lists
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -  
sudo apt-get update  
Install the dependencies for database hosts (host1,host2)
sudo apt-get install postgresql-9.4  
sudo apt-get install postgresql-9.4-repmgr  
sudo apt-get install postgresql-client-9.4  
Install the dependencies for pgBouncer host (bouncer-host)
sudo apt-get install postgresql-client-9.4  
sudo apt-get install pgbouncer 
2) Configure password-less ssh between hosts (host1,host2)
by default postgreSQL creates a postgres local linux user which is used to do all database management operations. To create slaves or recreate a master after failure repmgr requires that the user running the commands have password-less ssh access to the source host. Since the postgres user is the user which has permissions on the postrgres directories, we will configure that user for ssh.

switch to the user

$ sudo su postgres
create ssh key pair
$ ssh-keygen
copy the public key id_rsa.pub contents and add it to the authorized_keys file in the other host's postgres user ssh directory. For more details see https://github.com/2ndQuadrant/repmgr/blob/master/SSH-RSYNC.md
test that you can ssh between servers before going to the next step.

3) Configure Databases and users (host1)
We will need two databases one will be used by repmgr and the other will be our application database. All commands are to be executed using the postgres user.

for repmgr database
$ createuser -s repmgr
$ createdb repmgr -O repmgr
for our application database
$ createuser test_user
$ createdb test_db -O test_user
since this user will be used by the applications (clients) we will set a password for it. Run the following command from inside a psql shell with the default postgres user.
postgres=# ALTER USER test_user WITH PASSWORD '<newpassword>';
4) Configure PostgreSQL replication (host1)
We will now configure the master host (host1) for replication. This is acheived by editing the postgresql.conf file and updating the follwing items
wal_level = 'hot_standby'  
archive_mode = on  
archive_command = 'cd .'  
max_wal_senders = 10  
max_replication_slots = 1  
hot_standby = on  
shared_preload_libraries = 'repmgr_funcs' 
We also need to configure the authentication configuration (pg_hba.conf) to allow repmgr to access replication database and also allow password based access for our application database (test_db). We will add the following lines:
host    repmgr          repmgr     trust  
host    replication     repmgr     trust  
host    test_db         test_user     md5 
restart the postgres service after the changes are done
$ sudo service postgresql restart
5) Configure Replication Manager (host1,host2)
We will need to create a repmgr.conf on each database host. The file could be stored anywhere but we will use /etc/repmgr/repmgr.conf.

The file contents for the 1host1 should be
conninfo='host=host1 user=repmgr dbname=repmgr'  
We are now ready to join our master to the replication topology. Using the postgres user run the command
$ repmgr -f /etc/repmgr/repmgr.conf master register
and similarly for host2 we need to create the file /etc/repmgr/repmgr.conf with the contents
conninfo='host=host2 user=repmgr dbname=repmgr'  

6) Clone Slave (host2)
ssh into our slave server host
stop the postgresql service
$ sudo service postgresql stop
run the following command using the postgres user to clone the master
$ repmgr -f /etc/repmgr/repmgr.conf --force --rsync-only -h host1 -d repmgr -U repmgr --verbose standby clone
start the postgresql server
$ sudo service postgresql start
register the slave as a standby
$ repmgr -f /etc/repmgr/repmgr.conf --force standby register
if all the previous steps ran successfully we should be able to see the state of our replication cluster using the command
$ repmgr -f /etc/repmgr/repmgr.conf cluster show
and it should return an output like this
   Role     | Connection String  
*   master  | host=host1 user=repmgr dbname=repmgr
    standby | host=host2 user=repmgr dbname=repmgr

7) Configure pgBouncer (bouncer-host)
All pgBouncer needs is a configuration file pgbouncer.ini which can be stored anywhere in the system. The contents of the file should be
test_db = host=host1 port=5432 dbname=test_db

listen_port = 6543  
listen_addr =  
auth_type = md5  
auth_file = users.txt  
logfile = pgbouncer.log  
pidfile = pgbouncer.pid  
admin_users = test_user 
The configuration above instructs pgBouncer to listen for connections on port 6543 and relay connections to the database test_db to our current master host1 on port 5432.

Because pgBouncer uses md5 based authentication with postgreSQL 8.0 style files we need to create a users.txt file which will have the user credentials for the database users. A sample of this file is below
"test_user" "somepassword"
To run pgBouncer we run the command
$ pgbouncer -d pgbouncer.ini -R
Congratulations!! you now have a running master-slave replicated postgreSQL setup.

8) Failover (host2,bouncer-host)
In case of failover due to the master node failure. All that is needed to do is first make sure the master is truely down. Preferably, stopping the postgresql service if it is running. Then we just need to promote our slave to become the new master using the command:
$ repmgr -f /etc/repmgr/repmgr.conf standby promote
and then change the pgBouncer configuration to point to host2 and re run the command
$ pgbouncer -d pgbouncer.ini -R


Popular posts from this blog

rman interview and scenario based questions and answer and also rman(backup and recovery) discussions panel

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

PostgreSQL pgBadger