4 ms·

The thing that always gets me about backup consistency is that it's impossibly difficult to ensure that application data is in a consistent state without bringi

by kayson 1y ago

The thing that always gets me about backup consistency is that it's impossibly difficult to ensure that application data is in a consistent state without bringing everything down. You can create a disk snapshot, but there's no guarantee that some service isn't mid-write or mid-procedure at the point of the snapshot. So if you were to restore the backup from the snapshot you would encounter some kind of corruption. Database dumps help with this, to a large extent, especially if the application itself is making the dumps at an appropriate time. But often you have to make the dump outside the application, meaning you could hit it in the middle of a sequence of queries. Curious if anyone has useful tips for dealing with this.

booi 1y ago
I think generally speaking, databases are resilient to this so taking a snapshot of the disk at any point is sufficient as a backup. The only danger is if you're using some sort of on-controller disk cache with no battery backup, then basically you're lying to the database about what has flushed and there can be inconsistencies on "power failure" (i.e. live snapshot). But for the most part as especially in the cloud, this shouldn't be an issue.
- immibis 1y ago
  Beware that although databases are resilient to snapshotting, they're not resilient to inconsistent snapshots. All files have to be snapshotted at the exact same moment, which means either a filesystem-level or disk-level snapshot, or SIGSTOP all database processes before doing your recursive copy or rsync. Some databases have the ability to stop writing and hold all changes in memory (or only append to WAL, which is recursive-copy-safe) while you tell it you're doing a backup.
9337throwaway 1y ago
[dead]
Jedd 1y ago
It's not clear if there are other places that application state is being stored, outside your database, that you need to capture. Do you mean things like caches? (I'd hope not.) pg_dump / mysqldump both solve the problem of snapshotting your live database safely, but can introduce some bloat / overhead you may have to deal with somehow. All pretty well documented and understood though. For larger postgresql databases I've sometimes adopted the other common pattern of a read-only replica dedicated for backups: you pause replication, run the dump against that backup instance (where you're less concerned about how long that takes, and what cruft it leaves behind that'll need subsequent vacuuming) and then bring replication back.

by kayson 1y ago

booi 1y ago

I think generally speaking, databases are resilient to this so taking a snapshot of the disk at any point is sufficient as a backup. The only danger is if you're using some sort of on-controller disk cache with no battery backup, then basically you're lying to the database about what has flushed and there can be inconsistencies on "power failure" (i.e. live snapshot). But for the most part as especially in the cloud, this shouldn't be an issue.

immibis 1y ago
Beware that although databases are resilient to snapshotting, they're not resilient to inconsistent snapshots. All files have to be snapshotted at the exact same moment, which means either a filesystem-level or disk-level snapshot, or SIGSTOP all database processes before doing your recursive copy or rsync. Some databases have the ability to stop writing and hold all changes in memory (or only append to WAL, which is recursive-copy-safe) while you tell it you're doing a backup.

9337throwaway 1y ago

[dead]

Jedd 1y ago

It's not clear if there are other places that application state is being stored, outside your database, that you need to capture. Do you mean things like caches? (I'd hope not.) pg_dump / mysqldump both solve the problem of snapshotting your live database safely, but can introduce some bloat / overhead you may have to deal with somehow. All pretty well documented and understood though. For larger postgresql databases I've sometimes adopted the other common pattern of a read-only replica dedicated for backups: you pause replication, run the dump against that backup instance (where you're less concerned about how long that takes, and what cruft it leaves behind that'll need subsequent vacuuming) and then bring replication back.