By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,982 Members | 1,919 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,982 IT Pros & Developers. It's quick & easy.

How to create replication of multiple clusters?

P: 1
----------------------------------------------------------
HOT STANDBY OF MULTIPLE CLUSTERS - POSTGRESQL
----------------------------------------------------------


################################################## ###################################

This document contain steps to create hot standby of multiple clusters in postgres.

MACHINE1: MASTER 10.0.2.15 ------------------ Cluster1@10.0.2.15:5432
------------------ Cluster2@10.0.2.15:5433


MACHINE2: SLAVE 10.0.2.4 ------------------ ReplicatedCluster@10.0.2.4:5432
------------------ ReplicatedCluster@10.0.2.4:5433

################################################## ###################################







1. Set up two machines. (Master and Slave)
Do the necessary configuration of both the machines so that they can ping each other.

>eth0 interface settings for both the machines.
>Check they are able to ping each other.


2. Install postgresql-9.4 in both the machines.

>rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-1.noarch.rpm
>yum update
>yum install postgresql94-server postgresql94-contrib
>service postgresql-9.4 initdb
>service postgresql-9.4 start
>service postgresql-9.4 on
>chkconfig postgresql-9.4 on


3.
>su postgres

Create a database mydb(name anything) on master.

bash-4.1$ create database mydb;
DATABASE CREATED

Create user, tables and populate them for testing purpose.



4. In MASTER server, change the following paramters in postgresql.conf:

>vim /var/lib/pgsql/9.3/data/postgresql.conf

wal_level = hot_standby
max_wal_senders = 1 (number of slave servers. can be any depending on req))
wal_keep_segments = 100


Save the configuration.

>su postgres
$psql
>create user replica replication;
>\du+ (to see user replica is created or not)
>exit

>/etc/init.d/postgresql-9.4 restart

Then change in file pg_hba.conf

>vi /var/lib/pgsql/9.3/data/pg_hba.conf


Make an entry under localhosts:

host replication all 10.0.2.4(IP of SLAVE)/32 trust


Save and Exit.


5. On SLAVE server
Stop the postgresql service -> service postgresql-9.4 stop

>cd /var/lib/pgsql/9.4/data/
>rm -rf * #this will remove all the files from data folder
>su postgres
>pg_basebackup -D /var/lib/pgsql/9.4/data -h 10.0.2.15(IP of MASTER) -U replica
>ls #You can see the all data from MASTER is replicated on SLAVE

Create a recovery file in /data
>touch recovery.conf
>vi recovery.conf


Add the following lines:

standby_mode=on
trigger_file='/tmp/promotedb'
primary_conninfo='host=10.0.2.15 port=5432 user=replica'
mkdir


Edit postgresql.conf of SLAVE and locate:

hot_standby=on

And run:
$ /usr/pgsql-9.4/bin/pg_ctl -D /var/lib/pgsql/9.4/data/ start
> /etc/init.d/postgresql-9.4 start

Now, all data inserted on the master server will be updated to slave.

To Check:::
ON MASTER
>ps aux | grep postgresql

...You can see the Wal Sender Process...


ON SLAVE
>ps aux | grep postgresql

...You can see the Wal Reciever Process...




------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
TEST whether changes are reflecting in SLAVE DB or NOT
------------------------------------------------------------------------------------
Connect to database in MASTER server

=#\l (to list available databases in MASTER)
=#\d (to list available tables in MASTER)

Create new table in MASTER.


Connect to database in SLAVE server.

=#\d (to check tables in SLAVE database. New table created in MASTER will be there)


Similarly, create new databases in MASTER and check replication of same in SLAVE.

------------------------------------------------------------------------------------






-------------------------------------------------------------------------
-------------------------------------------------------------------------
CREATING A NEW DATABASE INSTANCE IN MASTER
-------------------------------------------------------------------------
-------------------------------------------------------------------------


1. Create a new database directory for second instance. Make postgres as owner.

>mkdir /var/lib/pgsql/9.4/data2
>chown postgres.postgres /var/lib/pgsql/9.4/data2

2. Create new init script for new instance and rename it to postgresql2-9.4

>cp /etc/init.d/postgresql-9.4 /etc/init.d/postgresql2-9.4
>vi /etc/init.d/postgresql2-9.4

Modify the following lines in postgresql2-9.4

PGENGINE=/usr/pgsql-9.4/bin
PGDATA=/var/lib/pgsql/9.4/data2
PGLOG=/var/lib/pgsql/9.4/pgstartup2.log
PGUPLOG=/var/lib/pgsql/$PGMAJORVERSION/pgupgrade2.log

pidfile="/var/run/postmaster2-9.4.pid"


3. Initialize and start new instance

>service postgresql2-9.4 initdb
>service postgresql2-9.4 start

4. startup will fail. Now change in

>vi /var/lib/pgsql/9.2/data2/postgresql.conf

listen_addresses = '*'

port = 5433

SAVE and now start.


5. Try to connect to new instance.


>su postgres
>psql -p5433 -dpostgres

6. add it to server startup list

>chkconfig --add postgresql2-9.4
>chkconfig postgresql2-9.4 on


--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
REPLICATION OF THE SECOND(NEW) CLUSTER ON SLAVE SERVER
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
1. Create a directory for new cluster.

>mkdir /var/lib/pgsql/9.4/data2
>chown postgres.postgres /var/lib/pgsql/9.4/data2

2. Create new init script for new instance and rename it to postgresql2-9.4

>cp /etc/init.d/postgresql-9.4 /etc/init.d/postgresql2-9.4
>vi /etc/init.d/postgresql2-9.4

Modify the following lines in postgresql2-9.4

PGENGINE=/usr/pgsql-9.4/bin
PGDATA=/var/lib/pgsql/9.4/data2
PGLOG=/var/lib/pgsql/9.4/pgstartup2.log
PGUPLOG=/var/lib/pgsql/$PGMAJORVERSION/pgupgrade2.log



1. Create data2 in SLAVE.
2. #su postgres
$ pg_basebackup -D /var/lib/pgsql/9.4/data2 -h 10.0.2.15(MASTER IP) -p 5433 --xlog-method=stream

3. Now check in data2, files from MASTER data2 copied.
4. copy recovery.conf file from data.
#cp /var/lib/pgsql/9.4/data/recovery.conf /var/lib/pgsql/9.4/data2


Edit in data2/recovery.conf and Add :

standby_mode=on
trigger_file='/tmp/promotedb'
primary_conninfo='host=10.0.2.15 port=5433 application_name=abc'



5. Edit in pg_hba.conf

host replication all 10.0.2.15 trust

6. Edit postgresql.conf change only

comment #max_wal_senders=2
Make hot_standby=on

7. chown postgres.postgres recovery.conf (give postgres owner permissions to recovery.conf file).

8. Service postgresql2-9.4 start


DONE!!!!!!!!

IN MASTER check whether streaming is performing for both the clusters or not.

#su postgres
$psql -p 5433 -d postgres
postgres=# SELECT * from pg_stat_replication;

************************************************** *******************************************
May 5 '16 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.