SQLite backups: restore never applies the dump (docker run without -i)

[oouiy]

I maintain an unofficial package for Peergos (tripod0429/peergos-app) that lists its databases under localstorage.sqlite.paths. While testing backup and restore, I found that the SQLite dumps Cloudron takes during a backup are never applied on restore. A restored app gets the copies of its live database files that the backup upload made while the app was running.

What happens

From box master 21b1abd:

1. backupSqlite dumps each listed database with sqlite3 <db> .dump before the upload starts services.js#L331-353. That part works as intended.
2. The upload then copies the whole data directory, including the live database files. The listed paths aren't excluded.
3. On restore, restoreSqlite replays each dump like this services.js#L363-382:

   docker run --rm ... <app image> sqlite3 <db> < <dump>
   

   There's no -i, so Docker doesn't pass stdin to the container. sqlite3 reads nothing and exits 0, and the restore carries on as if it worked.

Quick check

printf 'CREATE TABLE t(x); INSERT INTO t VALUES(42); SELECT x FROM t;\n' | docker run --rm cloudron/base:6.0.0 sqlite3
printf 'CREATE TABLE t(x); INSERT INTO t VALUES(42); SELECT x FROM t;\n' | docker run --rm -i cloudron/base:6.0.0 sqlite3

On my Cloudron server (Docker 29.6.2), the first command prints nothing and exits 0. The second prints 42. Podman 5.8.7 behaves the same way.

Adding -i alone isn't enough

The live database is already in the restored data, so every CREATE TABLE in the dump fails and sqlite3 exits 1. Cloudron's shell helper turns any non-zero exit into an error (shell.js#L58-84), so with only -i added, every restore of an app with sqlite.paths would fail. If that error were ignored instead, tables without a unique key would get duplicate rows:

$ sqlite3 live.db "CREATE TABLE notes (body text); INSERT INTO notes VALUES ('one');"
$ sqlite3 live.db .dump > dump.sql
$ sqlite3 live.db < dump.sql; echo "exit $?"
Parse error near line 3: table notes already exists
exit 1
$ sqlite3 live.db "select count(*) from notes;"
2

Why it matters

For most apps a live copy is close enough, which is probably why this hasn't come up. But:

• A database being written while it's copied can come back corrupted, which is what the dumps are there to prevent.
• In WAL mode, the database and its -wal file are copied at different moments.
• Apps that keep files next to their database can get rows pointing to files the upload had already passed.

Suggested fix

• In restoreSqlite, pass -i to docker run and replay each dump into a new file instead of the restored copy. Once the replay succeeds, move the new file over the database and delete any -journal, -wal or -shm file left from the live copy.
• Optionally, leave the listed paths out of the upload, since the dumps replace them. That also avoids uploading every database twice.

[girish]

Really good catch! I have fixed this for the next release. Thanks for reporting.