When I was using MySQL, I could run a command that would SSH 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
  • For anyone reading this in the future: Enter the password for the first (remote) DB, press enter, type the password for your localhost DB user, press enter. The fact that it prompted for both passwords (and that UNIX never shows passwords as you tye them) confused me as well. – AleksandrH May 13 '20 at 19:57

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.