Is it possible to create differential backups based on LVM snapshots (or maybe a similar technique)?
I'd like to perform backups of my intranet server, which hosts multiple services, and their databases (currently using Postgres 8, Postgres 9 and MySQL). So I thought about setting up log shipping based backups (in addition to backing up the file system), but the whole thing is getting a little bit complex.
As an alternative, I'm considering LVM snapshots - but the databases are not small, and I'll need to save backup space...
It's also important, that LVM snapshots are highly safe to use while a database is constantly writing files.
LVM snapshots are copy-on-write at the block device level. Without any special activity in the filesystem or application they behave the same as a crash-and-reboot. If you can quiesce the application and filesystem and flush any pending writes before taking the snapshot you can greatly increase the consistency of the data in the snapshot. For example take a global write lock in the database, then flush the filesystem and make it filesystem read-only (some filesystems like XFS have an explicit quiesce option), make the snapshot then unlock everything.
As far as backup space, snapshots are copy-on-write so you only need enough space allocated to the snapshot to cover the number of blocks which are overwritten on the live filesystem during the lifetime of the snapshot.
The answer (for me) is simply to create a temporary LVM snapshot, use any incremental backup technique on it, and then discard the snapshot.
As for the safety of LVM snapshots (which I usually have to take while the DB is running), I'm pretty confident that they will work fine. To cover the residual risk, I'm shutting down the database once per week before taking a full backup.