Export to CSV and Compress with GZIP in postgres

Question

I need to export a big table to csv file and compress it.

I can export it using COPY command from postgres like -

COPY foo_table to '/tmp/foo_table.csv' delimiters',' CSV HEADER;

And then can compress it using gzip like -

gzip -c foo_table.csv > foo.gz

The problem with this approach is, I need to create this intermediate csv file, which itself is huge, before I get my final compressed file.

Is there a way of export table in csv and compressing the file in one step?

Regards, Sujit

Answer 1

Expanding a bit on @Joey's answer, below adds support for a couple more features available in the manual.

psql -c "COPY \"Foo_table\" (column1, column2) TO stdout DELIMITER ',' CSV HEADER" \
    | gzip > foo_table.csv.gz

If you have capital letters in your table name (woe be onto you), you need the \" before and after the table name.

The second thing I've added is column listing.

Also note from the docs:

This operation is not as efficient as the SQL COPY command because all data must pass through the client/server connection. For large amounts of data the SQL command might be preferable.

Answer 2

You can use directly, as per docs, https://www.postgresql.org/docs/9.4/sql-copy.html

COPY foo_table to PROGRAM 'gzip > /tmp/foo_table.csv' delimiter ',' CSV HEADER;

Answer 3

PostgreSQL 13.4

psql command \copy also works combined with SELECT column_1, column_2, ... and timestamp date +"%Y-%m-%d_%H%M%S" for filename dump.

\copy (SELECT id, column_1, column_2, ... FROM foo_table) \ 
TO PROGRAM 'gzip > ~/Downloads/foo_table_dump_`date +"%Y-%m-%d_%H%M%S"`.csv.gz' \
DELIMITER ',' CSV HEADER ;