Re: Having trouble configuring a Master with multiple standby Servers in PostgreSQL 9.3.3

On Fri, Apr 18, 2014 at 1:19 AM, <fburgess(at)radiantblue(dot)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(at)1(dot)2(dot)3(dot)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