Creating backup of sqlite database when running Borgmatic as systemd service fails

stfn commented

2026-02-27 08:47:13 +00:00

What I'm trying to do and why

I have a VPS with a SQLite database. I want to create a back up of it every night using Borgmatic. The backup works correctly when running borgmatic manually, but fails, when Borgmatic is triggered as a systemd service.

When running borgmatic manually and listing the files of the newest archive, the file run/borgmatic/sqlite_databases/localhost/gotosocial has a reasonable size of several GB, but when triggered the from systemd, the db file content is:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
/**** ERROR: (8) attempt to write a readonly database *****/
ROLLBACK; -- due to errors

Steps to reproduce

The SQLite database is /gotosocial/sqlite.db and belongs to the user gotosocial. The systemd service is run as root.

Attaching my borgmatic config file located at /etc/borgmatic/ (I had to change the file extensions to txt to be able to upload them). As described above, the systemd service is being run every night, triggered by the systemd timer.

Actual behavior

This is the output of sudo journalctl -u borgmatic:

Feb 01 00:39:43 ubuntu-8gb-fsn1-2 systemd[1]: Starting borgmatic.service - borgmatic backup...
Feb 01 00:40:45 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO local: Running before create action command hook
Feb 01 00:40:45 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO local: Creating archive
Feb 01 00:40:45 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO local: Dumping SQLite databases
Feb 01 00:40:45 ubuntu-8gb-fsn1-2 borgmatic[125208]: WARNING local: Ignoring configured "read_special" value of false, as true is needed for database hooks.
Feb 01 00:41:02 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO Creating archive at "/root/backups::ubuntu-8gb-fsn1-2-2026-02-01-004102"
Feb 01 00:41:09 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO sql error: attempt to write a readonly database (8)
Feb 01 00:41:09 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO local: Running after create action command hook
Feb 01 00:41:09 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO local: Pruning archives
Feb 01 00:41:10 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO local: Compacting segments
Feb 01 00:41:10 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO compaction freed about 5.94 kB repository space.
Feb 01 00:41:10 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO local: Running consistency checks
Feb 01 00:41:12 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO Starting repository check
Feb 01 00:41:13 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO finished segment check at segment 3511
Feb 01 00:41:13 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO Starting repository index check
Feb 01 00:41:13 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO Index object count match.
Feb 01 00:41:13 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO Finished full repository check, no problems found.
Feb 01 00:41:13 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO Starting archive consistency check...
Feb 01 00:41:13 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO Analyzing archive ubuntu-8gb-fsn1-2-2025-05-03-143958 (1/22)
Feb 01 00:41:13 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO Analyzing archive ubuntu-8gb-fsn1-2-2025-05-31-024357 (2/22)

Expected behavior

I would like to have a reliable way to back up my sqlite database.

Other notes / implementation ideas

No response

borgmatic version

2.0.5

borgmatic installation method

pipx

Borg version

1.2.8

Python version

3.12.3

Database version (if applicable)

3.45.1 2024-01-30 16:01:20 e876e51a0ed5c5b3126f52e532044363a014bc594cfefa87ffb5b82257ccalt1 (64-bit)

Operating system and version

Ubuntu 24.04.3 LTS

