Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have access to DB on remote server where I can do only select command, So, I can execute:

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

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

So, the questions are:

When I connected to remote DB, how to execute PG command to save file on my local machine instead to remote server?

Or, how to execute Linux command to connect to remote DB, execute query and save file on my local machine?

share|improve this question
add comment

3 Answers

up vote 2 down vote accepted

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.

share|improve this answer
add comment

The Linux command is:

psql -h 127.0.0.1 -U username -o file.csv -c 'select id, name from clients;'
share|improve this answer
    
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 '13 at 14:21
    
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 '13 at 14:28
add comment

Use TO STDOUT instead of TO filename and redirect the output to a local file.

If you are in psql, first specify the output file with

\o nice_file_name.csv

then do the COPY:

COPY products 
TO STDOUT 
DELIMITER ',';

This will hopefully create the file and populate it with all products data.

This solution is applicable if using psql interactively, your solution is just as good otherwise (and could be used in a script for example).

share|improve this answer
    
Please, Can You write some example query for that? –  tasmaniski Mar 8 '13 at 11:36
    
Puzled ... why not just \copy ? –  Craig Ringer Mar 8 '13 at 14:19
    
@CraigRinger because I'm not used to it, haha :) –  dezso Mar 8 '13 at 16:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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