Re: Postgres backup tool recommendations for multi-terabyte database in Google Cloud

--000000000000bb2e360598fb4ae7
Content-Type: text/plain; charset="UTF-8"

Thanks, I'll check it out.

On Thu, Dec 5, 2019 at 12:51 PM Craig James <[email protected]> wrote:

> On Thu, Dec 5, 2019 at 9:48 AM Craig Jackson <[email protected]>
> wrote:
>
>> Hi,
>>
>> We are in the process of migrating an oracle database to postgres in
>> Google Cloud and are investigating backup/recovery tools. The database is
>> size is > 20TB. We have an SLA that requires us to be able to complete a
>> full restore of the database within 24 hours. We have been testing
>> pgbackreset, barman, and GCP snapshots but wanted to see if there are any
>> other recommendations we should consider.
>>
>> *Desirable features*
>> - Parallel backup/recovery
>> - Incremental backups
>> - Backup directly to a GCP bucket
>> - Deduplication/Compression
>>
>
> For your 24-hour-restore requirement, there's an additional feature you
> might consider: incremental restore, or what you might call "recovery in
> place"; that is, the ability to keep a more-or-less up-to-date copy, and
> then in an emergency only restore the diffs on the file system. pgbackup
> uses a built-in rsync-like feature, plus a client-server architecture, that
> allows it to quickly determine which disk blocks need to be updated.
> Checksums are computed on each side, and data are only transferred if
> checksums differ. It's very efficient. I assume that a 20 TB database is
> mostly static, with only a small fraction of the data updated in any month.
> I believe the checksums are precomputed and stored in the pgbackrest
> repository, so you can even do this from an Amazon S3 (or whatever Google's
> Cloud equivalent is for low-cost storage) backup with just modest bandwidth
> usage.
>
> In a cloud environment, you can do this on modestly-priced hardware (a few
> CPUs, modest memory). In the event of a failover, unmount your backup disk,
> spin up a big server, mount the database, do the incremental restore, and
> you're in business.
>
> Craig (James)
>
>
>> Any suggestions would be appreciated.
>>
>> Craig Jackson
>>
>
>
>

-- 
Craig

--000000000000bb2e360598fb4ae7
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Thanks, I&#39;ll check it out.=C2=A0<br></div><br><div cla=
ss=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, Dec 5, 201=
9 at 12:51 PM Craig James &lt;<a href=3D"mailto:[email protected]">cjam=
[email protected]</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote"=
 style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);p=
adding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">On Thu, Dec 5, 2019 at 9=
:48 AM Craig Jackson &lt;<a href=3D"mailto:[email protected]" targ=
et=3D"_blank">[email protected]</a>&gt; wrote:<br></div><div class=
=3D"gmail_quote"><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 dir=
=3D"ltr">Hi,<div><br></div><div>We are in the process of migrating an oracl=
e database to postgres in Google Cloud and are investigating backup/recover=
y tools. The database is size is &gt; 20TB. We have an SLA that requires us=
 to be able to complete a full restore of the database within 24 hours. We =
have been testing pgbackreset,=C2=A0barman, and GCP snapshots but wanted to=
 see if there are any other recommendations we should consider.=C2=A0</div>=
<div><br></div><div><u>Desirable=C2=A0features</u></div><div><div>- Paralle=
l backup/recovery<br>- Incremental backups</div><div>- Backup directly to a=
 GCP bucket</div><div>- Deduplication/Compression</div></div></div></blockq=
uote><div><br></div><div>For your 24-hour-restore requirement, there&#39;s =
an additional feature you might consider: incremental restore, or what you =
might call &quot;recovery in place&quot;; that is, the ability to keep a mo=
re-or-less up-to-date copy, and then in an emergency only restore the diffs=
 on the file system. pgbackup uses a built-in rsync-like feature, plus a cl=
ient-server architecture, that allows it to quickly determine which disk bl=
ocks need to be updated. Checksums are computed on each side, and data are =
only transferred if checksums differ. It&#39;s very efficient. I assume tha=
t a 20 TB database is mostly static, with only a small fraction of the data=
 updated in any month. I believe the checksums are precomputed and stored i=
n the pgbackrest repository, so you can even do this from an Amazon S3 (or =
whatever Google&#39;s Cloud equivalent is for low-cost storage) backup with=
 just modest bandwidth usage.</div><div><br></div><div>In a cloud environme=
nt, you can do this on modestly-priced hardware (a few CPUs, modest memory)=
. In the event of a failover, unmount your backup disk, spin up a big serve=
r, mount the database, do the incremental restore, and you&#39;re in busine=
ss.</div><div><br></div><div>Craig (James)</div><div><br></div><blockquote =
class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px sol=
id rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div><div><br></div>=
<div>Any suggestions would be appreciated.</div><br><div dir=3D"ltr"><div d=
ir=3D"ltr">Craig Jackson</div><div></div></div></div></div>
</blockquote></div><br clear=3D"all"><div><br></div></div>
</blockquote></div><br clear=3D"all"><div><br></div>-- <br><div dir=3D"ltr"=
 class=3D"gmail_signature"><div dir=3D"ltr">Craig=C2=A0</div></div>

--000000000000bb2e360598fb4ae7--