pg_dump from remote server to localhost

Question

Hi can anyone help me dump from a postgreSQL database on a remote AWS server to a postgreSQL database on my local machine.

I've been trying to do it using the answer in this stack post but it keeps failing.

The command I'm using is

pg_dump -C -h ssh [email protected] -U dev_user paycloud_dev | psql -h localhost -U dev_user paycloud_dev

But I keep getting the error

pg_dump: too many command-line arguments (first is "paycloud_dev")

Can't figure out what I'm doing wrong

Just to add, dev_user is the role I've set up in postgreSQL on both the local machine and remote server. paycloud_dev is the name of the database on both (owner is dev_user)

Edit 1

Tried the command below as per a post that has since been deleted for some reason

pg_dump -C -h ec2-59-16-143-85.eu-west-1.compute.amazonaws.com -U dev_user paycloud_dev | psql -h localhost -U dev_user paycloud_dev 

This is now giving me the error

pg_dump: [archiver (db)] connection to database "paycloud_dev" failed: could not connect to server: Connection refused
Is the server running on host "ec2-59-16-143-85.eu-west-1.compute.amazonaws.com" (59.16.143.85) and accepting
TCP/IP connections on port 5432?

I went on to AWS and noted that is the elastic ip of the server. I then tried the following (the private IP address)

pg_dump -C -h 170.30.43.35 -U dev_user paycloud_dev | psql -h localhost -U dev_user paycloud_dev

This asks me for the password for paycloud_dev and when I enter it pauses for a good 2 or 3 minutes and comes back with:

pg_dump: [archiver (db)] connection to database "paycloud_dev" failed: could not connect to server: Connection refused
Is the server running on host "170.30.43.35" and accepting
TCP/IP connections on port 5432?

I've tried editing the AWS security group to add a rule that accepts all traffic (port range 0-65535) but the same error is occurring.

Edit 2

Tried the following as per post by pokoli

ssh [email protected] pg_dump -C -h  -U dev_user paycloud_dev | psql -U dev_user paycloud_dev

It's not working though. It first asks me for the psql password for my laptop then before I can input anything, it gives an error.

[sudo] password for alzer: pg_dump: too many command-line arguments (first is "paycloud_dev")

Try "pg_dump --help" for more information.

Anyone?

Answer 1

You have to connect with ssh to remote host, execute the dump and pipe it to your local machine. The following command should do:

ssh [email protected] -C pg_dump  -U dev_user paycloud_dev | psql -U dev_user paycloud_dev

The command will ask for password of both users if needed and the playcloud_dev database should exists on localhost, otherwise the dump will fail.

Answer 2

Try do this over ssh tunnel.

ssh -fT -L 5432:127.0.0.1:5432 %remote_user_login%@%your_aws_host% sleep 10
pg_dump -C -h localhost -U dev_user paycloud_dev | psql -h localhost -U dev_user paycloud_dev

First line create ssh tunnel and port mapping.

And view your AWS security settings in "Security Groups", may be you forgot opening ports.