Обсуждение: Restore fails if using multiple threads and database is dumpedthrough pipe

Hi,

After upgrading from Postgresql 11 to 12 I noticed pg_restore fails if -j / jobs parameter is specified and database is
dumpedthrough pipe.
 
This doesn't happen if dump is saved through redirect (for example: > file.dmp) or file parameter (-f).
Is this intended behaviour?


Some examples:

Dump to file: pg_dump -Fc -f dumptest-file.dmp dumptest
Restore: pg_restore -j4 -d dumptest -O -Fc --role=tomcat dumptest-file.dmp
No errors

Dump to file via stdout: pg_dump -Fc dumptest > dumptest-stdout.dmp 
Restore: pg_restore -j4 -d dumptest -O -Fc --role=tomcat dumptest-stdout.dmp
No errors

Dump to file via pipe: pg_dump -Fc dumptest | tee dumptest-pipe.dmp >/dev/null
Restore: pg_restore -j4 -d dumptest -O -Fc --role=tomcat dumptest-pipe.dmp
pg_restore: error: could not find block ID 5648 in archive -- possibly due to out-of-order restore request, which
cannotbe handled due to lack of data offsets in archive
 
pg_restore: error: a worker process died unexpectedly

Piped dump is successfully restored if using only 1 thread/job.



Regards,
Joni Ruuskanen

Joni Ruuskanen <joni.ruuskanen@likeit.fi> writes:
> After upgrading from Postgresql 11 to 12 I noticed pg_restore fails if -j / jobs parameter is specified and database
isdumped through pipe. 
> This doesn't happen if dump is saved through redirect (for example: > file.dmp) or file parameter (-f).
> Is this intended behaviour?

> Dump to file via stdout: pg_dump -Fc dumptest > dumptest-stdout.dmp
> Restore: pg_restore -j4 -d dumptest -O -Fc --role=tomcat dumptest-stdout.dmp
> No errors

> Dump to file via pipe: pg_dump -Fc dumptest | tee dumptest-pipe.dmp >/dev/null
> Restore: pg_restore -j4 -d dumptest -O -Fc --role=tomcat dumptest-pipe.dmp
> pg_restore: error: could not find block ID 5648 in archive -- possibly due to out-of-order restore request, which
cannotbe handled due to lack of data offsets in archive 

Yes, if you don't write directly to the output file then pg_dump has
no way to seek in the file, so it can't go back and fill in the data
offsets in the table of contents after it finishes dumping the tables.
pg_restore can cope with the lack of information as long as it's doing
a simple serial restore; but in parallel mode, not so much.

            regards, tom lane