Thread: pg_restore V12 fails consistently against piped pg_dumps
Hi all,
When doing a parallel pg_restore (v12) against a dump created through a pipe using an earlier version (11 all the way to 9.6), it fails with the known error of not finding data offsets. I understand the reasons for it (potential inability to seek), which is documented in pg_restore.
What I don't understand is why the same `pg_restore -j` worked in earlier versions (say running pg_restore_v11 against the same dumps). Has anything changed in terms of ordering? I am actually quite curious what led to this finally breaking consistently.
I also timed things, and found that pg_restore_12 against a pg_dump_12 takes about the same time as pg_restore_11 vs pg_dump_11, on the same dataset. I initially did this to see if for some reason pg_restore_v11 was compensating for the lack of the offsets, but this didn't seem to be the case (or perhaps the dump is too small to matter, around 1.5GB).
--
Wael
Mohamed Wael Khobalatte <mkhobalatte@grubhub.com> writes:
> When doing a parallel pg_restore (v12) against a dump created through a
> pipe using an earlier version (11 all the way to 9.6), it fails with the
> known error of not finding data offsets. I understand the reasons for it
> (potential inability to seek), which is documented in pg_restore.
> What I don't understand is why the same `pg_restore -j` worked in earlier
> versions (say running pg_restore_v11 against the same dumps). Has anything
> changed in terms of ordering? I am actually quite curious what led to this
> finally breaking consistently.
Without a concrete example it's hard to say, but maybe the issue is that
v12 is more aggressive about parallelizing restores --- see 548e50976.
regards, tom lane
On Thu, May 7, 2020 at 2:35 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Without a concrete example it's hard to say, but maybe the issue is that > v12 is more aggressive about parallelizing restores --- see 548e50976. Thanks Tom, I will review those changes tonight. In the meantime, to reproduce, I run the following: - createdb test - create some tables, I used a simple loop to create 10 empty ones. - pg_dump -v -Fc test | tee test.dump > /dev/null (so it's through a pipe, same as if the file is streamed through curl which was the original case) - pg_restore -j4 --verbose --clean --no-acl --no-owner -d test test.dump That should fail consistently. pg_restore_12 and pg_dump_12. Same passes in if I run in earlier versions.
On Thu, 2020-05-07 at 11:04 -0400, Mohamed Wael Khobalatte wrote: > Thanks Tom, I will review those changes tonight. In the meantime, to > reproduce, I run the following: > > - createdb test > - create some tables, I used a simple loop to create 10 empty ones. > - pg_dump -v -Fc test | tee test.dump > /dev/null (so it's through a > pipe, same as if the file is streamed through curl which was the > original case) > - pg_restore -j4 --verbose --clean --no-acl --no-owner -d test test.dump > > That should fail consistently. pg_restore_12 and pg_dump_12. Same > passes in if I run in earlier versions. I just tried that and didn't encounter any errors. Please come up with a more complete example. Are you OS user "postgres" when you run that? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
On 5/7/20 11:47 PM, Laurenz Albe wrote: > On Thu, 2020-05-07 at 11:04 -0400, Mohamed Wael Khobalatte wrote: >> Thanks Tom, I will review those changes tonight. In the meantime, to >> reproduce, I run the following: >> >> - createdb test >> - create some tables, I used a simple loop to create 10 empty ones. >> - pg_dump -v -Fc test | tee test.dump > /dev/null (so it's through a >> pipe, same as if the file is streamed through curl which was the >> original case) >> - pg_restore -j4 --verbose --clean --no-acl --no-owner -d test test.dump >> >> That should fail consistently. pg_restore_12 and pg_dump_12. Same >> passes in if I run in earlier versions. > > I just tried that and didn't encounter any errors. I did: pg_dump -d production -U postgres -Fc |tee test.dump > /dev/null pg_restore -j4 -d production_test -U postgres test.dump pg_restore: error: could not find block ID 4804 in archive -- possibly due to out-of-order restore request, which cannot be handled due to lack of data offsets in archive pg_restore: error: could not find block ID 4806 in archive -- possibly due to out-of-order restore request, which cannot be handled due to lack of data offsets in archive pg_restore: error: a worker process died unexpectedly Whereas a restore without -j4 succeeded. > > Please come up with a more complete example. > > Are you OS user "postgres" when you run that? > > Yours, > Laurenz Albe > -- Adrian Klaver adrian.klaver@aklaver.com
On Fri, May 8, 2020 at 10:59 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote: > > On 5/7/20 11:47 PM, Laurenz Albe wrote: > > On Thu, 2020-05-07 at 11:04 -0400, Mohamed Wael Khobalatte wrote: > >> Thanks Tom, I will review those changes tonight. In the meantime, to > >> reproduce, I run the following: > >> > >> - createdb test > >> - create some tables, I used a simple loop to create 10 empty ones. > >> - pg_dump -v -Fc test | tee test.dump > /dev/null (so it's through a > >> pipe, same as if the file is streamed through curl which was the > >> original case) > >> - pg_restore -j4 --verbose --clean --no-acl --no-owner -d test test.dump > >> > >> That should fail consistently. pg_restore_12 and pg_dump_12. Same > >> passes in if I run in earlier versions. > > > > I just tried that and didn't encounter any errors. > > I did: > > pg_dump -d production -U postgres -Fc |tee test.dump > /dev/null > > pg_restore -j4 -d production_test -U postgres test.dump > > pg_restore: error: could not find block ID 4804 in archive -- possibly > due to out-of-order restore request, which cannot be handled due to lack > of data offsets in archive > pg_restore: error: could not find block ID 4806 in archive -- possibly > due to out-of-order restore request, which cannot be handled due to lack > of data offsets in archive > pg_restore: error: a worker process died unexpectedly > > > Whereas a restore without -j4 succeeded. > > > > > Please come up with a more complete example. > > > > Are you OS user "postgres" when you run that? > > > > > Yours, > > Laurenz Albe > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com Laurenz, I did the same as Adrian above, but with my own non-"postgres" user. Between my check and Adrian's, it seems to be user-indepedent (although I don't know what that would mean precisely).