Thread: PostgreSQL Dump based backup using pipe

PostgreSQL Dump based backup using pipe

From
girish R G peetle
Date:
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


Re: PostgreSQL Dump based backup using pipe

From
Jan Lentfer
Date:
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



Re: PostgreSQL Dump based backup using pipe

From
Tom Lane
Date:
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


Re: PostgreSQL Dump based backup using pipe

From
girish R G peetle
Date:
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

Re: PostgreSQL Dump based backup using pipe

From
Tom Lane
Date:
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


Re: PostgreSQL Dump based backup using pipe

From
girish R G peetle
Date:
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 ?

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

Attachment

Re: PostgreSQL Dump based backup using pipe

From
Tom Lane
Date:
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


Re: PostgreSQL Dump based backup using pipe

From
girish R G peetle
Date:
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

Re: PostgreSQL Dump based backup using pipe

From
Scott Ribe
Date:
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







Re: PostgreSQL Dump based backup using pipe

From
naveen kumar
Date:

/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