I've been (proudly) noting these elegant one-liners for ... 18 years now:
pg_dump -U postgres db | ssh user@rsync.net "dd of=db_dump"
mysqldump -u mysql db | ssh user@rsync.net "dd of=db_dump"
... but what is the equivalent command for SQLite ?
I see that there is a '.dump' command for use within the SQLite console but that wouldn't be suitable for pipelining ... is there not a standalone 'sqlitedump' binary ?
The .backup command is better than than the .dump command suggested in siblings, as it doesn’t block all writes until it completes. You can then do anything with the backup. (Do keep in mind there’s the .backup never finishing problem on a db with non-stop writes.)
The '.backup' command also carries across the metadata too, so you don't lose things like 'application_id'¹.
I mention this as I once wasted a bunch of time trying to get a backup created from a '.dump | sqlite3' pipe to work before taking a proper look at the application code, and finally saw that it silently ignored databases without the correct 'application_id' or 'user_version'.
It is not, due to the many restrictions upon it, and warnings in its use.
“To accelerate searching the WAL, SQLite creates a WAL index in shared memory. This improves the performance of read transactions, but the use of shared memory requires that all readers must be on the same machine [and OS instance]. Thus, WAL mode does not work on a network filesystem.”
“It is not possible to change the page size after entering WAL mode.”
“In addition, WAL mode comes with the added complexity of checkpoint operations and additional files to store the WAL and the WAL index.”
SQLite does not guarantee ACID consistency with ATTACH DATABASE in WAL mode. “Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not.
You could replace ‘dd’ with ‘cat’ here, as in “ssh user@rsync.net "cat > d_dump"”. The ‘dd’ command has absolutely no reason to be used in the vast majority of cases.
AFAIK, ‘dd’ reads and writes a block at a time (whatever the its default block size is), while ‘cat’ uses whatever buffer sizes it thinks is the most efficient for the devices it is using for that invocation.
Sadly, that doesn't look like it will work. The '.backup' command needs a filename and won't accept '-' or even '/dev/stdout', at least in the version available on Debian it will simply error out with "missing FILENAME argument on .backup".
As noted '.dump' will work in your pipeline, but with the caveats I and others noted elsewhere.
Technically .backup is better because it locks less and batches operations, rather than doing a bunch of selects in a transaction. Do you prefer slowly filling up local disk or locking all writes?
In many cases, `cp` on the DB file won't be enough. For instance, when WAL (Write Ahead Logging) is enabled (which is much faster than the old rollback journal), some data may lie in other files aside the main DB.
I once had a problem where my application couldn't read from a SQLite DB whose file was read-only. Turned out that even a read-only access to the DB required a write access to the directory, for a WAL DB. This was partly fixed years later, but I'd learned the hard way that a SQLite DB may be more than a single file.
I see that there is a '.dump' command for use within the SQLite console but that wouldn't be suitable for pipelining ... is there not a standalone 'sqlitedump' binary ?