pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery related to pg_dump write to a pipe on a windows client and with compressed format
4+ messages / 4 participants
[nested] [flat]
* Query related to pg_dump write to a pipe on a windows client and with compressed format
@ 2023-12-01 02:57 Meera Nair <mnair@commvault.com>
0 siblings, 3 replies; 4+ messages in thread
From: Meera Nair @ 2023-12-01 02:57 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>; +Cc: Joel Howard <joelhoward@commvault.com>
Hi all,
https://www.postgresql.org/message-id/flat/CAKKd065aJ1LuUMw_bhBgmgoM6Ng-cLdBobpzRiU%2BUsdsmW2aOg%40m...
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Query related to pg_dump write to a pipe on a windows client and with compressed format
@ 2023-12-01 15:04 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Meera Nair <mnair@commvault.com>
2 siblings, 0 replies; 4+ messages in thread
From: Ron Johnson @ 2023-12-01 15:04 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Fri, Dec 1, 2023 at 9:10 AM Meera Nair <mnair@commvault.com> wrote:
> Hi all,
>
>
> https://www.postgresql.org/message-id/flat/CAKKd065aJ1LuUMw_bhBgmgoM6Ng-cLdBobpzRiU%2BUsdsmW2aOg%40m...
>
> 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Query related to pg_dump write to a pipe on a windows client and with compressed format
@ 2023-12-01 16:30 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Meera Nair <mnair@commvault.com>
2 siblings, 0 replies; 4+ messages in thread
From: Adrian Klaver @ 2023-12-01 16:30 UTC (permalink / raw)
To: Meera Nair <mnair@commvault.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>; +Cc: Joel Howard <joelhoward@commvault.com>
On 11/30/23 18:57, Meera Nair wrote:
> Hi all,
>
> https://www.postgresql.org/message-id/flat/CAKKd065aJ1LuUMw_bhBgmgoM6Ng-cLdBobpzRiU%2BUsdsmW2aOg%40m... <https://www.postgresql.org/message-id/flat/CAKKd065aJ1LuUMw_bhBgmgoM6Ng-cLdBobpzRiU%2BUsdsmW2aOg%40m...;
>
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Query related to pg_dump write to a pipe on a windows client and with compressed format
@ 2023-12-01 18:19 Tom Lane <tgl@sss.pgh.pa.us>
parent: Meera Nair <mnair@commvault.com>
2 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2023-12-01 18:19 UTC (permalink / raw)
To: Meera Nair <mnair@commvault.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>; Joel Howard <joelhoward@commvault.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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-12-01 18:19 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-12-01 02:57 Query related to pg_dump write to a pipe on a windows client and with compressed format Meera Nair <mnair@commvault.com>
2023-12-01 15:04 ` Ron Johnson <ronljohnsonjr@gmail.com>
2023-12-01 16:30 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-12-01 18:19 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox