Re: How to backup a postgreSQL of 80 GByte ?
От | Steve Crawford |
---|---|
Тема | Re: How to backup a postgreSQL of 80 GByte ? |
Дата | |
Msg-id | 20030103221317.D8E8E103D8@polaris.pinpointresearch.com обсуждение исходный текст |
Ответ на | Re: How to backup a postgreSQL of 80 GByte ? ("scott.marlowe" <scott.marlowe@ihs.com>) |
Ответы |
Re: How to backup a postgreSQL of 80 GByte ?
Re: How to backup a postgreSQL of 80 GByte ? |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: