dump a remote db via ssh and populate local db with postgres

ssh user@remoteserver "PGPASSWORD='passwd' pg_dump -U user bd_name | bzip2 -zv9" | bzcat | psql -U user bd_name

0
By: chronos
2010-09-24 20:25:42

These Might Interest You

  • This command will copy files and directories from a remote machine to the local one. Ensure you are in the local directory you want to populate with the remote files before running the command. To copy a directory and it's contents, you could: ssh user@host "(cd /path/to/a/directory ; tar cvf - ./targetdir)" | tar xvf - This is especially useful on *nix'es that don't have 'scp' installed by default.


    1
    ssh user@host "(cd /path/to/remote/top/dir ; tar cvf - ./*)" | tar xvf -
    dopeman · 2009-03-31 13:08:45 3
  • This command will dump a database on a remote stream to stdout, compress it, stream it to your local machine, decompress it and put it into a file called database.sql.You could even pipe it into mysql on your local machine to restore it immediately. I had to use this recently because the server I needed a backup from didn't have enough disk space.


    8
    ssh user@host "mysqldump -h localhost -u mysqluser -pP@$$W3rD databasename | gzip -cf" | gunzip -c > database.sql
    daws · 2009-10-05 00:57:51 3
  • You can use this to dump you database from remote db to your local db.


    1
    mysqldump --host=[remote host] --user=[remote user] --password=[remote password] -C db_name | mysql --host=localhost --user=[local user] --password=[local password] db_name
    all4artz · 2009-12-19 19:47:07 3
  • This improves on #9892 by compressing the directory on the remote machine so that the amount of data transferred over the network is much smaller. The command uses ssh(1) to get to a remote host, uses tar(1) to archive and compress a remote directory, prints the result to STDOUT, which is written to a local file. In other words, we are archiving and compressing a remote directory to our local box.


    20
    ssh user@host "tar -zcf - /path/to/dir" > dir.tar.gz
    __ · 2011-12-16 05:48:38 2

What Others Think

FYI: you can eliminate the PGPASSWORD= bit by using a ~/.pgpass file on the far side. It also means you aren't storing your pass in your .bash_history and putting it in the process list. Of course there may be a reason you don't want a .pgpass file.
splante · 355 weeks and 2 days ago

What do you think?

Any thoughts on this command? Does it work on your machine? Can you do the same thing with only 14 characters?

You must be signed in to comment.

What's this?

commandlinefu.com is the place to record those command-line gems that you return to again and again. That way others can gain from your CLI wisdom and you from theirs too. All commands can be commented on, discussed and voted up or down.

Share Your Commands



Stay in the loop…

Follow the Tweets.

Every new command is wrapped in a tweet and posted to Twitter. Following the stream is a great way of staying abreast of the latest commands. For the more discerning, there are Twitter accounts for commands that get a minimum of 3 and 10 votes - that way only the great commands get tweeted.

» http://twitter.com/commandlinefu
» http://twitter.com/commandlinefu3
» http://twitter.com/commandlinefu10

Subscribe to the feeds.

Use your favourite RSS aggregator to stay in touch with the latest commands. There are feeds mirroring the 3 Twitter streams as well as for virtually every other subset (users, tags, functions,…):

Subscribe to the feed for: