<html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>
Management requires that we implement a master server with two standby database servers. Is it recommended we keep the archives logs in separate directories in each VM, or that we have one directory on a shared drive that each of the three VM's can access? We would like to leverage the archive_cleanup_command = ‘pg_archivecleanup /sharedata/postgres_archive_master %r’ in our recovery.conf configuration on both standby servers.<br><br>-------- Original Message --------</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">
Subject: Re: [BUGS] Configuring Standby Server in PostgreSQL 9.3.3<br>
From: <<a href="mailto:fburgess(at)radiantblue(dot)com">fburgess(at)radiantblue(dot)com</a>><br>
Date: Tue, April 08, 2014 12:34 pm<br>
To: "Jeff Frost" <<a href="mailto:jeff(at)pgexperts(dot)com">jeff(at)pgexperts(dot)com</a>>, "Heikki Linnakangas"<br>
<<a href="mailto:hlinnakangas(at)vmware(dot)com">hlinnakangas(at)vmware(dot)com</a>><br>
Cc: "Michele" <<a href="mailto:michele(dot)mariani(at)databtech(dot)com">michele(dot)mariani(at)databtech(dot)com</a>>, <a href="mailto:pgsql-bugs(at)postgresql(dot)org">pgsql-bugs(at)postgresql(dot)org</a><br>
<br>
<span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>Our standby server sits on a separate VM,. Should archive_mode = on be set only on the master and not the slave? My colleague is convinced that the archive log directory should sit on a shared drive that is accessible to both VM's (master and Slave)</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] Configuring Standby Server in PostgreSQL 9.3.3<br> From: Jeff Frost <<a target="_blank" href="mailto:jeff(at)pgexperts(dot)com">jeff(at)pgexperts(dot)com</a>><br> Date: Tue, April 08, 2014 9:39 am<br> To: Heikki Linnakangas <<a target="_blank" href="mailto:hlinnakangas(at)vmware(dot)com">hlinnakangas(at)vmware(dot)com</a>><br> Cc: <a target="_blank" href="mailto:fburgess(at)radiantblue(dot)com">fburgess(at)radiantblue(dot)com</a>, Michele <<a target="_blank" href="mailto:michele(dot)mariani(at)databtech(dot)com">michele(dot)mariani(at)databtech(dot)com</a>>,<br> <a target="_blank" href="mailto:pgsql-bugs(at)postgresql(dot)org">pgsql-bugs(at)postgresql(dot)org</a><br> <br> <br> On Apr 8, 2014, at 3:16 AM, Heikki Linnakangas <<a target="_blank" href="mailto:hlinnakangas(at)vmware(dot)com">hlinnakangas(at)vmware(dot)com</a>> wrote:<br> <br> > On 04/08/2014 01:25 AM, <a target="_blank" href="mailto:fburgess(at)radiantblue(dot)com">fburgess(at)radiantblue(dot)com</a> wrote:<br> >> Did you guys benchmark the basebackup utility? The master database will have to<br> >> remain online during this backup/restore process, since it is receiving real<br> >> time data feeds. Not sure which technique is better for our 7TB db.<br> >> <br> >> 1.) Running from the slave. 1.2.3.4 is the ip of master database.<br> >> <br> >> basebackup -D /u01/fiber/postgreSQL_data -F p -x stream -c fast -P -v -h 1.2.3.4<br> >> -p 5432 -U replication<br> >> <br> >> 2.) compared to ...<br> >> <br> >> psql -c "select pg_start_backup('initial_backup');"<br> >> rsync -cva --inplace --exclude=*pg_xlog* /u01/fiber/postgreSQL_data/<br> >> postgres(at)1(dot)2(dot)3(dot)4:/u01/fiber/postgreSQL_data/<br> >> psql -c " select pg_stop_backup () ;"<br> >> <br> >> 3.) or this ...<br> >> <br> >> psql –c “select pg_start_backup(‘hot backup’)”<br> >> cp –pr /u01/fiber/postgreSQL_data 1.2.3.4:/u01/fiber/postgreSQL_data<br> >> psql –c “select pg_stop_backup(‘hot backup’)”<br> > <br> > I bet the rsync or cp method is faster. Dunno how much, though, that depends on what the bottleneck is; the network, or the disk, or something else. You'll have to measure it yourself, to know how it is in your environment.<br> > - Heikki<br> <br> FWIW, I benchmarked a few methods here:<br> <br> <a target="_blank" href="http://frosty-postgres.blogspot.com/2011/12/postgresql-base-backup-benchmark.html">http://frosty-postgres.blogspot.com/2011/12/postgresql-base-backup-benchmark.html</a><br> <br> admittedly, the base backup was much smaller, but I suspect you would see similar performance with a larger base backup.<br> <br> You might also try running the rsync once, before issuing pg_start_backup() and then doing a normal base backup so that rsync can take advantage of partial file copy.<br> <br> If you use rsync over ssh, make sure to use the arcfour cipher for best performance.<br> <br> </div> </blockquote></span>
</div>
</blockquote></span></body></html>