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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 7 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
But this doesn't allow for transactions :( – Reza S Dec 24 '15 at 2:35
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 '15 at 2:36
Thanks for getting back... that's what I ended up doing and it worked =) – Reza S Dec 24 '15 at 4:29

The Linux command is:

psql -h -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

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 

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

Your Answer


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.