Possible TODO item: copy to/from pipe

Tom Lane wrote:

You can accomplish COPY piping now through psql, but it's a bit awkward:

psql -c "COPY mytable TO stdout" mydb | gzip ...

Thoughts? Is this worth doing, or is the psql -c approach good enough?

I think it's good enough. And there is also

pg_dump -F c -t bigtable -f bigtable.dump

cheers

andrew

On Wed, May 31, 2006 at 11:03:14AM -0400, Tom Lane wrote:

After re-reading what I just wrote to Andreas about how compression
of COPY data would be better done outside the backend than inside,
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';

That's a great syntax :)

Similarly,

COPY mytable FROM 'create_sample_data --table mytable --rows 10000000 |';

would be cool.

(I'm not wedded to the above syntax, it's just an off-the-cuff
thought.)

It will be familiar to Perl users, for better or worse. Come to that,
should the prefixes > and >> also mean their corresponding shell
things?

Of course psql would need the same capability, since the server-side
copy would still be restricted to superusers.

Roight.

You can accomplish COPY piping now through psql, but it's a bit awkward:

psql -c "COPY mytable TO stdout" mydb | gzip ...

Thoughts? Is this worth doing, or is the psql -c approach good enough?

I think it's worth doing :)

Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

Tom Lane wrote:

After re-reading what I just wrote to Andreas about how compression of
COPY data would be better done outside the backend than inside, 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';

(I'm not wedded to the above syntax, it's just an off-the-cuff thought.)

Of course psql would need the same capability, since the server-side
copy would still be restricted to superusers.

Won't help too much, until gzip's output is piped back too, so a
replacement for COPY .. TO STDOUT COMPRESSED would be
COPY ... TO '| /bin/gzip |' STDOUT, to enable clients to receive the
reduced stuff. But clients should be agnostic of server side installed
tools, and probably not be able to address them directly. Sounds like a
potential security issue.

Regards,
Andreas

Andreas Pflug wrote:

Won't help too much, until gzip's output is piped back too, so a
replacement for COPY .. TO STDOUT COMPRESSED would be
COPY ... TO '| /bin/gzip |' STDOUT, to enable clients to receive the
reduced stuff.

Forgot to mention:
COPY COMPRESSED was also meant to introduce a portable format that's
efficient for both text and binary data. Relying on some external XYZzip
version seems not too portable to me.

Regards,
Andreas

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Andreas Pflug
Sent: 31 May 2006 16:41
Cc: Tom Lane; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Possible TODO item: copy to/from pipe

Andreas Pflug wrote:

Won't help too much, until gzip's output is piped back too, so a
replacement for COPY .. TO STDOUT COMPRESSED would be
COPY ... TO '| /bin/gzip |' STDOUT, to enable clients to

receive the

reduced stuff.

Forgot to mention:
COPY COMPRESSED was also meant to introduce a portable format that's
efficient for both text and binary data. Relying on some
external XYZzip
version seems not too portable to me.

It does have that advantage. Gzip and others are not particularly
Windows friendly for example.

Regards, Dave.

Andreas Pflug <pgadmin@pse-consulting.de> writes:

Forgot to mention:
COPY COMPRESSED was also meant to introduce a portable format that's
efficient for both text and binary data. Relying on some external XYZzip
version seems not too portable to me.

I dislike putting this into the backend precisely because it's trying to
impose a one-size-fits-all compression solution. Someone might wish to
use bzip2 instead of gzip, for instance, or tweak the compression level
options of gzip. It's trivial for the user to do that if the
compression program is separate, not trivial at all if it's wired into
COPY. Also, a pipe feature would have uses unrelated to compression,
such as on-the-fly analysis or generation of data.

regards, tom lane

Dave Page wrote:

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Andreas Pflug
Sent: 31 May 2006 16:41
Cc: Tom Lane; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Possible TODO item: copy to/from pipe

Andreas Pflug wrote:

Won't help too much, until gzip's output is piped back too, so a
replacement for COPY .. TO STDOUT COMPRESSED would be
COPY ... TO '| /bin/gzip |' STDOUT, to enable clients to

receive the

reduced stuff.

Forgot to mention:
COPY COMPRESSED was also meant to introduce a portable format that's
efficient for both text and binary data. Relying on some
external XYZzip
version seems not too portable to me.

It does have that advantage. Gzip and others are not particularly
Windows friendly for example.

... as most windows programs are pipe agnostic.

Regards,
Andreas

