Wednesday, 24 January 2018

How To Set Up Master Slave Streaming Replication on PostgreSQL

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.

There are many ways to set up replication on a postgres system. In this tutorial, we will cover how to configure replication using a hot standby.


The master/slave database replication is a process of copying (syncing) data from a database on one server (the master) to a database on another server (the slaves). The main benefit of this process is to distribute databases to multiple machines, so when the master server has a problem, there is a backup machine with same data available for handling requests without interruption.

Streaming Replication (SR) provides the capability to continuously ship and apply the WAL XLOG records to some number of standby servers in order to keep them current.

Prerequisites: 

  1.  Install postgres in the primary and standby server as usual. 

         Master Server IP  : 192.168.47.181
         Slave Server IP  : 192.168.202.128

Configure Master-server (server1)


We will create a new user/role with special permission to perform the replication, then we edit the PostgreSQL configuration file to enable the hot standby replication mode.

1. Setting ssh connectivity :

 Generate an ssh key for the postgres user:

su - postgres

ssh-keygen -t rsa

Generating public/private rsa key pair.
Enter file in which to save the key (/var/lib/pgsql/.ssh/id_rsa): 
Created directory '/var/lib/pgsql/.ssh'.
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /var/lib/pgsql/.ssh/id_rsa.
Your public key has been saved in /var/lib/pgsql/.ssh/id_rsa.pub.
The key fingerprint is:
28:2a:e1:06:8a:16:6d:ac:2c:74:5c:aa:3e:25:d3:a9 postgres@server22
The key's randomart image is:
+--[ RSA 2048]----+
|                 |
|                 |
|     .           |
|  + o  .         |
|oo.Bo . S        |
|B+*= .           |
|=B*              |
|=E               |
| ..              |
-------------------

 Transfer the keys to the other server

The public key can now be traced to the link ~/.ssh/id_rsa.pub
cat .ssh/id_rsa
id_rsa      id_rsa.pub  

cat .ssh/id_rsa.pub | ssh postgres@server2 'cat >> .ssh/authorized_keys'

 You should now be able to ssh freely between your two servers as the postgres user.

2. Create Directory for archive folder :

cd /var/lib/pgsql/9.4/

mkdir archive

chown -R postgres:postgres archive

3. Replication User set up :

 Access the Postgres shell with the psql command and type in this PostgreSQL query to create the new user/role:

CREATE USER replication REPLICATION LOGIN ENCRYPTED PASSWORD 'password';

postgres=# \du
                              List of roles
  Role name  |                   Attributes                   | Member of
-------------+------------------------------------------------+-----------
 nagios      | Superuser                                      | {}
 postgres    | Superuser, Create role, Create DB, Replication | {}
 replication | Replication                                    | {}
 senthil     |                                                | {}
 test        |                                                | {}

4. Configuration Setup :

 Next, go to the PostgreSQL directory  to edit the configuration file.

vi /var/lib/pgsql/9.4/data/postgresql.conf

Modify parameters as below :

listerner address ='*' 
wal_level = hot_standby
max_wal sender = 5
wal_keep_segment =16
hot_standby =on
archive_mode =on
archive_command = 'scp %p postgres@server2:/var/lib/pgsql/9.4/archive/%f  &&  cp %p /var/lib/pgsql/9.4/archive/%f' 

Set up connections and authentication on the primary so that the standby server can successfully connect to the replication pseudo-database on the primary.

vi /var/lib/pgsql/9.4/data/pg_hba.conf

Edit pg_hba.conf file to allow the replication connection.

In the end of the line, add a new configuration for user'replication' to make connection.

#local   replication     postgres                                peer
host    replication     postgres        192.168.202.128/30       md5

Restart PostgreSQL Service:

systemctl restart postgresql-9.4.service

5. Verify the Configurations (example ) :

-bash-4.2$ psql
psql (9.4.10)
Type "help" for help.

postgres=# show wal_level ;
 wal_level 
-----------
 archive
(1 row)

postgres=# show  archive_command ;
                    archive_command                     
--------------------------------------------------------
 scp %p postgres@server2:/var/lib/pgsql/9.4/archive/%f  &&  cp %p /var/lib/pgsql/9.4/archive/%f

(1 row)

postgres=# show archive_mode ;
 archive_mode 
--------------
 on
(1 row)

6. Make a base backup by copying the primary server's data directory to the standby server :


psql # select pg_start_backup('repl') ;
psql # \q

$ cd /var/lib/pgsql/9.4/

