4 ms·

Yes, if they have ZFS too, it is basically snapshot, send & receive. Or you can clone it and rsync it to them. Do not try to snapshot a running database though

by olavgg 10y ago

Yes, if they have ZFS too, it is basically snapshot, send & receive. Or you can clone it and rsync it to them. Do not try to snapshot a running database though. Before you execute the snapshot you should make sure everything is flushed to disk. For example with PostgreSQL you need to create a checkpoint before the snapshot, so you write: SELECT pg_start_backup('prepare_my_snapshot'); AND when the snapshot is done: SELECT pg_stop_backup();

db48x 10y ago
It's not so critical as that. Any transactions in flight when you take the snapshot won't be finalized and will be discarded when you start it back up from the snapshot, but that won't affect the consistency of the database.
Bromskloss 10y ago
> Do not try to snapshot a running database though. Before you execute the snapshot you should make sure everything is flushed to disk. Is a database (or anything else) really supposed to behave that way? Shouldn't every state along the way be possible to resume from? In particular, shouldn't database transactions take care of this?
- amelius 10y ago
  If the snapshot is atomic as it should be, then yes.
- shanemhansen 10y ago
  A database should handle a snapshot gracefully. I think you should only expect corruption if you are taking a non-atomic backup with something like tar.
- majewsky 10y ago
  Yes, it is. One of the fundamental principles of practical RDBMS is durability (the D in https://en.wikipedia.org/wiki/ACID https://en.wikipedia.org/wiki/ACID), meaning that when the DB acknowledges a commit to the client, it must be guaranteed that the transaction has been reflected in permanent storage (i.e., on disk). The fine print: Due to the architecture of file system access in contemporary kernels (esp. file system caches), it's next to impossible for a user-space application to actually guarantee that writes are durable (i.e. synced to disk at a defined point in time), but most RDBMS manage well enough in practice.
  - cyphar 10y ago
    > The fine print: Due to the architecture of file system access in contemporary kernels (esp. file system caches), it's next to impossible for a user-space application to actually guarantee that writes are durable (i.e. synced to disk at a defined point in time), but most RDBMS manage well enough in practice. While this doesn't relate to a specific file, you can always look at the size of Dirty and Writeback in /proc/meminfo. They correspond to the amount of dirty file page caches that have yet to be synced.
- hodgesrm 10y ago
  Maybe. RDBMS should be able to recover from any crash consistent copy of storage, where crash consistency means the storage represents a state of the system at run time. FS snapshots provide this property for single volumes. The problem is that production RDBMS tend to span multiple volumes, e.g., for logs vs. data to pick a simple case. FS snapshots in this case may not be consistent which would cause recovery to fail. AFAIK this is why even robust RDBMS like MS SQL server use mechanisms like VSS to ensure data are flushed, which guarantees not just crash consistency but application consistency.
xenophonf 10y ago
It's too bad Unix/Linux doesn't offer something like Windows' VSS API, where the operating system can inform registered applications or services of an impending backup operation.
- greedo 10y ago
  It's too bad that VSS is unreliable. If I had a nickel for everytime one of my backups failed due to a VSS writer issue, I'd own Symantec AND EMC. This has been across Win2k/2003/2008. We've been using 2012 for the last year (yeah, old stodgy corporations move slowly), and I haven't seen the same number of errors, but we're also not doing as many client based backups either.
  - olavgg 10y ago
    Have you guys thought about giving Windows ZFS volumes over iSCSI and then take ZFS snapshots? For example with SQL Server, it should be possible to talk to the SQL Writer Service and create a consistent database snapshot of a live SQL Server database with ZFS. When this snapshot is taken you can send & recv the diff to a remote backup server.
    - greedo 10y ago
      Our experience with iSCSI hasn't been very good; its use is limited to some dev ESX systems. Our DBAs prefer to perform cold backups that we backup with traditional tools (Avamar).
    - ryao 10y ago
      I believe that SoftNAS is able to do that when Windows is using Samba shares hosted on ZFS.
      - leostrat 10y ago
        You're right. Here's more info about it: https://www.softnas.com/docs/softnas/v3/html-reference-guide/configuring_cifs_shares.html https://www.softnas.com/docs/softnas/v3/html-reference-guide...
ryao 10y ago
If it is the same pool, you can just do a clone. Send/recv is completely unnecessary there. As for flushing the database first, it is unnecessary as long as the database is ACID compliant. If you use MySQL, you will probably want to freeze and flush the database first to be absolutely certain of safety. DDL statements and a few minor other things in MySQL are known not to be ACID compliant.

by olavgg 10y ago

db48x 10y ago

It's not so critical as that. Any transactions in flight when you take the snapshot won't be finalized and will be discarded when you start it back up from the snapshot, but that won't affect the consistency of the database.

Bromskloss 10y ago

> Do not try to snapshot a running database though. Before you execute the snapshot you should make sure everything is flushed to disk. Is a database (or anything else) really supposed to behave that way? Shouldn't every state along the way be possible to resume from? In particular, shouldn't database transactions take care of this?

amelius 10y ago
If the snapshot is atomic as it should be, then yes.
shanemhansen 10y ago
A database should handle a snapshot gracefully. I think you should only expect corruption if you are taking a non-atomic backup with something like tar.
majewsky 10y ago
Yes, it is. One of the fundamental principles of practical RDBMS is durability (the D in https://en.wikipedia.org/wiki/ACID https://en.wikipedia.org/wiki/ACID), meaning that when the DB acknowledges a commit to the client, it must be guaranteed that the transaction has been reflected in permanent storage (i.e., on disk). The fine print: Due to the architecture of file system access in contemporary kernels (esp. file system caches), it's next to impossible for a user-space application to actually guarantee that writes are durable (i.e. synced to disk at a defined point in time), but most RDBMS manage well enough in practice.
- cyphar 10y ago
  > The fine print: Due to the architecture of file system access in contemporary kernels (esp. file system caches), it's next to impossible for a user-space application to actually guarantee that writes are durable (i.e. synced to disk at a defined point in time), but most RDBMS manage well enough in practice. While this doesn't relate to a specific file, you can always look at the size of Dirty and Writeback in /proc/meminfo. They correspond to the amount of dirty file page caches that have yet to be synced.
hodgesrm 10y ago
Maybe. RDBMS should be able to recover from any crash consistent copy of storage, where crash consistency means the storage represents a state of the system at run time. FS snapshots provide this property for single volumes. The problem is that production RDBMS tend to span multiple volumes, e.g., for logs vs. data to pick a simple case. FS snapshots in this case may not be consistent which would cause recovery to fail. AFAIK this is why even robust RDBMS like MS SQL server use mechanisms like VSS to ensure data are flushed, which guarantees not just crash consistency but application consistency.

xenophonf 10y ago

It's too bad Unix/Linux doesn't offer something like Windows' VSS API, where the operating system can inform registered applications or services of an impending backup operation.

greedo 10y ago
It's too bad that VSS is unreliable. If I had a nickel for everytime one of my backups failed due to a VSS writer issue, I'd own Symantec AND EMC. This has been across Win2k/2003/2008. We've been using 2012 for the last year (yeah, old stodgy corporations move slowly), and I haven't seen the same number of errors, but we're also not doing as many client based backups either.
- olavgg 10y ago
  Have you guys thought about giving Windows ZFS volumes over iSCSI and then take ZFS snapshots? For example with SQL Server, it should be possible to talk to the SQL Writer Service and create a consistent database snapshot of a live SQL Server database with ZFS. When this snapshot is taken you can send & recv the diff to a remote backup server.
  - greedo 10y ago
    Our experience with iSCSI hasn't been very good; its use is limited to some dev ESX systems. Our DBAs prefer to perform cold backups that we backup with traditional tools (Avamar).
  - ryao 10y ago
    I believe that SoftNAS is able to do that when Windows is using Samba shares hosted on ZFS.
    - leostrat 10y ago
      You're right. Here's more info about it: https://www.softnas.com/docs/softnas/v3/html-reference-guide/configuring_cifs_shares.html https://www.softnas.com/docs/softnas/v3/html-reference-guide...

ryao 10y ago

If it is the same pool, you can just do a clone. Send/recv is completely unnecessary there. As for flushing the database first, it is unnecessary as long as the database is ACID compliant. If you use MySQL, you will probably want to freeze and flush the database first to be absolutely certain of safety. DDL statements and a few minor other things in MySQL are known not to be ACID compliant.