Thread: Inherited an 18TB DB & need to backup

On 15/05/20, Suhail Bamzena (suhailsalem@gmail.com) wrote:
> Hello All,
> I have very recently inherited an 18 TB DB that is running version 9.2.
> Apparently this database has never been backed up and I have been tasked to
> set in a periodic backup routine (weekly full & daily incremental) and dump
> it into a NAS. What is the best way to go about this? Did some reading and
> hear that pgbackrest does a good job with such huge sizes. Your expert
> advise is needed.

Incremental backups suggest the need to backup WAL archives. See
https://www.postgresql.org/docs/9.2/continuous-archiving.html

pgbackrest looks very cool but we haven't used it.

A very simple solution could be just to dump the database daily with
pg_dump, if you have the space and machine capacity to do it. Depending
on what you are storing, you can achieve good compression with this, and
it is a great way of having a simple file from which to restore a
database.

Our ~200GB cluster resolves to under 10GB of pg_dump files, although
18TB is a whole different order of size.

Rory

Re: Rory Campbell-Lange
> On 15/05/20, Suhail Bamzena (suhailsalem@gmail.com) wrote:
> > Hello All,
> > I have very recently inherited an 18 TB DB that is running version 9.2.

Push hard to get that upgraded to a supported version.

Christoph

Hi Suhail,

Hi Suhail,

On 15/05/20, Suhail Bamzena (suhailsalem@gmail.com) wrote:
> Thanks Rory, the machine has the capacity to pull through pg_dumps but like
> u rightly mentioned incremental backups mean that we will need to work with
> the wal's.. 18TB is what is the scary part and with compression I dont see
> it being less than 2TB a day...

I suggest you try one immediately, assuming you can dump it somewhere
and the machine has the resources. Then you will at least have a backup
and a sense of what is possible. (Which will help inform your upgrade
strategy too.)

Rory

IMO a database of this size should only be backed up in s3.  pgbackrest has support for backup to s3.

--

> On May 15, 2020, at 12:01, Scottix <scottix@gmail.com> wrote:
>
> Also when you get in the multi TB data storage the bill gets a little harder to digest in S3.

Indeed.  Right now, just buying off of Amazon, a 12TB Seagate IronWolf drive is $0.03/GB.  S3 infrequent access is
$0.0125/GB/month,so the drive pays for itself (storage only) in 2.4 months.  Even with an annualized failure rate of
1.4%(per Backblaze), and with the required host, rack, etc., etc., it be significantly more economical to run your own
backupserver.  If you regularly do restores off of the backups (for example, to prime staging environments or developer
systems),the outbound transfer can add up fast, too. 
--
-- Christophe Pettus
   xof@thebuild.com

For a database that size, I'd install pgbackrest, since it features parallel backups and compression.  With it, I'd do monthly full backups with daily differential backups.

(If it's mostly historical data, I'd split the database into multiple instances, so that older data rarely needs to be backed up. The application, of course, would have to be modified.)

Thanks Rory, the machine has the capacity to pull through pg_dumps but like u rightly mentioned incremental backups mean that we will need to work with the wal's.. 18TB is what is the scary part and with compression I dont see it being less than 2TB a day...

On Fri, 15 May 2020, 17:02 Rory Campbell-Lange, <rory@campbell-lange.net> wrote:

On 15/05/20, Suhail Bamzena (suhailsalem@gmail.com) wrote:
> Hello All,
> I have very recently inherited an 18 TB DB that is running version 9.2.
> Apparently this database has never been backed up and I have been tasked to
> set in a periodic backup routine (weekly full & daily incremental) and dump
> it into a NAS. What is the best way to go about this? Did some reading and
> hear that pgbackrest does a good job with such huge sizes. Your expert
> advise is needed.

Incremental backups suggest the need to backup WAL archives. See
https://www.postgresql.org/docs/9.2/continuous-archiving.html

pgbackrest looks very cool but we haven't used it.

A very simple solution could be just to dump the database daily with
pg_dump, if you have the space and machine capacity to do it. Depending
on what you are storing, you can achieve good compression with this, and
it is a great way of having a simple file from which to restore a
database.

Our ~200GB cluster resolves to under 10GB of pg_dump files, although
18TB is a whole different order of size.

Rory

On 5/15/20 6:01 PM, Michael Nolan wrote:

On Fri, May 15, 2020 at 12:51 PM Ravi Krishna <srkrishna1@comcast.net> wrote:

Why should the backup land in S3, and not local somewhere?

Any good reason why one should pay for the additional storage and transfer costs?

Good question. The key point in my statement was "db of this size".

The problem with local backup is that space is not infinite. 

Another problem is storage devices fail.  S3 storage lakes _should_ be checking your data integrity on a regular basis and possibly maintaining copies of it iin multiple locations so you're not vulnerable to a site disaster.

Tape FTW!!

> O
>> Another problem is storage devices fail.  S3 storage lakes _should_ be checking your data integrity on a regular
basisand possibly maintaining copies of it iin multiple locations so you're not vulnerable to a site disaster. 
>
> Tape FTW!!
>
> --
Or WTF Tape??   :)
> Angular momentum makes the world go 'round.