$ tar -cvf data_old.tar data 

psql # select pg_stop_backup();


Either you can use tar and scp the file or use rsync for copying the data 


$ scp data_old.tar postgres@server2:/var/lib/pgsql/9.4/


$ rsync -ac ${PGDATA}/ postgres@server2:/var/lib/pgsql/9.4/ --exclude postmaster.pid


Slave Server Configuration (server2): 

Set up replication-related parameters, connections and authentication in the standby server like the primary, so that the standby might work as a primary after failover.

The prerequisite is that you make sure the standby's data directory is empty.


systemctl stop postgresql-9.4.service


$ mv data data.old
$ tar -xvf data_old.tar 

7. Configure the slave server like the master server.Edit PostgreSQL config file 

listerner address ='*' 
wal_level = hot_standby
max_wal sender = 5
wal_keep_segment =16
hot_standby =on
archive_mode =on

8. Create a recovery command file in the standby server; the following parameters are required for streaming replication.


$ cd data 


vi recovery.conf

$ $EDITOR recovery.conf
# Note that recovery.conf must be in $PGDATA directory.
# It should NOT be located in the same directory as postgresql.conf

# Specifies whether to start the server as a standby. In streaming replication,
# this parameter must to be set to on.
standby_mode          = 'on'

# Specifies a connection string which is used for the standby server to connect
# with the primary.
primary_conninfo      = 'user=postgres host=192.168.47.181 port=5432'
# Specifies a trigger file whose presence should cause streaming replication to
# end (i.e., failover).
trigger_file = '/tmp/failover'

# Specifies a command to load archive segments from the WAL archive. If
# wal_keep_segments is a high enough number to retain the WAL segments
# required for the standby server, this may not be necessary. But
# a large workload can cause segments to be recycled before the standby
# is fully synchronized, requiring you to start again from a new base backup.
restore_command = ' cp /var/lib/pgsql/9.4/archive/%f “%p”'

Remove old PID file of exists  


$ rm postmaster.pid

Start postgres in the standby server. It will start streaming replication.


systemctl start postgresql-9.4.service

You can calculate the replication lag by comparing the current WAL write location on the primary with the last WAL location received/replayed by the standby. 
They can be retrieved using pg_current_xlog_location on the primary and  the pg_last_xlog_receive_location/pg_last_xlog_replay_location on the pg_last_xlog_receive_location/pg_last_xlog_replay_location on the standby, respectively.


psql# select pg_is_in_recovery() ;

Note :  This should be true for Standby 

psql# select now() - pg_last_xact_replay_timestamp() ;

psql#  pg_last_xlog_receive_location() ; select pg_last_xlog_replay_location() ;

pg_last_xlog_receive_location 
-------------------------------
 0/2000000
(1 row)
 pg_last_xlog_replay_location 
------------------------------
 0/2000000
(1 row)

In Master Server :


psql# select * from pg_stat_replication ;

psql# SELECT pg_current_xlog_location() ;

 pg_last_xlog_replay_location 
------------------------------
 0/2000000
(1 row)

You can also check the progress of streaming replication by using ps command.


# The displayed LSNs indicate the byte position that the standby server has
# written up to in the xlogs.
[primary] $ ps -ef | grep sender
postgres  6879  6831  0 10:31 ?        00:00:00 postgres: wal sender process postgres 127.0.0.1(44663) streaming 0/2000000

[standby] $ ps -ef | grep receiver
postgres  6878  6872  1 10:31 ?        00:00:01 postgres: wal receiver process   streaming 0/2000000


Questions: 

  • How to do failover

Create the trigger file in the standby after the primary fails.

  • How to stop the primary or the standby server

Shut down it as usual (pg_ctl stop).

  • How to restart streaming replication after failover

Repeat the operations from 6th; making a fresh backup, some configurations and starting the original primary as the standby. The primary server doesn't need to be stopped during these operations.

  • How to restart streaming replication after the standby fails

Restart postgres in the standby server after eliminating the cause of failure.

  • How to disconnect the standby from the primary

Create the trigger file in the standby while the primary is running. Then the standby would be brought up.

  • How to re-synchronize the stand-alone standby after isolation

Shut down the standby as usual. And repeat the operations from 6th.

If you have more than one slave, promoting one will break the other(s). Update their recovery.conf settings to point to the new master, set recovery_target_timeline to 'latest', scp/rsync the pg_xlog directory, and restart the slave.






0 comments:

Post a Comment