473,324 Members | 2,400 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

How to create replication of multiple clusters?

----------------------------------------------------------
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
0 2202

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

Similar topics

2
by: Craig Keightley | last post by:
I need to create a form using multiple quantities that can be updated by one single update. eg: (HTML PAGE) <form method="post" enctype = "multipart/formdata"> <? do { ?> <?php echo...
1
by: John | last post by:
Hi I have create replication between a laptop and the server. Now I need to create replication for another laptop. Do I create another replica for the second laptop or copy the one from the...
1
by: KemperR | last post by:
Dear All, I want to achieve that the combination of values over multiple columns in a table are unique. I know this works for SQL Server, but how do I declare this in an ACCESS 2000 data base...
1
by: michelle | last post by:
Hi, I'm brand new to Crystal Reports and have just gotten my feet wet with VS.Net. I have a web application in ASP.NET 2.0 (VB) that writes to a SQL Server 2005 database. I need to utilize...
2
by: angie | last post by:
I need to figure out how to create a user interface to search a query, but here's the bad part...I need to account for criteria on at least 7 of the fields. Here's what I'm thinking I need to do:...
3
by: Jay Ruyle | last post by:
I'm trying to figure out a way to list several items in a listbox and let the user select any number of items in the listbox. I have tried to code in the items as bitwise items but all it stores...
0
by: Curt | last post by:
I am planning a replication project where we will be rolling up data from several (about 5) offices into a central database that will be used for management reporting. The tables I want to...
1
by: pankajit09 | last post by:
Hello , I want to create multiple sheets(and pass data to them) in a Excel file using Perl. I can create a single Excel sheet but how to create multiple worksheets in a single Excel file.
1
by: Faith0999 | last post by:
I am currently a novice to using MS-Access. I currently have a report that I created from the following query. SELECT item.id, item.type, item.location, item.shipdate FROM Item WHERE item.type...
3
vyon13
by: vyon13 | last post by:
hello... i have a problem regarding to my proj that i was working... this is the prob.. in my application form there are so many categories to be fill up... i realize that if i will put all the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.