Speed improvements (db:pull/db:push)

[brodock]

You can use bzcat instead of uncompressing the file, it will be faster (as will not require 2 steps and hd writes).

The command will look like: "bzcat dumpfile.bz2 | mysql -p -u user..."

[kakra]

It should completely stop working with intermediate remote files at all. You can run ssh -L127.33.0.6:3306:$DBHOST:$DBPORT and connect mysqldump to the localhost instead: mysqldump ... -h 127.33.0.6. This would also eliminate the need for transferring the DB password over the wire and being logged plain text. I'm not sure if sshkit allows setting up port forwards, tho.

Storing a compressed local copy may still be beneficial but everything could be forged into one single step:

mysqldump --compress -h 127.33.0.6 -u remoteuser -p remotepw ... |\
  tee localcopy.sql |\
  mysqldump -h 127.0.0.1 -u localuser -p localpw ...

or if compressed local storage is needed (bash only):

mysqldump --compress -h 127.33.0.6 -u remoteuser -p remotepw ... |\
  tee >(bzip2 >localcopy.sql.bz2) |\
  mysqldump -h 127.0.0.1 -u localuser -p localpw ...

or with unobstrusive progress meter (bash only):

mysqldump --compress -h 127.33.0.6 -u remoteuser -p remotepw ... |\
  tee >(bzip2 >localcopy.sql.bz2) >(pv -br) |\
  mysqldump -h 127.0.0.1 -u localuser -p localpw ...

In these examples, the port forwarding to the remote side lives on 127.33.0.6. This was chosen to not collide with the local mysql server probably already listening von 127.0.0.1 (as in this example). Some important parameters have been omitted (e.g., database name). Both examples pretend that you've setup the tunnel before:

ssh -L127.33.0.6:3306:dbserver:3306

There's nothing to type in the SSH shell, thus no command line is transferred. After mysqldump is done, just exit the SSH session. If mysqldump is still connected, SSH won't exit just yet. The remote infrastructure would see database connects coming from the SSH server, not your client.

I never used Postgres but it should work in a very similar way.