How to backup a postgreSQL of 80 GByte ? 
Author Message
 How to backup a postgreSQL of 80 GByte ?

Oops... and Hello,=20

Am 11:39 2002-12-23 +0100 hat SZUCS G=E1bor geschrieben:

Quote:

>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

Quote:
>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.

Quote:
>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 ???=20

OK, I have three drives and the first drive is a normal 120 GByte HD,=20
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 ???=20

It is a Productionssystem and will go online in Paris at amen.fr next=20
year. I think, 'bzip2' nos no solution...=20

Never I have tried it. - I think, it is amegalomania !=20

OK, I use the HP Surstore 12000e with 16 GByte Tapes...=20

Thanks
Michelle

---------------------------(end of broadcast)---------------------------



Wed, 22 Jun 2005 01:33:10 GMT
 How to backup a postgreSQL of 80 GByte ?

Quote:

> Oops... and Hello,

> Am 11:39 2002-12-23 +0100 hat SZUCS Gbor 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

Quote:
> 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.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



Wed, 22 Jun 2005 02:08:07 GMT
 How to backup a postgreSQL of 80 GByte ?

Quote:

> > Oops... and Hello,

> > Am 11:39 2002-12-23 +0100 hat SZUCS Gbor 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly



Wed, 22 Jun 2005 06:13:26 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. How to backup a postgreSQL of 80 GByte ?

2. How to backup a postgreSQL of 80 GByte ?

3. How to backup a postgreSQL of 80 GByte ?

4. msolap 80 dll installation problem

5. Create an alert when transaction log is 80% full

6. Web based reporting question - ability to operate with DHTML over port 80

7. Empty Strings : sp_dbcmptlevel 65/80

8. Calculating the 80 % of sales dynamically

9. Port 80 connectivity

10. Upgrading 70 to 80 in Win2K

11. MS SQL Server 2000.80.194.00

12. empty string converting to 1900/1/1 (SQL 80)


 
Powered by phpBB® Forum Software