9 ms·
A faster way to copy SQLite databases between computers
- forinti 2y agoOne of the coolest things you can do with Postgresql is pipe pg_dump straight into psql connected to another cluster on another host.
- actinium226 2y agoI recently set up some scripts to do this and it wasn't quite as simple as I had hoped. I had to pass some extra flags to pg_restore for --no-owner --no-acl, and then it still had issues when the target db has data in it, even with --clean and --create. And sometimes it would leave me in a state where it dropped the database and had trouble restoring, and so I'd be totally empty. What I ended up doing is creating a new database, pg_restore'ing into that one with --no-owner and --no-acl, forcibly dropping the old database, and then renaming the new to the old one's name. This has the benefit of not leaving me high and dry should there be an issue with restoring.
- dundundundun 2y agoThis is basically the way every other database is moved around.
- d34th0rl1f3 2y agoYou can save time by using `zcat` instead of `cat` and skip the `gunzip my_local_database.db.txt.gz` step.
- _joel 2y agoYou could speed up by using pigz (parallel gzip) too.
- masklinn 2y agoIf you're going to use a less universal tool for compression you might as well go with zstd.
- Slasher1337 2y agoWhy would anyone use gzip instead of zstd in 2025? zstd is superior in every dimension. gzip is a legacy algorithm that imo only gets used for compatibility with legacy software that understands nothing but gzip.
- nodesocket 2y agoYou don’t need cat at all for the restore. Can simply do: sqlite3 data/database.db < “{backup_file}"
- pvorb 2y agoHow long does this procedure take in comparison to the network transfer? My first try would've been to copy the db file first, gzip it and then transfer it but I can't tell whether compression will be that useful in binary format.
- MPSimmons 2y agoThe sqlite file format (https://www.sqlite.org/fileformat.html https://www.sqlite.org/fileformat.html) does not talk about compression, so I would wager unless you are storing already compressed content (media maybe?) or random numbers (encrypted data), it should compress reasonably well.
- chasil 2y agoNative compression in sqlite is offered as a closed and licensed extension. https://sqlite.org/com/cerod.html https://sqlite.org/com/cerod.html
- simlevesque 2y agoIn DuckDB you can do the same but export to Parquet, this way the data is an order of magnitude smaller than using text-based SQL statements. It's faster to transfer and faster to load. https://duckdb.org/docs/stable/sql/statements/export.html https://duckdb.org/docs/stable/sql/statements/export.html
- uwemaurer 2y agoyou can do it with a command line like this: duckdb -c "attach 'sqlite-database.db' as db; copy db.table_name to 'table_name.parquet' (format parquet, compression zstd)" in my test database this is about 20% smaller than the gzipped text SQL statements.
- simlevesque 2y agoThat's not it. This only exports the table's data, not the database. You lose the index, comments, schemas, partitioning, etc... The whole point of OP's article is how to export the indices in an efficient way. You'd want to do this: duckdb -c "ATTACH 'sqlite-database.db' (READ-ONLY); EXPORT DATABASE 'target_directory' (FORMAT parquet, COMPRESSION zstd)" Also I wonder how big your test database is and it's schema. For large tables Parquet is way more efficient than a 20% reduction. If there's UUIDs, they're 36 bits each in text mode and 16 bits as binary in Parquet. And then if they repeat you can use a dictionary in your Parquet to save the 16 bits only once. It's also worth trying to use brotli instead of zstd if small files is your goal.
- mromanuk 2y agoI was surprised that he didn't try to use on the flight compression, provided by rsync: -z, --compress compress file data during the transfer --compress-level=NUM explicitly set compression level Probably it's faster to compress to gzip and later transfer. But it's nice to have the possibility to improve the transfer with a a flag.
- jddj 2y agoOr better yet, since they cite corruption issues, sqlite3_rsync (https://sqlite.org/rsync.html https://sqlite.org/rsync.html) with -z sqlite transaction- and WAL-aware rsync with inflight compression.
- worldsavior 2y agoI believe compression is only good on slow speed networks.
- berbec 2y agoValve tends to take a different view...
- stackskipton 2y agoValve has different needs then most. Their files are rarely change so they only need to do expensive compression once and they save a ton in bandwidth/storage along with fact that their users are more tolerant of download responsiveness.
- rollcat 2y agoDepends. Run a benchmark on your own hardware/network. ZFS uses in-flight compression because CPUs are generally faster than disks. That may or may not be the case for your setup.
- PhilipRoman 2y agoIt would have to be one really fast network... zstd compresses and decompresses at 5+ GB (bytes, not bits) per second.
- rabysh 2y agoI think this could be a single pipeline? ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c" | gunzip -c | sqlite3 my_local_database.db
- feverzsj 2y agoThat makes zero sense. Incremental backup via rsync/sqlite3_rsync should always be faster.
- Retr0id 2y agoFor incremental backups sure, but I think OP's solution would win for one-off snapshots.
- iambear 2y agoI usually use scp for this case, sometimes rsync version is not compatible between 2 machines
- deleted 2y ago[deleted]
- Cthulhu_ 2y agoI used to work at a company that had a management interface that used sqlite as database, its multi-node / fallover approach was also just... copying the file and rsyncing it. I did wonder about data integrity though, what if the file is edited while it's being copied over? But there's probably safeguards in place. Anyway I don't think the database file size was really an issue, it was a relatively big schema but not many indices and performance wasn't a big consideration - hence why the backend would concatenate query results into an XML file, then pass it through an xml->json converter, causing 1-2 second response times on most requests. I worked on a rewrite using Go where requests were more like 10-15 milliseconds. But, I still used sqlite because that was actually a pretty good solution for the problem at hand; relatively low concurrency (up to 10 active simultaneous users), no server-side dependencies or installation needed, etc.
- MPSimmons 2y ago>I did wonder about data integrity though, what if the file is edited while it's being copied over? But there's probably safeguards in place. You could do a filesystem snapshot and copy from that, but neither a cp or rsync is atomic.
- formerly_proven 2y agosqlite3 has a backup API for this, which you can invoke using the .backup command in the sqlite3 CLI. And then there is also https://www.sqlite.org/rsync.html https://www.sqlite.org/rsync.html
- rollcat 2y agoSQLite has a write-ahead log (WAL). You can use Litestream on top of that. You get single RW, multiple readers (you lose the C in CAP), and can promote a reader when the writer fails.
- rarrrrrr 2y agoIf you're regularly syncing from an older version to a new version, you can likely optimize further using gzip with "--rsyncable" option. It will reduce the compression by ~1% but make it so differences from one version to the next are localized instead of cascading through the full length of the compression output. Another alternative is to skip compression of the dump output, let rsync calculate the differences from an previous uncompressed dump to the current dump, then have rsync compress the change sets it sends over the network. (rsync -z)
- ukuina 2y agoDoesn't this just push the runtime into index recomputation on the destination database?
- masklinn 2y agoYes, however they seem to have a pretty slow internet connection > Downloading a 250MB database from my web server takes about a minute over my home Internet connection So for the original 3.4GB database that's nearly 15mn waiting for the download.
- zeroq 2y agoHow 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.
- JamesonNetworks 2y ago30 minutes seems long. Is there a lot of data? I’ve been working on bootstrapping sqlite dbs off of lots of json data and by holding a list of values and then inserting 10k at a time with inserts, Ive found a good perf sweet spot where I can insert plenty of rows (millions) in minutes. I had to use some tricks with bloom filters and LRU caching, but can build a 6 gig db in like 20ish minutes now
- pessimizer 2y agoSaying that 30 minutes seems long is like saying that 5 miles seems far.
- thechao 2y agoMillions of rows in minutes sounds not ok, unless your tables have a large number of columns. A good rule is that SQLite's insertion performance should be at least 1% of sustained max write bandwidth of your disk; preferably 5%, or more. The last bulk table insert I was seeing 20%+ sustained; that came to ~900k inserts/second for an 8 column INT table (small integers).
- zeroq 2y agoIt's roughly 10Gb across several CSV files. I create a new in-mem db, run schema and then import every table in one single transaction (in my testing it showed that it doesn't matter if it's a single batch or multiple single inserts as long are they part of single transaction). I do a single string replacement per every CSV line to handle an edge case. This results in roughly 15 million inserts per minute (give or take, depending on table length and complexity). 450k inserts per second is a magic barrier I can't break. I then run several queries to remove unwanted data, trim orphans, add indexes, and finally run optimize and vacuum. Here's quite recent log (on stock Ryzen 5900X): 08:43 import 13:30 delete non-essentials 18:52 delete orphans 19:23 create indexes 19:24 optimize 20:26 vacuum
- RenThraysk 2y agoSQLite has an session extension, which will track changes to a set of tables and produce a changeset/patchset which can patch previous version of an SQLite database. https://www.sqlite.org/sessionintro.html https://www.sqlite.org/sessionintro.html
- simonw 2y agoHave you used that? I've read the documentation but I don't think I've ever heard from anyone who uses the extension.
- RenThraysk 2y agoI have, atleast to confirm it does what it says on the tin. Idea for an offline first app, where each app install call pull a changeset and apply it to their local db.
- oefrha 2y agoI have yet to see a single SQLite binding supporting this, so it’s quite useless unless you’re writing your application in C, or are open to patching the language binding. In one of my projects I have implemented my own poor man’s session by writing all the statements and parameters into a separate database, then sync that and replay. Works well enough for a ~30GB database that changes by ~0.1% every day.
- RenThraysk 2y agoThere are atleast two SQLite bindings for Go. https://github.com/crawshaw/sqlite https://github.com/crawshaw/sqlite https://github.com/eatonphil/gosqlite/ https://github.com/eatonphil/gosqlite/ Ended up with the latter, but did have to add one function binding in C, to inspect changesets.
- ncruces 2y agoI'm open to adding it to my driver, if people consider it essential. Every extra bit makes AOT compiling the Wasm slower (impacting startup time). I also wanna keep the number of variants reasonable, or my repo blows up. Add your votes for additional features to this issue: https://github.com/ncruces/go-sqlite3/issues/126 https://github.com/ncruces/go-sqlite3/issues/126
- jbverschoor 2y agoIn curious how your indices are twice the data. Sounds like you just put indices in anything you see.
- hobs 2y agoHah they need to try harder then, I have seen more than 20x the data volume in systems where people are both paranoid and ignorant, a dangerous combo!
- crazygringo 2y agoI definitely have databases like this. It's not carelessness, it's performance. Quite simply, I have a table with 4 columns -- A, B, C, D. Each column is just an 8-byte integer. It has hundreds of millions of rows. It has an index on B+C+D, an index on C+D, and one on D. All of these are required because the user needs to be able to retrieve aggregate data based on range conditions around lots of combinations of the columns. Without all the indices, certain queries take a couple minutes. With them, each query takes milliseconds to a couple seconds. I thought of every possible way to avoid having all three indices, but it just wasn't possible. It's just how performant data lookup works. You shouldn't assume people are being careless with indices. Far too often I see the opposite.
- deleted 2y ago[deleted]
- emadda 2y agoSometimes using mmap for SQL queries can speed them up (if you want to avoid adding too many indexes or cannot modify the db). Set this on the connection: PRAGMA mmap_size=… https://www.sqlite.org/mmap.html https://www.sqlite.org/mmap.html I think this works as it removes data copying between the os and your process. Your process reads data from the os to execute the query. With mmap the data reads have less overhead, so a full table scan may be fast enough.
- Levitating 2y agoI am sure you can just pipe all this so you don't have to use an intermediate gunzip file. Just ssh the machine, dump the SQL and load it back into SQLite locally.
- rollcat 2y agorsync will transmit only the delta between the source and destination.
- mixmastamyk 2y agoBelieve there are command-line flags necessary to make it do that though: --inplace --no-whole-file
- exsysadmin 2y ago--no-whole-file is the standard, except for local paths (on the same system, the delta-transfer algorithm would be a waste of work, as it reads src and dst to find the delta with rolling checksums -- over the network it can save lots of time and bandwidth) --inplace is independent of that: it specifies writing directly to target files (instead of write to temp file and rename to target after completion)
- wang_li 2y agoI've seen a suggestion several times to compress the data before sending. If remote means in the same data center, there's a good chance compressing the data is just slowing you down. Not many machines can gzip/bzip2/7zip at better than the 1 gigabyte per second you can get from 10 Gbps networks.
- whalesalad 2y agoit's a file - what am I missing? scp host:path .
- ninth_ant 2y agoThen entire point of the article is to answer this specific question
- cwmma 2y agohow well does just the sqlite database gzip, the indexes are a lot of redundant data so your going to get some efficiencies there, probably less locality of data then the text file though so maybe less?
- nottorp 2y agoWait... why would you even think about rsyncing a database that can get changed while being copied? Isn't this a case for proper database servers with replication? Or if it's an infrequent process done for dev purposes just shut down the application doing writes on the other side?
- bambax 2y ago> 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 https://github.com/benbjohnson/litestream
- quotemstr 2y ago> Of course! You can't copy the file of a running, active db receiving updates, that can only result in corruption Do people really not understand how file storage works? I cannot rightly apprehend the confusion of ideas that would produce an attempt to copy a volatile database without synchronization and expect it to work.
- ahazred8ta 2y ago> I cannot rightly apprehend the confusion of ideas I see you are a man of culture.
- kccqzy 2y agoCharles Babbage is smart, but either he lacks empathy to understand other people or he's just saying that deliberately for comedic effect.
- jerf 2y agoIt was early days... very early days. He didn't have the benefit of trying to help his (metaphorical) grandparents get their emails or worked under a manager who thinks 2023-era ChatGPT is only slightly less reliable than the Standard Model of Physics, if not slightly more.
- bambax 2y agoOh he definitely lacked empathy. But things haven't improved much. Today we have "prompt engineers" whose only job is to input the right question in order to get the right answer.
- ollybee 2y agosqlite provide the sqlite3_rsync command to safely copy databases https://sqlite.org/rsync.html https://sqlite.org/rsync.html
- npn 2y ago??? Why not just compress the whole database using `gzip` or `lz4` before rsyncing it instead? `zstd` works too but seems like it had a bug regarding compressing file with modified content. better yet, split your sqlite file to smaller piece. it is not like it needs to contain all the app data in a single sqlite file.
- hundredwatt 2y agoThe 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-works https://nochlin.com/blog/how-the-new-sqlite3_rsync-utility-w...
- jgalt212 2y agosqlite_rsync can only be used in WAL mode. A further constraint of WAL mode is the database file must be stored on local disk. Clearly, you'd want to do this almost all the time, but for the times this is not possible this utility won't work.
- SQLite 2y agoI just checked in an experimental change to sqlite3_rsync that allows it to work on non-WAL-mode database files, as long as you do not use the --wal-only command-line option. The downside of this is that the origin database will block all writers while the sync is going on, and the replicate database will block both reads and writers during the sync, because to do otherwise requires WAL-mode. Nevertheless, being able to sync DELETE-mode databases might well be useful, as you observe. If you are able, please try out this enhancement and let me know if it solves your problem. See <https://sqlite.org/src/info/2025-05-01T16:07Z https://sqlite.org/src/info/2025-05-01T16:07Z> for the patch.
- SQLite 2y agoUpdate: This enhancement is now on trunk and will be included in the 3.50.0 release of SQLite due out in about four weeks.
- remram 2y agoWAL mode works on many network filesystems provided it's being written from a single host at a time.
- actinium226 2y agoI have recently discovered a tool called mscp which opens open multiple scp threads to copy down large files. It works great for speeding up these sorts of downloads. https://github.com/upa/mscp https://github.com/upa/mscp
- 404mm 2y agozstd would be a better choice. It’s bonkers fast (especially when used with multithreading) and still compresses better than gzip. Alternatively, I’d recommend looking into bzip3, but I’m not sure if it would save time.
- ozim 2y agoI guess for me it is obvious you don't try to copy running DB only a backup. So I see basic stuff needs to be repeated as people still miss those kinds of things. But I learned that you can easily dump SQLite to a text file - neat!
- yapyap 2y agoVery neat walkthrough, clear commands and I appreciate the explanations as to why this may help in OPs case
- markhahn 2y agoisn't this rather obvious? doesn't everyone do this when it makes sense? obviously, it applies to other DBs, and you don't even need to store the file (just a single ssh from dumper to remote undumper). if retaining the snapshot file is of value, great. I'd be a tiny bit surprised if rsync could recognize diffs in the dump, but it's certainly possible, assuming the dumper is "stable" (probably is because its walking the tables as trees). the amount of change detected by rsync might actually be a useful thing to monitor.
- RKFADU_UOFCCLEL 2y agoPretty good point. I just wonder if databases in generally can be perfectly reconstructed from a text dump. For instance, do the insertion orders change in any of the operations between dumping and importing?
- M95D 2y agoSaving 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 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.
- gwbas1c 2y agoDoes that preserve the indexes? As the TFA mentioned, the indexes are why the sqlite files are huge.
- M95D 2y agoYou're right. It does. I never thought about it until you asked.
- 4silvertooth 2y agoI think it won't preserve the index but it will recreate the index while running the text sql.
- nine_k 2y agoIt's cool but it does not address the issue of indexes, mentioned in the original post. Not carrying index data over the slow link was the key idea. The VACUUM INTO approach keeps indexes. A text file may be inefficient as is, but it's perfectly compressible, even with primitive tools like gzip. I'm not sure the SQLite binary format compresses equality well, though it might.
- vlovich123 2y ago> A text file may be inefficient as is, but it's perfectly compressible, even with primitive tools like gzip. I'm not sure the SQLite binary format compresses equality well, though it might. I hope you’re saying because of indexes? I think you may want to revisit how compression works to fix your intuition. Text+compression will always be larger and slower than equivalent binary+compression assuming text and binary represent the same contents? Why? Binary is less compressible as a percentage but starts off smaller in absolute terms which will result in a smaller absolute binary. A way to think about it is information theory - binary should generally represent the data more compactly already because the structure lived in the code. Compression is about replacing common structure with noise and it works better if there’s a lot of redundant structure. However while text has a lot of redundant structure, that’s actually bad for the compressor because it has to find that structure and process more data to do that. Additionally, is using generic mathematical techniques to remove that structure which are genetically optimal but not as optimal as removing that structure by hand via binary is. There’s some nuance here because the text represents slightly different things than the raw binary SQLite (how to restore data in the db vs the precise relationships + data structures for allowing insertion/retrieval. But still I’d expect it to end up smaller compressed for non trivial databases
- xnx 2y agoGreat example of Cunningham's Law: https://en.wikipedia.org/wiki/Ward_Cunningham#:~:text=%22Cunningham's%20Law%22 https://en.wikipedia.org/wiki/Ward_Cunningham#:~:text=%22Cun...
- bluefirebrand 2y agoAll this obsession with making processes like this faster When is a guy supposed to get a coffee and stretch his legs anymore?
- gwbas1c 2y agoI wonder if there's a way to export to parquet files? They are designed to be extremely compact.
- aldonius 2y agoDuckDB can read and write both SQLite and Parquet.
- kazinator 2y agoDoes the author not know that rsync can use compression (rsync -z | --compress | --compress-level=<n> ), or does he not think it worthwhile to compare that data point? I just tried some comparisons (albeit with a fairly small sqlite file). The text compressed to only about 84% of the size of the compressed binary database, which isn't negligible, but not necessarily worth fussing over in every situation. (The binary compressed to 7.1%, so it's 84% relative to that). bzip2 performed better on both formats; its compression of the binary database was better than gzip's compression of the text (91.5%) and bzip2's text was better than binary (92.5). Though that is not available inside rsync, it indicates that if you're going with an external compression solution, maybe gzip isn't the best choice if you care about every percentage reduction. If you don't care about every percentage reduction, maybe just rsync compression. One thing worth mentioning is that if you are updating the file, rsync will only compress what is sent. To replicate that with the text solution, you will have to be retaining the text on both sides to do the update between them.
- isaacvando 2y agoNice!
- alienbaby 2y agoI'm surprised sqlite is duplicating data to make indexes? Surely it would just be manipulating groups of pointers?
- nodesocket 2y agoI’ve been looking into a way to replicate a SQLite database and came across the LiteFS project by Fly.io. Seems like a solid drop-in solution backed by FUSE and Consul. Anybody used it in production? My use case is high availability between multiple VMs.
- deleted 2y ago[deleted]
- baxter001 2y agoWhat technologies we have in 2025!
- quantadev 2y agoSince sqlite is just a simple file-level locking DB, I'm pretty shocked they don't have an option to let the indexes be stored in separate files for all kinds of obvious and beneficial reasons, like the fact that you can easily exclude them from backups if they were, and you can make them "rebuild" just by deleting them. Probably their reason for keeping all internal has to do with being sure indexes are never out of sync, but that could just as easily be accomplished with hashing algos.
- conradev 2y agoSQLite has an official tool for this, fwiw: https://www.sqlite.org/rsync.html 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.
- CBLT 2y agoYeah, but unfortunately the SQLite team doesn't include that tool with their "autotools" tarball, which is what most distros (and brew) use to package SQLite. The only way to use the tool is to compile it yourself.
- dgfitz 2y agoRealistically, are you using SQLite if you can’t compile and source control your rev of the codebase? Is that really a big deal?
- rcxdude 2y agoYes, it's extremely common to be using it and not even be compiling anything yourself, let alone C or any support libraries.
- CBLT 2y ago`sqlite3_rsync` must be installed on the remote host too, so now you're cross-compiling for all the hosts you manage. It also must be installed into the PATH the ssh uses, which for a number of operating systems doesn't include /usr/local/bin. So I guess you're now placing your sshd config under configuration management to allow that. These tasks aren't that challenging but they sure are a yak shave.
- conradev 2y agoYeah, that’s a bummer. It does appear to be in nixpkgs, though: nix-shell -p sqlite-rsync
- limaoscarjuliet 2y agoIn case someone is looking for a proper way of making db copy in PostgreSQL and not SQLLite, pg_basebackup is the proper way: https://www.postgresql.org/docs/current/app-pgbasebackup.html https://www.postgresql.org/docs/current/app-pgbasebackup.htm...
- deepsun 2y ago"Skip the index and turn it on later" is an old trick from mainframes and DB2.
- jasonlingx 2y agoHe could’ve skipped the intermediate steps and made it a one-liner by piping the sql dump over ssh right into the sqlite3 reconstruction command.
- pnm45678 2y agoIt all depends what you’re optimizing for. Clearly, when you don’t transfer indexes, you will not have to transfer as much data. However, the tradeoff is that the database is going to have to do more work to regenerate those indexes when you reconstruct it from the text dump at the destination (as opposed to the case where the indexes were included)
- kylecordes 2y agoThis reminds me of something I did back in 2008, we were using Postgres, not SQLite, and we needed to get a backup from one production machine onto several other machines. Copying saturated the network for an hour - And we needed to copy four times if I remember right. We didn't want to wait four hours. I found this tool laying around "udpcast" and used it to send the backup over the network just once to all the destinations at the same time. https://kylecordes.com/2008/multicast-your-db-backups-with-udpcast https://kylecordes.com/2008/multicast-your-db-backups-with-u...
- wyager 2y ago`zfs snapshot` and `zfs send -i` is hard to beat for DB backup.
- elmolino89 2y agoIgnoring for the moment issues of syncing a database where the source DB may be running inserts/updates: if one dumps tables as separate CSV files/streams and using DuckDB converts them to individual parquet files the rsync should run faster since hopefully not every table is modified between each new syncing. There is an obvious overhead of the back and forth conversions but DuckDB can directly export a database to SQLite. I have not tested it myself, so it is just a brainstorming. Last but not least: when compressing/decompressing text dumps use igzip or pigz if you want to speed things up. Also benchmark the compression levels.
- xk3 2y agoGit works pretty well for syncing binary files. You can even do this: $ cat .gitattributes *.db diff=sqlite3 And you can know how the sqlite files changed between commits--but yeah if you make commits while a process is writing new rows or updating data then you'll have data loss if you make a commit on another machine--better to use something like litestream in that case
- KETpXDDzR 1y agossh username@server "sqlite3 my_remote_database.db .dump | gzip -c" | gunzip -c | sqlite3 my_local_database.db