Export remote Postgres table to CSV file on local machine

Question

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?

Craig Ringer · Accepted Answer · 2013-03-08T14:32:14.483

37

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.

edited Mar 08 '13 at 14:32

answered Mar 08 '13 at 14:16

Craig Ringer

56,343
5
158
190

But this doesn't allow for transactions :( – Reza S Dec 24 '15 at 02: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 '15 at 02:36
Thanks for getting back... that's what I ended up doing and it worked =) – Reza S Dec 24 '15 at 04:29
You can use -A instead of -P format=unaligned and also i think you need a -P fieldsep=, – Evan Carroll Nov 08 '17 at 03:25

score 4 · Answer 2 · answered Mar 08 '13 at 11:51

4

The Linux command is:

psql -h 127.0.0.1 -U username -o file.csv -c 'select id, name from clients;'

answered Mar 08 '13 at 11:51

tasmaniski

1,175
4
13
16

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 08 '13 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 08 '13 at 14:28

Export remote Postgres table to CSV file on local machine

2 Answers2

Linked

Related