Re: BUG #16147: postgresql 12.1 (from homebrew) - pg_restore -h localhost --jobs=2 crashes

I did some of the digging here. The issue still exists in the latest
git master. A git bisect pointed me to commit
548e50976ce721b5e927d42a105c2f05b51b52a6 which did touch the
pg_restore work. The bug doesn't seem to be with the pg_dump -Fc
format as I was using an older dump version to do the bisect and it
could still trigger the issue in recent PostgreSQL. I have noticed
this issue on several Macs. Doing some Google searching you also see
people complaining about it on Mac:

https://dba.stackexchange.com/questions/257398/pg-restore-with-jobs-flag-results-in-pg-restore-error-a-worker-process-di

https://github.com/thoughtbot/parity/issues/175

mentioning things like Homebrew and Postgres.app that are only on Mac.
The recommendation seems to be to go back to pg_restore from
postgresql 11.

I tried a bit of sleuthing here. I've attached a file
toc_contents.txt which is the pg_restore -l output of my dump file
with some relevant bits grepped. I've attached a file
printf_output.txt that has some manual printfs shoved into pg_restore
to try and see what's going on. Note that I dropped all but the last
few restore_toc_entry calls. And finally I attached printf.patch so
you can see what I'm logging. The issue seems to be that pg_restore
is trying to find a TOC that was earlier in the file than the current
offset, never finds that TOC and fails with the "possibly due to
out-of-order restore request". I've run pg_restore with these patches
a few times now and it always fails in the same way (never seeks up in
the file) but in different places each time.

Tom, if you or anyone else with PostgreSQL would appreciate the
pg_dump file I can send it to you out of band, it's only a few
megabytes. I have pg_restore with debug symbols too if you want me to
try anything.

On Fri, May 15, 2020 at 9:43 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> > The following bug has been logged on the website:
> > Bug reference: 16147
> > Logged by: Bill Tihen
> > Email address: btihen(at)gmail(dot)com
> > PostgreSQL version: 12.1
> > Operating system: MacOS 10.15.1
> > Description:
>
> > The following command crashes with any database I've tried (both large and
> > small) DBs:
> > `pg_restore -U wti0405 -d stage3 -h localhost --jobs=8 -Fc
> > database_12_04-01-00.bak -x`
>
> I failed to reproduce this on my own 10.15.1 laptop, using manual
> builds of either HEAD or the v12 branch. Plausible reasons for
> the difference in results might include:
>
> * There's something different about the homebrew build (could we
> see the output of pg_config?)
>
> * There's something unusual about your configuration (one thought
> that comes to mind: do you have SSL turned on for localhost
> connections?)
>
> * There's something about the data in this specific database
> (your report that it happens for multiple databases puts a crimp
> in this idea, though maybe they all share a common feature)
>
> Anyway, we need more info to investigate. You might try looking
> into the server log to see what the failure looks like from that
> side --- is there a query error, or just the worker disconnecting
> unexpectedly?
>
> regards, tom lane
>
>
>
>

--
David Gilman
:DG<