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?
Add a comment
|
1 Answer
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
2 Comments
Himmators
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 [email protected] 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
Craig Ringer
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