I dislike putting this into the backend precisely because it's trying to
impose a one-size-fits-all compression solution. Someone might wish to
use bzip2 instead of gzip, for instance, or tweak the compression level
options of gzip. It's trivial for the user to do that if the
compression program is separate, not trivial at all if it's wired into
COPY. Also, a pipe feature would have uses unrelated to compression,
such as on-the-fly analysis or generation of data.

It seems that it would be better to have the options within pg_dump
which would give the most flexibility.

Sincerely,

Joshua D. Drake

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Joshua D. Drake wrote:

I dislike putting this into the backend precisely because it's trying to
impose a one-size-fits-all compression solution. Someone might wish to
use bzip2 instead of gzip, for instance, or tweak the compression level
options of gzip. It's trivial for the user to do that if the
compression program is separate, not trivial at all if it's wired into
COPY. Also, a pipe feature would have uses unrelated to compression,
such as on-the-fly analysis or generation of data.

It seems that it would be better to have the options within pg_dump
which would give the most flexibility.

What about all other client tools?

My COPY WITH COMPRESSION is not the same as taking a copy file and
zipping it; it creates a copy file with BinarySignature that has
compressed bytes in the data part, thus it can be handled by any client
app that can stream binary copy files from/to the server.

Regards,
Andreas

Won't help too much, until gzip's output is piped back too, so a
replacement for COPY .. TO STDOUT COMPRESSED would be

COPY ... TO '|

/bin/gzip |' STDOUT, to enable clients to

receive the

reduced stuff.

Forgot to mention:
COPY COMPRESSED was also meant to introduce a portable

format that's

efficient for both text and binary data. Relying on some external
XYZzip version seems not too portable to me.

It does have that advantage. Gzip and others are not particularly
Windows friendly for example.

... as most windows programs are pipe agnostic.

For the record, gzip on win32 works perfectly fine both as a separate
program and running in a pipe. No problem at all. The only issue is that
it's not available by default. (And possible issues with programs
launching it that don't know how to deal with windows style directory
naming)

//Magnus

pgadmin@pse-consulting.de (Andreas Pflug) writes:

Dave Page wrote:

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Andreas
Pflug
Sent: 31 May 2006 16:41
Cc: Tom Lane; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Possible TODO item: copy to/from pipe

Andreas Pflug wrote:

Won't help too much, until gzip's output is piped back too, so a
replacement for COPY .. TO STDOUT COMPRESSED would be
COPY ... TO '| /bin/gzip |' STDOUT, to enable clients to

receive the

reduced stuff.

Forgot to mention:
COPY COMPRESSED was also meant to introduce a portable format
that's efficient for both text and binary data. Relying on some
external XYZzip version seems not too portable to me.

It does have that advantage. Gzip and others are not particularly
Windows friendly for example.

... as most windows programs are pipe agnostic.

Shall we make PostgreSQL less powerful because of that?
--
"cbbrowne","@","cbbrowne.com"
http://cbbrowne.com/info/advocacy.html
"Love is like a snowmobile flying over the frozen tundra that suddenly
flips, pinning you underneath. At night, the ice weasels come."
-- Matt Groening

Chris Browne wrote:

pgadmin@pse-consulting.de (Andreas Pflug) writes:

Dave Page wrote:

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Andreas
Pflug
Sent: 31 May 2006 16:41
Cc: Tom Lane; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Possible TODO item: copy to/from pipe

Andreas Pflug wrote:

Won't help too much, until gzip's output is piped back too, so a
replacement for COPY .. TO STDOUT COMPRESSED would be
COPY ... TO '| /bin/gzip |' STDOUT, to enable clients to

receive the

reduced stuff.

Forgot to mention:
COPY COMPRESSED was also meant to introduce a portable format
that's efficient for both text and binary data. Relying on some
external XYZzip version seems not too portable to me.

It does have that advantage. Gzip and others are not particularly
Windows friendly for example.

... as most windows programs are pipe agnostic.

Shall we make PostgreSQL less powerful because of that?

I never said that. We shall seek solutions that run painless on most
popular platforms are useful to users.
I wonder if we'd be able to ship gzip with the windows installer, to
insure proper integration.

Regards,
Andreas

Andreas Pflug wrote:

Chris Browne wrote:

pgadmin@pse-consulting.de (Andreas Pflug) writes:

Dave Page wrote:

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Andreas
Pflug
Sent: 31 May 2006 16:41
Cc: Tom Lane; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Possible TODO item: copy to/from pipe

Andreas Pflug wrote:

