Thread: Having trouble configuring a Master with multiple standby Servers in PostgreSQL 9.3.3
<span style=3D"font-family:Verdana; color:#000000; font-size:10=
pt;">Trying to configure a master with two standby server vm's=
Postrgresql.conf - Master Server VMwal_level =3D hot_standby &=
nbsp; # minimal, archive, or hot_standb=
y &n=
bsp; =
; &n=
bsp; # (change requires restart)# - Archi=
ving -archive_mode =3D on # allows=
archiving to be done # (change requires restart)archive_co=
mmand =3D 'test ! -f /mnt/server/master_archivedir/%f && cp %p /mnt=
/server/master_archivedir/%f' # command to use to archive a logfile segment=
&n=
bsp; # placeholders: %p =3D path of file to archive#archive_timeout =3D=
0 # force a logfile segment switch af=
ter this =
&=
nbsp; # number of seconds; 0 disables#--------------------=
----------------------------------------------------------# REPLICATION=
#----------------------------------------------------------------------=
--------# - Sending Server(s) -# Set these on the master and on any=
standby that will send replication data.max_wal_senders =3D 3 &nb=
sp; # max number of walsender processes &=
nbsp; =
; # (change r=
equires restart)_______________________________________________________=
__________________________________________________Postrgresql.conf - Sl=
ave1 Server VMwal_level =3D hot_standby &nbs=
p; # minimal, archive, or hot_standby &nb=
sp; &nb=
sp; # (chang=
e requires restart)# - Archiving -archive_mode =3D on &n=
bsp; # allows archiving to be done =
&=
nbsp; # (change requires restart)arch=
ive_command =3D 'test ! -f /mnt/server/slave1_archivedir/%f && cp %=
p /mnt/server/slave1_archivedir/%f' # command to use to archive a logfile s=
egment &n=
bsp; # placeholders: %p =3D path of file to archive#archive_timeo=
ut =3D 0 # force a logfile segment swi=
tch after this =
&=
nbsp; # number of seconds; 0 disables#--------------------=
----------------------------------------------------------# REPLICATION=
#----------------------------------------------------------------------=
--------# - Sending Server(s) -# Set these on the master and on any=
standby that will send replication data.max_wal_senders =3D 3 &nb=
sp; # max number of walsender processes &=
nbsp; &nbs=
p; # (change requir=
es restart)# - Standby Servers -# These settings are ignored on a m=
aster server.hot_standby =3D on*** Completed this step, with 1.=
2.3.4 being the IP of slave1psql -c "select pg_start_backup('initia=
l_backup');"rsync -cvar --inplace --exclude=3D*pg_xlog* /u01/fiber/post=
greSQL_data/postgres@1.2.3.4:/u01/fiber/postgreSQL_data/psql -c " selec=
t pg_stop_backup ();"Now the issue is with the recovery.conf fil=
e on slave1, should the restore_command point to the archivelogs on the mas=
ter? Do I run the archive_cleanup_command when I recover slave1 or do I=
wait until I have finished backup/copy from the slave2standby_=
mode =3D 'on'primary_conninfo =3D 'host=3D<master database ip addres=
s> port=3D5432 dbname=3Dtumsdb user=3Dreplication password=3D<passwor=
d> application_name=3Dslave1 sslmode=3Drequire'restore_command =3D '=
cp /mnt/server/master_archivedir/%f "%p%"' <b=
><--- **** Is this correct! **** The master remains on-line and is produ=
cing archive logs. archive_cleanup_command =3D 'pg_archivecleanup /=
mnt/server/master_archivedir/ %r'trigger_file=3D '/opt/PostgreSQL/9.3/d=
ata/pgsql.trigger.file'________________________________________________=
______________________________________________________Postrgresql.conf =
- Slave2 Server VMwal_level =3D hot_standby =
# minimal, archive, or hot_standby =
&nb=
sp; #=
(change requires restart)# - Archiving -archive_mode =3D on &=
nbsp; # allows archiving to be done =
; &=
nbsp; # (change requires restart)<=
br>archive_command =3D 'test ! -f /mnt/server/slave2_archivedir/%f &&am=
p; cp %p /mnt/server/slave2_archivedir/%f' # command to use to archive a lo=
gfile segment &=
nbsp; # placeholders: %p =3D path of file to archive#archiv=
e_timeout =3D 0 # force a logfile segm=
ent switch after this =
=
# number of seconds; 0 disables#------------=
------------------------------------------------------------------# REP=
LICATION#--------------------------------------------------------------=
----------------# - Sending Server(s) -# Set these on the master an=
d on any standby that will send replication data.max_wal_senders =3D 3&=
nbsp; # max number of walsender processes<br=
> &nbs=
p; # (=
change requires restart)# - Standby Servers -# These settings are i=
gnored on a master server.hot_standby =3D on*** I HAVE NOT COMP=
LETED this step yet from the master, with 1.2.3.5 being the IP of slave2. T=
his takes about 3 days to finish the rsync copypsql -c "select pg_s=
tart_backup('initial_backup');"rsync -cvar --inplace --exclude=3D*pg_xl=
og* /u01/fiber/postgreSQL_data/postgres@1.2.3.5:/u01/fiber/postgreSQL_data/=
psql -c " select pg_stop_backup ();"Recov=
ery.conf file slave2standby_mode =3D 'on'primary_con=
ninfo =3D 'host=3D<master database ip address> port=3D5432 dbname=3Dt=
umsdb user=3Dreplication password=3D<password> application_name=3Dsla=
ve2 sslmode=3Drequire'restore_command =3D 'cp /mnt/server/slave2_archiv=
edir/%f "%p%"' <--- **** Is this correc=
t! **** The master remains on-line and is producing archive logs. a=
rchive_cleanup_command =3D 'pg_archivecleanup /mnt/server/slave2_archivedir=
/ %r'trigger_file=3D '/opt/PostgreSQL/9.3/data/pgsql.trigger.file'<=
br>These directories reside on a share accessible to all three VM's=
/mnt/server/master_archivedir/mnt/server/slave1_archivedir/mnt/serv=
er/slave2_archivedirThanks for any assistance. <b=
r>
Re: Having trouble configuring a Master with multiple standby Servers in PostgreSQL 9.3.3
From
Michael Paquier
Date:
TODO On Thu, Apr 17, 2014 at 1:29 AM, <fburgess@radiantblue.com> wrote: > Now the issue is with the recovery.conf file on slave1, should the > restore_command point to the archivelogs on the master? Yes, this is where archive_command of master copies the WAL files. You need them for recovery operations on slaves. > Do I run the archive_cleanup_command when I recover slave1 or do I wait > until I have finished backup/copy from the slave2 Be careful here, this command may remove WAL files that are needed by other slaves. For example, if slave1 kicks this command, you may remove files still needed by slave2 that has not yet done any recovery operation and it may need them. > postgresql.conf - Slave1 > restore_command = 'cp /mnt/server/master_archivedir/%f "%p%"' <--- **** > Is this correct! **** The master remains on-line and is producing archive > logs. No need to have that much complexity for %p: restore_command = 'cp -i /mnt/server/master_archivedir/%f %p' > postgresql.conf - Slave2 Server VM > restore_command = 'cp /mnt/server/slave2_archivedir/%f "%p%"' <--- **** > Is this correct! **** The master remains on-line and is producing archive > logs. Please see above, it could be more simple. -- Michael