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 Christoph
Thats very high on my agenda.. but need to make sure i can backup this
beast to start with..

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

Hi Suhail,

That is not an issue. We have customers backing up Postgres databases up to 80 TB.

Mit freundlichem Gruß, kind regards,

Jan Karremans
Director of Sales Engineering, EMEA
Senior Sales Engineer DACH-Region
EDB Postgres Advanced Server Professional
-- Postgres Everywhere --

Oracle ACE Alumni

- Deutsch ist nicht meine Muttersprache, bitte verzeihen Sie Verstöße gegen die Rechtschreibung -

Mobile: +31-(0)6-1638 9607

http://www.enterprisedb.com/

Don't walk behind me; I may not lead.
Don't walk in front of me; I may not follow.
Just walk beside me and be my friend.
+*+ Albert Camus +*+

Depending on your storage subsystem, perhaps storage-level snapshots might be an option? They often seem to be the best choice for VLDBs.

From: Suhail Bamzena <suhailsalem@gmail.com>
Sent: Friday, May 15, 2020 7:23 AM
To: Christoph Berg <christoph.berg@credativ.de>
Cc: Rory Campbell-Lange <rory@campbell-lange.net>; pgsql-general@lists.postgresql.org; pgeu-general@lists.postgresql.org
Subject: EXTERNAL: Re: Inherited an 18TB DB & need to backup

Hi Christoph
Thats very high on my agenda.. but need to make sure i can backup this beast to start with..
On Fri, 15 May 2020, 17:08 Christoph Berg, <christoph.berg@credativ.de<mailto:christoph.berg@credativ.de>> wrote:
Re: Rory Campbell-Lange

On 15/05/20, Suhail Bamzena (suhailsalem@gmail.com<mailto: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

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:

Thanks Jan.. just to know more is it using the native pg_dump or another
tool like pgbackrest or barman ??

On Fri, 15 May 2020, 17:26 Jan Karremans, <jan.karremans@enterprisedb.com>
wrote:

Hi Suhail,

I do not know personally, but I can figure out / get you setup with someone who can tell you more.

Mit freundlichem Gruß, kind regards,

Jan Karremans
Director of Sales Engineering, EMEA
Senior Sales Engineer DACH-Region
EDB Postgres Advanced Server Professional
-- Postgres Everywhere --

Oracle ACE Alumni

- Deutsch ist nicht meine Muttersprache, bitte verzeihen Sie Verstöße gegen die Rechtschreibung -

Mobile: +31-(0)6-1638 9607

http://www.enterprisedb.com/

Don't walk behind me; I may not lead.
Don't walk in front of me; I may not follow.
Just walk beside me and be my friend.
+*+ Albert Camus +*+

I can look into that.. not very sure abt the storage infra..

On Fri, 15 May 2020, 17:26 Wolff, Ken L, <ken.l.wolff@lmco.com> wrote:

Thanks Jan.. would appreciate all the info I can get.

On Fri, 15 May 2020, 17:32 Jan Karremans, <jan.karremans@enterprisedb.com>
wrote:

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

Yeah Rory want to pull one asap..hopefully by COB tonight..

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

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

Hi Suhail,

We developed Barman (www.pgbarman.org), which works with older versions of
PostgreSQL too. It works with very large databases and is open source (GPL).

This is an old article in which I could publicly mention the size of the
database:

https://www.2ndquadrant.com/en/blog/incremental-backup-barman-1-4-0/

More articles about Barman from our blog:
https://www.2ndquadrant.com/en/blog/tag/barman/

Good luck.

Cheers,
Gabriele

Il giorno ven 15 mag 2020 alle ore 15:49 Ravi Krishna <
srkrishna1@comcast.net> ha scritto:

Thanks Gabriele... i will review this in detail.

On Fri, 15 May 2020, 18:41 Gabriele Bartolini, <gabriele.bartolini@gmail.com>
wrote:

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

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

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?

--
Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors
Volunteer Regional Contact, Germany - PostgreSQL Project

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. If your
business requires you to
store backups for say 7 years, storing it locally will be a problem. In
one large financial
company I use to work, full backup was used to store old data.
(except last 30 days where WAL logs were used for a real PIT). We use
to store full backups
for about 60 days and then send older backup to an off site storage.
Nothing is free.

I remember a case where we were requested by business to restore a db of
a given date two yrs
prior as they had to look at old data. It took us close to 96 hrs to
give the users the required database.

S3 storage is ridiculously cheap. Off site storage companies like Iron
Mountain should find their client base
ditching them big time.

On Fri, May 15, 2020 at 7:52 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. If your
business requires you to
store backups for say 7 years, storing it locally will be a problem. In
one large financial
company I use to work, full backup was used to store old data.
(except last 30 days where WAL logs were used for a real PIT). We use to
store full backups
for about 60 days and then send older backup to an off site storage.
Nothing is free.

I remember a case where we were requested by business to restore a db of a
given date two yrs
prior as they had to look at old data. It took us close to 96 hrs to give
the users the required database.

S3 storage is ridiculously cheap. Off site storage companies like Iron
Mountain should find their client base
ditching them big time.

If your database is running somewhere in the cloud, then yes, that might
make
sense. If your database runs in your own data center, then usually you also
have
disk space available there. Plus a transfer out of your data center will
take time.

There is no "per se" recommendation to move data to S3. And there might be
additional requirements like data protection laws, encryption requirements
ect.

--
Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors
Volunteer Regional Contact, Germany - PostgreSQL Project

Also when you get in the multi TB data storage the bill gets a little
harder to digest in S3.

On Fri, May 15, 2020 at 11:49 Andreas 'ads' Scherbaum <adsmail@wars-nicht.de>
wrote:

On Fri, May 15, 2020 at 7:52 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. If your
business requires you to
store backups for say 7 years, storing it locally will be a problem. In
one large financial
company I use to work, full backup was used to store old data.
(except last 30 days where WAL logs were used for a real PIT). We use to
store full backups
for about 60 days and then send older backup to an off site storage.
Nothing is free.

I remember a case where we were requested by business to restore a db of
a given date two yrs
prior as they had to look at old data. It took us close to 96 hrs to give
the users the required database.

S3 storage is ridiculously cheap. Off site storage companies like Iron
Mountain should find their client base
ditching them big time.

If your database is running somewhere in the cloud, then yes, that might
make
sense. If your database runs in your own data center, then usually you
also have
disk space available there. Plus a transfer out of your data center will
take time.

There is no "per se" recommendation to move data to S3. And there might be
additional requirements like data protection laws, encryption requirements
ect.

--
Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors
Volunteer Regional Contact, Germany - PostgreSQL Project

--
T: @Thaumion
IG: Thaumion
Scottix@Gmail.com