Обсуждение: Query related to pg_dump write to a pipe on a windows client and with compressed format
Hi all,
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.
Regards,
Meera
On Fri, Dec 1, 2023 at 9:10 AM Meera Nair <mnair@commvault.com> wrote:
Hi all,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.
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 <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.klaver@aklaver.com
Meera Nair <mnair@commvault.com> 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