News for hackers. Stuff that matters.
Re:Why Does Everyone Run Ancient Postgres Versions? by eru on Friday October 18, 2024 @08:01AM (#41878590)
Of course, sqlite is even easier on the sys-admins, (but not necessarily the right tool for the job.)
Re:Why Does Everyone Run Ancient Postgres Versions? by gramakri2 on Friday October 18, 2024 @08:24AM (#41878750)
How does one backup a sqlite without stopping the app or disrupting service? I couldn't find a simple answer to this
Re:Why Does Everyone Run Ancient Postgres Versions? by zaphar on Friday October 18, 2024 @09:43AM (#41879410)
The typical solution is https://litestream.io/. Comparatively easy to setup for most usages of sqlite.
Re:Why Does Everyone Run Ancient Postgres Versions? by tommy_axle on Friday October 18, 2024 @08:38AM (#41878854)
With sqlite3 -backup There's an online backup api
Re:Why Does Everyone Run Ancient Postgres Versions? by jlokier on Friday October 18, 2024 @12:22PM (#41880876)
There is a generic way to do it that works with SQLite and other databases, including Postgres. On Linux, take a filesystem or block device coherent snapshot, take your backup of all the SQLite files from the snapshot, then delete the snapshot.

The app or service continues to run without disruption, and the backup is a coherent database image. Perhaps you have other state in addition to the database, or multiple databses. For example cache files. This covers them too, if they are all in the same snapshot.

There are many ways to take a snapshot: ZFS and btrfs offer a filesystem command, and any Linux filesystem, such as ext4, can be snapshotted with LVM or LVM-thin. Well known cloud providers like AWS, GCP, Azure also provide ways to snapshot block devices, through their APIs. However, to ensure a coherent image, it may be necessary to use the Linux `fsfreeze` command around API calls.

The database backup files can have incomplete transactions, but if the files are restored it will be as if the OS was abruptly stopped at the moment of the snapshot. SQLite and other good databases are designed to recover well from this sort of abrupt stop, without corrupting the database. They clean up incomplete transactions on recovery.