7 ms·

The issue with using a setup like this is that you run the risk of having inconsistent snapshots, where several related files were snapshotted at different poin

by cyphar 10y ago

The issue with using a setup like this is that you run the risk of having inconsistent snapshots, where several related files were snapshotted at different points in time. I'm not sure how this lash-up works with something like a database, where you have to have a consistent copy of a single file as well. If you use Btrfs (or ZFS if you're on FreeBSD or illumos) then you can have proper filesystem snapshots with essentially no overhead. Since both filesystems are transactional, you're guaranteed to get a snapshot of a single transaction (resulting in no inconsistency). Unfortunately due to partial writes in database files, Btrfs has some performance issues when having copy-on-write enabled (which is required if you want snapshots) for a database file.

fl0wenol 10y ago
If you release your snapshot used for mirroring via rsync right away as part of the cron job, and only take it and maintain it in CoW mode during off-peak hours, then it's usually a good compromise for getting something crash consistent.
inopinatus 10y ago
Or you can just put your database in backup mode. We do not need a perfectly consistent file system backup as the starting point. Any internal inconsistency in the backup will be corrected by log replay. - https://www.postgresql.org/docs/current/static/continuous-archiving.html https://www.postgresql.org/docs/current/static/continuous-ar...
reidrac 10y ago
You're right, but you can use LVM to solve that problem: make an LVM snapshot, run your backup solution (eg, rsnapshot) on the LVM snapshot, remove the LVM snapshot. You still have the problem of caches and transient data, but all files will share the same point in time. In my experience LVM snapshot performance is good, you can even make it small and let it grow to track the changes while you backup is running. EDIT: actually rsnapshot has LVM snapshot support (since version 1.3.1 from Aug 31, 2008), so there you are.
- cyphar 10y ago
  From what I heard, LVM snapshots have significant overhead unless you delete them. Btrfs snapshots don't have such a limitation.
kijin 10y ago
You can't use rsync, rsnapshot, or any other tool that manipulates files and/or filesystems, to get a consistent backup of most databases. Databases come with their own tools and/or commands for creating a consistent "dump". Nothing else is guaranteed to be safe. I use mysqldump/pg_dump/etc. to create database dumps and copy them to backup storage, and rsync/rsnapshot to back up the app itself. There is no need to use a single tool to back up everything. If your app is well designed, there's not even any need to back up the database and the filesystem simultaneously. Even SQLite has a .dump command.
- vacri 10y ago
  > Nothing else is guaranteed to be safe. Well, stopping the database generally allows you to be able to back up static files.
- koolba 10y ago
  > You can't use rsync, rsnapshot, or any other tool that manipulates files and/or filesystems, to get a consistent backup of most databases. Sure you can and if you can't, then get a better database! A well functioning database should be able to deal with being inconsistently shutdown. If you can't take a snapshot of the disk then what's the difference between that and the power cord being pulled? The main reason not to do this on a DB server is that the recovery time may large. That doesn't mean it can't be done though. > Databases come with their own tools and/or commands for creating a consistent "dump". Nothing else is guaranteed to be safe. Sure they do and you should use those as well, that doesn't mean you can't do this too.
  - cyphar 10y ago
    The issue is not that you have an atomic copy of the state at a certain point in time, which in a bad database couldn't be loaded. The problem is that when you make a full copy of a file you are copying linearly and if it's a big file a chunk could change while you're reading it (or multiple related chunks which you are in the middle of copying) -- then you have a very inconsistent state which a database can't recover from no matter if their updates are atomically written. The database could do some magic with unlinking, but that would cause massive performance problems.
Daviey 10y ago
I don't follow.... rsnapshot does give you entire folders of the state of the source at time of backup. It makes efficient use of space by using hardlinks for identical files, so only the delta takes up more space.
- cyphar 10y ago
  If your tool is copying a huge directory. It's half-way done and then a file changes in the half not yet copied (as well as a file in the half you did copy). Now your directory is in an inconsistent state: the "snapshot" doesn't show a valid state of the directory at any point in time. Unfortunately the only way to practically fix this is to have a filesystem that supports snapshots (luckily you have btrfs, which does). In addition, btrfs send-recieve sends deltas of a transaction history so you have very little storage and network overhead as a result (and snapshots are actually even cheaper than how rsync uses hard links because rather than having a hard link for every file you essentially have a "hard link" to the state of the filesystem at that time (the transaction)).

