Postgres Pro
Downloads
Home   >   mailing lists

Thread: COPY TO '|gzip > /my/cool/file.gz'

COPY TO '|gzip > /my/cool/file.gz'

From
Date:
From May 31, 2006; 12:03pm . . .

"It struck me that we are missing a feature that's fairly common in Unix programs.
Perhaps COPY ought to have the ability to pipe its output to a shell command,
or read input from a shell command. "
Maybe something like:
        COPY mytable TO '| gzip >/home/tgl/mytable.dump.gz';

Is such a feature (ie being able to tell postgres to write a compressed file via COPY TO) being worked on ?

-dvs-

Re: COPY TO '|gzip > /my/cool/file.gz'

From
Vibhor Kumar
Date:
On Jul 20, 2011, at 11:29 PM, <david.sahagian@emc.com> <david.sahagian@emc.com> wrote:

> From May 31, 2006; 12:03pm . . .
>
> "It struck me that we are missing a feature that's fairly common in Unix programs.
> Perhaps COPY ought to have the ability to pipe its output to a shell command,
> or read input from a shell command. "
> Maybe something like:
>        COPY mytable TO '| gzip >/home/tgl/mytable.dump.gz';
>
> Is such a feature (ie being able to tell postgres to write a compressed file via COPY TO) being worked on ?
>

You can use STDOUT to pipe output to a shell command and STDIN to read input from shell command.
Something like given below:
psql -c "COPY mytable to STDOUT"|gzip >/home/tgl/mytable.dump.gz

cat filename|psql -c "COPY mytable from STDIN;"

OR psql -c "COPY mytable from STDIN;" < filename

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
Blog: http://vibhork.blogspot.com

Re: COPY TO '|gzip > /my/cool/file.gz'

From
Craig Ringer
Date:
On 21/07/11 01:59, david.sahagian@emc.com wrote:
> From May 31, 2006; 12:03pm . . .
>
> "It struck me that we are missing a feature that's fairly common in Unix programs.
> Perhaps COPY ought to have the ability to pipe its output to a shell command,
> or read input from a shell command. "
> Maybe something like:
>         COPY mytable TO '| gzip >/home/tgl/mytable.dump.gz';
>
> Is such a feature (ie being able to tell postgres to write a compressed file via COPY TO) being worked on ?

Not that I've heard of.

In addition to the hint given about using copy to stdout from a "psql
-c" invocation, there is another option. You can create a named pipe
(fifo) file node and use COPY TO to write to it. eg:

$ mkfifo gzfifo; gzip < gzfifo > out.gz &
$ psql -c "COPY tablename TO '/server/path/to/gzfifo';"

gzip will automatically terminate when the output file is closed. The
fifo will not be removed and can be re-used.

Supporting COPY to a pipe would be interesting, though the security
implications would need plenty of thought.

--
Craig Ringer

Re: COPY TO '|gzip > /my/cool/file.gz'

From
Willy-Bas Loos
Date:
On Wed, Jul 20, 2011 at 8:53 PM, Vibhor Kumar
<vibhor.kumar@enterprisedb.com> wrote:
> You can use STDOUT to pipe output to a shell command and STDIN to read input from shell command.
> Something like given below:
> psql -c "COPY mytable to STDOUT"|gzip >/home/tgl/mytable.dump.gz
>
> cat filename|psql -c "COPY mytable from STDIN;"
>
> OR psql -c "COPY mytable from STDIN;" < filename

nice one, that works great!
(zcat instead of cat, though)

--
"Patriotism is the conviction that your country is superior to all
others because you were born in it." -- George Bernard Shaw