PostgreSQL COPY管道输出到gzip，然后到STDOUT [英] PostgreSQL COPY pipe output to gzip and then to STDOUT

问题描述

以下命令效果很好

$ psql -c "copy (select * from foo limit 3) to stdout csv header"

# output
column1,column2
val1,val2
val3,val4
val5,val6

但是，以下内容却没有:

However the following does not:

$ psql -c "copy (select * from foo limit 3) to program 'gzip -f --stdout' csv header"

# output
COPY 3

为什么我将 COPY 3 作为此命令的输出?我希望通过 gzip 后，输出将是压缩的CSV字符串.

Why do I have COPY 3 as the output from this command? I would expect that the output would be the compressed CSV string, after passing through gzip.

下面的命令有效，例如:

The command below works, for instance:

$ psql -c "copy (select * from foo limit 3) to stdout csv header" | gzip -f -c

# output (this garbage is just the compressed string and is as expected)
߉T`M�A �0 ﾥ}6�BL�I+�^E�gv�ijAp���qH�1����� FfВ�,Д���}������+��

如何制作一条直接直接将结果通过管道传输到 gzip 并将压缩后的字符串发送到 STDOUT 的SQL命令?

How to make a single SQL command that directly pipes the result into gzip and sends the compressed string to STDOUT?

推荐答案

当您使用 COPY ... TO PROGRAM 时，PostgreSQL服务器进程(后端)将启动一个新进程并将文件通过管道传输到流程的标准输入.该过程的标准输出丢失.仅当被调用程序将数据写入文件或类似文件时，才使用 COPY ... TO PROGRAM .

When you use COPY ... TO PROGRAM, the PostgreSQL server process (backend) starts a new process and pipes the file to the process's standard input. The standard output of that process is lost. It only makes sense to use COPY ... TO PROGRAM if the called program writes the data to a file or similar.

如果您的目标是压缩通过网络传输的数据，则可以在连接字符串中使用 sslmode = require sslcompression = on 来使用我已内置到PostgreSQL 9.2 中.不幸的是，此功能已被弃用，并且大多数OpenSSL二进制文件都附带了禁用的功能.

If your goal is to compress the data that go across the network, you could use sslmode=require sslcompression=on in your connect string to use the SSL network compression feature I built into PostgreSQL 9.2. Unfortunately this has been deprecated and most OpenSSL binaries are shipped with the feature disabled.

当前存在本机网络压缩正在开发的补丁程序，但是否可疑?即可制作v14.

There is currently a native network compression patch under development, but it is questionable whether that will make v14.

除此之外，您目前无法获得想要的东西.

Other than that, you cannot get what you want at the moment.

这篇关于PostgreSQL COPY管道输出到gzip，然后到STDOUT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！