title: pg_dump from remote server to localhost
body: <p>Hi can anyone help me dump from a postgreSQL database on a remote AWS server to a postgreSQL database on my local machine.</p>
  
  <p>I've been trying to do it using the answer in <a href="https://stackoverflow.com/questions/1237725/how-to-copy-postgres-database-to-another-server">this</a> stack post but it keeps failing.</p>
  
  <p>The command I'm using is </p>
  
  <pre><code>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
  </code></pre>
  
  <p>But I keep getting the error</p>
  
  <pre><code>pg_dump: too many command-line arguments (first is "paycloud_dev")
  </code></pre>
  
  <p>Can't figure out what I'm doing wrong</p>
  
  <p>Just to add, <code>dev_user</code> is the role I've set up in postgreSQL on both the local machine and remote server. <code>paycloud_dev</code> is the name of the database on both (owner is <code>dev_user</code>)</p>
  
  <blockquote>
    <p><strong>Edit 1</strong></p>
  </blockquote>
  
  <p>Tried the command below as per a post that has since been deleted for some reason</p>
  
  <pre><code>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 
  </code></pre>
  
  <p>This is now giving me the error</p>
  
  <pre><code>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?
  </code></pre>
  
  <p>I went on to AWS and noted that is the elastic ip of the server. I then tried the following (the private IP address)</p>
  
  <pre><code>pg_dump -C -h 170.30.43.35 -U dev_user paycloud_dev | psql -h localhost -U dev_user paycloud_dev
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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?
  </code></pre>
  
  <p>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.</p>
  
  <blockquote>
    <p><strong>Edit 2</strong></p>
  </blockquote>
  
  <p>Tried the following as per post by pokoli</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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. </p>
  
  <pre><code>[sudo] password for alzer: pg_dump: too many command-line arguments (first is "paycloud_dev")
  </code></pre>
  
  <p>Try "pg_dump --help" for more information.</p>
  
  <p>Anyone?</p>