<html><body><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 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 href="mailto:hlinnakangas(at)vmware(dot)com">hlinnakangas(at)vmware(dot)com</a>><br>
Cc: <a href="mailto:fburgess(at)radiantblue(dot)com">fburgess(at)radiantblue(dot)com</a>, Michele <<a href="mailto:michele(dot)mariani(at)databtech(dot)com">michele(dot)mariani(at)databtech(dot)com</a>>,<br>
<a 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 href="mailto:hlinnakangas(at)vmware(dot)com">hlinnakangas(at)vmware(dot)com</a>> wrote:<br>
<br>
> On 04/08/2014 01:25 AM, <a 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 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></body></html>