Re: Query related to pg_dump write to a pipe on a windows client and with compressed format

[Tom Lane]

Meera Nair  writes:
> We tried pg_dump write to a pipe with compressed format and faced issue as in 
> thread referred here.
> It was on windows client and for postgres 9.2 version. So during pg_restore, 
> seek error was seen.

Are you really using PG 9.2.something?  If not, then what?

The problem here probably is that pg_restore is failing to detect
that the pipe input file is not seekable.  We've had repeated rounds
of fixes to try to detect that reliably on Windows.  I think it works
if you are running a current PG release (something shipped in the last
six months), but maybe not with older ones.

regards, tom lane

Re: Query related to pg_dump write to a pipe on a windows client and with compressed format

[Adrian Klaver]

On 11/30/23 18:57, Meera Nair wrote:

Hi all,

https://www.postgresql.org/message-id/flat/CAKKd065aJ1LuUMw_bhBgmgoM6Ng-cLdBobpzRiU%2BUsdsmW2aOg%40mail.gmail.com#996021734fa788bd1bc737254002ad11
 


We tried pg_dump write to a pipe with compressed format and faced issue 
as in thread referred here.


The complete command line was?

It was on windows client and for postgres 9.2 version. So during 
pg_restore, seek error was seen.
With pg_dump writing the dump to a file and then move to another machine 
for backup, this problem was not there.


The pg_dump command used for this was?

But now the issue is that we need to have a  lot of free space to write 
the dump file

Size of file?

So trying to understand if the compressed format still needs the staging 
of output file with the latest versions? Or we can write the output to a 
pipe to move the data to another machine for backup.


What happens if you don't compress the pg_dump output?

In other words is the network fast enough to compensate for not compressing?



Regards,

Meera



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Query related to pg_dump write to a pipe on a windows client and with compressed format

[Ron Johnson]

On Fri, Dec 1, 2023 at 9:10 AM Meera Nair  wrote:

> Hi all,
>
>
> https://www.postgresql.org/message-id/flat/CAKKd065aJ1LuUMw_bhBgmgoM6Ng-cLdBobpzRiU%2BUsdsmW2aOg%40mail.gmail.com#996021734fa788bd1bc737254002ad11
>
> We tried pg_dump write to a pipe with compressed format and faced issue as
> in thread referred here.
> It was on windows client and for postgres 9.2 version. So during
> pg_restore, seek error was seen.
> With pg_dump writing the dump to a file and then move to another machine
> for backup, this problem was not there.
> But now the issue is that we need to have a  lot of free space to write
> the dump file.
>
> If we use tar format, temporary files created in C:\ take space.
> If we use parallel dump format, then also staging the dump output is must.
>
> So trying to understand if the compressed format still needs the staging
> of output file with the latest versions? Or we can write the output to a
> pipe to move the data to another machine for backup.
>

In PG 9.6 (which I have experience with) and beyond, pg_dump
--format=directory compresses by default, and on the fly.