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
The trick is to make COPY
send its output to stdout, then pipe the output through gzip:
psql -c "COPY foo_table TO stdout DELIMITER ',' CSV HEADER" \
| gzip > foo_table.csv.gz
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With