Won't help too much, until gzip's output is piped back too, so a
replacement for COPY .. TO STDOUT COMPRESSED would be
COPY ... TO '| /bin/gzip |' STDOUT, to enable clients to

receive the

reduced stuff.

Forgot to mention:
COPY COMPRESSED was also meant to introduce a portable format
that's efficient for both text and binary data. Relying on some
external XYZzip version seems not too portable to me.

It does have that advantage. Gzip and others are not particularly
Windows friendly for example.

... as most windows programs are pipe agnostic.

Shall we make PostgreSQL less powerful because of that?

I never said that. We shall seek solutions that run painless on most
popular platforms are useful to users.
I wonder if we'd be able to ship gzip with the windows installer, to
insure proper integration.

I wish somebody would explain to me the compelling use case for this. We
do not have to build every possible capability into Postgres. As Tony
Hoare said, "Inside every large problem is a small problem struggling to
get out."

This just seems like creeping featurism to me.

cheers

andrew

On Wed, May 31, 2006 at 02:46:29PM -0400, Andrew Dunstan wrote:

I wish somebody would explain to me the compelling use case for
this.

As with "in-place upgrades,"[1]A feature people seem to think we don't need, although convincing cases have been made for it. -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter the compelling use case is being short
on disk space. For somebody with a multi-TB (or whatever figure
sounds big this week) PostgreSQL database, it may be impossible to get
space for twice or more that. Giving people the option to stream
COPYs through a pipe would alleviate a lot of pain.

Cheers,
D

[1]: A feature people seem to think we don't need, although convincing cases have been made for it. -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter
cases have been made for it.
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

David Fetter wrote:

On Wed, May 31, 2006 at 02:46:29PM -0400, Andrew Dunstan wrote:

I wish somebody would explain to me the compelling use case for
this.

As with "in-place upgrades,"[1] the compelling use case is being short
on disk space. For somebody with a multi-TB (or whatever figure
sounds big this week) PostgreSQL database, it may be impossible to get
space for twice or more that. Giving people the option to stream
COPYs through a pipe would alleviate a lot of pain.

Cheers,
D

[1] A feature people seem to think we don't need, although convincing
cases have been made for it.

But why is that hugely better than piping psql output to gzip?

The Unix philosophy is to use small chains of tools rather than put
everything into one big tool.

Thus, this is quite unlike inplace upgrade, which I agree would be great
(and where I would far rather see people spend their efforts), because
unlike for this "feature" there is no viable alternative.

cheers

andrew

On 31/5/06 18:28, "Magnus Hagander" <mha@sollentuna.net> wrote:

Won't help too much, until gzip's output is piped back too, so a
replacement for COPY .. TO STDOUT COMPRESSED would be

COPY ... TO '|

/bin/gzip |' STDOUT, to enable clients to

receive the

reduced stuff.

Forgot to mention:
COPY COMPRESSED was also meant to introduce a portable

format that's

efficient for both text and binary data. Relying on some external
XYZzip version seems not too portable to me.

It does have that advantage. Gzip and others are not particularly
Windows friendly for example.

... as most windows programs are pipe agnostic.

For the record, gzip on win32 works perfectly fine both as a separate
program and running in a pipe. No problem at all. The only issue is that
it's not available by default. (And possible issues with programs
launching it that don't know how to deal with windows style directory
naming)

Exactly my point; how many production Windows servers do you have with gzip
anywhere near them? Andreas' point about pipes is also valid though - it's
simply not the norm on Windows as I found when we were porting Slony
(more.exe barfs at >8MB being pipe in).

Regards, Dave.

Andrew Dunstan wrote:

David Fetter wrote:

As with "in-place upgrades,"[1] the compelling use case is being short
on disk space. For somebody with a multi-TB (or whatever figure
sounds big this week) PostgreSQL database, it may be impossible to get
space for twice or more that. Giving people the option to stream
COPYs through a pipe would alleviate a lot of pain.

But why is that hugely better than piping psql output to gzip?

psql output has already travelled over the network.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On 31/5/06 19:13, "Andreas Pflug" <pgadmin@pse-consulting.de> wrote:

I wonder if we'd be able to ship gzip with the windows installer, to
insure proper integration.

'Fraid not. It's GPL'd.

Regards, Dave.

On May 31, 2006, at 12:58 PM, Dave Page wrote:

On 31/5/06 19:13, "Andreas Pflug" <pgadmin@pse-consulting.de> wrote:

I wonder if we'd be able to ship gzip with the windows installer, to
insure proper integration.

