21

I have read-only access to a database on a remote server. So, I can execute:

COPY products TO '/tmp/products.csv' DELIMITER ',';

But on that server I don't have permissions to create/save a file, so I need to do this on my local machine.

When I connect to the remote database, how can I execute a command to save the file on my local machine instead of the remote server?

Or, how can I execute a Linux command to connect to the remote database, execute a query, and save the output as a file to my local machine?

Improve this question

2 Answers 2

Reset to default
36

Both the approaches already suggested appear to be unnecessarily complicated.

Just use psql's built-in \copy command, which works just like server-side COPY but does a copy over the wire protocol to the client and uses client paths.

Because it's a psql backslash command you omit the trailing semicolon, eg:

\copy products TO '/tmp/products.csv' CSV DELIMITER ','

See the \copy entry in the manual for the psql command and the COPY command documenation for more detail.

Just like COPY you can use \copy with a (SELECT ...) query instead of a table name when copying data out (but not in).

A generally inferior alternative that can be useful in a few limited situations is to use:

psql -t -P format=unaligned -P fieldsep_zero=on -c 'SELECT * FROM tablename'

and use the -o flag or shell output redirection to write the output to a file. You should almost always use \copy in preference to this.

Improve this answer
4
  • But this doesn't allow for transactions :(
    – Reza S
    Dec 24, 2015 at 2:35
  • 2
    Um, sure it does. Use a here-document to feed psql a script, starting with BEGIN, then doing your \copy commands, then a COMMIT. Or use psql -f to run a script rather than using a here-document.
    – Craig Ringer
    Dec 24, 2015 at 2:36
  • Thanks for getting back... that's what I ended up doing and it worked =)
    – Reza S
    Dec 24, 2015 at 4:29
  • You can use -A instead of -P format=unaligned and also i think you need a -P fieldsep=,
    – Evan Carroll
    Nov 8, 2017 at 3:25
3

The Linux command is:

psql -h 127.0.0.1 -U username -o file.csv -c 'select id, name from clients;'
Improve this answer
2
  • 2
    That won't produce CSV, it'll produce formatted text output. If you added -t -P format=unaligned to that command you'd get something a little closer, like buggy pipe-delimited CSV, but pipes in the text wouldn't get escaped so it'd be invalid.
    – Craig Ringer
    Mar 8, 2013 at 14:21
  • 1
    Oh, you'd also want -P fieldsep=',' except that this would be even more likely to cause errors due to lack of escaping. -P fieldsep_zero=on would be OK if you didn't mind parsing null-byte-delimited text, as null bytes can't occur in psql output naturally.
    – Craig Ringer
    Mar 8, 2013 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.