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