Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 4 down vote accepted

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.

share|improve this answer
means you need to have the same password on both servers, right? – Florian Heigl Mar 13 '15 at 23:23

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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