pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL Dump based backup using pipe
10+ messages / 5 participants
[nested] [flat]
* PostgreSQL Dump based backup using pipe
@ 2015-05-27 11:19 girish R G peetle <giri.anamika0@gmail.com>
2015-05-27 11:32 ` Re: PostgreSQL Dump based backup using pipe Jan Lentfer <Jan.Lentfer@web.de>
2015-05-27 14:24 ` Re: PostgreSQL Dump based backup using pipe Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 10+ messages in thread
From: girish R G peetle @ 2015-05-27 11:19 UTC (permalink / raw)
To: pgsql-admin
Hi
We were using named pipe to read dump data from 'pg_dump -Fc' (compressed
format) on Windows.
Restore was failing with 'corrupt dump data' error.
Later found that pg_dump was performing 'illegal seek' on pipe to write the
data, which was causing corrupt dump.
I've two questions.
1. With tar format ( -Ft ) I see that data is written in serial fashion so
we are planning to switched to this format.
Is it safe to use name pipe to read from 'pg_dump -Ft' (tar format) ?
2. On Linux also from strace output of 'pg_dump -Fc' (compressed dump) used
with fifo file, I see 'illegal seek' performed on fifo file too.
But restore doesn't have any issues. Is it safe to use 'pg_dump -Fc' with
output file as fifo on Linux ?
Thanks
Girish
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PostgreSQL Dump based backup using pipe
2015-05-27 11:19 PostgreSQL Dump based backup using pipe girish R G peetle <giri.anamika0@gmail.com>
@ 2015-05-27 11:32 ` Jan Lentfer <Jan.Lentfer@web.de>
1 sibling, 0 replies; 10+ messages in thread
From: Jan Lentfer @ 2015-05-27 11:32 UTC (permalink / raw)
To: pgsql-admin
Am 2015-05-27 13:19, schrieb girish R G peetle:
[...]
> 2. On Linux also from strace output of pg_dump -Fc (compressed dump)
> used with fifo file, I see illegal seek performed on fifo file too.
> But restore doesnt have any issues. Is it safe to use pg_dump -Fc
> with output file as fifo on Linux ?
I have used pg_dump -Fc with fifos on both Linux and BSDs for several
years without any problem, afair.
Jan
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PostgreSQL Dump based backup using pipe
2015-05-27 11:19 PostgreSQL Dump based backup using pipe girish R G peetle <giri.anamika0@gmail.com>
@ 2015-05-27 14:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-05-27 16:25 ` Re: PostgreSQL Dump based backup using pipe girish R G peetle <giri.anamika0@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2015-05-27 14:24 UTC (permalink / raw)
To: girish R G peetle <giri.anamika0@gmail.com>; +Cc: pgsql-admin
girish R G peetle <giri.anamika0@gmail.com> writes:
> We were using named pipe to read dump data from 'pg_dump -Fc' (compressed
> format) on Windows.
> Restore was failing with 'corrupt dump data' error.
> Later found that pg_dump was performing 'illegal seek' on pipe to write the
> data, which was causing corrupt dump.
That should work. What PG version are you using exactly, and can we see a
full example of what you were trying to do?
There are unsupported cases --- I recall in particular that you can't do
a parallel restore from a pipe --- but you should not be getting "corrupt
file" errors.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PostgreSQL Dump based backup using pipe
2015-05-27 11:19 PostgreSQL Dump based backup using pipe girish R G peetle <giri.anamika0@gmail.com>
2015-05-27 14:24 ` Re: PostgreSQL Dump based backup using pipe Tom Lane <tgl@sss.pgh.pa.us>
@ 2015-05-27 16:25 ` girish R G peetle <giri.anamika0@gmail.com>
2015-05-27 16:50 ` Re: PostgreSQL Dump based backup using pipe Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: girish R G peetle @ 2015-05-27 16:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Hi Tom,
Thanks for checking. Here is the details.
PostgreSQL Version - 9.2.8
Dump command : pg_dump.exe -U postgres -Fc -b test
Platform : Windows Server 2008 R2
Output of dump command is redirected to write end of named pipe. From read
end of the pipe data is read.
Also I see below errors in dump output.
*pg_dump: [custom archiver] WARNING: ftell mismatch with expected position
-- ftell used*
Error returned by pg_restore
pg_restore -Fc -U postgres --port=5488 -d <DB> <DUMP-FILE>
*pg_restore: processing data for table "tabel35" pg_restore: [compress_io]
could not uncompress data: invalid bit length repeat*
Thanks
Girish
On Wed, May 27, 2015 at 7:54 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> girish R G peetle <giri.anamika0@gmail.com> writes:
> > We were using named pipe to read dump data from 'pg_dump -Fc' (compressed
> > format) on Windows.
> > Restore was failing with 'corrupt dump data' error.
> > Later found that pg_dump was performing 'illegal seek' on pipe to write
> the
> > data, which was causing corrupt dump.
>
> That should work. What PG version are you using exactly, and can we see a
> full example of what you were trying to do?
>
> There are unsupported cases --- I recall in particular that you can't do
> a parallel restore from a pipe --- but you should not be getting "corrupt
> file" errors.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PostgreSQL Dump based backup using pipe
2015-05-27 11:19 PostgreSQL Dump based backup using pipe girish R G peetle <giri.anamika0@gmail.com>
2015-05-27 14:24 ` Re: PostgreSQL Dump based backup using pipe Tom Lane <tgl@sss.pgh.pa.us>
2015-05-27 16:25 ` Re: PostgreSQL Dump based backup using pipe girish R G peetle <giri.anamika0@gmail.com>
@ 2015-05-27 16:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-05-28 09:10 ` Re: PostgreSQL Dump based backup using pipe girish R G peetle <giri.anamika0@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2015-05-27 16:50 UTC (permalink / raw)
To: girish R G peetle <giri.anamika0@gmail.com>; +Cc: pgsql-admin
girish R G peetle <giri.anamika0@gmail.com> writes:
> Thanks for checking. Here is the details.
> PostgreSQL Version - 9.2.8
Hm. A quick trawl of the git logs didn't find any relevant bug fixes
since 9.2.8.
> Dump command : pg_dump.exe -U postgres -Fc -b test
> Platform : Windows Server 2008 R2
> Output of dump command is redirected to write end of named pipe.
I'm suspicious that that's exposing you to Windows' infamous text-mode
data mangling. You might try using "-f namedpipe" instead of a shell
redirect.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PostgreSQL Dump based backup using pipe
2015-05-27 11:19 PostgreSQL Dump based backup using pipe girish R G peetle <giri.anamika0@gmail.com>
2015-05-27 14:24 ` Re: PostgreSQL Dump based backup using pipe Tom Lane <tgl@sss.pgh.pa.us>
2015-05-27 16:25 ` Re: PostgreSQL Dump based backup using pipe girish R G peetle <giri.anamika0@gmail.com>
2015-05-27 16:50 ` Re: PostgreSQL Dump based backup using pipe Tom Lane <tgl@sss.pgh.pa.us>
@ 2015-05-28 09:10 ` girish R G peetle <giri.anamika0@gmail.com>
2015-05-28 15:27 ` Re: PostgreSQL Dump based backup using pipe Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: girish R G peetle @ 2015-05-28 09:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Hi Tom,
I just tracked pg_dump writing output to a file. (using procmon)
As indicated below data is not written in serial fashion.
If we use name pipe would this behavior cause problem ?
[image: Inline image 2]
Thanks
Girish
On Wed, May 27, 2015 at 10:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> girish R G peetle <giri.anamika0@gmail.com> writes:
> > Thanks for checking. Here is the details.
> > PostgreSQL Version - 9.2.8
>
> Hm. A quick trawl of the git logs didn't find any relevant bug fixes
> since 9.2.8.
>
> > Dump command : pg_dump.exe -U postgres -Fc -b test
>
> > Platform : Windows Server 2008 R2
>
> > Output of dump command is redirected to write end of named pipe.
>
> I'm suspicious that that's exposing you to Windows' infamous text-mode
> data mangling. You might try using "-f namedpipe" instead of a shell
> redirect.
>
> regards, tom lane
>
Attachments:
image.png (image/png, 16.2K) download
view image
* Re: PostgreSQL Dump based backup using pipe
2015-05-27 11:19 PostgreSQL Dump based backup using pipe girish R G peetle <giri.anamika0@gmail.com>
2015-05-27 14:24 ` Re: PostgreSQL Dump based backup using pipe Tom Lane <tgl@sss.pgh.pa.us>
2015-05-27 16:25 ` Re: PostgreSQL Dump based backup using pipe girish R G peetle <giri.anamika0@gmail.com>
2015-05-27 16:50 ` Re: PostgreSQL Dump based backup using pipe Tom Lane <tgl@sss.pgh.pa.us>
2015-05-28 09:10 ` Re: PostgreSQL Dump based backup using pipe girish R G peetle <giri.anamika0@gmail.com>
@ 2015-05-28 15:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-05-28 17:58 ` Re: PostgreSQL Dump based backup using pipe girish R G peetle <giri.anamika0@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2015-05-28 15:27 UTC (permalink / raw)
To: girish R G peetle <giri.anamika0@gmail.com>; +Cc: pgsql-admin
girish R G peetle <giri.anamika0@gmail.com> writes:
> I just tracked pg_dump writing output to a file. (using procmon)
> As indicated below data is not written in serial fashion.
> If we use name pipe would this behavior cause problem ?
It's less efficient with a named pipe, yes, because then pg_dump
can't do that. But this doesn't prove anything one way or another
concerning your original complaint.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PostgreSQL Dump based backup using pipe
2015-05-27 11:19 PostgreSQL Dump based backup using pipe girish R G peetle <giri.anamika0@gmail.com>
2015-05-27 14:24 ` Re: PostgreSQL Dump based backup using pipe Tom Lane <tgl@sss.pgh.pa.us>
2015-05-27 16:25 ` Re: PostgreSQL Dump based backup using pipe girish R G peetle <giri.anamika0@gmail.com>
2015-05-27 16:50 ` Re: PostgreSQL Dump based backup using pipe Tom Lane <tgl@sss.pgh.pa.us>
2015-05-28 09:10 ` Re: PostgreSQL Dump based backup using pipe girish R G peetle <giri.anamika0@gmail.com>
2015-05-28 15:27 ` Re: PostgreSQL Dump based backup using pipe Tom Lane <tgl@sss.pgh.pa.us>
@ 2015-05-28 17:58 ` girish R G peetle <giri.anamika0@gmail.com>
2015-05-28 18:06 ` Re: PostgreSQL Dump based backup using pipe Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 10+ messages in thread
From: girish R G peetle @ 2015-05-28 17:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Hi Tom,
Let me try to explain my concern.
Here two writes were attempted by pg_dump, one from offset 0 and size
2000. In second attempt at offset 79 n length 1888.
WRITE - 0, 2000
WRITE - 79, 1,888
If it is a regular file data written during first attempt will be
overwritten by second attempt ( from file offset 79 will be overwritten by
second write attempt ) and total dump size would be 2000 bytes
If pipe is used this won't happen, total dump size would be ( 2000 + 1888 )
= 3888
Dump data fed to pg_restore will be totally different for 'non-pipe' and
'pipe' based even though they are taken for same DB.
Also here how will pg_restore handle extra ( or unwanted) dump data
collected in case of piped dump ?
I believe this extra ( or unwanted) dump data is causing below error.
*pg_restore: processing data for table "tabel35"pg_restore: [compress_io]
could not uncompress data: invalid bit length repeat*
Thanks
Girish
On Thu, May 28, 2015 at 8:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> girish R G peetle <giri.anamika0@gmail.com> writes:
> > I just tracked pg_dump writing output to a file. (using procmon)
> > As indicated below data is not written in serial fashion.
> > If we use name pipe would this behavior cause problem ?
>
> It's less efficient with a named pipe, yes, because then pg_dump
> can't do that. But this doesn't prove anything one way or another
> concerning your original complaint.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PostgreSQL Dump based backup using pipe
2015-05-27 11:19 PostgreSQL Dump based backup using pipe girish R G peetle <giri.anamika0@gmail.com>
2015-05-27 14:24 ` Re: PostgreSQL Dump based backup using pipe Tom Lane <tgl@sss.pgh.pa.us>
2015-05-27 16:25 ` Re: PostgreSQL Dump based backup using pipe girish R G peetle <giri.anamika0@gmail.com>
2015-05-27 16:50 ` Re: PostgreSQL Dump based backup using pipe Tom Lane <tgl@sss.pgh.pa.us>
2015-05-28 09:10 ` Re: PostgreSQL Dump based backup using pipe girish R G peetle <giri.anamika0@gmail.com>
2015-05-28 15:27 ` Re: PostgreSQL Dump based backup using pipe Tom Lane <tgl@sss.pgh.pa.us>
2015-05-28 17:58 ` Re: PostgreSQL Dump based backup using pipe girish R G peetle <giri.anamika0@gmail.com>
@ 2015-05-28 18:06 ` Scott Ribe <scott_ribe@elevated-dev.com>
2015-06-25 14:23 ` Re: PostgreSQL Dump based backup using pipe naveen kumar <mnaveendba2@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Scott Ribe @ 2015-05-28 18:06 UTC (permalink / raw)
To: girish R G peetle <giri.anamika0@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
On May 28, 2015, at 11:58 AM, girish R G peetle <giri.anamika0@gmail.com> wrote:
>
> If pipe is used…
You’re assuming that it writes to files and writes to pipes the same way. That is not a valid assumption.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PostgreSQL Dump based backup using pipe
2015-05-27 11:19 PostgreSQL Dump based backup using pipe girish R G peetle <giri.anamika0@gmail.com>
2015-05-27 14:24 ` Re: PostgreSQL Dump based backup using pipe Tom Lane <tgl@sss.pgh.pa.us>
2015-05-27 16:25 ` Re: PostgreSQL Dump based backup using pipe girish R G peetle <giri.anamika0@gmail.com>
2015-05-27 16:50 ` Re: PostgreSQL Dump based backup using pipe Tom Lane <tgl@sss.pgh.pa.us>
2015-05-28 09:10 ` Re: PostgreSQL Dump based backup using pipe girish R G peetle <giri.anamika0@gmail.com>
2015-05-28 15:27 ` Re: PostgreSQL Dump based backup using pipe Tom Lane <tgl@sss.pgh.pa.us>
2015-05-28 17:58 ` Re: PostgreSQL Dump based backup using pipe girish R G peetle <giri.anamika0@gmail.com>
2015-05-28 18:06 ` Re: PostgreSQL Dump based backup using pipe Scott Ribe <scott_ribe@elevated-dev.com>
@ 2015-06-25 14:23 ` naveen kumar <mnaveendba2@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: naveen kumar @ 2015-06-25 14:23 UTC (permalink / raw)
To: girish R G peetle <giri.anamika0@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin; Scott Ribe <scott_ribe@elevated-dev.com>
/pg_dump -U postgres -h <source host> $srcdb | /usr/local/pgsql/bin/psql -p
$port -U postgres -h <destination host > $destinationdb
It will help you, i guess.
thanks,
Thanks & Regards,
M Naveen Kuamr,
PostgreSQL Database Administrator,
Mobile Number: +91 7755929449.
On Thu, May 28, 2015 at 11:36 PM, Scott Ribe <scott_ribe@elevated-dev.com>
wrote:
> On May 28, 2015, at 11:58 AM, girish R G peetle <giri.anamika0@gmail.com>
> wrote:
> >
> > If pipe is used…
>
> You’re assuming that it writes to files and writes to pipes the same way.
> That is not a valid assumption.
>
>
> --
> Scott Ribe
> scott_ribe@elevated-dev.com
> http://www.elevated-dev.com/
> https://www.linkedin.com/in/scottribe/
> (303) 722-0567 voice
>
>
>
>
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2015-06-25 14:23 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-05-27 11:19 PostgreSQL Dump based backup using pipe girish R G peetle <giri.anamika0@gmail.com>
2015-05-27 11:32 ` Jan Lentfer <Jan.Lentfer@web.de>
2015-05-27 14:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-05-27 16:25 ` girish R G peetle <giri.anamika0@gmail.com>
2015-05-27 16:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-05-28 09:10 ` girish R G peetle <giri.anamika0@gmail.com>
2015-05-28 15:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-05-28 17:58 ` girish R G peetle <giri.anamika0@gmail.com>
2015-05-28 18:06 ` Scott Ribe <scott_ribe@elevated-dev.com>
2015-06-25 14:23 ` naveen kumar <mnaveendba2@gmail.com>
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