9

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 ubuntu@ec2-59-16-143-85.eu-west-1.compute.amazonaws.com -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 ubuntu@ec2-59-16-143-85.eu-west-1.compute.amazonaws.com 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?

Community
  • 1
  • 1
Al D
  • 657
  • 2
  • 10
  • 27
  • Your PostgreSQL may not be allowing outside connections. See [this tutorial](http://www.cyberciti.biz/tips/postgres-allow-remote-access-tcp-connection.html) on how to allow it. Also if you're using firewall, you need to allow inbound connection to TCP port 5432, unless you use non-standard port. – Gyrocode.com May 14 '15 at 02:43
  • The answer that you link to is nothing like the command that you posted. – Falmarri May 14 '15 at 07:14

2 Answers2

8

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 ubuntu@ec2-59-16-143-85.eu-west-1.compute.amazonaws.com -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.

rud
  • 1,012
  • 8
  • 22
pokoli
  • 1,045
  • 7
  • 15
  • Thanks I'll try this when at my machine this evening. – Al D May 14 '15 at 07:23
  • Sorry it was an error on commandline (-C must be before pg_dump and -h flag is not required). Could you try with the new command? – pokoli May 15 '15 at 06:43
  • No that's failing as well - Tried a few variations but getting the error `[sudo] password for alzer: pg_dump: [archiver (db)] connection to database "paycloud_dev" failed: FATAL: Peer authentication failed for user "dev_user"` – Al D May 15 '15 at 18:47
  • @AI-D was able to pull this off using another type of connection string to Postgres.. Maybe you can try that too? `ssh -i -C "pg_dump -Fc postgresql://@:5432/" | pg_restore -d ` – peralmq Jan 09 '17 at 15:32
3

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.

PRIHLOP
  • 1,441
  • 1
  • 16
  • 16