Inherited an 18TB DB & need to backup
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. -- Cheers, Suhail Cell# +97150 8194870
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, wrote: > Re: Rory Campbell-Lange > > On 15/05/20, Suhail Bamzena (suhailsa...@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 >
Re: Inherited an 18TB DB & need to backup
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, wrote: > On 15/05/20, Suhail Bamzena (suhailsa...@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: Inherited an 18TB DB & need to backup
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, 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 +*+ > > Op 15 mei 2020, om 15:23 heeft Suhail Bamzena het > volgende geschreven: > > 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, > wrote: > >> Re: Rory Campbell-Lange >> > On 15/05/20, Suhail Bamzena (suhailsa...@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 >> > >
Re: Inherited an 18TB DB & need to backup
I can look into that.. not very sure abt the storage infra.. On Fri, 15 May 2020, 17:26 Wolff, Ken L, wrote: > 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 > *Sent:* Friday, May 15, 2020 7:23 AM > *To:* Christoph Berg > *Cc:* Rory Campbell-Lange ; > pgsql-general@lists.postgresql.org; pgeu-gene...@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, > wrote: > > Re: Rory Campbell-Lange > > On 15/05/20, Suhail Bamzena (suhailsa...@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 > >
Re: Inherited an 18TB DB & need to backup
Thanks Jan.. would appreciate all the info I can get. On Fri, 15 May 2020, 17:32 Jan Karremans, 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 +*+ > > Op 15 mei 2020, om 15:31 heeft Suhail Bamzena het > volgende geschreven: > > 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, > 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 +*+ >> >> Op 15 mei 2020, om 15:23 heeft Suhail Bamzena >> het volgende geschreven: >> >> 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, >> wrote: >> >>> Re: Rory Campbell-Lange >>> > On 15/05/20, Suhail Bamzena (suhailsa...@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 >>> >> >> > > >
Re: Inherited an 18TB DB & need to backup
Yeah Rory want to pull one asap..hopefully by COB tonight.. On Fri, 15 May 2020, 17:35 Rory Campbell-Lange, wrote: > On 15/05/20, Suhail Bamzena (suhailsa...@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 > >
Re: Inherited an 18TB DB & need to backup
Thanks Gabriele... i will review this in detail. On Fri, 15 May 2020, 18:41 Gabriele Bartolini, wrote: > 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 < > srkrish...@comcast.net> ha scritto: > >> IMO a database of this size should only be backed up in s3. pgbackrest >> has support for backup to s3. >> >
Re: Inherited an 18TB DB & need to backup
Thanks Ron.. pgbackrest and barman seem to b good options.. On Sat, 16 May 2020, 02:26 Ron, wrote: > 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.) > > On 5/15/20 8:26 AM, Suhail Bamzena 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... > > On Fri, 15 May 2020, 17:02 Rory Campbell-Lange, > wrote: > >> On 15/05/20, Suhail Bamzena (suhailsa...@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 >> > > -- > Angular momentum makes the world go 'round. >
Re: Inherited an 18TB DB & need to backup
Hi Peter Thanks for the info & the entire forum for their inputs i did fireup a pg_dump last night pairing it with gzip & split it to 1TB size.. will let you all know how it goes. On Sat, 16 May 2020, 18:12 Peter J. Holzer, wrote: > On 2020-05-15 14:02:46 +0100, Rory Campbell-Lange wrote: > > On 15/05/20, Suhail Bamzena (suhailsa...@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. > |_|_) || > | | | h...@hjp.at |-- Charles Stross, "Creative writing > __/ | http://www.hjp.at/ | challenge!" >
Re: Inherited an 18TB DB & need to backup
Thanks James... that is reassuring, will be working on it this week. On Mon, 18 May 2020, 04:33 James Sewell, wrote: > > > On Fri, 15 May 2020 at 17:09, Suhail Bamzena > 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. >> >> > Just chiming in for pgbackrest - we are backing up multiple databases in > the 50TB - 150TB range across multiple customers and it works very well > (and most importantly at that scale, very efficiently). > > I've found the team really responsive to issues / bugs / errors reported > via GitHub when we hit them, which is always a bonus. > > James Sewell, > *Chief Architect, Jirotech* > Suite 46, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009 > *P *(+61) 2 8099 9000 <(+61)%202%208099%209000> *W* www.jirotech.com > *F *(+61) 2 8099 9099 <(+61)%202%208099%209000> > > > -- > 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.
