Обсуждение: pg_restore with -j fails (works without -j option)

Поиск
Список
Период
Сортировка

pg_restore with -j fails (works without -j option)

От
Lonni J Friedman
Дата:
Greetings,
I've got a postgresql-9.0.4 cluster running on a Linux-x86_64 system.
I'm going to need to do some maintanence next week which will require
dumping & reloading the database on a different physical system.
Since I'm interested in minimizing downtime, I figured I'd use
pg_restore's "-j" option to improve performance.  Unfortunately, when
I attempt to do this, the restore fails after a while with what looks
like a race condition:
#############
$ cat 2011-08-25-1314280801-nightly.out | pg_restore -j2 -U lfriedman
-v -d nightly
...
pg_restore: executing SEQUENCE SET wsmtests_id_seq
pg_restore: processing item 2512 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2513 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2516 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2517 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2518 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2740 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2787 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2796 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2519 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2520 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2622 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2765 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2769 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2781 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2793 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2642 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2671 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2522 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2665 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2525 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2668 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2527 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2674 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2529 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2677 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2531 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2680 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2533 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2683 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2535 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2686 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2537 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2538 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2541 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2543 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2656 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2799 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2804 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2807 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2812 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2545 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2698 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2547 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2701 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2549 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2704 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2551 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2626 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2553 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2559 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2561 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2707 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2563 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2647 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2566 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2815 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2820 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2659 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2638 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2692 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2636 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2689 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2640 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2695 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2631 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2823 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2828 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2664 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2772 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2778 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2568 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2737 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2570 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2572 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2710 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2574 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2713 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2576 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2716 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2578 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2719 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2580 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2722 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2583 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2653 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2585 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2755 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2760 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2741 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2587 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2744 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2589 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2725 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2845 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2847 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2591 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2594 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2728 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2619 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2849 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2597 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2599 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2837 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2842 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2601 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2650 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2603 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2731 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2605 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2606 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2608 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2609 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2734 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2611 DEFAULT id_user
pg_restore: creating DEFAULT id_user
pg_restore: processing item 2612 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2614 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2747 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2752 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2615 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2834 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2617 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: processing item 2831 DEFAULT id
pg_restore: creating DEFAULT id
pg_restore: entering main parallel loop
pg_restore: launching item 5127 TABLE DATA amodelbuild
pg_restore: launching item 5128 TABLE DATA appleoclapps
pg_restore: [custom archiver] cannot reopen stdin
pg_restore: *** aborted because of error
pg_restore: launching item 5129 TABLE DATA appstracker
pg_restore: [custom archiver] cannot reopen stdin
pg_restore: *** aborted because of error
pg_restore: launching item 5130 TABLE DATA build
pg_restore: [custom archiver] cannot reopen stdin
pg_restore: *** aborted because of error
pg_restore: launching item 5131 TABLE DATA buildvalidate
pg_restore: [custom archiver] cannot reopen stdin
pg_restore: *** aborted because of error
pg_restore: launching item 5132 TABLE DATA buildvalidatelist
pg_restore: [custom archiver] cannot reopen stdin
pg_restore: *** aborted because of error
pg_restore: launching item 5222 TABLE DATA cbspkg
pg_restore: [custom archiver] cannot reopen stdin
pg_restore: *** aborted because of error
pg_restore: launching item 5234 TABLE DATA cublassmoke
pg_restore: [custom archiver] cannot reopen stdin
pg_restore: *** aborted because of error
pg_restore: finished item 5127 TABLE DATA amodelbuild
pg_restore: [archiver] worker process failed: exit code 1
pg_restore: *** aborted because of error
pg_restore: [custom archiver] cannot reopen stdin
pg_restore: *** aborted because of error
#############

This dump was created by running "pg_dump -o -Fc nightly >
2011-08-25-1314280801-nightly.out".

Repeating the restore using the same dump file, without the -j option
succeeds, so this definitely looks like a -j bug?  Anyone know if this
is a known issue, and better if there are any workarounds?

