pg_restore V12 fails consistently against piped pg_dumps

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).