5 ms·

As I read the report I notice a lot of PostgreSQL "backup" systems depend on snapshotting from the FS & Rsync. This may work for database write logs, but it cer

• kentonv 10y ago
  > Git repo corruption from snapshotting tech (tarball, zfs, rsync, etc): The link discusses why rsync and tarballs are not good backup solutions. But, those wouldn't rightly be called "snapshots". "Snapshot" implies atomic, right? Surely an atomic snapshot would not corrupt git -- I would expect git, like any database, is designed to be recoverable after power failure, to which recovering from an atomic snapshot should be equivalent. Or is that not the case?
  • jamesmiller5 10y ago
    That is actually not the case. Say a git server is in the middle of a write to refs/heads/master . You atomically snapshot the FS and then a power outage kills the server. The repository state will have a small chance of lock files that are never removed. Depending on the lock, future writes to a ref or to the repository can fail. Not the worst situation as data lose won't occur but definitely not a stable state. If you treat git repositories as a service that needs a recovery step it would be fine, unfortunately most don't. (edit) source: http://joeyh.name/blog/entry/difficulties_in_backing_up_live_git_repositories/ http://joeyh.name/blog/entry/difficulties_in_backing_up_live...
    • kentonv 10y ago
      OK, from your link, it looks like the only problem affecting atomic snapshots is that `.git/index.lock` could be left in existence which will block git from doing any operations until it is deleted. For some reason git will instruct the user to delete this file but will not do it automatically, even though it could actually check if any live processes have the file locked, and assume that, if not, it is stale. Seems like a bug in git IMO, but still reasonably easy to recover from.
• koolba 10y ago
  I see LVM[1] mentioned in the notes. It allows you to, among other things, snapshot a filesystem atomically which you could then mount read-only to a separate location to read for backups or export to a different environment. That would give you a point in time view of the state of all the repos that should be as consistent as a "stop the world then backup" approach. [1]: https://en.wikipedia.org/wiki/Logical_volume_management https://en.wikipedia.org/wiki/Logical_volume_management
  • rleigh 10y ago
    LVM snapshots the raw block device (logical volume). The filesystem is layered on top of that, and then open and partially written files on top of that. So snapshotting an active database is really not the best idea; it might work, it should work, but it'll need to discard any dirty state from the WAL when you restart it with the snapshot. You might be in for more trouble with other data and applications, depending upon their requirement for consistency. It's definitely not as consistent as "stop the world then backup" because the filesystem is dirty, and the database is dirty. It's equivalent to yanking the power cord from the back of the system, then running fsck, then replaying all the uncommitted transactions from the WAL. It's for this reason that I use ZFS for snapshotting. It guarantees filesystem consistency and data consistency at a given point in time. It'll still need to deal with replaying the WAL, but you don't need to worry about the filesytem being unmountable (it does happen), and you don't need to worry about the snapshot becoming unreadable (once the snapshot LV runs out of space). LVM was neat in the early 2000s, but there are much better solutions today.
    • koolba 10y ago
      > LVM snapshots the raw block device (logical volume). The filesystem is layered on top of that, and then open and partially written files on top of that. So snapshotting an active database is really not the best idea; it might work, it should work, but it'll need to discard any dirty state from the WAL when you restart it with the snapshot. You might be in for more trouble with other data and applications, depending upon their requirement for consistency. > It's definitely not as consistent as "stop the world then backup" because the filesystem is dirty, and the database is dirty. It's equivalent to yanking the power cord from the back of the system, then running fsck, then replaying all the uncommitted transactions from the WAL. I was referring to using LVM to snapshot the filesystem where the git repos are hosted. It'd work for a database as well, assuming your database correctly uses fsync/fdatasync, and for git specifically it works fine. Using LVM snapshots with a journaled filesystem (i.e. any modern/sane choice for a fs) should have no issues though there would be some journal replay at mount time to get things consistent (v.s. say ZFS which wouldn't require it). If it does have issues, you'd have the same issues with the raw device in the event of hard shutdown (ex: power failure).
• marcosdumay 10y ago
  Filesystem snapshots and rsync are between the PostgreSQL backup best practices. Not really for logs, but for stored data. (For logs you don't really need snapshots.) The other good alternative is atomic backups (like with pg_dump), but that does put some extra load on your database that may be unacceptable. Yes, you will want something different for your git repositories. There is no backup procedure that is best for all cases.
• scriptkiddy 10y ago
  Quick question for you since it seems like you are very knowledgeable. I am the sole back end developer for a greenfield web application that is very data heavy. The application is still in alpha at the moment, but part of the development process involved prepopulating the database with about 10 mil rows or so spread out over about 15 tables. Nothing too crazy. However, once the application is launched I expect to have exponential growth in data due to the nature of the application. Currently, this application is set up on Linode. The database server is standalone and I have the ability to spin up multiple application machines and a load balancer. Each of these application machines read from and write to the single database machine. The database machine itself has full disk image backups taken every 24 hours, 7 days, and month. I also do a manual snapshot from time to time. On top of this I also usually dump into a tar file on my own external drive every once and a while as well. I'm fairly new to devops stuff and most of my experience involves building applications and not necessarily deploying them. I'm wondering if what I'm doing as far as backups and stability is enough or if I should be incorporating other methods as well. Have any thoughts?