On Monday 05 February 2007 20:19, Darien Hager wrote: > 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.) You may want to take a look at Slony here : if I remember correctly it can generate 'logs' of SQL statement to replay the changes on the database > > * 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...
I think it's probably the best way to do it (it's the one I use :) ): - The backups aren't going through the whole DB stack, it's just a regular file backup, so it's very fast (near disk speed) - The archived WAL recovery is quite fast, depending on how much 'replay' you have to do, and you have a great deal of flexibility (you can choose exactly to what point in time you want to recover) - It's at its best coupled with LVM snapshot, if you're using linux... The main problems are that you have to recover a full postgresql cluster, so each client should have it's dedicated cluster, if you want to recover them separately, and that the online backup is much bigger than a compressed pg_dump (you backup indexes, and all the overhead from the table structures) Anyhow, you should have a look at the 8.2 new features on this ... > > * Use pg_dump's "-Ft" option and somehow break down the problem via > the the ####.dat files it creates in the TAR. It's not incremental ... and slower than the 'online backup' ------------------------------------------------------------------------- Using Tomcat but need to do more? Need to support web services, security? Get stuff done quickly with pre-integrated technology to make your job easier. Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642 _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
