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

34

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

51,279
3
136
175

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=,` – NO WAR WITH RUSSIA Nov 08 '17 at 03:25

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

2

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,005
4
11
16

1

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