'Fraid not. It's GPL'd.

Well, one implementation of it is. zlib is new-bsd-ish, though, and
includes
minigzip, which should be just fine for use in a pipe on windows.

(Not that that's an argument one way or the other as to whether this
is something we should do).

Cheers,
Steve

I wonder if we'd be able to ship gzip with the windows

installer, to

insure proper integration.

'Fraid not. It's GPL'd.

Well, if we want to go down that route, we could probably hack up
something simple around zlib. IIRC, there's even sample code in there
for how to write a gzip pipe program...

No, not as convenient, but should be handlable.

//Magnus

Andreas Pflug <pgadmin@pse-consulting.de> writes:

My COPY WITH COMPRESSION is not the same as taking a copy file and
zipping it; it creates a copy file with BinarySignature that has
compressed bytes in the data part, thus it can be handled by any client
app that can stream binary copy files from/to the server.

If you mean you're compressing each data field separately, that's surely
a very bad idea. If you mean you're compressing everything except the
file header, I fail to see the value. Binary is binary. I *seriously*
doubt there are clients out there that look for a PGCOPY header before
deciding whether to send the file to the server or not. And a client
that did know that much about a PGCOPY file would likely spit up on a
critical flag it didn't recognize, anyway.

regards, tom lane

Dave Page <dpage@vale-housing.co.uk> writes:

Exactly my point; how many production Windows servers do you have with gzip
anywhere near them? Andreas' point about pipes is also valid though - it's
simply not the norm on Windows as I found when we were porting Slony
(more.exe barfs at >8MB being pipe in).

I don't see that we should allow Windows' deficiencies to be an argument
against providing a facility that would be useful on all our other platforms.

regards, tom lane

Alvaro Herrera wrote:

Andrew Dunstan wrote:

But why is that hugely better than piping psql output to gzip?

psql output has already travelled over the network.

As I understand Tom's suggestion, it does not involve compression of
over the wire data. He suggested that on the server you would be able to do:

COPY mytable TO '| gzip >/home/tgl/mytable.dump.gz';

and that there could be an equivalent extension on psql's \copy command, as an alternative to doing

psql -c "COPY mytable TO stdout" mydb | gzip ...

It's the second piece especially that seems to me unnecessary.

So I am still unconvinced.

cheers

andrew

On 31/5/06 21:10, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

Dave Page <dpage@vale-housing.co.uk> writes:

Exactly my point; how many production Windows servers do you have with gzip
anywhere near them? Andreas' point about pipes is also valid though - it's
simply not the norm on Windows as I found when we were porting Slony
(more.exe barfs at >8MB being pipe in).

I don't see that we should allow Windows' deficiencies to be an argument
against providing a facility that would be useful on all our other platforms.

It's not about a primarily GUI based OS not being able to do everything a
traditionally command line based OS can do on the command line, it's about
providing a solution that will work on either and remain portable. Whilst I
agree with your objection to using pg_lzcompress, I for one would rather see
a builtin solution that I know will work whatever platform/box I'm on
without having to go and download additional tools.

Regards, Dave.

On Wed, May 31, 2006 at 01:08:28PM -0700, Steve Atkins wrote:

On May 31, 2006, at 12:58 PM, Dave Page wrote:

On 31/5/06 19:13, "Andreas Pflug" <pgadmin@pse-consulting.de> wrote:

I wonder if we'd be able to ship gzip with the windows installer, to
insure proper integration.

'Fraid not. It's GPL'd.

Well, one implementation of it is. zlib is new-bsd-ish, though, and
includes minigzip, which should be just fine for use in a pipe on
windows.

Even then it's not relevent. The Windows Installer is already GPL'd by
the fact it includes readline. zlib is indeed straight BSD like.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Dave Page wrote:

It's not about a primarily GUI based OS not being able to do
everything a traditionally command line based OS can do on the
command line, it's about providing a solution that will work on
either and remain portable. Whilst I agree with your objection to
using pg_lzcompress,

Well, pg_lzcompress is in the backend for more than 6 years now, strange
the objections arise now. However, a replacement for it might be a good
idea, since apparently the fastest gzip algorithm is 3x faster for 10%
better compression. TOAST write performance would probably profit
significantly from a better algorithm.

I wonder what other use-cases exist for server side copy filters beyond
compression.

Regards,
Andreas

I wonder if we'd be able to ship gzip with the windows

installer, to

insure proper integration.

'Fraid not. It's GPL'd.