### What I'm trying to do and why I have a VPS with a SQLite database. I want to create a back up of it every night using Borgmatic. The backup works correctly when running `borgmatic` manually, but fails, when Borgmatic is triggered as a systemd service. When running `borgmatic` manually and listing the files of the newest archive, the file `run/borgmatic/sqlite_databases/localhost/gotosocial` has a reasonable size of several GB, but when triggered the from systemd, the db file content is: ``` PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; /**** ERROR: (8) attempt to write a readonly database *****/ ROLLBACK; -- due to errors ``` ### Steps to reproduce The SQLite database is `/gotosocial/sqlite.db` and belongs to the user `gotosocial`. The systemd service is run as root. Attaching my borgmatic config file located at `/etc/borgmatic/` (I had to change the file extensions to txt to be able to upload them). As described above, the systemd service is being run every night, triggered by the systemd timer. ### Actual behavior This is the output of `sudo journalctl -u borgmatic`: ``` Feb 01 00:39:43 ubuntu-8gb-fsn1-2 systemd[1]: Starting borgmatic.service - borgmatic backup... Feb 01 00:40:45 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO local: Running before create action command hook Feb 01 00:40:45 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO local: Creating archive Feb 01 00:40:45 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO local: Dumping SQLite databases Feb 01 00:40:45 ubuntu-8gb-fsn1-2 borgmatic[125208]: WARNING local: Ignoring configured "read_special" value of false, as true is needed for database hooks. Feb 01 00:41:02 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO Creating archive at "/root/backups::ubuntu-8gb-fsn1-2-2026-02-01-004102" Feb 01 00:41:09 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO sql error: attempt to write a readonly database (8) Feb 01 00:41:09 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO local: Running after create action command hook Feb 01 00:41:09 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO local: Pruning archives Feb 01 00:41:10 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO local: Compacting segments Feb 01 00:41:10 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO compaction freed about 5.94 kB repository space. Feb 01 00:41:10 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO local: Running consistency checks Feb 01 00:41:12 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO Starting repository check Feb 01 00:41:13 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO finished segment check at segment 3511 Feb 01 00:41:13 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO Starting repository index check Feb 01 00:41:13 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO Index object count match. Feb 01 00:41:13 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO Finished full repository check, no problems found. Feb 01 00:41:13 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO Starting archive consistency check... Feb 01 00:41:13 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO Analyzing archive ubuntu-8gb-fsn1-2-2025-05-03-143958 (1/22) Feb 01 00:41:13 ubuntu-8gb-fsn1-2 borgmatic[125208]: INFO Analyzing archive ubuntu-8gb-fsn1-2-2025-05-31-024357 (2/22) ``` ### Expected behavior I would like to have a reliable way to back up my sqlite database. ### Other notes / implementation ideas _No response_ ### borgmatic version 2.0.5 ### borgmatic installation method pipx ### Borg version 1.2.8 ### Python version 3.12.3 ### Database version (if applicable) 3.45.1 2024-01-30 16:01:20 e876e51a0ed5c5b3126f52e532044363a014bc594cfefa87ffb5b82257ccalt1 (64-bit) ### Operating system and version Ubuntu 24.04.3 LTS

borgmatic.service.txt

2.9 KiB

config.yaml.txt

1.1 KiB

witten commented

2026-02-27 18:54:49 +00:00

Owner

Thanks for the detailed ticket. A few thoughts:

