Hacker News Reader

Comment by wongarsu | Hacker News Reader

when doing backups, managing file permissions, etc, it's really convenient to only have a fixed number of known files to worry about.

Switching to WAL already makes handling Sqlite databases much less convenient, since you now have three files instead of one, and need a filesystem snapshotting mechanism to reliably back them up (so you don't have one state in the database and another in the wal). Making the filenames and number of files less predictable would make that mode not worth it for many use cases

View on HN

You can't use a filesystem snapshotting mechanism... Even when not using wal mode, you can't just cp the .db file somewhere (in some circumstances, yes).

Instead, sqlite provides an online backup api specifically for creating backups. This also takes wal mode into account.

Even the mechanism predating WALv1 (rollback journal) uses two files. I don't think there is any way of using SQLite crash-proof with just a single file.

Besides, even if the database is single-file it's still necessary to use filesystem snapshotting for live backup, or it's likely to get an inconsistent copy.

With smallish databases, just pipe the .dump command to gzip. No need to snapshot an entire filesystem just to back up a few hundred megabytes (what I would consider "smallish"). Backup and restore speeds aren't a significant concern at those sizes, anyway, and you get human-readable backups that can be inspected with standard Unix tools.

We would back up double-digit GB MySQL databases by piping mysqldump into gzip as well. Like you I’m sure there’s a size limit where that doesn’t work. I never found out if it was CPU or memory constrained though.

Where I’m at, the DBAs shifted from pipes to temporary files. They said at the double-digit TB size, it would occasionally fail otherwise. This was with Postgres / barman / pigz, FWIW.

Never saw it myself, so I have no idea what the cause was.

I've used mysqldump|gzip when migrating a ~1TB database to an incompatible version of MySQL/MariaDB. It's slow but very reliable. I think the single-threaded pipe is the bottleneck. The process never took up more than one full CPU core.

Or use "VACUUM INTO" to get a backup that is is even faster (and easier) to restore.

> need a filesystem snapshotting mechanism to reliably back them up (so you don't have one state in the database and another in the wal)

VACUUM INTO?

Pretty much every journaling mechanism requires snapshots for reliable backups. This includes either of SQLite's modes (undo journal and WAL).

Reply on news.ycombinator.com