Well, one implementation of it is. zlib is new-bsd-ish, though, and
includes minigzip, which should be just fine for use in a pipe on
windows.

Even then it's not relevent. The Windows Installer is already
GPL'd by the fact it includes readline. zlib is indeed
straight BSD like.

Uh. The installer does *not* include readline.

We do include PostGIS, but the PostGIS people themselves don't consider
us GPLed because of that ;-)

Everything else is != GPL.

//Magnus

Uh. The installer does *not* include readline.

We do include PostGIS, but the PostGIS people themselves don't consider
us GPLed because of that ;-)

That is a tad different. PostgreSQL does not link to Postgis. Readline
does :)

Sincerely,

Joshua D. Drake

Everything else is != GPL.

//Magnus

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

On Wed, May 31, 2006 at 11:20:21PM +0200, Magnus Hagander wrote:

Uh. The installer does *not* include readline.

Terribly sorry, I misinterpreted the thread about it at the beginning
of the year.

http://archives.postgresql.org/pgsql-hackers/2006-02/msg00539.php

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

On 5/31/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:

After re-reading what I just wrote to Andreas about how compression of
COPY data would be better done outside the backend than inside, 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';

(I'm not wedded to the above syntax, it's just an off-the-cuff thought.)

Of course psql would need the same capability, since the server-side
copy would still be restricted to superusers.

You can accomplish COPY piping now through psql, but it's a bit awkward:

psql -c "COPY mytable TO stdout" mydb | gzip ...

Thoughts? Is this worth doing, or is the psql -c approach good enough?

You can accomplish it now with help of FIFOs, like

\! mkfifo /tmp/psqlfifo
\! chmod 666 /tmp/psqlfifo
-- I know 666 is a dangerous number. ;)
\! gzip -9 < /tmp/psqlfifo > /tmp/psqlcopy.gz
COPY foo TO '/tmp/psqlfifo';

...though ability to pipe "directly" is desirable feature.

Regards,
Dawid

After re-reading what I just wrote to Andreas about how compression of
COPY data would be better done outside the backend than inside, 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';

(I'm not wedded to the above syntax, it's just an off-the-cuff thought.)

Of course psql would need the same capability, since the server-side
copy would still be restricted to superusers.

You can accomplish COPY piping now through psql, but it's a bit awkward:

psql -c "COPY mytable TO stdout" mydb | gzip ...

Thoughts? Is this worth doing, or is the psql -c approach good enough?

To be honest, I don't see much benefit in it. You can already accomplish
what you want to accomplish easily enough.

If you want to muck with COPY, I'd like to see it accept a query as:

psql -c "COPY select * from mytable where foo='bar' TO stdout" mydb | gzip
...

Martijn van Oosterhout wrote:
-- Start of PGP signed section.

On Wed, May 31, 2006 at 01:08:28PM -0700, Steve Atkins wrote:

On May 31, 2006, at 12:58 PM, Dave Page wrote:

On 31/5/06 19:13, "Andreas Pflug" <pgadmin@pse-consulting.de> wrote:

I wonder if we'd be able to ship gzip with the windows installer, to
insure proper integration.

'Fraid not. It's GPL'd.

Well, one implementation of it is. zlib is new-bsd-ish, though, and
includes minigzip, which should be just fine for use in a pipe on
windows.

Even then it's not relevent. The Windows Installer is already GPL'd by
the fact it includes readline. zlib is indeed straight BSD like.

I assume gzip would be binary in the installer. Does putting a GPL
binary in the installer make the entire thing GPL? I don't think so.

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Andreas Pflug wrote:

Tom Lane wrote:

After re-reading what I just wrote to Andreas about how compression of
COPY data would be better done outside the backend than inside, 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';

For use case, consider this:

COPY mytable TO '| rsh x@y.com > test ';

so you can COPY to another server directly.

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:

For use case, consider this:

COPY mytable TO '| rsh x@y.com > test ';

so you can COPY to another server directly.

Why not rsh psql -c "\copy foobar to test" ?

Regards,
Andreas

Andreas Pflug <pgadmin@pse-consulting.de> writes:

Bruce Momjian wrote:

For use case, consider this:

COPY mytable TO '| rsh x@y.com > test ';

so you can COPY to another server directly.

Why not rsh psql -c "\copy foobar to test" ?

Who knows? The sysadmin could have any reason to prefer one to the other.

One that comes to mind is that he or she may want to automate this and may be
happier granting password-free access to a filesystem server that just holds
encrypted backups than to the live production database.

--
greg