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.