SQLite Forum

Opening DB from readonly filesystem (btrfs snapshot)
Login

Opening DB from readonly filesystem (btrfs snapshot)

(1) By Anchor on 2021-06-05 11:28:56 [link] [source]

I have problems opening the sqlite files on a readonly filesystem. I'll outline the reasoning for RO snapshots and then the rest.

I have an application that constantly writes to a multitude of sqlite files. These files are 32G each and continue to grow. Backing up the sqlite files was a pain, as it required the application to be halted to get a consisten state of the db files and then these files had to be backed up, which also takes a considerable amount of time. To circumvent the long downtimes of the application, it was extended with code to halt any db transactions, comit whatever is left, get the files in a consistant state and wait for another api call to continue working. The sqlite files were placed onto a btrfs subvolume that can be snapshotted. Creating the following workflow: stop working with dbs > create consistant state > snapshot subvolume > continue working on dbs > backup files from snapshot

This works flawlessly, which is great. Minimal downtime, clean backups. Then comes the idea, I could work (read) with the snapshotted sqlite files.

And here is the problem. It seems impossible to work with these (readonly) files/folder using slqite3(SQLite version 3.34.1 2021-01-20 14:10:07).

sqlite3 -readonly client.db
SQLite version 3.34.1 2021-01-20 14:10:07
Enter ".help" for usage hints.
sqlite> .schema
Error: unable to open database file
$ sqlite3 file:client.db?mode=ro
SQLite version 3.16.2 2017-01-06 16:32:41
Enter ".help" for usage hints.
sqlite> .schema
Error: unable to open database file
These DBs did use -wal and -shm which are cleaned up on "consistant state" by the application and the sqlite files work flawlessly once copied to a read write filesystem, but not on read-only system. I know that sqlite does the whole checking and locking so that the database is not corrupted, but the folder and files are read only. Nothing can write to them, only read. So I would have expected that at least the -readonly flag would allow me to interact with the db files, but it doesn't.

Is there really no way to work with files/filesystems that are trully readonly?

(2) By Warren Young (wyoung) on 2021-06-05 11:40:55 in reply to 1 [link] [source]

You could switch the transaction mode to DELETE or to TRUNCATE prior to doing all of this shutdown and snapshot work you speak of.

However, I wonder why you're bothering given that you're using a snapshotting filesystem. Why not leave the application up and running, take the snapshot, and move on?

Yes, this means that if you have to roll back to a snapshot you might lose the last few uncommitted transactions, but that's also true if the application crashes, the computer gets hit by a meteor, or whatever. The nature of WAL is that it takes such events in stride and gets the DB back into a consistent state despite having interrupted transactions.

Indeed, if you hadn't spoken of btrfs, ZFS, disk virtualization, etc., that's exactly how I'd recommend you solve this case. It's one of the great benefits of filesystem-level snapshots.

(3) By Dan Kennedy (dan) on 2021-06-05 14:35:15 in reply to 1 [link] [source]

To open a wal mode database on a read-only medium, use a URI filename and add the immutable=1 argument.

https://sqlite.org/uri.html#recognized_query_parameters

(4) By Anchor on 2021-06-05 17:23:46 in reply to 3 [link] [source]

This, I can't read. I was able to open the db and read the data. Thank you.

(5) By anonymous on 2021-06-06 12:24:31 in reply to 1 [source]

Backing up the sqlite files was a pain, as it required the application to be halted to get a consisten state of the db files

In case you haven't considered it yet, SQLite has online backup API which makes it possible to back-up the database in small portions, interleaved with other database access, without interrupting the main application.