Hacker News Reader

Comment by dezgeg | Hacker News Reader

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.

View on HN

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.

Reply on news.ycombinator.com