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;">=0A=0AManagement requires that we implement a master server with =
two standby database servers. Is it recommended we keep the archives logs i=
n separate directories in each VM, or that we have one directory on a share=
d drive that each of the three VM's can access? We would like to leverage t=
he archive_cleanup_command =3D =E2=80=98pg_archivecleanup /sharedata/postgr=
es_archive_master %r=E2=80=99 in our recovery.conf configuration on both st=
andby servers.-------- Original Message --------<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;">=0ASubject: Re: [BUGS] Configuring S=
tandby Server in PostgreSQL 9.3.3=0AFrom: <<a href=3D"mailto:fburges=
s@radiantblue.com">fburgess@radiantblue.com>=0ADate: Tue, April =
08, 2014 12:34 pm=0ATo: "Jeff Frost" <<a href=3D"mailto:jeff@pgexper=
ts.com">jeff@pgexperts.com>,  "Heikki Linnakangas"=0A<<a href=
=3D"mailto:hlinnakangas@vmware.com">hlinnakangas@vmware.com>=0AC=
c: "Michele" <michele.m=
ariani@databtech.com>, =
pgsql-bugs@postgresql.org=0A=0A<span style=3D"font-family:Verda=
na; color:#000000; font-size:10pt;">Our standby server sits on a separ=
ate VM,. Should archive_mode =3D on be set only on the master and not the s=
lave? My colleague is convinced that the archive log directory should sit o=
n a shared drive that is accessible to both VM's (master and Slave)<d=
iv> <blockquote id=3D"replyBlockquote" webmail=3D"1" style=3D"bor=
der-left: 2px solid blue; margin-left: 8px; padding-left: 8px; font-size:10=
pt; color:black; font-family:verdana;">  -------=
- Original Message -------- Subject: Re: [BUGS] Configuring Standby Ser=
ver in PostgreSQL 9.3.3 From: Jeff Frost <<a target=3D"_blank" href=
=3D"mailto:jeff@pgexperts.com">jeff@pgexperts.com> Date: Tue, Ap=
ril 08, 2014 9:39 am To: Heikki Linnakangas <<a target=3D"_blank" hr=
ef=3D"mailto:hlinnakangas@vmware.com">hlinnakangas@vmware.com> C=
c: fburgess@r=
adiantblue.com, Michele <<a target=3D"_blank" href=3D"mailto:michele=
.mariani@databtech.com">michele.mariani@databtech.com>, <a targe=
t=3D"_blank" href=3D"mailto:pgsql-bugs@postgresql.org">pgsql-bugs@postgresq=
l.org   On Apr 8, 2014, at 3:16 AM, Heikki Linnakangas <=
hlinnakangas@v=
mware.com> wrote:  > On 04/08/2014 01:25 AM, <a target=3D=
"_blank" href=3D"mailto:fburgess@radiantblue.com">fburgess@radiantblue.com<=
/a> wrote: >> Did you guys benchmark the basebackup utility? The =
master database will have to >> remain online during this backup/=
restore process, since it is receiving real >> time data feeds. N=
ot sure which technique is better for our 7TB db. >>  >>=
; 1.) Running from the slave. 1.2.3.4 is the ip of master database. >=
;>  >> basebackup -D /u01/fiber/postgreSQL_data -F p -x stream=
 -c fast -P -v -h 1.2.3.4 >> -p 5432 -U replication >> =
 >> 2.) compared to ... >>  >> psql -c "selec=
t pg_start_backup('initial_backup');" >> rsync -cva --inplace --e=
xclude=3D*pg_xlog* /u01/fiber/postgreSQL_data/ >> postgres@1.2.3.=
4:/u01/fiber/postgreSQL_data/ >> psql -c " select pg_stop_backup =
() ;" >>  >> 3.) or this ... >>  >>=
 psql =E2=80=93c =E2=80=9Cselect pg_start_backup(=E2=80=98hot backup=E2=80=
=99)=E2=80=9D >> cp =E2=80=93pr /u01/fiber/postgreSQL_data 1.2.3.=
4:/u01/fiber/postgreSQL_data >> psql =E2=80=93c =E2=80=9Cselect p=
g_stop_backup(=E2=80=98hot backup=E2=80=99)=E2=80=9D >  > I b=
et the rsync or cp method is faster. Dunno how much, though, that depends o=
n 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. =
> - Heikki  FWIW, I benchmarked a few methods here:  <a =
target=3D"_blank" href=3D"http://frosty-postgres.blogspot.com/2011/12/postg=
resql-base-backup-benchmark.html">http://frosty-postgres.blogspot.com/2011/=
12/postgresql-base-backup-benchmark.html  admittedly, the base =
backup was much smaller, but I suspect you would see similar performance wi=
th a larger base backup.  You might also try running the rsync once=
, before issuing pg_start_backup() and then doing a normal base backup so t=
hat rsync can take advantage of partial file copy.  If you use rsyn=
c over ssh, make sure to use the arcfour cipher for best performance. <=
br>   =0A=0A</ht=
ml>