4 ms·

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 d

• comex 3y ago
  You can pass console commands as arguments: sqlite3 /path/to/db.sqlite .dump
• deleted 3y ago
  [deleted]
• troyjfarrell 3y ago
  I believe that you can echo the '.dump' command to SQLite. echo .dump | sqlite3 db | ssh user@rsync.net "dd of=db_dump" It worked for me.
  • dmw_ng 3y ago
    You can also pass special commands in the second argument to SQLite: sqlite3 db .dump | ...
  • rsync 3y ago
    Thanks! I have added that one-liner to the remote commands page: https://www.rsync.net/resources/howto/remote_commands.html https://www.rsync.net/resources/howto/remote_commands.html ... although based on some of your siblings, I changed .dump to .backup ...
    • JNRowe 3y ago
      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.
      • rsync 3y ago
        Fixed. Thank you.
• throwawaaarrgh 3y ago
  sqlite3 my_database .dump | gzip -c | ssh foo "dd of=db_dump" 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?
• oefrha 3y ago
  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.)
  • polyrand 3y ago
    The .dump command creates a read transaction[0], so it shouldn't block any writes. From the SQLite docs: When a SAVEPOINT is the outer-most savepoint and it is not within a BEGIN...COMMIT then the behavior is the same as BEGIN DEFERRED TRANSACTION Internally, the .dump command only runs SELECT queries. [0]: https://github.com/sqlite/sqlite/blob/3748b7329f5cdbab0dc486ac484c1801fa6f08dc/src/shell.c.in#L8439 https://github.com/sqlite/sqlite/blob/3748b7329f5cdbab0dc486...
    • oefrha 3y ago
      A read transaction does block writes, unless you enable WAL mode, which I forgot to mention.
      • diarrhea 3y ago
        WAL Mode is the default anyway, I thought.
        • chasil 3y ago
          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.” https://www.vldb.org/pvldb/vol15/p3535-gaffney.pdf https://www.vldb.org/pvldb/vol15/p3535-gaffney.pdf 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. https://www.sqlite.org/lang_attach.html https://www.sqlite.org/lang_attach.html
• jamietanna 3y ago
  Couldn't you also just `cp` the SQLite DB file?
  • idoubtit 3y ago
    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.
    • jamietanna 3y ago
      Ah super interesting, thanks for sharing!
• teddyh 3y ago
  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.
  • sitkack 3y ago
    What is the computational difference between cat and dd?
    • teddyh 3y ago
      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.
• chasil 3y ago
  Why don't you write one? The whole API is presented in C. https://sqlite.org/c3ref/backup_finish.html https://sqlite.org/c3ref/backup_finish.html