Export Postgres table on remote server to CSV file on local machine?

Question

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?

Answer 1

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.

Answer 2

The Linux command is:

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

Answer 3

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