Thread: PostgreSQL Dump based backup using pipe
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
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
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
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
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
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
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 ?
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
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
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
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
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
/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 & Regards,
M Naveen Kuamr,
PostgreSQL Database Administrator,
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