Dump Postgres databases through SSH

Sometimes you might want to import a remote postgres database into your local database. You could use some intermediate files, but there is a much nicer way to do it:

ssh <host> "pg_dump --clean -Z 9 -U <postgres username> <remote database>" | zcat | psql <local database>

You ssh into the remote host, create a dump which will be send to standard-out on your local machine. There you uncompress it and import it into the local database.

It's as easy as that…

One important node: --clean will drop your local tables, that might not be what you wish for – but I did.