The SQLite error you're getting is probably occurring because borgmatic (when run under systemd) doesn't have the permissions to write to its runtime directory.
Try running borgmatic from systemd with --verbosity 2 and looking for a log line like: Using runtime directory .... The path listed there is likely the path that borgmatic under systemd can't write to. You're running as root though, so the problem shouldn't be a file permission issue.
You can try commenting out all security-related options in your borgmatic systemd service to see if that "solves" the problem. Everything from LockPersonality through CapabilityBoundingSet. Don't forget to reload your systemd daemon.
If that fixes it, then you can add each option back, either one at a time or half of them at a time until you locate the problematic option. (Again, you'll need to daemon reload after each change.)
Starting from borgmatic 2.0.12+, SQLite errors will actually trigger borgmatic errors. So while upgrading won't fix your issue, it will make issues like this more likely to come to light (via a loud borgmatic error).
Side note: You should now be able to upload .yaml files directly without a rename.

Thanks for the detailed ticket. A few thoughts: - The SQLite error you're getting is probably occurring because borgmatic (when run under systemd) doesn't have the permissions to write to its runtime directory. - Try running borgmatic from systemd with `--verbosity 2` and looking for a log line like: `Using runtime directory ...`. The path listed there is likely the path that borgmatic under systemd can't write to. You're running as root though, so the problem shouldn't be a file permission issue. - You can try commenting out all security-related options in your borgmatic systemd service to see if that "solves" the problem. Everything from `LockPersonality` through `CapabilityBoundingSet`. Don't forget to reload your systemd daemon. - If that fixes it, then you can add each option back, either one at a time or half of them at a time until you locate the problematic option. (Again, you'll need to daemon reload after each change.) - Starting from borgmatic 2.0.12+, SQLite errors will actually trigger borgmatic errors. So while upgrading won't fix your issue, it will make issues like this more likely to come to light (via a loud borgmatic error). - Side note: You should now be able to upload `.yaml` files directly without a rename.

witten added the

question / support

label

2026-02-27 18:55:00 +00:00

stfn commented

2026-03-06 08:05:42 +00:00

Author

Thank you for your reply. So far I have turned off all of the security measures in the systemd file and that still did not fix the issue. I will continue to investigate the issue when I have a bit more time. I think I'll also try changing the runtime directory? I'll update the ticket once I find anything interesting. Thanks again for your help :)

witten commented

2026-03-06 18:18:55 +00:00

Owner

Yeah, you could try changing the runtime directory in case it's at a path where you don't have write permission when run under systemd. And I assume after making the service changes, you did a systemctl daemon-reload?

Yeah, you could try changing the runtime directory in case it's at a path where you don't have write permission when run under systemd. And I assume after making the service changes, you did a `systemctl daemon-reload`?

daenney commented

2026-03-25 22:18:25 +00:00

I'm running into this problem as well. I'm getting some weird filesystem paths:

Command 'sqlite3 -bail /data/gotosocial/database/sqlite.db .dump > /data/borgmatic/runtime/./borgmatic/sqlite_databases/localhost/gotosocial'

I have the following in my config:

user_runtime_directory: /data/borgmatic/runtime

That /./borgmatic looks out of place to me. That command however does work, as long as I mkdir -p /data/borgmatic/runtime/./borgmatic/sqlite_databases/localhost first.

However, watching the /data/borgmatic/runtime directory, I never see a borgmatic/sqlite_databases/localhost directory getting created. That would then also cause the dump to fail, since it's redirecting the output to a location that doesn't exist.

Borgmatic: 2.1.3
SQLite: 3.52.0 2026-03-06 16:01:44 557aeb43869d3585137b17690cb3b64f7de6921774daae9e56403c3717dcalt1 (64-bit)

I'm running into this problem as well. I'm getting some weird filesystem paths: ``` Command 'sqlite3 -bail /data/gotosocial/database/sqlite.db .dump > /data/borgmatic/runtime/./borgmatic/sqlite_databases/localhost/gotosocial' ``` I have the following in my config: ```yaml user_runtime_directory: /data/borgmatic/runtime ``` That `/./borgmatic` looks out of place to me. That command however does work, as long as I `mkdir -p /data/borgmatic/runtime/./borgmatic/sqlite_databases/localhost` first. However, watching the `/data/borgmatic/runtime` directory, I never see a `borgmatic/sqlite_databases/localhost` directory getting created. That would then also cause the dump to fail, since it's redirecting the output to a location that doesn't exist. Borgmatic: 2.1.3 SQLite: 3.52.0 2026-03-06 16:01:44 557aeb43869d3585137b17690cb3b64f7de6921774daae9e56403c3717dcalt1 (64-bit)

witten commented

2026-03-25 22:37:27 +00:00

Owner

That destination path actually appears correct to me. The /./ portion is borgmatic leveraging a Borg 1.4+ feature (often called the "slashdot hack") that means anything before and including the /./ gets stripped off before the path gets stored in the archive. It's handy for backing up a file at a particular path but storing it as something else. In this case, the file would get stored in the Borg archive as just borgmatic/sqlite_databases/localhost/gotosocial.

You shouldn't have to manually mkdir any directories though.

However, watching the /data/borgmatic/runtime directory, I never see a borgmatic/sqlite_databases/localhost directory getting created. That would then also cause the dump to fail, since it's redirecting the output to a location that doesn't exist.

It's possible that the directory gets created and deleted too quick to see it. borgmatic creates the directory before dumping and cleans it up after the Borg backup.

Some questions:

What error message do you get when this fails?
Are you running borgmatic under systemd when this fails?
Does running this outside of system succeed?
Can I see your full log (redacted as necessary) from a failure?
Can I see the SQLite portion of your borgmatic config file (redacted as necessary)?
What does your systemd service look like?
Have you tried some of the steps above about temporarily trimming it down and doing a daemon reload?

That destination path actually appears correct to me. The `/./` portion is borgmatic leveraging a Borg 1.4+ feature (often called the "slashdot hack") that means anything before and including the `/./` gets stripped off before the path gets stored in the archive. It's handy for backing up a file at a particular path but storing it as something else. In this case, the file would get stored in the Borg archive as just `borgmatic/sqlite_databases/localhost/gotosocial`. You shouldn't have to manually `mkdir` any directories though. > However, watching the /data/borgmatic/runtime directory, I never see a borgmatic/sqlite_databases/localhost directory getting created. That would then also cause the dump to fail, since it's redirecting the output to a location that doesn't exist. It's possible that the directory gets created and deleted too quick to see it. borgmatic creates the directory before dumping and cleans it up after the Borg backup. Some questions: * What error message do you get when this fails? * Are you running borgmatic under systemd when this fails? * Does running this outside of system succeed? * Can I see your full log (redacted as necessary) from a failure? * Can I see the SQLite portion of your borgmatic config file (redacted as necessary)? * What does your systemd service look like? * Have you tried some of the steps above about temporarily trimming it down and doing a daemon reload?

zjean referenced this issue

2026-03-31 08:00:21 +00:00

Sqlite backup fails #1290

Rows
Columns

Creating backup of sqlite database when running Borgmatic as systemd service fails #1276

What I'm trying to do and why

Steps to reproduce

Actual behavior

Expected behavior

Other notes / implementation ideas

borgmatic version

borgmatic installation method

Borg version

Python version

Database version (if applicable)

Operating system and version