Обсуждение: Re: 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;">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>

On Fri, Apr 18, 2014 at 1:19 AM,  <fburgess@radiantblue.com> wrote:
> Hi Michael, thanks for your reply.
>
> I discussed this my colleague, and we decided to change the archive_command
> to execute a shell script.
That's wiser as it allows more flexibility.

> This will copy the archivelogs from the master to both slaves. Will that
> avoid the issue with removing needed WAL files?
> slave 1
> archive_cleanup_command = 'pg_archivecleanup /mnt/server/slave1_archivedir/
> %r'
> slaves #2
> archive_cleanup_command = 'pg_archivecleanup /mnt/server/slave2_archivedir/
> %r'
> Does this look correct?
Looks fine. You are copying each WAL file to a different archive
folder, and pg_archivecleanup will clean only the path it uses for
each folder, so there is no risk to have a WAL file removed by one
slave and needed by the other.

> 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 to redo this backup step?
Not if the slaves have already fetched necessary WAL files from the
single master archive before you changed the command.

> psql -c "select pg_start_backup('initial_backup');"
> rsync -cvar --inplace --exclude=*pg_xlog*
> /u01/fiber/postgreSQL_data/postgres@1.2.3.5:/u01/fiber/postgreSQL_data/
> psql -c " select pg_stop_backup ();"
>
> or can I just copy all of the missing archivelog files from the
> /mnt/server/master_archivedir to the slaves, and then restart the slaves in
> recovery mode?
Taking a new base backup will be fine. But you actually do not need to
do so if your slaves have already caught up enough. Your slaves are
using streaming replication and are on the same server as the master
AFAIU so they should be fine, but there is always a possibility that
they need some WAL from archives if one of them for example was not
able to connect to the master for a long time and master already
dropped the necessary WAL files from its pg_xlog.
--
Michael