thanks!

Re: pg_restore with -j fails (works without -j option)

От
Tom Lane
Дата:
Lonni J Friedman <netllama@gmail.com> writes:
> [ this doesn't work: ]
> $ cat 2011-08-25-1314280801-nightly.out | pg_restore -j2 -U lfriedman -v -d nightly

It's basically impossible for that to work.  -j implies spawning
multiple processes that will be wanting to read concurrently from
different places in the input file.  That cannot happen when the source
is a pipe.  In this particular example, the pipe construct isn't even
doing anything useful; you could perfectly well write this instead:

$ pg_restore -j2 -U lfriedman -v -d nightly 2011-08-25-1314280801-nightly.out


> pg_restore: [custom archiver] cannot reopen stdin
> pg_restore: *** aborted because of error

I agree though that that's not a terribly friendly error message.
I thought we had some code in there to complain about non-seekable
input files, but it looks like we missed a case.

            regards, tom lane

Re: pg_restore with -j fails (works without -j option)

От
Lonni J Friedman
Дата:
On Thu, Aug 25, 2011 at 7:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Lonni J Friedman <netllama@gmail.com> writes:
>> [ this doesn't work: ]
>> $ cat 2011-08-25-1314280801-nightly.out | pg_restore -j2 -U lfriedman -v -d nightly
>
> It's basically impossible for that to work.  -j implies spawning
> multiple processes that will be wanting to read concurrently from
> different places in the input file.  That cannot happen when the source
> is a pipe.  In this particular example, the pipe construct isn't even
> doing anything useful; you could perfectly well write this instead:
>
> $ pg_restore -j2 -U lfriedman -v -d nightly 2011-08-25-1314280801-nightly.out

Indeed, that works perfectly.  thanks!

>
>
>> pg_restore: [custom archiver] cannot reopen stdin
>> pg_restore: *** aborted because of error
>
> I agree though that that's not a terribly friendly error message.
> I thought we had some code in there to complain about non-seekable
> input files, but it looks like we missed a case.

Let me know if you need more details from me to fix this.

Re: pg_restore with -j fails (works without -j option)

От
Tom Lane
Дата:
I wrote:
> Lonni J Friedman <netllama@gmail.com> writes:
>> [ this doesn't work: ]
>> $ cat 2011-08-25-1314280801-nightly.out | pg_restore -j2 -U lfriedman -v -d nightly
>> pg_restore: [custom archiver] cannot reopen stdin
>> pg_restore: *** aborted because of error

> I agree though that that's not a terribly friendly error message.
> I thought we had some code in there to complain about non-seekable
> input files, but it looks like we missed a case.

On closer inspection, I think it's just that nobody thought very hard
about presenting this limitation in a user-friendly fashion.  I've
committed a patch that will make it fail like this:

pg_restore: [custom archiver] parallel restore from stdin is not supported

immediately upon startup.

            regards, tom lane

Re: pg_restore with -j fails (works without -j option)

От
Lonni J Friedman
Дата:
On Sun, Aug 28, 2011 at 6:56 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I wrote:
>> Lonni J Friedman <netllama@gmail.com> writes:
>>> [ this doesn't work: ]
>>> $ cat 2011-08-25-1314280801-nightly.out | pg_restore -j2 -U lfriedman -v -d nightly
>>> pg_restore: [custom archiver] cannot reopen stdin
>>> pg_restore: *** aborted because of error
>
>> I agree though that that's not a terribly friendly error message.
>> I thought we had some code in there to complain about non-seekable
>> input files, but it looks like we missed a case.
>
> On closer inspection, I think it's just that nobody thought very hard
> about presenting this limitation in a user-friendly fashion.  I've
> committed a patch that will make it fail like this:
>
> pg_restore: [custom archiver] parallel restore from stdin is not supported
>
> immediately upon startup.
>

Awesome, thanks!