Why would SQLite data disappear when scaling machines?

, ,
1

Context:
I have an app with 60+ users
The app arq is meant to be for fast development and easy maintainability. I used SQLite.
Yesterday I scaled the number of fly machines of that application. The application has a volume attach and inside a .sqlite file.

All data disappeared, the database voided - no tables, no rows, no pragmas - and my heart stop beating too.

After the initial shock I revert back to the original 2 fly machines - it worked all data reappeared on my app.

Then I read about LiteFS. In principle this exactly what I need.

Is litefs compatible with this pragmas?

        PRAGMA journal_mode = WAL;
        PRAGMA synchronous = NORMAL;
        PRAGMA busy_timeout = 5000;
        PRAGMA cache_size = -20000;
        PRAGMA foreign_keys = ON;
        PRAGMA auto_vacuum = INCREMENTAL;
        PRAGMA temp_store = MEMORY;
        PRAGMA mmap_size = 2147483648;
        PRAGMA page_size = 8192;
2 Likes
3

Yikes… It’s surprising that it was working even with 2 Machines, actually. (Since those have independent volumes.) Maybe one has been asleep all this time?

Anyway, I don’t use PRAGMAs myself, but it sounds like they’re rarely (or maybe even never) a problem…

Hope this helps!

1 Like
4

If you doubt your database technology then, depending on your application and usage patterns, you could set up a frequent database backup pattern.

For example, if the loss of an hour’s data is acceptable to you, set up hourly off-host database backups, which of course with SQLite is as easy as copying a file. You could even do that on all your hosts, if you don’t trust LiteFS to sync correctly.

Then you can monitor your app, and adjust your backup processes as your trust level improves.

1 Like
5

Indeed… In that same vein, there is even LiteFS Backup, which is ultimate frequency—in a sense…

Litefs-backup is a standalone backup process that streams backups of your LiteFS cluster to any s3-compatible storage provider, with 5 minute point-in-time restore granularity.

1 Like
6

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.