[Bacula-users] Database Diff/Incremental Backups

[Darien H. <da...@et...>]

I'm wondering if anyone has advice for backing up databases. I have  
some python scripts working to do per-database backup/restore over  
FIFOs (pg_dump, pg_restore, nonblocking fifo polling), but the nature  
of the method means that there is almost no such thing as a  
differential or incremental backup--it's all full dumps.

My goal is to be able to satisfy customers who say "We want our  
website like it was yesterday" in an efficient manner, while some of  
our servers have very large databases. Ideas I've been kicking around:

* Grab the full database over a only once every week or two, and on  
intervening days grab a diff/patch of the plaintext SQL instead.  
(Which of course makes all my FIFO work moot, since we'd need to keep  
a full dump present on the disk for daily textual diff'ing.)

* Use PostGresql's recovery method here: http://www.postgresql.org/ 
docs/8.1/interactive/backup-online.html , along with a VMWare server  
to do the restoring on, and then use THAT to generate whatever output  
we might need...

* Use pg_dump's "-Ft" option and somehow break down the problem via  
the the ####.dat files it creates in the TAR.

By the way, if anyone else wants to do dump or restore potentially  
hundreds of individual databases over individual FIFOs without  
exhausting a connection pool, let me know--I figure my past suffering  
should help humanity somehow.

Thanks,
--
--Darien A. Hager
da...@et...