Details about my system:
I plan to use one Raspberry Pi 3, one Raspberry Pi 3+ and a single-node Ceph installation which can serve CephFS (already does) or Ceph RBD.
The idea would be to run pi-hole inside a container using podman or docker or maybe even k3s and mount a CephFS folder for data persistence. An alternative scenario is to mount the folder on the OS and run pi-hole without containers.
My question is whether someone has already experience with something like this, in particular I don't know how sqlite would play with the shared storage and how it would behave as both instances would access the same file.
I would probably have a load balancer in front of the two instances so I wouldn't necessarily know on which I'm editing settings, and I don't really know if I'd lose any data.
A much simpler approach is the run two completely separate and independent Pi-hole instances, and list both as DNS servers in your DHCP settings.
Since either Pi-hole can easily handle your entire network, you don't need a load balancer. The clients will pick one or the other Pi-hole and use that one. The load balance between the two Pi-holes will depend primarily on how your clients deal with multiple available DNS servers, but doesn't really matter.
That's for sure, but it's also harder to keep synced, especially with big lists.
I already have the software and the hardware to do it, I'm just looking for some insight about the compatibility of it.
Fair enough.
It does not really answer the question can the gravity.db file be put in a shared filesystem without corrupting everything?
I did some digging and it seems NFS would not work (How To Corrupt An SQLite Database File) there's a whole section dedicated to file locking and other reasons there can't be multiple processes writing to sqlite... however it seems the people over at Ceph thought about a solution: New in Pacific: SQL on Ceph.
Whatever you do, you first need to stop pihole-FTL and restart it after you are done. This ensures that there will be no active transactions with the database while moving which is likely no. 2 of "Top database corruption strategies".
I quickly looked at your linked solution, owever, this involves using a dedicated VFS which is something Pi-hole does not support right now. In fact, no database application I know of supports this, except the sqlite3 CLI tool. The SQLite extension loading API is a favored entrance gate for all kinds of bad things and the SQLite3 guys themselves recommend to disable it unless you really need it IIRC.
So I don't think this will be of any use here. I honestly also don't see a straightforward way to add this VFS to Pi-hole unless the maintainers of libcephsqlite integrate their extension natively into SQLite3. Then it will be immediately available to you, also within Pi-hole.
Is there no way for me to patch FTL and maybe the pi-hole scripts to attempt to load this extension? Maybe have it controlled via an environment variable at runtime?
I'm not familiar enough with the inner workings of pi-hole to evaluate how difficult this would be.
This is surely not impossible but it may will require quite some involved tinkering and a lot of trial and error - likely with a lot of broken database ahead. You will have to maintain your changes, we can only give advise where to make what changed. Especially as the instructions on the page you linked do not mention anything about running a setup in an embedded application (but only the command line interface sqlite3).
However, you have all the tools at hand you'd need if you are willing to do. We'll also support you in the best way we can, however, this is surely well beyond what Pi-hole can offer out of the box.
Thank you for the support, I'll give it a shot and see what happens.
My interpretation is that they expect, once the extension is available and loaded, that nothing else would need to change.
Once FTL can open the VFS URI everything should work.
Yet they don't give recommendations how to load the extension in any embedded application. Not even in popular frameworks such as Python or PHP. This somewhat just lowers my exceptions but we'll see. As I said, I don't see any problem in FTL but you'll need to maintain your own patched version for this.
Plus you'll also have to modify the source code in other places where the CLI sqlite3 is actually used.
Just do a
grep -R "sqlite3" /etc/.pihole
to see what needs changing (you may not want to use all of these features, then you can change fewer than returned).