Re: [ADMIN] parallel option in pg_restore
--- On Tue, 22/6/10, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Glyn Astill glynast...@yahoo.co.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. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] parallel option in pg_restore
Glyn Astill glynast...@yahoo.co.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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] parallel option in pg_restore
--- On Wed, 23/6/10, Tom Lane t...@sss.pgh.pa.us wrote: Glyn Astill glynast...@yahoo.co.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. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] parallel option in pg_restore
Igor Neyman iney...@perceptron.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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] parallel option in pg_restore
Igor Neyman iney...@perceptron.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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] parallel option in pg_restore
-Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Tuesday, June 22, 2010 10:37 AM To: Igor Neyman Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] parallel option in pg_restore Igor Neyman iney...@perceptron.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 CM_200608_Restore.log Description: CM_200608_Restore.log -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] parallel option in pg_restore
From: Igor Neyman iney...@perceptron.com Subject: Re: [ADMIN] parallel option in pg_restore To: Tom Lane t...@sss.pgh.pa.us Cc: pgsql-admin@postgresql.org Date: Tuesday, 22 June, 2010, 16:05 -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Tuesday, June 22, 2010 10:37 AM To: Igor Neyman Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] parallel option in pg_restore Igor Neyman iney...@perceptron.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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] parallel option in pg_restore
Igor Neyman iney...@perceptron.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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] parallel option in pg_restore
On Tue, Jun 22, 2010 at 11:05:02AM -0400, Igor Neyman wrote: Igor Neyman iney...@perceptron.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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] parallel option in pg_restore
-Original Message- From: John Rouillard [mailto:rou...@renesys.com] Sent: Tuesday, June 22, 2010 11:52 AM To: Igor Neyman Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] parallel option in pg_restore On Tue, Jun 22, 2010 at 11:05:02AM -0400, Igor Neyman wrote: Igor Neyman iney...@perceptron.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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] parallel option in pg_restore
--- On Tue, 22/6/10, Igor Neyman iney...@perceptron.com wrote: From: Igor Neyman iney...@perceptron.com Subject: Re: [ADMIN] parallel option in pg_restore To: John Rouillard rou...@renesys.com Cc: pgsql-admin@postgresql.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. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] parallel option in pg_restore
-Original Message- From: Glyn Astill [mailto:glynast...@yahoo.co.uk] Sent: Tuesday, June 22, 2010 12:36 PM To: John Rouillard; Igor Neyman Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] parallel option in pg_restore --- On Tue, 22/6/10, Igor Neyman iney...@perceptron.com wrote: From: Igor Neyman iney...@perceptron.com Subject: Re: [ADMIN] parallel option in pg_restore To: John Rouillard rou...@renesys.com Cc: pgsql-admin@postgresql.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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] parallel option in pg_restore
Glyn Astill glynast...@yahoo.co.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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] parallel option in pg_restore
-Original Message- From: Kevin Grittner [mailto:kevin.gritt...@wicourts.gov] Sent: Tuesday, June 22, 2010 12:40 PM To: Igor Neyman; John Rouillard; Glyn Astill Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] parallel option in pg_restore Glyn Astill glynast...@yahoo.co.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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] parallel option in pg_restore
Igor Neyman iney...@perceptron.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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] parallel option in pg_restore
-Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Tuesday, June 22, 2010 1:10 PM To: Igor Neyman Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] parallel option in pg_restore Igor Neyman iney...@perceptron.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 parent_tables.sql Description: parent_tables.sql -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] parallel option in pg_restore
Igor Neyman iney...@perceptron.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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] parallel option in pg_restore
-Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Tuesday, June 22, 2010 2:41 PM To: Igor Neyman Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] parallel option in pg_restore Igor Neyman iney...@perceptron.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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
