1

I connect to a sever over ssh and run 

psql

I want to make a database extraction from the server but I can't save files on the remote because I don't have write-permission. How can I save it locally?

Himmators
  • 14,278
  • 36
  • 132
  • 223

1 Answers1

3

Rather than

ssh myhost psql

use an ssh tunnel to connect a local psql to the remote PostgreSQL, so you can \copy files locally, e.g.

ssh -L 5599:localhost:5432 myhost

then while that session is open, in a separate, local terminal session:

psql -h localhost -p 5599

to connect over the ssh tunnel. This will only work if the remote system allows tcp/ip connections from localhost (127.0.0.1 / ::1). It doesn't have to allow tcp/ip PostgreSQL connections from remote nodes.

Alternately, you can:

ssh myhost psql -c '"copy mytable to stdout"' > local_file.csv

to stream stdout over ssh to a local file.

Note the two layers of quoting, because the local shell consumes the first layer and the remote shell consumes the second layer.

Generally the best solution is to enable PostgreSQL to listen on external public ports, with SSL set up, and use hostssl entries in pg_hba.conf to allow yourself to connect directly over TCP/IP using psql with SSL. Then you'd just:

psql -h myhost
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • I don't have psql installed on my local machine so the first option doesn't work for me. For the second option, I tried $ ssh knolgren@host.net psql -c "SELECT display_product.internal_name, display_product.primary_image_id FROM products.disp lay_product WHERE display_product.primary_image_id IS NOT NULL;" > /test.csv but got a bunch of errors, cold not chdir tohome directory, and psql: warning extra command-line argument "FROM" ignored – Himmators Jun 26 '15 at 14:12
  • Yes, you'll need an extra layer of quoting. Added in edit. As for the first option: it's free, just install the PostgreSQL client tools – Craig Ringer Jun 26 '15 at 14:54