Re:SQLite is not a toy database (2021)
by 5e92cb50239222b on Tuesday August 16, 2022 @02:02AM
(#32479646)
FWIW, it's perfectly fine to backup both MariaDB and PostgreSQL by simply copying files. It's one of the recommended methods to set up PostgreSQL replicas:
https://www.postgresql.org/docs/current/continuous-archiving... (section 26.3.3.3)
You might want to configure the database to put binary logs into a separate directory (or you might want to leave them there, depends on your requirements).
Re:SQLite is not a toy database (2021)
by stormdennis on Tuesday August 16, 2022 @03:10AM
(#32480027)
Isn't that a bit different?
As I understand it, he's talking about just copying the sql db file while you're talking about running a cluster wide backup tool to create a separate set of files, that you can then copy elsewhere as a backup.
> Using SQLite for a network service when the app runs on the same machine as the SQLite file is perfectly fine
If preventing data loss is important for your app then this would be more hassle than using a networked db because now you have to replicate SQLite across a network to another machine.
> If preventing data loss is important for your app then this would be more hassle than using a networked db
How so? A networked database requires a persistent connection, while SQLite backups work fine with an intermittent connection. The best case (100% network uptime) and worst case (total network outage) are the same, but in the middle, SQLite-with-backups is more resilient to brief outages.
> https://www.sqlite.org/useovernet.html
It's not what this page says. This page suggests PostgreSQL if accessing the SQLite file from the application would induce a network connection (because of a network filesystem). Not if the application is accessed over the network.
Using SQLite for a network service when the app runs on the same machine as the SQLite file is perfectly fine.
> (unless you use WAL or rollback, in which case why not just use Postgres?)
Again. WAL / rollback is how SQLite works by default. The page does not suggest using WAL, it suggests using WAL, "but do all reads and writes from processes on the same machine that stores the database file", is you use a networked FS. And yes, in this case, you might as well use PostgreSQL indeed.
> In fact, I’d argue there’s no networked use case in which SQLite is cheaper, easier to deploy, maintain or run than Postgres.
I disagree. I now use SQLite whenever I can for low traffic services, unless the app developers strongly advise something else.I'd say there's no reason to bother with a client/server database like PostgreSQL / MariaDB / MySQL if you can help it. It's one less database to administer, one less user and password to create and manage, and the SQLite file is saved using my regular backup routine. It works great and it's less work for me. You also get to use the database engine with probably the most extensive and comprehensive test suite in the world , by far[1]. It is probably more robust than anything else. SQLite is probably capable of handling high traffic too, especially if the majority of accesses are reads and there are only a few writes.
Of course, Postgres is a really good choice too and you can't really be wrong when choosing it. It's very robust and works very well too, and does not have loosy typing.
[1] https://www.sqlite.org/testing.html