postgresql 导出为 CSV 并在 postgres 中使用 GZIP 进行压缩
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3981730/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Export to CSV and Compress with GZIP in postgres
提问by Sujit
I need to export a big table to csv file and compress it.
我需要将一个大表导出到 csv 文件并压缩它。
I can export it using COPY command from postgres like -
我可以使用 postgres 中的 COPY 命令导出它，例如 -
COPY foo_table to '/tmp/foo_table.csv' delimiters',' CSV HEADER;
COPY foo_table to '/tmp/foo_table.csv' delimiters',' CSV HEADER;
And then can compress it using gzip like -
然后可以使用 gzip 压缩它 -
gzip -c foo_table.csv > foo.gz
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.
这种方法的问题是，在获得最终压缩文件之前，我需要创建这个中间 csv 文件，该文件本身很大。
Is there a way of export table in csv and compressing the file in one step?
有没有一种在csv中导出表格并一步压缩文件的方法？
Regards, Sujit
问候， 苏吉特
回答by Joey Adams
The trick is to make COPYsend its output to stdout, then pipe the output through gzip:
诀窍是COPY将其输出发送到标准输出，然后通过 gzip 管道输出：
psql -c "COPY foo_table TO stdout DELIMITER ',' CSV HEADER" \
| gzip > foo_table.csv.gz
回答by mlissner
Expanding a bit on @Joey's answer, below adds support for a couple more features available in the manual.
对@Joey 的回答进行了一些扩展，下面添加了对手册中更多可用功能的支持。
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.
此操作不如 SQL COPY 命令有效，因为所有数据都必须通过客户端/服务器连接。对于大量数据，SQL 命令可能更可取。
回答by abhi shukla
You can use directly, as per docs, https://www.postgresql.org/docs/9.4/sql-copy.html
您可以根据文档直接使用https://www.postgresql.org/docs/9.4/sql-copy.html
COPY foo_table to PROGRAM 'gzip > /tmp/foo_table.csv' delimiters',' CSV HEADER;
