----------------------------------------------------------
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;
************************************************** *******************************************