Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have a posgresql dumpfile every night and i like to keep every one of them. Each is 14 GB in size, so my backupdrive will be full soon.

The difference from one day to another are only some 100MB. How do i make the daily backups without wasting a lot of space.

PS: i used tar to compress 1 file and the size went down to 5GB. I hoped that when i compress two files the ratio would be better, but no. 2 dumps compressed are 10Gb

share|improve this question

5 Answers 5

I would like to continue on the Tometzyk's mention of rdiff and suggest the backup software rdiff-backup. It keeps a mirror copy of the most recent backup, and saves history as compressed reverse diffs. Those times I've used it on database dumps I've remember have goten away with a quite nice (small) delta. That is, you have your mysqldump create a new file with the same filename each dump, and then you run rdiff-backup on that dump.

rdiff-backup can either be run locally (including towards a network mount) as well as across ssh. Feel free to look at the rdiff-backup example page for a few common usage scenarios.

share|improve this answer

Here's an alternative approach; since your incremental backup size is small, it's likely that only a few MySQL tables are changing regularly, and you're probably backing up a number of large tables that don't change from day to day. If you can figure out whether this is the case, you could back up the entire database on a reduced schedule - maybe once a week - and take more frequent backups of the more volatile tables, using:

mysqldump [database name] [table1 table2 ....]

Here's a link to the mysqldump documentation.

share|improve this answer
    
On this server only ArcGIS with the database is running. Nothing else. And with many users working on it i can't tell which tables are changed and which are not. –  wurlog Apr 12 '10 at 14:18

rdiff from librsync is an opensource program that will compute difference (so called deltas) between any (binary or text) files.

There's also a backup system — duplicity — which uses librsync for efficient storage of backups.

share|improve this answer

There are binary differential prgrams around. Tometzky mentioned rdiff. I've used bsdiff before, but it doesn't work on large files.

Consider running something like LZMA to compress the dump/diff file after you extract it. From my experience most average 3:1 compression (though images and video do worse, text much better, normal stuff). LZMA is considerably better than GZIP and BZIP found in Tar.

share|improve this answer
    
LZMA is considerably better than gzip, but not consistently much better than bzip2, while being considerably slower. Also, I couldn't find a parallel implementation for LZMA, while pbzip2 can use all my cores to speed up the compression process. –  Prof. Moriarty Apr 9 '10 at 16:08
    
While LZMA might not consistently hit the ball out of the park; it's consistently better than bzip2. BZip2 will always be held back by it's use of Huffman coding. Most implementations of LZMA are fairly new, soon enough there will be parallel implementations, give it a little time. There's also a LZMA2 which has a few refinements to the process. Admittedly, there's some room for improvement in compression time; decompression is faster than bzip however. –  Chris S Apr 9 '10 at 23:12

You can archive your files with 7zip utility and pack it as solid archive

share|improve this answer
    
tried that but the pack ratio is nearly the same (3:1) –  wurlog Apr 19 '10 at 14:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.