public inbox for [email protected]
help / color / mirror / Atom feedFrom: Doug Reynolds <[email protected]>
To: Pgsql-admin <[email protected]>
Subject: Re: Running rsync backups in pg15
Date: Thu, 7 Nov 2024 19:49:51 +0000
Message-ID: <01000193082d1077-34d9461d-49e4-44ef-b83a-0201df4fc0ba-000000@email.amazonses.com> (raw)
In-Reply-To: <CANzqJaCr-id_4YotukPeH=rHCELS58Z63FoEbkNFBSqzjGMueQ@mail.gmail.com>
References: <CANzqJaCr-id_4YotukPeH=rHCELS58Z63FoEbkNFBSqzjGMueQ@mail.gmail.com>
--Apple-Mail-B452B1D4-B5D1-406C-BF06-6C4E17910D9E
Content-Type: text/html;
charset=utf-8
Content-Transfer-Encoding: quoted-printable
<html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D=
utf-8"></head><body dir=3D"auto">I would be curious to see how long it would=
take to restore a 5TB database from each WAL ever created on the system.<di=
v><br></div><div>We used to do a similar process with Oracle on a 30TB datab=
ase years ago, but it literally took 26-28 hours to do a full backup.</div><=
div><br id=3D"lineBreakAtBeginningOfSignature"><div dir=3D"ltr">Doug</div><d=
iv dir=3D"ltr"><br><blockquote type=3D"cite">On Nov 7, 2024, at 1:05=E2=80=AF=
PM, Ron Johnson <[email protected]> wrote:<br><br></blockquote><=
/div><blockquote type=3D"cite"><div dir=3D"ltr">=EF=BB=BF<div dir=3D"ltr"><d=
iv dir=3D"ltr">On Thu, Nov 7, 2024 at 12:47=E2=80=AFPM Evan Rempel <<a hr=
ef=3D"mailto:[email protected]">[email protected]</a>> wrote:<br></div><div c=
lass=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0=
px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div c=
lass=3D"msg-1779621256372920782">
<div dir=3D"ltr">
<div style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calib=
ri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
We use a similar approach, but instead of using rsync, we use our backup sof=
tware directly which is an incremental forever tool. Allows backup of TB DBs=
in short minutes. Switching to pgbackrest is actually a step backwards for u=
s.</div></div></div></blockquote><div><br></div><div>Last night's pgbackrest=
incremental backup of a 5.1TB database took a whopping 92 <b>seconds</b>.&n=
bsp; How's that a backwards step?</div><div><br></div><div>Sure, the weekly f=
ull backup takes 84 minutes, but that's in so way shape or form painfully sl=
ow.</div><div> </div><blockquote class=3D"gmail_quote" style=3D"margin:=
0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><=
div class=3D"msg-1779621256372920782"><div dir=3D"ltr">
<div style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calib=
ri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)"><span style=3D"font=
-size:12pt">But as the OP states, if you have to keep the postgresql session=
open for the pg_start_backup and the pg_stop_backup then we will have t=
o do a significant architectual change.</span><br></div>
<div style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calib=
ri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
<br>
</div>
<div style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calib=
ri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
Anyone know if there is a straight forward way to allows the pg_start_backup=
and the pg_stop_backup to be run in different sessions?</div>
<div id=3D"m_-1779621256372920782Signature">
<div style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calib=
ri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
<br>
</div>
<div style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calib=
ri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
<br>
</div>
<div style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calib=
ri,Helvetica,sans-serif;font-size:12pt;color:rgb(153,153,153)">
--</div>
<div style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calib=
ri,Helvetica,sans-serif;font-size:12pt;color:rgb(153,153,153)">
Evan</div>
</div>
<div id=3D"m_-1779621256372920782appendonsend"></div>
<div style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calib=
ri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
<br>
</div>
<hr style=3D"display:inline-block;width:98%">
<div id=3D"m_-1779621256372920782divRplyFwdMsg" dir=3D"ltr"><span style=3D"f=
ont-family:Calibri,sans-serif;font-size:11pt;color:rgb(0,0,0)"><b>From:</b>&=
nbsp;Ron Johnson <<a href=3D"mailto:[email protected]" target=3D"_b=
lank">[email protected]</a>><br>
<b>Sent:</b> November 7, 2024 9:34 AM<br>
<b>To:</b> <a href=3D"mailto:[email protected]" target=3D"_bla=
nk">[email protected]</a> <<a href=3D"mailto:pgsql-admin@postgre=
sql.org" target=3D"_blank">[email protected]</a>><br>
<b>Subject:</b> Re: Running rsync backups in pg15</span>
<div> </div>
</div>
<div style=3D"direction:ltr">On Thu, Nov 7, 2024 at 11:35=E2=80=AFAM Murthy N=
unna <<a href=3D"mailto:[email protected]" id=3D"m_-1779621256372920782OWA9=
253b4f7-8ed8-0b40-fa4d-91d75f5f0882" target=3D"_blank">[email protected]</a>&g=
t; wrote:</div>
<blockquote style=3D"margin:0px 0px 0px 0.8ex;padding-left:1ex;border-left:1=
px solid rgb(204,204,204)">
<p style=3D"direction:ltr"><span style=3D"font-size:12pt">Hi,</span></p>
<p style=3D"direction:ltr"><span style=3D"font-size:12pt"> </span></p>
<p style=3D"direction:ltr"><span style=3D"font-size:12pt">In PG14 and earlie=
r, there is no requirement to keep database connection while rsync is in pro=
gress. However, there is a change in PG15+ that requires rsync to be while w=
e have the same database session
open that executes </span>SELECT pg_backup_start('label'). This change requ=
ires a rewrite of existing scripts we have.</p>
<p style=3D"direction:ltr"> </p>
<p style=3D"direction:ltr">Currently (pg14):</p>
<p style=3D"direction:ltr"> </p>
<p style=3D"direction:ltr"> &=
nbsp; In bash script (run from cron)</p>=
<ol start=3D"1" style=3D"direction:ltr;margin-top:0in">
<li style=3D"direction:ltr;margin-left:0.25in">psql Select pg_start_backup</=
li><li style=3D"direction:ltr;margin-left:0.25in">rsync</li><li style=3D"dir=
ection:ltr;margin-left:0.25in">psql Select pg_stop_backup</li></ol>
<p style=3D"direction:ltr;margin-left:0.75in"> </p>
<p style=3D"direction:ltr">In pg15 and later:</p>
<p style=3D"direction:ltr"> </p>
<p style=3D"direction:ltr;margin-left:0.75in">In bash script (run from cron)=
</p>
<p style=3D"direction:ltr;margin-left:0.75in"> </p>
<p style=3D"direction:ltr;margin-left:0.75in">psql</p>
<p style=3D"direction:ltr;margin-left:1in">Select pg_start_backup</p>
<p style=3D"direction:ltr;margin-left:1in">! run-rsync-script</p>
<p style=3D"direction:ltr;margin-left:1in">Select pg_stop_backup</p>
<p style=3D"direction:ltr"> </p>
<p style=3D"direction:ltr">It can be done, but it makes it ugly to check err=
ors and so forth that occur in the rsync script.</p>
<p style=3D"direction:ltr"> </p>
<p style=3D"direction:ltr">Anybody found an elegant way of doing this?</p>
</blockquote>
<div style=3D"direction:ltr"><br>
</div>
<div style=3D"direction:ltr">Run pgbackrest instead of rsync,</div>
<div style=3D"direction:ltr"><br>
</div>
<div style=3D"direction:ltr">--</div>
<div style=3D"direction:ltr">Death to <Redacted>, and butter sauce.</d=
iv>
<div style=3D"direction:ltr">Don't boil me, I'm still alive.</div>
<div style=3D"direction:ltr"><Redacted> lobster!</div>
</div>
</div></blockquote></div><div><br clear=3D"all"></div><div><br></div><span c=
lass=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmai=
l_signature"><div dir=3D"ltr">Death to <Redacted>, and butter sauce.<d=
iv>Don't boil me, I'm still alive.<br><div><div><Redacted> lobster!</d=
iv></div></div></div></div></div>
</div></blockquote></div></body></html>=
--Apple-Mail-B452B1D4-B5D1-406C-BF06-6C4E17910D9E--
view thread (24+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Running rsync backups in pg15
In-Reply-To: <01000193082d1077-34d9461d-49e4-44ef-b83a-0201df4fc0ba-000000@email.amazonses.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox