Thread: Configuring Standby Server in PostgreSQL 9.3.3
<span style=3D"font-family:Verdana; color:#000000; font-size:10= pt;">PostgreSQL version: 9.3.3=0AOperating system: Redhat 6.4 Linu= x=0ADescription: =0AWe are in the proc= ess of setting up a standby server. Instead of restoring from a backup take= n from primary server, we want to shutdown the primary server, and then= copy the primary database VM image to the standby server.We want t= o do this because our database is 7TB and takes 4-5 to backup and 8-9 days = to restore to the standby server.Mock Primary :192.168.1.50Mock= standby:192.168.1.1001.) Configure pg_hda.conf on primary#= Allow the user "postgres" from host 192.168.1.100 to connect to the primar= y# as a replication standby if the user's password is correctly supplie= d.## TYPE DATABASE = USER ADDR= ESS = METHODhost replication = postgres 192.1= 68.1.100/32 md52.) Co= nfigure recovery.conf on standbystandby_mode =3D 'on'primary_co= nninfo =3D 'host=3D192.168.1.50 port=3D5432 user=3Dpostgres password=3Dfoop= ass'restore_command =3D 'cp /path/to/archive/%f %p'archive_cleanup_= command =3D 'pg_archivecleanup /path/to/archive %r'3.) re-start bot= h serversQuestion #1 Is this feasible?Question #2 Have I missed= critical steps?Thanks</h= tml>
On 4/2/2014 11:07 AM, fburgess@radiantblue.com wrote: > We want to do this because our database is 7TB and takes 4-5 to backup > and 8-9 days to restore to the standby server. that might be true for pg_dump/pg_restore type backups, but the initial copy made for streaming replication is a file system copy/clone. how long does pg_basebackup take with your system operational? -- john r pierce 37N 122W somewhere on the middle of the left coast
<span style=3D"font-family:Verdana; color:#000000; font-size:10=
pt;">HI John, all of the backups we have taken were from the 24/7 upti=
me operational production servers. The 4-5 days duration is taken from thes=
e backups. Several months ago we restored one of these backups to another h=
igher capacity database server on fiber channel storage in a different loca=
tion, then we upgraded postgis from 1.5.8 to 2.1.1 and PostgreSQL from 9.1.=
6 to 9.3.3, that restore took 9-10 days to finish. We have another VM that =
has been set aside to be stood up asap as our standby server to this primar=
y. Currently, this standby VM only has Linux 6.4 installed, no PostgreSQL o=
r PostGIS. I was thinking if we can perform a VM clone, we wouldn't have to=
install PostgreSQL, Postgis, packages, etc., on the standby server.</d=
iv>=0A<blockquote id=3D"replyBlockquote" webm=
ail=3D"1" style=3D"border-left: 2px solid blue; margin-left: 8px; padding-l=
eft: 8px; font-size:10pt; color:black; font-family:verdana;">=0A<div id=3D"=
wmQuoteWrapper">=0A-------- Original Message --------=0ASubject: Re: [B=
UGS] Configuring Standby Server in PostgreSQL 9.3.3=0AFrom: John R Pier=
ce <pierce@hogranch.com><b=
r>=0ADate: Wed, April 02, 2014 1:46 pm=0ATo: <a href=3D"mailto:fburgess=
@radiantblue.com">fburgess@radiantblue.com, <a href=3D"mailto:pgsql-bug=
s@postgresql.org">pgsql-bugs@postgresql.org=0A=0A <div clas=
s=3D"moz-cite-prefix">On 4/2/2014 11:07 AM, <a target=3D"_blank" class=3D"m=
oz-txt-link-abbreviated" href=3D"mailto:fburgess@radiantblue.com">fburgess@=
radiantblue.com wrote: <blockquote cite=3D"mid:2014040211074=
9.5a830134ae84016b0174832fdc1a3173.28ee073a59.wbe@email11.secureserver.net"=
type=3D"cite"><span style=3D"font-family:Verdana; color:#000000; font-size=
:10pt;">We want to do this because our database is 7TB and takes 4-5 to bac=
kup and 8-9 days to restore to the standby server. =
that might be true for pg_dump/pg_restore type backups, but the =
initial copy made for streaming replication is a file system copy/clone.<br=
> how long does pg_basebackup take with your system operational?=
-- =0Ajohn r pierce =
37N 122W=0Asomewhere on the middle of the =
left coast =0A=0A
On 4/2/2014 2:58 PM, fburgess@radiantblue.com wrote: > HI John, all of the backups we have taken were from the 24/7 uptime > operational production servers. The 4-5 days duration is taken from > these backups. Several months ago we restored one of these backups to > another higher capacity database server on fiber channel storage in a > different location, then we upgraded postgis from 1.5.8 to 2.1.1 and > PostgreSQL from 9.1.6 to 9.3.3, that restore took 9-10 days to finish. > We have another VM that has been set aside to be stood up asap as our > standby server to this primary. Currently, this standby VM only has > Linux 6.4 installed, no PostgreSQL or PostGIS. I was thinking if we > can perform a VM clone, we wouldn't have to install PostgreSQL, > Postgis, packages, etc., on the standby server. > was that a file backup of the $PGDATA directory, or a database dump ? you CANT start streaming replication with a database dump, as its not the same timeline. you have to use a file system level backup. quite frankly, dealing with a production database this large, I do believe I am going to recommend you bring an experienced postgresql consultant on board who's dealt extensively with large scale replication, someone like 2ndQuadrant, or CommandPrompt, or PGExperts, etc, and let them advise you. -- john r pierce 37N 122W somewhere on the middle of the left coast
On 04/02/2014 09:07 PM, fburgess@radiantblue.com wrote: > We are in the process of setting up a standby server. Instead of restoring from > a backup taken from primary server, > we want to shutdown the primary server, and then copy the primary database VM > image to the standby server. > > We want to do this because our database is 7TB and takes 4-5 to backup and 8-9 > days to restore to the standby server. > > Mock Primary :192.168.1.50 > Mock standby:192.168.1.100 > > 1.) Configure pg_hda.conf on primary > > # Allow the user "postgres" from host 192.168.1.100 to connect to the primary > # as a replication standby if the user's password is correctly supplied. > # > # TYPE DATABASE USER ADDRESS METHOD > host replication postgres 192.168.1.100/32 md5 > > > 2.) Configure recovery.conf on standby > > standby_mode = 'on' > primary_conninfo = 'host=192.168.1.50 port=5432 user=postgres password=foopass' > restore_command = 'cp /path/to/archive/%f %p' > archive_cleanup_command = 'pg_archivecleanup /path/to/archive %r' > > 3.) re-start both servers > > Question #1 Is this feasible? > Question #2 Have I missed critical steps? The short answer is: yes, it's feasible. I think you got steps right. But I agree with the other discussions that it really shouldn't take days to do a filesystem-level backup/restore of a 7TB database, which is what you need to set up a standby. There must be some misunderstanding there. - Heikki
have u already edit postgres.conf on primary? ----- Michele Mariani Database & Technology s.r.l. tel: +39 02 89500080 -- View this message in context: http://postgresql.1045698.n5.nabble.com/Configuring-Standby-Server-in-PostgreSQL-9-3-3-tp5798372p5798529.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
<span style=3D"font-family:Verdana; color:#000000; font-size:10= pt;">No, Haven't set the server as primary yet. Spent yesterday instal= ling PostgreSQL 9.3.3 and PostGIS 2.1.1 on the standby server. Now that's d= one, I think the next step is to run a backup from the yet to be setup Prim= ary server.=0A<blockquote id=3D"replyBlockquote" webmail=3D"1= " style=3D"border-left: 2px solid blue; margin-left: 8px; padding-left: 8px= ; font-size:10pt; color:black; font-family:verdana;">=0A<div id=3D"wmQuoteW= rapper">=0A-------- Original Message --------=0ASubject: Re: [BUGS] Con= figuring Standby Server in PostgreSQL 9.3.3=0AFrom: chileme88 <<a hr= ef=3D"mailto:michele.mariani@databtech.com">michele.mariani@databtech.com</= a>>=0ADate: Thu, April 03, 2014 8:31 am=0ATo: <a href=3D"mailto:= pgsql-bugs@postgresql.org">pgsql-bugs@postgresql.org=0A=0Ahave = u already edit postgres.conf on primary?=0A=0A=0A=0A-----<b= r>=0AMichele Mariani=0ADatabase & Technology s.r.l. =0Atel: +39= 02 89500080=0A--=0AView this message in context: <a href=3D"http:/= /postgresql.1045698.n5.nabble.com/Configuring-Standby-Server-in-PostgreSQL-= 9-3-3-tp5798372p5798529.html">http://postgresql.1045698.n5.nabble.com/Confi= guring-Standby-Server-in-PostgreSQL-9-3-3-tp5798372p5798529.html=0A= Sent from the PostgreSQL - bugs mailing list archive at <a href=3D"http://N= abble.com">Nabble.com.=0A=0A=0A-- =0ASent via pgsql-bug= s mailing list (pgsql-bugs@pos= tgresql.org)=0ATo make changes to your subscription:=0A<a href= =3D"http://www.postgresql.org/mailpref/pgsql-bugs">http://www.postgresql.or= g/mailpref/pgsql-bugs=0A=0A=0A</ht= ml>
<span style=3D"font-family:Verdana; color:#000000; font-size:10=
pt;">Did you guys benchmark the basebackup utility? The master databas=
e will have to remain online during this backup/restore process, since it i=
s receiving real time data feeds. Not sure which technique is better for ou=
r 7TB db.1.) Running from the slave. 1.2.3.4 is the ip of mas=
ter database.basebackup -D /u01/fiber/postgreSQL_data -F p -x strea=
m -c fast -P -v -h 1.2.3.4 -p 5432 -U replication2.) compared to ..=
.psql -c "select pg_start_backup('initial_backup');"rsync -cva =
--inplace --exclude=3D*pg_xlog* /u01/fiber/postgreSQL_data/ postgres@1.2.3.=
4:/u01/fiber/postgreSQL_data/psql -c " select pg_stop_backup () ;"<=
br>3.) or this ...psql =E2=80=93c =E2=80=9Cselect pg_start_backup(=
=E2=80=98hot backup=E2=80=99)=E2=80=9Dcp =E2=80=93pr /u01/fiber/postgre=
SQL_data 1.2.3.4:/u01/fiber/postgreSQL_datapsql =E2=80=93c =E2=80=9Csel=
ect pg_stop_backup(=E2=80=98hot backup=E2=80=99)=E2=80=9Dthanks for=
the feedback. much appreciated.=0A<blockquote id=3D"repl=
yBlockquote" webmail=3D"1" style=3D"border-left: 2px solid blue; margin-lef=
t: 8px; padding-left: 8px; font-size:10pt; color:black; font-family:verdana=
;">=0A=0A-------- Original Message --------=
=0ASubject: Re: [BUGS] Configuring Standby Server in PostgreSQL 9.3.3=
=0AFrom: Michele <miche=
le.mariani@databtech.com>=0ADate: Sun, April 06, 2014 1:59 am<br=
>=0ATo: fburgess@radiantblue.co=
m=0A=0A hi, we have just configured a primary/secondary ser=
ver in POostgreSQL 9.3. I send you our configuration guide. if=
u want we create a php application for secondary, primary server managemen=
t and switch. if u want info write to Sergio Mior: <a target=3D"_blank" cla=
ss=3D"moz-txt-link-abbreviated" href=3D"mailto:sergio.mior@databtech.com">s=
ergio.mior@databtech.com. Kind regards <div class=3D=
"moz-cite-prefix">Il 04/04/2014 16:49, <a target=3D"_blank" class=3D"moz-tx=
t-link-abbreviated" href=3D"mailto:fburgess@radiantblue.com">fburgess@radia=
ntblue.com ha scritto: <blockquote cite=3D"mid:2014040407495=
4.5a830134ae84016b0174832fdc1a3173.85ff9d3a15.wbe@email11.secureserver.net"=
type=3D"cite"><span style=3D"font-family:Verdana; color:#000000; font-size=
:10pt;"> No, Haven't set the server as primary yet. Spent yesterday in=
stalling PostgreSQL 9.3.3 and PostGIS 2.1.1 on the standby server. Now that=
's done, I think the next step is to run a backup from the yet to be setup =
Primary server. <blockquote id=3D"replyBlockquote" webmail=
=3D"1" style=3D"border-left: 2px solid blue; margin-left: 8px; padding-left=
: 8px; font-size:10pt; color:black; font-family:verdana;"> <div id=3D"wmQuo=
teWrapper"> -------- Original Message -------- Subject: Re: [BUGS] Conf=
iguring Standby Server in PostgreSQL 9.3.3 From: chileme88 <<a targe=
t=3D"_blank" moz-do-not-send=3D"true" href=3D"mailto:michele.mariani@databt=
ech.com">michele.mariani@databtech.com> Date: Thu, April 03, 201=
4 8:31 am To: <a target=3D"_blank" moz-do-not-send=3D"true" href=3D"mai=
lto:pgsql-bugs@postgresql.org">pgsql-bugs@postgresql.org have =
u already edit postgres.conf on primary? ----- Miche=
le Mariani Database & Technology s.r.l. tel: +39 02 89500080<b=
r> -- View this message in context: <a target=3D"_blank" moz-do-not-sen=
d=3D"true" href=3D"http://postgresql.1045698.n5.nabble.com/Configuring-Stan=
dby-Server-in-PostgreSQL-9-3-3-tp5798372p5798529.html">http://postgresql.10=
45698.n5.nabble.com/Configuring-Standby-Server-in-PostgreSQL-9-3-3-tp579837=
2p5798529.html Sent from the PostgreSQL - bugs mailing list archive=
at <a target=3D"_blank" moz-do-not-send=3D"true" href=3D"http://Nabble.com=
">Nabble.com. -- Sent via pgsql-bugs mailing list (<=
a target=3D"_blank" moz-do-not-send=3D"true" href=3D"mailto:pgsql-bugs@post=
gresql.org">pgsql-bugs@postgresql.org) To make changes to your subs=
cription: <a target=3D"_blank" moz-do-not-send=3D"true" href=3D"http://=
www.postgresql.org/mailpref/pgsql-bugs">http://www.postgresql.org/mailpref/=
pgsql-bugs <div cla=
ss=3D"moz-signature">-- <img src=3D"cid:part7.04050605.04010306@data=
btech.com" wbeuser=3D"fburgess@radiantblue.com" border=3D"0"> <br=
> <hr style=3D"border:none; color:#909090; background-color:#B0B0B0; height=
: 1px; width: 99%;"> <table style=3D"border-collapse:collapse;border:none;"=
> <a target=
=3D"_blank" href=3D"http://www.avast.com/"> <img src=3D"http://static.ava=
st.com/emails/avast-mail-stamp.png" border=3D"0"> <div styl=
e=3D"color:#3d4d5a; font-family:"Calibri","Verdana",&qu=
ot;Arial","Helvetica"; font-size:12pt;"> Questa e-mail =C3=
=A8 priva di virus e malware perch=C3=A9 =C3=A8 attiva la protezione <a tar=
get=3D"_blank" href=3D"http://www.avast.com/">avast! Antivirus . =
=0A=0A</body=
>
On 04/08/2014 01:25 AM, fburgess@radiantblue.com 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. Not 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 "select pg_start_backup('initial_backup');"
> rsync -cva --inplace --exclude=*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 âc âselect pg_start_backup(âhot backupâ)â
> cp âpr /u01/fiber/postgreSQL_data 1.2.3.4:/u01/fiber/postgreSQL_data
> psql âc âselect pg_stop_backup(âhot backupâ)â
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.
- Heikki
On Apr 8, 2014, at 3:16 AM, Heikki Linnakangas <hlinnakangas@vmware.com> =
wrote:
> On 04/08/2014 01:25 AM, fburgess@radiantblue.com 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. Not sure which technique is better for our 7TB db.
>>=20
>> 1.) Running from the slave. 1.2.3.4 is the ip of master database.
>>=20
>> basebackup -D /u01/fiber/postgreSQL_data -F p -x stream -c fast -P -v =
-h 1.2.3.4
>> -p 5432 -U replication
>>=20
>> 2.) compared to ...
>>=20
>> psql -c "select pg_start_backup('initial_backup');"
>> rsync -cva --inplace --exclude=3D*pg_xlog* =
/u01/fiber/postgreSQL_data/
>> postgres@1.2.3.4:/u01/fiber/postgreSQL_data/
>> psql -c " select pg_stop_backup () ;"
>>=20
>> 3.) or this ...
>>=20
>> psql =96c =93select pg_start_backup(=91hot backup=92)=94
>> cp =96pr /u01/fiber/postgreSQL_data =
1.2.3.4:/u01/fiber/postgreSQL_data
>> psql =96c =93select pg_stop_backup(=91hot backup=92)=94
>=20
> 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.
> - Heikki
FWIW, I benchmarked a few methods here:
=
http://frosty-postgres.blogspot.com/2011/12/postgresql-base-backup-benchma=
rk.html
admittedly, the base backup was much smaller, but I suspect you would =
see similar performance with 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 that rsync can =
take advantage of partial file copy.
If you use rsync over ssh, make sure to use the arcfour cipher for best =
performance.
<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
<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>