Dear Folks,

I have to backup pgsql database dump everynight on a routine. The database dump actually contains sql(text) statements. The actual size of the database dump is aroung 800 MB. Between two days backup, only few lines of statements are modified/added/deleted.

I dont want to do incremental backup. Instead, I want everyday's full dump to be stored on to a folder named DD-MM-YY(ex: 01-05-08). Everyday there will be a folder created with name DD-MM-YY and the dump will be stored there.

Since the difference between the two day's backup are pretty minimal, I dont want to resend the 800 MB files everyday.

Suppose,

1. I have taken yesterdays dump in a folder named 04-05-08 in database server
2. I have rsynced the folder 04-05-08 to the backup server.
3. I have taken today's dump in folder named 05-05-08 in database server.
4. Now while doing rsync, I want rsync to compare the <database server's 05-05-08/dump file> with the <04-05-08/dump file> which already exists in backup server. (then eventually, I want rsync to send only the differences and store the output in folder 05-05-08).

Typicaly I want to take advantage of yesterday's file which already present in a different folder.

Can anyone suggest a working method to implement this ? (I tried --copy-dest / --compare-dest .. but couldnt get it to work)

Any response in this regard is most appreciated.

Regards,
rssrik

Let's low tech this for a minute and test this by doing the steps manually. I am *assuming* the dump file name is the same everyday, or it has simple enough naming convention that you can make a script to rename a file.

1) You already have rsync'd and you have a folder named 04-05-08 on the backup server. It's now time to get the 05-05-08 version, and magically you know the DB Server is done creating today's dumpfile....
2) On the backup server, cp -rp ./04-05-08 ./05-05-08
-- you now have a new folder to rsync into, and it has yesterday's version of the dump file itself, with yesterday's name and we've preserved the timestamps and permissions on the files.
3) If necessary, rename the ./05-05-08/dumpfile to match today's dump name.
3) start your rsync between the 05-05-08 folders.

To quote the rsync man page:
If any of the files already exist on the remote system then the rsync remote-update protocol is used to update the file by sending only the differences. Your file already exists, so it will run update instead of a copy.

With any luck, the compare will take less time than an actual copy normally does. No matter what, 800MB is a lot to accomplish.


Important Question: Does your pgsql dump actually need to create a NEW 800MB file every day on the DB server, or can it just update the existing dumpfile?

If you can update the same "dump" instance, then I'd just start an rsync of the master dumpfile, and on the backup server just schedule a cp of the the currently rsync'd instance over to the daily folder. Schedule the rsync for every 3 hours, and you have a decent current backup, plus yesterday's copy on the shelf.

Please remember: If you are out of disk space, or your dumpfile failed to be created, it's really hard to finish a backup, so script with that in mind, or confirm something is monitoring your diskspace....

A backup is a terrible thing to lose.