by cyphar 10y ago

fl0wenol 10y ago

If you release your snapshot used for mirroring via rsync right away as part of the cron job, and only take it and maintain it in CoW mode during off-peak hours, then it's usually a good compromise for getting something crash consistent.

inopinatus 10y ago

Or you can just put your database in backup mode. We do not need a perfectly consistent file system backup as the starting point. Any internal inconsistency in the backup will be corrected by log replay. - https://www.postgresql.org/docs/current/static/continuous-archiving.html https://www.postgresql.org/docs/current/static/continuous-ar...

reidrac 10y ago

You're right, but you can use LVM to solve that problem: make an LVM snapshot, run your backup solution (eg, rsnapshot) on the LVM snapshot, remove the LVM snapshot. You still have the problem of caches and transient data, but all files will share the same point in time. In my experience LVM snapshot performance is good, you can even make it small and let it grow to track the changes while you backup is running. EDIT: actually rsnapshot has LVM snapshot support (since version 1.3.1 from Aug 31, 2008), so there you are.

cyphar 10y ago
From what I heard, LVM snapshots have significant overhead unless you delete them. Btrfs snapshots don't have such a limitation.

kijin 10y ago

You can't use rsync, rsnapshot, or any other tool that manipulates files and/or filesystems, to get a consistent backup of most databases. Databases come with their own tools and/or commands for creating a consistent "dump". Nothing else is guaranteed to be safe. I use mysqldump/pg_dump/etc. to create database dumps and copy them to backup storage, and rsync/rsnapshot to back up the app itself. There is no need to use a single tool to back up everything. If your app is well designed, there's not even any need to back up the database and the filesystem simultaneously. Even SQLite has a .dump command.

vacri 10y ago
> Nothing else is guaranteed to be safe. Well, stopping the database generally allows you to be able to back up static files.
koolba 10y ago
> You can't use rsync, rsnapshot, or any other tool that manipulates files and/or filesystems, to get a consistent backup of most databases. Sure you can and if you can't, then get a better database! A well functioning database should be able to deal with being inconsistently shutdown. If you can't take a snapshot of the disk then what's the difference between that and the power cord being pulled? The main reason not to do this on a DB server is that the recovery time may large. That doesn't mean it can't be done though. > Databases come with their own tools and/or commands for creating a consistent "dump". Nothing else is guaranteed to be safe. Sure they do and you should use those as well, that doesn't mean you can't do this too.
- cyphar 10y ago
  The issue is not that you have an atomic copy of the state at a certain point in time, which in a bad database couldn't be loaded. The problem is that when you make a full copy of a file you are copying linearly and if it's a big file a chunk could change while you're reading it (or multiple related chunks which you are in the middle of copying) -- then you have a very inconsistent state which a database can't recover from no matter if their updates are atomically written. The database could do some magic with unlinking, but that would cause massive performance problems.

Daviey 10y ago

I don't follow.... rsnapshot does give you entire folders of the state of the source at time of backup. It makes efficient use of space by using hardlinks for identical files, so only the delta takes up more space.

cyphar 10y ago
If your tool is copying a huge directory. It's half-way done and then a file changes in the half not yet copied (as well as a file in the half you did copy). Now your directory is in an inconsistent state: the "snapshot" doesn't show a valid state of the directory at any point in time. Unfortunately the only way to practically fix this is to have a filesystem that supports snapshots (luckily you have btrfs, which does). In addition, btrfs send-recieve sends deltas of a transaction history so you have very little storage and network overhead as a result (and snapshots are actually even cheaper than how rsync uses hard links because rather than having a hard link for every file you essentially have a "hard link" to the state of the filesystem at that time (the transaction)).