Copy PostgreSQL database from a remote server

Posted on

Question :

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?

Answer :

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.

Leave a Reply

Your email address will not be published. Required fields are marked *