Re: How to backup a postgreSQL of 80 GByte ?

On Friday 03 January 2003 9:57 am, scott.marlowe wrote:
> On Mon, 23 Dec 2002, Michelle Konzack wrote:
> > Oops... and Hello,
> >
> > Am 11:39 2002-12-23 +0100 hat SZUCS Gábor geschrieben:
> > >Call me suspicious, but 830 THOUSAND millions of tables in 80Gbyte is a
> >
> > 8-O ...a little BIG error... they are ONLY 830.000 tables ! :-P
> >
> > >As for the hardware, I agree with Scott regarding tapes. The typical
> > > thing to backup/archive. As for its speed-- well, let's hope you won't
> > > have to restore from it ;)
> >
> > Curently I am using the Software RAID-1 with two Harddrives...
> > Maybe I will switch to RAID-5 with 3 Drives.
> >
> > >As for compression, bzip2 is really much better on text files. Also
> > > don't forget the "-9" flag (though I think this should be the default).
> >
> > But HOW do you backup 80 GBytes ???
> >
> > OK, I have three drives and the first drive is a normal 120 GByte HD,
> > on which I have the OS and a around 120 Gbyte TEMP Partition !!!
> >
> > If I try to 'gzip' or 'bzip2' the 80 GBytes, how many days need it ???
>
> If you have a VXA2 drive (80 gig native, 160 gig compressed) then you
> don't do anything of the sort.  Those drives achieve a respectable amount
> of compression on database backups, and pre-compressing your data means
> the compression in the drive hasn't got much left to work with.
>
> So, just dump it straight to tape.
>
> mt density 0x00
> mt rewind
> pg_dump -options here database_name >/dev/nst0
>
> > OK, I use the HP Surstore 12000e with 16 GByte Tapes...
>
> That's way too small.  You can get a VXA2 drive for <$1000 and while not
> quite as fast as the monster mammoth from exabyte, it is 1/3 the cost.
>
> Plus, with it's packet writing scheme, you almost can't get an error on
> restore.  Great tape drives, I'm glad exabyte bought them out so they
> didn't disappear due to being underfunded/marketed.

I'll second the vote for the VXA2 - nice little drive. As to compression this
is a big case of "your mileage may vary". I did a test dump of a 1.8 million
record table (routing output to wc to get file sizes which I determined is
only a tiny bit slower than to /dev/null and which also correlate nicely with
a couple tests to the VXA drive):
As full SQL inserts (-d flag): 7:15, size 484MB
Same, with gzip: 9:08, size 51.8MB
As default copy (without -d flag): 3:23, size 333.8MB
Same, with gzip: 4:21, size 47.8MB

So, assuming the margarita grande at lunch hasn't killed my ability to do
math, the highest data rate (copy, no compression) is ~1.7MB/second which is
well under the ~6MB/second native speed of the VXA2. So if speed is paramount
then don't compress but if you need to fit lots on a tape then do
(compression is from 7:1 to 9:1 in these tests or 3.5 to 4.5 times better
than the best hardware compression on the tape drive and would allow you to
fit over 1/2 terabyte on a tape).

Using the -9 flag with gzip gave me a very modest improvement in compression
(down to 48.8 from 51.8MB or 9.9:1 instead of 9:1) but the gzip CPU cost even
for this relatively small dump tripled to 3 minutes from 1 minute.

So it looks like you will need lots faster hardware than the desktop I used
for these tests (850MHz, single IDE...) or more data than will fit on a tape
before compression will be of any use (unless you want to fit that 80GB onto
a single 16GB surestore tape - the compression overhead is not that terrible).

Note, you may want to experiment with piping your backups through a program
like "buffer" (http://www.microwerks.net/~hugo/download/buffer-1.19.tgz) or
one of the many similar buffering programs - your backup speed may not
improve at all or it may increase by a factor of 2-3. You will have to test
to tell.

There is nothing that makes this look terribly undoable - even using my
non-server-grade desktop I could dump this database to one tape in 13 hours.

Cheers,
Steve