May I suggest using a statement log of the "primary" database to update the secondary sites? If you have a sequence counter (separate or embedded) then the state of the database could be recovered from a backup (sequence counter x) plus the log files for all statements from x+1 to "current".
-----Original Message----- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Stephan Buchert Sent: Freitag, 06. Mai 2016 14:32 To: sqlite-users at mailinglists.sqlite.org Subject: [sqlite] Incremental backup/sync facility? We are using Sqlite for data from satellite Earth observations. It works very well. Thanks to everybody contributing to Sqlite, uppermost Dr. Hipp. The largest database file has now grown to about 180 GB. I need to have copies of the files at at least two different places. The databases are updated regularly as new data from the satellites become available. Having the copies of the file synced becomes increasingly tedious as their sizes increase. Ideal would be some kind of incremental backup/sync facility. I looked at https://www.sqlite.org/backup.html, but, as it does a complete backup, this takes a long time for such large databases (files are mounted over networks), and other work gets blocked. An alternative is perhaps https://www.sqlite.org/sqldiff.html, but it is also slow for large databases. Most of the data are binary, and the SQL input/output of sqldiff/sqlite3 is probably not very efficient. A solution might be to add an extension to the standard VFS http://www.sqlite.org/vfs.html. Each page would need a counter which increments when the page is modified (and there seems to be spare place for such counters). Then the backup could test which pages need to be updated. Is there any chance that such an extension could be added? A kind of hack-ish solution might be to update the primary database files in WAL mode, copy only the WAL file to the secondary place, and force there WAL checkpoint. Would this work? /Stephan _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
