Sunday 12 February 2017

Postgresql-9.6.1 Database Streamline Replication from Master to Slave

Postgresql-9.6.1 Database Streamline Replication from Master to Slave:
=====================================================
I have two VM's with Centos 7 and Ip's are as follows::

Master :  192.168.1.1
Slave   :  192.168.1.2

Master Configuration::

Create user postgres 
Download postgresql-9.6.1.tar.gz and untar it

# ./configure --prefix=/home/postgres/pgsql
# make
# make install


Initialize new database:

#  /home/postgres/pgsql/bin/initdb -D /home/postgres/pgsql/data
# /home/postgres/pgsql/bin/pg_ctl -D /home/postgres/pgsql/data/ -l logfile start
# /home/postgres/pgsql/bin/pg_ctl -D /home/postgres/pgsql/data/ -l logfile stop
Edit pg_hba.conf

Add entries for host ips to connect and the replications slave details as follows::

# host            all                                   all                          192.168.1.0/24                                        trust


Edit postgresql.conf
listen_addresses = '*'
wal_level = hot_standby
checkpoint_segments = 8
archive_mode = on
max_wal_senders = 3
wal_keep_segments = 8

# /home/postgres/pgsql/bin/psql -U 

Create replication user on master::

postgres=# CREATE ROLE rep WITH REPLICATION PASSWORD '********' LOGIN





Client Configuration ::

Create user postgres 

Download postgresql-9.6.1.tar.gz and untar it

# ./configure --prefix=/home/postgres/pgsql
# make
# make install

Initialize new database:

#  /home/postgres/pgsql/bin/initdb -D /home/postgres/pgsql/data

Rename the main directory in the slave as follows:
mv /home/postgres/pgsql/data  /home/postgres/pgsql/data.backup

pg_basebackup -h 192.168.1.1 -D /home/postgres/pgsql/data -U rep -v -P



Edit pg_hba.conf

Add entries for host ips to connect and the replications slave details as follows::

# host            all                                   all                          192.168.1.0/24                                        trust
# host            replication                    rep                        192.168.1.2/24                                        trust


Now create recovery.conf and add the entries as follows in slave


vi /home/postgres/pgsql/data/recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=192.168.1.1 port=5432 user=rep password=password'
trigger_file = '/tmp/postgresql.trigger.5432'
 
or  
standby_mode = 'on'
primary_conninfo = 'host=192.168.240.41 port=5432 user=rep'
trigger_file = '/tmp/postgresql.trigger.5432'
and create .pgpass file under the home directory of the postgres user as follows 
192.168.240.41:5432:*:rep:123456
 
Now configure pgadmin on your desktop and create tables on the master database server. 
It should be automatically replicated to the slave server.

No comments:

Post a Comment