<html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>Hi Michael, thanks for your reply.</div><div><br></div><div>I discussed this my colleague, and we decided to change the archive_command to execute a shell script.</div><div><br></div><div>#!/bin/bash<br># archive_command script to replicate archivelogs to standby server slaves<br># <br># postgresql.conf parameter<br>#<br># archive_command = '<$PGDATA>/<a href="http://replica_achive_set.sh">replica_achive_set.sh</a> "%p" "%f"'<br>#<br>set -e<br>set -u<br>ARCHIVE1="/mnt/server/slave1_archivedir"<br>ARCHIVE2="/mnt/server/slave2_archivedir"<br>if [ -f ${ARCHIVE1}/$2 ] && [ -f ${ARCHIVE2}/$2 ] ; then<br> echo Archive file $2 already exists in one of the replicated sets archive, skipping >&2<br> exit 0<br>fi<br>echoerr() { echo "$@" 1>&2; }<br>FAIL=0<br>`/usr/bin/rsync -aq $1 ${ARCHIVE1}/$2` & pid_1=$! ; `/usr/bin/rsync -aq $1 ${ARCHIVE2}/$2` & pid_2=$!<br>echoerr "Spawned replication processes $pid_1 AND $pid_2"<br>wait $pid_1 || let "FAIL+=1"<br>wait $pid_2 || let "FAIL+=1"<br>if [ "$FAIL" == "0" ];<br>then<br>echoerr "Replication success $1 $2"<br>else<br>echoerr "Replication failed $1 $2"<br>fi<br></div><div><br></div><div>This will copy the archivelogs from the master to both slaves. <b>Will that avoid the issue with removing needed WAL files?</b></div><div><br></div><div>I should be able to use these recovery.conf files</div><div><br></div><div>slave #1</div><div><br></div><div><span style="font-family:Verdana; color:#000000; font-size:10pt;">standby_mode = 'on'<br>primary_conninfo
= 'host=<master database ip address> port=5432 dbname=tumsdb
user=replication password=<password> application_name=slave1
sslmode=require'<br>restore_command = 'cp </span><span style="font-family:Verdana; color:#000000; font-size:10pt;">/mnt/server/slave1_archivedir/%f "%p%"' <br>archive_cleanup_command = 'pg_archivecleanup </span><span style="font-family:Verdana; color:#000000; font-size:10pt;">/mnt/server/slave1_archivedir/ %r'<br>trigger_file= '/opt/PostgreSQL/9.3/data/pgsql.trigger.file'</span></div><div><br></div><div><br></div><div>slaves #2</div><div><br></div><div><span style="font-family:Verdana; color:#000000; font-size:10pt;">standby_mode = 'on'<br>primary_conninfo
= 'host=<master database ip address> port=5432 dbname=tumsdb
user=replication password=<password> application_name=slave2
sslmode=require'<br>restore_command = 'cp </span><span style="font-family:Verdana; color:#000000; font-size:10pt;">/mnt/server/slave2_archivedir/%f "%p%"' <b></b><br>archive_cleanup_command = 'pg_archivecleanup </span><span style="font-family:Verdana; color:#000000; font-size:10pt;">/mnt/server/slave2_archivedir/ %r'<br>trigger_file= '/opt/PostgreSQL/9.3/data/pgsql.trigger.file'</span></div><div><span style="font-family:Verdana; color:#000000; font-size:10pt;"><br></span></div><div><b>Does this look correct?</b></div><div><br></div><div>Finally, question about the backup.</div><div><br></div><div>I did a pg_clt reload to change the archivelog destination from /mnt/server/master_archivedir to be redistributed to slave1 and slave2.<b> Do I need to redo this backup step? </b></div><div><br></div><div>psql -c "select pg_start_backup('initial_backup');"<br>rsync -cvar --inplace --exclude=*pg_xlog* /u01/fiber/postgreSQL_data/postgres(at)1(dot)2(dot)3(dot)5:/u01/fiber/postgreSQL_data/<br>psql -c " select pg_stop_backup ();"<br></div><div><span style="font-family:Verdana; color:#000000; font-size:10pt;"><br></span></div><div><span style="font-family:Verdana; color:#000000; font-size:10pt;">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?</span></div><div><span style="font-family:Verdana; color:#000000; font-size:10pt;"><br></span></div><div><span style="font-family:Verdana; color:#000000; font-size:10pt;">thanks</span></div><div><br></div>
<blockquote id="replyBlockquote" webmail="1" style="border-left: 2px solid blue; margin-left: 8px; padding-left: 8px; font-size:10pt; color:black; font-family:verdana;">
<div id="wmQuoteWrapper">
-------- Original Message --------<br>
Subject: Re: [BUGS] Having trouble configuring a Master with multiple<br>
standby Servers in PostgreSQL 9.3.3<br>
From: Michael Paquier <<a href="mailto:michael(dot)paquier(at)gmail(dot)com">michael(dot)paquier(at)gmail(dot)com</a>><br>
Date: Wed, April 16, 2014 6:07 pm<br>
To: <a href="mailto:fburgess(at)radiantblue(dot)com">fburgess(at)radiantblue(dot)com</a><br>
Cc: <a href="mailto:pgsql-bugs(at)postgresql(dot)org">pgsql-bugs(at)postgresql(dot)org</a><br>
<br>
<div dir="ltr"><div>TODO<br><br>On Thu, Apr 17, 2014 at 1:29 AM, <<a target="_blank" href="mailto:fburgess(at)radiantblue(dot)com">fburgess(at)radiantblue(dot)com</a>> wrote:<br>> Now the issue is with the recovery.conf file on slave1, should the<br> > restore_command point to the archivelogs on the master?<br>Yes, this is where archive_command of master copies the WAL files. You need them for recovery operations on slaves.<br><br>> Do I run the archive_cleanup_command when I recover slave1 or do I wait<br> > until I have finished backup/copy from the slave2<br>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.<br> <br>> postgresql.conf - Slave1<br>> restore_command = 'cp /mnt/server/master_archivedir/%f "%p%"' <--- ****<br>> Is this correct! **** The master remains on-line and is producing archive<br> > logs.<br>No need to have that much complexity for %p:<br>restore_command <span class=""></span>= 'cp -i /mnt/server/master_archivedir/%f %p'<br><br>> postgresql.conf - Slave2 Server VM<br>> restore_command = 'cp /mnt/server/slave2_archivedir/%f "%p%"' <--- ****<br> > Is this correct! **** The master remains on-line and is producing archive<br>> logs.<br></div>Please see above, it could be more simple.<br><div>-- <br>Michael</div></div>
</div>
</blockquote></span></body></html>