How to copy databases between computers? Just send a circle and forget about the rest of the owl.

As others have mentioned an incremental rsync would be much faster, but what bothers me the most is that he claims that sending SQL statements is faster than sending database and COMPLETELY omiting the fact that you have to execute these statements. And then run /optimize/. And then run /vacuum/.

Currently I have scenario in which I have to "incrementally rebuild *" a database from CSV files. While in my particular case recreating the database from scratch is more optimal - despite heavy optimization it still takes half an hour just to run batch inserts on an empty database in memory, creating indexes, etc.

> If it takes a long time to copy a database and it gets updated midway through, rsync may give me an invalid database file. The first half of the file is pre-update, the second half file is post-update, and they don’t match. When I try to open the database locally, I get an error

Of course! You can't copy the file of a running, active db receiving updates, that can only result in corruption.

For replicating sqlite databases safely there is

https://github.com/benbjohnson/litestream

Saving to text file is inefficient. I save sqlite databases using VACUUM INTO, like this:

  sqlite3 -readonly /path/db.sqlite "VACUUM INTO '/path/backup.sqlite';"

From https://sqlite.org/lang_vacuum.html :

  The VACUUM command with an INTO clause is an alternative to the backup API for generating backup copies of a live database. The advantage of using VACUUM INTO is that the resulting backup database is minimal in size and hence the amount of filesystem I/O may be reduced.

The recently released sqlite_rsync utility uses a version of the rsync algorithm optimized to work on the internal structure of a SQLite database. It compares the internal data pages efficiently, then only syncs changed or missing pages.

Nice tricks in the article, but you can more easily use the builtin utility now :)

I blogged about how it works in detail here: https://nochlin.com/blog/how-the-new-sqlite3_rsync-utility-w...

SQLite has an official tool for this, fwiw: https://www.sqlite.org/rsync.html

It works at the page level:

> The protocol is for the replica to send a cryptographic hash of each of its pages over to the origin side, then the origin sends back the complete content of any page for which the hash does not match.