On 2020-05-15 14:02:46 +0100, Rory Campbell-Lange wrote:
> On 15/05/20, Suhail Bamzena (suhailsalem@gmail.com) wrote:
> > I have very recently inherited an 18 TB DB that is running version 9.2.
> > Apparently this database has never been backed up
[...]
> A very simple solution could be just to dump the database daily with
> pg_dump, if you have the space and machine capacity to do it. Depending
> on what you are storing, you can achieve good compression with this, and
> it is a great way of having a simple file from which to restore a
> database.
>
> Our ~200GB cluster resolves to under 10GB of pg_dump files, although
> 18TB is a whole different order of size.

I love pg_dump (especially the -Fd format), but for a database of that
size it might be too slow. Ours is about 1TB, and «pg_dump --compress=5 -Fd»
takes a bit over 2 hours. Extrapolating to 18 TB that would be 40 hours
...

And restoring the database takes even more time because it only restores
the tables and has to rebuild the indexes.

Still - for a first backup, just firing off pg_dump might be the way to
go. Better to have a backup in two days than still none after two weeks
because you are still evaluating the fancier alternatives.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

On 5/16/20 7:18 AM, Rob Sargent wrote:
>> O
>>> Another problem is storage devices fail.  S3 storage lakes _should_ be checking your data integrity on a regular
basisand possibly maintaining copies of it iin multiple locations so you're not vulnerable to a site disaster.
 
>> Tape FTW!!
> Or WTF Tape??   :)

Tape is durable, long-lasting, high-density, under your control, can be 
taken off-site (don't underestimate the bandwidth of a station wagon full of 
tapes hurtling down the highway!) and -- with the proper software -- is 
multi-threaded.

-- 
Angular momentum makes the world go 'round.

On 17/05/2020 08:12, Ron wrote:
> On 5/16/20 7:18 AM, Rob Sargent wrote:
>>> O
>>>> Another problem is storage devices fail.  S3 storage lakes _should_ 
>>>> be checking your data integrity on a regular basis and possibly 
>>>> maintaining copies of it iin multiple locations so you're not 
>>>> vulnerable to a site disaster.
>>> Tape FTW!!
>> Or WTF Tape??   :)
>
> Tape is durable, long-lasting, high-density, under your control, can 
> be taken off-site (don't underestimate the bandwidth of a station 
> wagon full of tapes hurtling down the highway!) and -- with the proper 
> software -- is multi-threaded.
>
Don't you mean multi-spooled??? :-)

Fascinating problem.  If the dump & load programs are designed to take a 
parameter for N drives for effective parallel operation, and N > 2, then 
things will run a lot faster.

I can think of several ways the the data can be dumped in parallel, with 
various trade-offs.  Would love to know how it's implemented in practice.


Cheers,
Gavn

On 5/16/20 3:30 PM, Gavin Flower wrote:

On 17/05/2020 08:12, Ron wrote:

On 5/16/20 7:18 AM, Rob Sargent wrote:

O

Another problem is storage devices fail.  S3 storage lakes _should_ be checking your data integrity on a regular basis and possibly maintaining copies of it iin multiple locations so you're not vulnerable to a site disaster.

Tape FTW!!

Or WTF Tape??   :)

Tape is durable, long-lasting, high-density, under your control, can be taken off-site (don't underestimate the bandwidth of a station wagon full of tapes hurtling down the highway!) and -- with the proper software -- is multi-threaded.

Don't you mean multi-spooled??? :-)

That's a superset of multi-threaded IO.

Fascinating problem.  If the dump & load programs are designed to take a parameter for N drives for effective parallel operation, and N > 2, then things will run a lot faster.

I can think of several ways the the data can be dumped in parallel, with various trade-offs.  Would love to know how it's implemented in practice.

An OS with asynchronous, queued, non-blocking IO, and a programming language with callbacks.  OpenVMS has had it since since at least the early 1990s, and probably mid-1980s.  I remember backing up an Rdb/VMS database to 10 tape drives at the same time.  Typically, though, we "only" used six tape drives for that database, because we simultaneously backed up multiple databases.

---

The contents of this email are confidential and may be subject to legal or professional privilege and copyright. No representation is made that this email is free of viruses or other defects. If you have received this communication in error, you may not copy or distribute any part of it or otherwise disclose its contents to anyone. Please advise the sender of your incorrect receipt of this correspondence.

On Fri, 15 May 2020 15:08:05 +0200
Christoph Berg <christoph.berg@credativ.de> wrote:

> Re: Rory Campbell-Lange
> > On 15/05/20, Suhail Bamzena (suhailsalem@gmail.com) wrote:  
> > > Hello All,
> > > I have very recently inherited an 18 TB DB that is running
> > > version 9.2.  
> 
> Push hard to get that upgraded to a supported version.
> 
> Christoph

He can't change the version without getting at least one verified
backup. Can you imagine if installing the new version trashed
the database?

SteveT

Steve Litt 
May 2020 featured book: Troubleshooting Techniques
     of the Successful Technologist
http://www.troubleshooters.com/techniques