Re: Configuring Standby Server in PostgreSQL 9.3.3 - Mailing list pgsql-bugs

<span style=3D"font-family:Verdana; color:#000000; font-size:10=
pt;">Our standby server sits on a separate VM,. Should archive_mode =
=3D on be set only on the master and not the slave? My colleague is convinc=
ed that the archive log directory should sit on a shared drive that is acce=
ssible to both VM's (master and Slave)=0A<blockquote i=
d=3D"replyBlockquote" webmail=3D"1" style=3D"border-left: 2px solid blue; m=
argin-left: 8px; padding-left: 8px; font-size:10pt; color:black; font-famil=
y:verdana;">=0A=0A-------- Original Message ----=
----=0ASubject: Re: [BUGS] Configuring Standby Server in PostgreSQL 9.3=
.3=0AFrom: Jeff Frost <jeff@pg=
experts.com>=0ADate: Tue, April 08, 2014 9:39 am=0ATo: Heikk=
i Linnakangas <hlinnakangas@v=
mware.com>=0ACc: fbu=
rgess@radiantblue.com, Michele <<a href=3D"mailto:michele.mariani@da=
tabtech.com">michele.mariani@databtech.com>,=0A<a href=3D"mailto=
:pgsql-bugs@postgresql.org">pgsql-bugs@postgresql.org=0A=0A=
=0AOn Apr 8, 2014, at 3:16 AM, Heikki Linnakangas <<a href=3D"mailto:hli=
nnakangas@vmware.com">hlinnakangas@vmware.com> wrote:=0A=0A&=
gt; On 04/08/2014 01:25 AM, fbu=
rgess@radiantblue.com wrote:=0A>> Did you guys benchmark the =
basebackup utility? The master database will have to=0A>> remain =
online during this backup/restore process, since it is receiving real=
=0A>> time data feeds. Not sure which technique is better for our 7TB=
 db.=0A>> =0A>> 1.) Running from the slave.  1.2.3.4 is=
 the ip of master database.=0A>> =0A>> basebackup -D /u=
01/fiber/postgreSQL_data -F p -x stream -c fast -P -v -h 1.2.3.4=0A>=
> -p 5432 -U replication=0A>> =0A>> 2.) compared to =
...=0A>> =0A>> psql -c "select pg_start_backup('initial=
_backup');"=0A>> rsync -cva --inplace --exclude=3D*pg_xlog* /u01/=
fiber/postgreSQL_data/=0A>> postgres@1.2.3.4:/u01/fiber/postgreSQ=
L_data/=0A>> psql -c " select pg_stop_backup () ;"=0A>>=
 =0A>> 3.) or this ...=0A>> =0A>> psql =E2=80=
=93c =E2=80=9Cselect pg_start_backup(=E2=80=98hot backup=E2=80=99)=E2=80=9D=
=0A>> cp =E2=80=93pr /u01/fiber/postgreSQL_data 1.2.3.4:/u01/fibe=
r/postgreSQL_data=0A>> psql =E2=80=93c =E2=80=9Cselect pg_stop_ba=
ckup(=E2=80=98hot backup=E2=80=99)=E2=80=9D=0A> =0A> I bet th=
e rsync or cp method is faster. Dunno how much, though, that depends on wha=
t the bottleneck is; the network, or the disk, or something else. You'll ha=
ve to measure it yourself, to know how it is in your environment.=0A>=
; - Heikki=0A=0AFWIW, I benchmarked a few methods here:=0A=
=0A<a href=3D"http://frosty-postgres.blogspot.com/2011/12/postgresql-base-b=
ackup-benchmark.html">http://frosty-postgres.blogspot.com/2011/12/postgresq=
l-base-backup-benchmark.html=0A=0Aadmittedly, the base backup w=
as much smaller, but I suspect you would see similar performance with a lar=
ger base backup.=0A=0AYou might also try running the rsync once, be=
fore issuing pg_start_backup() and then doing a normal base backup so that =
rsync can take advantage of partial file copy.=0A=0AIf you use rsyn=
c over ssh, make sure to use the arcfour cipher for best performance.=
=0A=0A=0A=0A