parallel option in pg_restore

I'm testing 8.4.4 (on Windows) before upgrading our app to this PG
version.

When running pg_restore with "-j 2" parallel option, I'm getting the
following error:

"pg_restore: [custom archiver] dumping a specific TOC data block out of
order is not supported without ID on this input stream (fseek required)"

in the log file.

Mind you, the backup (which I'm restoring here) was done in "custom"
mode ( -F c) using pg_dump version 8.2.5.
Is this error results from version differences between pg_dump and
pg_restore?

The reason I'm using "old" backups (created with older pg_dump version)
is that I'm trying to save time during upgrade, and I have these big
backup files already created.

TIA,
Igor Neyman

"Igor Neyman" <ineyman(at)perceptron(dot)com> wrote:

> I'm testing 8.4.4

> pg_restore with "-j 2" parallel option

> using pg_dump version 8.2.5.

> Is this error results from version differences between pg_dump and
> pg_restore?

Yeah, probably.

I suspect that you have the choice of dumping with the newer
pg_dump, or not using the new "-j 2" option on pg_restore.

-Kevin

"Igor Neyman" <ineyman(at)perceptron(dot)com> writes:
> I'm testing 8.4.4 (on Windows) before upgrading our app to this PG
> version.
> When running pg_restore with "-j 2" parallel option, I'm getting the
> following error:
> "pg_restore: [custom archiver] dumping a specific TOC data block out of
> order is not supported without ID on this input stream (fseek required)"

We have gotten several reports of this, but none of the developers have
been able to reproduce it. Can you provide an exact test case?

regards, tom lane

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Tuesday, June 22, 2010 10:37 AM
> To: Igor Neyman
> Cc: pgsql-admin(at)postgresql(dot)org
> Subject: Re: [ADMIN] parallel option in pg_restore
>
> "Igor Neyman" <ineyman(at)perceptron(dot)com> writes:
> > I'm testing 8.4.4 (on Windows) before upgrading our app to this PG
> > version.
> > When running pg_restore with "-j 2" parallel option, I'm
> getting the
> > following error:
> > "pg_restore: [custom archiver] dumping a specific TOC data
> block out
> > of order is not supported without ID on this input stream
> (fseek required)"
>
> We have gotten several reports of this, but none of the
> developers have been able to reproduce it. Can you provide
> an exact test case?
>
> regards, tom lane
>
>

Tom,

Backup files I'm trying to restore "in parallel" contain partitions of
several partitioned tables.
Tables partitioned "by month", each backup file contains 1 month worth
of data for all partitioned tables.

Before restoring backed up partitions, I'm restoring from another backup
file (not using "-j"), which contains "base" (empty) tables, from which
partitions inherited. And this restore runs fine.

Is that the information you asked for, or you want a sample of small
backup file attached?
I'm attaching pg_restore log file, if it's of any help.

Regards,
Igor Neyman

> From: Igor Neyman <ineyman(at)perceptron(dot)com>
> Subject: Re: [ADMIN] parallel option in pg_restore
> To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Cc: pgsql-admin(at)postgresql(dot)org
> Date: Tuesday, 22 June, 2010, 16:05
> > -----Original Message-----
> > From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
>
> > Sent: Tuesday, June 22, 2010 10:37 AM
> > To: Igor Neyman
> > Cc: pgsql-admin(at)postgresql(dot)org
> > Subject: Re: [ADMIN] parallel option in pg_restore
> >
> > "Igor Neyman" <ineyman(at)perceptron(dot)com>
> writes:
> > > I'm testing 8.4.4 (on Windows) before upgrading
> our app to this PG
> > > version.
> > > When running pg_restore with "-j 2" parallel
> option, I'm
> > getting the
> > > following error:
> > > "pg_restore: [custom archiver] dumping a specific
> TOC data
> > block out
> > > of order is not supported without ID on this
> input stream
> > (fseek required)"
> >
> > We have gotten several reports of this, but none of
> the
> > developers have been able to reproduce it.  Can
> you provide
> > an exact test case?
> >
> >        
>     regards, tom lane
> >
> >
>
> Tom,
>
> Backup files I'm trying to restore "in parallel" contain
> partitions of
> several partitioned tables.
> Tables partitioned "by month", each backup file contains 1
> month worth
> of data for all partitioned tables.
>
> Before restoring backed up partitions, I'm restoring from
> another backup
> file (not using "-j"), which contains "base" (empty)
> tables, from which
> partitions inherited. And this restore runs fine.
>
> Is that the information you asked for, or you want a sample
> of small
> backup file attached?
> I'm attaching pg_restore log file, if it's of any help.
>

In my experiments the error went away when I reduced the amount of data in the tables being restored/size of the dump.

This is as far as I got, but I let it rest for a while due to lack of response on the list.

http://archives.postgresql.org/pgsql-general/2010-05/msg00778.php

"Igor Neyman" <ineyman(at)perceptron(dot)com> writes:
> Is that the information you asked for, or you want a sample of small
> backup file attached?
> I'm attaching pg_restore log file, if it's of any help.

If you can make a small archive file that provokes the problem, yes
please send it. Also, please show the exact pg_restore command line
you're using.

regards, tom lane

On Tue, Jun 22, 2010 at 11:05:02AM -0400, Igor Neyman wrote:
> > "Igor Neyman" <ineyman(at)perceptron(dot)com> writes:
> > > I'm testing 8.4.4 (on Windows) before upgrading our app to this PG
> > > version.
> > > When running pg_restore with "-j 2" parallel option, I'm getting the
> > > following error:
> > > "pg_restore: [custom archiver] dumping a specific TOC data block out
> > > of order is not supported without ID on this input stream
> > > (fseek required)"
> >
> > We have gotten several reports of this, but none of the
> > developers have been able to reproduce it. Can you provide
> > an exact test case?
> > regards, tom lane

> Backup files I'm trying to restore "in parallel" contain partitions of
> several partitioned tables.
> Tables partitioned "by month", each backup file contains 1 month worth
> of data for all partitioned tables.
>
> Before restoring backed up partitions, I'm restoring from another backup
> file (not using "-j"), which contains "base" (empty) tables, from which
> partitions inherited. And this restore runs fine.

I realise this may be a silly question (especially for windows), but
the fseek complaint has me wondering.

Are you running a pipleine reatore? E.G:

type dumpfile | pg_restore -j 2

or are you running:

pg_restore -j 2 dumpfile

in the latter case it should be fseekable, but in the former case I
don't think you can fseek stdin on either windows or *nix..

--
-- rouilj

John Rouillard System Administrator
Renesys Corporation 603-244-9084 (cell) 603-643-9300 x 111

> -----Original Message-----
> From: John Rouillard [mailto:rouilj(at)renesys(dot)com]
> Sent: Tuesday, June 22, 2010 11:52 AM
> To: Igor Neyman
> Cc: pgsql-admin(at)postgresql(dot)org
> Subject: Re: [ADMIN] parallel option in pg_restore
>
> On Tue, Jun 22, 2010 at 11:05:02AM -0400, Igor Neyman wrote:
> > > "Igor Neyman" <ineyman(at)perceptron(dot)com> writes:
> > > > I'm testing 8.4.4 (on Windows) before upgrading our app
> to this PG
> > > > version.
> > > > When running pg_restore with "-j 2" parallel option,
> I'm getting
> > > > the following error:
> > > > "pg_restore: [custom archiver] dumping a specific TOC
> data block
> > > > out of order is not supported without ID on this input stream
> > > > (fseek required)"
> > >
> > > We have gotten several reports of this, but none of the
> developers
> > > have been able to reproduce it. Can you provide an exact
> test case?
> > > regards, tom lane
>
> > Backup files I'm trying to restore "in parallel" contain
> partitions of
> > several partitioned tables.
> > Tables partitioned "by month", each backup file contains 1
> month worth
> > of data for all partitioned tables.
> >
> > Before restoring backed up partitions, I'm restoring from another
> > backup file (not using "-j"), which contains "base" (empty) tables,
> > from which partitions inherited. And this restore runs fine.
>
> I realise this may be a silly question (especially for
> windows), but the fseek complaint has me wondering.
>
> Are you running a pipleine reatore? E.G:
>
> type dumpfile | pg_restore -j 2
>
> or are you running:
>
> pg_restore -j 2 dumpfile
>
> in the latter case it should be fseekable, but in the former
> case I don't think you can fseek stdin on either windows or *nix..
>
> --
> -- rouilj
>
> John Rouillard System Administrator
> Renesys Corporation 603-244-9084 (cell) 603-643-9300 x 111
>
>

No piping, just regular restore from the backup file.

Regards,
Igor Neyman

--- On Tue, 22/6/10, Igor Neyman <ineyman(at)perceptron(dot)com> wrote:

> From: Igor Neyman <ineyman(at)perceptron(dot)com>
> Subject: Re: [ADMIN] parallel option in pg_restore
> To: "John Rouillard" <rouilj(at)renesys(dot)com>
> Cc: pgsql-admin(at)postgresql(dot)org
> Date: Tuesday, 22 June, 2010, 17:34
>
> No piping, just regular restore from the backup file.
>

Same here. If only I could get a small sample which exhibited the issues - so far I can only get the same error with large dump files.

> -----Original Message-----
> From: Glyn Astill [mailto:glynastill(at)yahoo(dot)co(dot)uk]
> Sent: Tuesday, June 22, 2010 12:36 PM
> To: John Rouillard; Igor Neyman
> Cc: pgsql-admin(at)postgresql(dot)org
> Subject: Re: [ADMIN] parallel option in pg_restore
>
> --- On Tue, 22/6/10, Igor Neyman <ineyman(at)perceptron(dot)com> wrote:
>
> > From: Igor Neyman <ineyman(at)perceptron(dot)com>
> > Subject: Re: [ADMIN] parallel option in pg_restore
> > To: "John Rouillard" <rouilj(at)renesys(dot)com>
> > Cc: pgsql-admin(at)postgresql(dot)org
> > Date: Tuesday, 22 June, 2010, 17:34
> >
> > No piping, just regular restore from the backup file.
> >
>
> Same here. If only I could get a small sample which
> exhibited the issues - so far I can only get the same error
> with large dump files.
>

I just sent some samples in reply to Tom's request.

Regards,
Igor Neyman

Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk> wrote:

> so far I can only get the same error with large dump files.

"Large" being a relative term --
ever see it on a file smaller than 2GB?

-Kevin

> -----Original Message-----
> From: Kevin Grittner [mailto:Kevin(dot)Grittner(at)wicourts(dot)gov]
> Sent: Tuesday, June 22, 2010 12:40 PM
> To: Igor Neyman; John Rouillard; Glyn Astill
> Cc: pgsql-admin(at)postgresql(dot)org
> Subject: Re: [ADMIN] parallel option in pg_restore
>
> Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk> wrote:
>
> > so far I can only get the same error with large dump files.
>
> "Large" being a relative term --
> ever see it on a file smaller than 2GB?
>
> -Kevin
>
>

Yes, just sent couple to the list.

Igor

"Igor Neyman" <ineyman(at)perceptron(dot)com> writes:
> Attached are couple smallish files (I suspect, CM_200909.bac might have
> just empty tables, no data - but it still produces an errror).

Hmm. I get

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2741; 1259 30866 TABLE gp_cycle_200907 vec_dba
pg_restore: [archiver (db)] could not execute query: ERROR: relation "gp_cycle" does not exist
Command was:
CREATE TABLE gp_cycle_200907 (CONSTRAINT gp_cycle_200907_cycle_date_time_check CHECK (((cycle_date_time >= '2009-07-01 00:0...

The tables all seem to inherit from tables you omitted from the dump,
so of course it's not restorable for anyone else.

Now I do see

pg_restore: [custom archiver] dumping a specific TOC data block out of order is not supported without ID on this input stream (fseek required)

after that, but I'm wondering if this is just a problem in error
recovery rather than the bug we thought we were looking for.

regards, tom lane

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Tuesday, June 22, 2010 1:10 PM
> To: Igor Neyman
> Cc: pgsql-admin(at)postgresql(dot)org
> Subject: Re: [ADMIN] parallel option in pg_restore
>
> "Igor Neyman" <ineyman(at)perceptron(dot)com> writes:
> > Attached are couple smallish files (I suspect, CM_200909.bac might
> > have just empty tables, no data - but it still produces an errror).
>
> Hmm. I get
>
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 2741; 1259
> 30866 TABLE gp_cycle_200907 vec_dba
> pg_restore: [archiver (db)] could not execute query: ERROR:
> relation "gp_cycle" does not exist
> Command was:
> CREATE TABLE gp_cycle_200907 (CONSTRAINT
> gp_cycle_200907_cycle_date_time_check CHECK
> (((cycle_date_time >= '2009-07-01 00:0...
>
> The tables all seem to inherit from tables you omitted from
> the dump, so of course it's not restorable for anyone else.
>
> Now I do see
>
> pg_restore: [custom archiver] dumping a specific TOC data
> block out of order is not supported without ID on this input
> stream (fseek required)
>
> after that, but I'm wondering if this is just a problem in
> error recovery rather than the bug we thought we were looking for.
>
> regards, tom lane
>
>

Right, like I mentioned, these are partitioned tables.

Attached is script that could be used to pre-create "parent" tables
(from which partitions were inherited).
You run it before restoring backed up partition.

Thank you for taking time to look into this issue.
Regards,
Igor Neyman

"Igor Neyman" <ineyman(at)perceptron(dot)com> writes:
> Attached is script that could be used to pre-create "parent" tables
> (from which partitions were inherited).

Thanks. Now that I dig into it, it looks like the actual trigger for
the problem is that pg_dump, not pg_restore, couldn't seek while it
was creating the dump file --- so it didn't seek back and update the
file's table-of-contents with exact dump offsets. What command did
you use to create the dump file, exactly?

regards, tom lane

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Tuesday, June 22, 2010 2:41 PM
> To: Igor Neyman
> Cc: pgsql-admin(at)postgresql(dot)org
> Subject: Re: [ADMIN] parallel option in pg_restore
>
> "Igor Neyman" <ineyman(at)perceptron(dot)com> writes:
> > Attached is script that could be used to pre-create "parent" tables
> > (from which partitions were inherited).
>
> Thanks. Now that I dig into it, it looks like the actual
> trigger for the problem is that pg_dump, not pg_restore,
> couldn't seek while it was creating the dump file --- so it
> didn't seek back and update the file's table-of-contents with
> exact dump offsets. What command did you use to create the
> dump file, exactly?
>
> regards, tom lane
>
>

Here is the backup script to backup all partitions for specific month
(200907) in one backup file:

SETLOCAL
set PGPASSFILE=%PGINSTALL%\DB_scripts\postgres.pgpass
SET PGBACKUPDRIVE=%PGBACKUP%

pg_dump -U vec_dba -F c -f
%PGBACKUPDRIVE%\PartitionedBackup\CM_200907.bac -v -Z 9 -t *200907
vector 2>> %PGBACKUPDRIVE%\Backup\Log\DB_Backup.log

ENDLOCAL

This script is a part of "bigger" backup, which backs up other
non-partitioned tables as well.

Regards,
Igor Neyman

--- On Tue, 22/6/10, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk>
> wrote:
>
> > so far I can only get the same error with large dump
> files.
>
> "Large" being a relative term --
> ever see it on a file smaller than 2GB?
>

Good point. No I've not seen it on a file smaller than 2GB, but the test I did was pretty basic - I just trimmed down the size of all of my tables to create a dump that was only 50Mb or so. It looks like Igor has a reproduceable case now though, so hopefully Tom can figure out what's going off.

Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk> writes:
> Good point. No I've not seen it on a file smaller than 2GB, but the test I did was pretty basic - I just trimmed down the size of all of my tables to create a dump that was only 50Mb or so. It looks like Igor has a reproduceable case now though, so hopefully Tom can figure out what's going off.

I neglected to follow up to this -admin thread, but see
http://archives.postgresql.org/pgsql-hackers/2010-06/msg01227.php

regards, tom lane

--- On Wed, 23/6/10, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk>
> writes:
> > Good point.  No I've not seen it on a file
> smaller than 2GB, but the test I did was pretty basic - I
> just trimmed down the size of all of my tables to create a
> dump that was only 50Mb or so.  It looks like Igor has
> a reproduceable case now though, so hopefully Tom can figure
> out what's going off.
>
> I neglected to follow up to this -admin thread, but see
> http://archives.postgresql.org/pgsql-hackers/2010-06/msg01227.php

Thanks Tom, that's sufficient information to solve our problems here.