Re: Having trouble configuring a Master with multiple standby Servers in PostgreSQL 9.3.3 - Mailing list pgsql-bugs

<span style=3D"font-family:Verdana; color:#000000; font-size:10=
pt;">Hi Michael, thanks for your reply.I dis=
cussed this my colleague, and we decided to change the archive_command to e=
xecute a shell script.#!/bin/bash# archive_co=
mmand script to replicate archivelogs to standby server slaves# # p=
ostgresql.conf parameter## archive_command =3D '<$PGDATA>/<a =
href=3D"http://replica_achive_set.sh">replica_achive_set.sh "%p" "%f"'<=
br>#set -eset -uARCHIVE1=3D"/mnt/server/slave1_archivedir"A=
RCHIVE2=3D"/mnt/server/slave2_archivedir"if [ -f ${ARCHIVE1}/$2 ] &=
& [ -f ${ARCHIVE2}/$2 ] ; then  echo Archive file $2 already e=
xists in one of the replicated sets archive, skipping >&2  =
exit 0fiechoerr() { echo "$@" 1>&2; }FAIL=3D0`/usr/b=
in/rsync -aq $1 ${ARCHIVE1}/$2` & pid_1=3D$! ; `/usr/bin/rsync -aq $1 $=
{ARCHIVE2}/$2` & pid_2=3D$!echoerr "Spawned replication processes $=
pid_1 AND $pid_2"wait $pid_1 || let "FAIL+=3D1"wait $pid_2 || let "=
FAIL+=3D1"if [ "$FAIL" =3D=3D "0" ];thenechoerr "Replication su=
ccess $1 $2"elseechoerr "Replication failed $1 $2"fi<=
div>This will copy the archivelogs from the master to both s=
laves. Will that avoid the issue with removing needed WAL files?</di=
v>I should be able to use these recovery.conf files</di=
v>slave #1<span style=3D"font=
-family:Verdana; color:#000000; font-size:10pt;">standby_mode =3D 'on'p=
rimary_conninfo=0A =3D 'host=3D<master database ip address> port=3D54=
32 dbname=3Dtumsdb =0Auser=3Dreplication password=3D<password> applic=
ation_name=3Dslave1 =0Asslmode=3Drequire'restore_command =3D 'cp </span=
>/mnt/s=
erver/slave1_archivedir/%f "%p%"'     archive_clean=
up_command =3D 'pg_archivecleanup <span style=3D"font-family:Verdana=
; color:#000000; font-size:10pt;">/mnt/server/slave1_archivedir/ %r'tri=
gger_file=3D '/opt/PostgreSQL/9.3/data/pgsql.trigger.file'<div=
>slaves #2<span sty=
le=3D"font-family:Verdana; color:#000000; font-size:10pt;">standby_mode =3D=
 'on'primary_conninfo=0A =3D 'host=3D<master database ip address>=
 port=3D5432 dbname=3Dtumsdb =0Auser=3Dreplication password=3D<password&=
gt; application_name=3Dslave2 =0Asslmode=3Drequire'restore_command =3D =
'cp <span style=3D"font-family:Verdana; color:#000000; font-size:10p=
t;">/mnt/server/slave2_archivedir/%f "%p%"'     =
archive_cleanup_command =3D 'pg_archivecleanup <span style=3D"fo=
nt-family:Verdana; color:#000000; font-size:10pt;">/mnt/server/slave2_archi=
vedir/ %r'trigger_file=3D '/opt/PostgreSQL/9.3/data/pgsql.trigger.file'=
<span style=3D"font-family:Verdana; color:#000000; font-s=
ize:10pt;">Does this look correct?<=
br>Finally, question about the backup.=
I did a pg_clt reload to change the archivelog destination from /mnt/server=
/master_archivedir to be redistributed to slave1 and slave2. Do I need t=
o redo this backup step? psql -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 ();"<span style=3D"font-=
family:Verdana; color:#000000; font-size:10pt;"><span=
 style=3D"font-family:Verdana; color:#000000; font-size:10pt;">or can I jus=
t copy all of the missing archivelog files from the /mnt/server/master_arch=
ivedir to the slaves, and then restart the slaves in recovery mode?<=
/div><span style=3D"font-family:Verdana; color:#000000; font-size:10pt=
;"><span style=3D"font-family:Verdana; color:#000000;=
 font-size:10pt;">thanks=0A<blockquote id=3D"re=
plyBlockquote" webmail=3D"1" style=3D"border-left: 2px solid blue; margin-l=
eft: 8px; padding-left: 8px; font-size:10pt; color:black; font-family:verda=
na;">=0A=0A-------- Original Message --------<br=
>=0ASubject: Re: [BUGS] Having trouble configuring a Master with multiple<b=
r>=0Astandby Servers in PostgreSQL 9.3.3=0AFrom: Michael Paquier <<a=
 href=3D"mailto:michael.paquier@gmail.com">michael.paquier@gmail.com>=
;=0ADate: Wed, April 16, 2014 6:07 pm=0ATo: <a href=3D"mailto:fburg=
ess@radiantblue.com">fburgess@radiantblue.com=0ACc: <a href=3D"mail=
to:pgsql-bugs@postgresql.org">pgsql-bugs@postgresql.org=0A=0A<d=
iv dir=3D"ltr">TODOOn Thu, Apr 17, 2014 at 1:29 AM,  <=
fburgess@radi=
antblue.com> wrote:> Now the issue is with the recovery.conf =
file on slave1, should the > restore_command point to the archivelog=
s 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 &=
gt; until I have finished backup/copy from the slave2Be careful here, t=
his command may remove WAL files that are needed by other slaves. For examp=
le, if slave1 kicks this command, you may remove files still needed by slav=
e2 that has not yet done any recovery operation and it may need them. <=
br>> postgresql.conf - Slave1> restore_command =3D 'cp /mnt/serve=
r/master_archivedir/%f "%p%"'      <--- ****> Is t=
his correct! **** The master remains on-line and is producing archive &=
gt; logs.No need to have that much complexity for %p:restore_comman=
d =3D 'cp -i /mnt/server/master_archivedir/%f %p'<b=
r>> postgresql.conf - Slave2 Server VM> restore_command =3D '=
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.<=
br>-- Michael =0A=0A<=
/html>