When I was using MySQL, I could run a command that SSHs into my server and copy the database to my local machine.

ssh -t remoteserver 'mysqldump --compress -u dbuser --password="password" db_name' | /usr/local/mysql/bin/mysql -u root --password="password" local_db_name

How can I do the same thing with PostgreSQL?


If the DB server takes database connections from your host (usually on port 5432), then you don't need to use SSH. In this case, you need to do the following:

pg_dump -h your_db_host -U db_user -C db_name | psql -h localhost -d local_db_name -U local_db_user

If you can only connect to your database from the host itself, you have to add the SSH logic from your MySQL example.

With -C, you will get a CREATE DATABASE command in the beginning of the dump so you don't have to create it yourself. The above command will ask for a password twice unless you store these in a .pgpass file (assuming you are on linux/Unix). Also, the local connection may be different if your DB doesn't listen on TCP (in this case just omit the -h localhost part).

For further details and options, see pg_dump documentation.

  • means you need to have the same password on both servers, right? – Florian Heigl Mar 13 '15 at 23:23
  • 1
    @FlorianHeigl No. – dezso Jan 18 '17 at 9:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.