Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1itZSP-0003E9-Vz for pgsql-admin@arkaria.postgresql.org; Mon, 20 Jan 2020 15:59:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1itZSN-0007oF-NC for pgsql-admin@arkaria.postgresql.org; Mon, 20 Jan 2020 15:59:15 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1itZSN-0007o8-4k for pgsql-admin@lists.postgresql.org; Mon, 20 Jan 2020 15:59:15 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1itZSF-0001nF-Go for pgsql-admin@lists.postgresql.org; Mon, 20 Jan 2020 15:59:13 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id 00KFx3SB012161; Mon, 20 Jan 2020 10:59:03 -0500 From: Tom Lane To: Joni Ruuskanen cc: pgsql-admin@lists.postgresql.org Subject: Re: Restore fails if using multiple threads and database is dumped through pipe In-reply-to: References: Comments: In-reply-to Joni Ruuskanen message dated "Mon, 20 Jan 2020 16:13:43 +0200" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <12159.1579535943.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Mon, 20 Jan 2020 10:59:03 -0500 Message-ID: <12160.1579535943@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Joni Ruuskanen writes: > After upgrading from Postgresql 11 to 12 I noticed pg_restore fails if -= j / jobs parameter is specified and database is dumped through pipe. > This doesn't happen if dump is saved through redirect (for example: > fi= le.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=3Dtomcat dumptest-stdo= ut.dmp > No errors > Dump to file via pipe: pg_dump -Fc dumptest | tee dumptest-pipe.dmp >/de= v/null > Restore: pg_restore -j4 -d dumptest -O -Fc --role=3Dtomcat dumptest-pipe= .dmp > pg_restore: error: could not find block ID 5648 in archive -- possibly d= ue to out-of-order restore request, which cannot be 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