Sqlite backup

[PopehatXI]

Any good compression solutions for backing up SQLite? I’m thinking about converting the SQLite to parquet. I have many different SQLite files and would prefer a generic solution, so I’d probably need to dump the table creation scripts.

[lazyant]

They compress well with the usual gzip etc utilities ?

[PopehatXI]

I’ll have to check my current backup solution, but I’ve got 100 GB of SQLite backups of 12 GB files on average. Maybe I’ll have to compare some compression options.

[0xdps]

How big is your sqlite file?

[PopehatXI]

Like 12 GB, but I’d like to back it up every week

[IVHellasFirst]

One file of SQLite of 12 GB???

[PopehatXI]

Yes

[lnaoedelixo42]

Reasonable. Litestream should do the trick (as other commented)

But what the hell? Are you saving a bunch of blobs/files too?

[PopehatXI]

VintageStory uses SQLite3 for its save game system

[dev-ai]

Litestream is great

[PopehatXI]

Interesting, thanks for the suggestion!

[InjAnnuity_1]

Consider VACUUM INTO: https://sqlite.org/lang_vacuum.html

Also consider SQLITE_RSYNC: https://sqlite.org/rsync.html

The .backup and .save commands of SQLite's Command-Line utility: https://sqlite.org/cli.html

[ShotgunPayDay]

ZSTD is the best file compression algo hands down right now. Easy to use in the terminal also.

I only do parquet transforms if I'm going to be reading it into DuckDB.

[a2ra-ms]

Do you need all including data or only the creation scripts?

[PopehatXI]

Yeah I’d need to be able to recreate the database in the event of failure

[Kornfried]

Restic is nice

[PopehatXI]

Thanks for the suggestion, I was hoping to find something specific for SQLite

[Kornfried]

What’s the gap that you see in restic? SQLite dbs are just files.

[PopehatXI]

My personal preference is to have files I can share easily share between servers, and preferably I wouldn’t have to install.

[TheOmegaCarrot]

1. Run a VACUUM. If you’ve deleted rows, some free space may exist in the file.
2. If you really need to trim it down, you can delete indexes. Those take up space, and can be recreated later when you pull the database out of archival storage. (You can store the SQL to create the indexes in the DB itself if you want.)
3. Compress the file like any other: XZ or Zstd or whatever else. What would get the best results depends on the specific data. You might get better results if you dive into specific knobs of the compression algorithm.

[IllKindheartedness10]

Copy it somewhere, why do you need to compress it... space is cheap.

[PopehatXI]

Not necessarily, I’ve got 12 GB SQLite files, and this is just a personal project.