Warn when parallel restoring a custom dump without data offsets

From 63ae0cabc3691dffc8227ac6f9d23aca96ea0095 Mon Sep 17 00:00:00 2001
From: David Gilman <davidgilman1@gmail.com>
Date: Wed, 20 May 2020 22:49:28 -0400
Subject: [PATCH] pg_restore fix v2

commit message to come
---
 doc/src/sgml/ref/pg_restore.sgml   |  9 +++++++++
 src/bin/pg_dump/pg_backup_custom.c | 20 ++++++++++++++++++--
 2 files changed, 27 insertions(+), 2 deletions(-)

diff --git doc/src/sgml/ref/pg_restore.sgml doc/src/sgml/ref/pg_restore.sgml
index 232f88024f..fd23d6720c 100644
--- doc/src/sgml/ref/pg_restore.sgml
+++ doc/src/sgml/ref/pg_restore.sgml
@@ -279,6 +279,15 @@ PostgreSQL documentation
         jobs cannot be used together with the
         option <option>--single-transaction</option>.
        </para>
+
+       <para>
+        The custom archive format may not work with the <option>-j</option>
+        option if the archive was originally created by writing the archive
+        to an unseekable output file. For the best concurrent restoration
+        performance with the custom archive format use
+        <application>pg_dump</application>'s <option>--file</option> option
+        to specify an output file.
+       </para>
       </listitem>
      </varlistentry>
 
diff --git src/bin/pg_dump/pg_backup_custom.c src/bin/pg_dump/pg_backup_custom.c
index 369dcea429..8dfb6581d1 100644
--- src/bin/pg_dump/pg_backup_custom.c
+++ src/bin/pg_dump/pg_backup_custom.c
@@ -423,9 +423,25 @@ _PrintTocData(ArchiveHandle *AH, TocEntry *te)
 	{
 		/*
 		 * We cannot seek directly to the desired block.  Instead, skip over
-		 * block headers until we find the one we want.  This could fail if we
-		 * are asked to restore items out-of-order.
+		 * block headers until we find the one we want.
 		 */
+
+		if (ctx->hasSeek)
+		{
+			/*
+			 * Start searching from the first block. If this is possible, we're
+			 * all but guaranteed to find the block, although at the cost of a bunch
+			 * of redundant, tiny reads. TOC requests aren't guaranteed to come in
+			 * disk order so this is a necessary evil.
+			 *
+			 * If the input file can't be seeked we're at the mercy of the
+			 * file's TOC layout on disk. An out-of-order restore request will
+			 * halt the restore.
+			 */
+			if (fseeko(AH->FH, ctx->dataStart, SEEK_SET) != 0)
+				fatal("error during file seek: %m");
+		}
+
 		_readBlockHeader(AH, &blkType, &id);
 
 		while (blkType != EOF && id != te->dumpId)
-- 
2.26.2

From 58c4c37f78194e9fdc1b167cf224980ffd8e7660 Mon Sep 17 00:00:00 2001
From: David Gilman <davidgilman1@gmail.com>
Date: Sat, 23 May 2020 10:49:33 -0400
Subject: [PATCH 3/3] Add integration test for out-of-order TOC requests in
 pg_restore

Also add undocumented --disable-seeking argument to pg_dump to emulate
writing to an unseekable output file.
---
 src/bin/pg_dump/pg_backup.h          |  4 +-
 src/bin/pg_dump/pg_backup_archiver.c | 14 +++--
 src/bin/pg_dump/pg_backup_archiver.h |  1 +
 src/bin/pg_dump/pg_backup_custom.c   | 10 +++-
 src/bin/pg_dump/pg_dump.c            |  6 +-
 src/bin/pg_dump/t/002_pg_dump.pl     | 87 ++++++++++++++++++++++++++--
 6 files changed, 109 insertions(+), 13 deletions(-)

diff --git src/bin/pg_dump/pg_backup.h src/bin/pg_dump/pg_backup.h
index 8c0cedcd98..160d705eb5 100644
--- src/bin/pg_dump/pg_backup.h
+++ src/bin/pg_dump/pg_backup.h
@@ -158,6 +158,7 @@ typedef struct _dumpOptions
 	int			use_setsessauth;
 	int			enable_row_security;
 	int			load_via_partition_root;
+	int			disable_seeking;
 
 	/* default, if no "inclusion" switches appear, is to dump everything */
 	bool		include_everything;
@@ -270,7 +271,8 @@ extern Archive *OpenArchive(const char *FileSpec, const ArchiveFormat fmt);
 /* Create a new archive */
 extern Archive *CreateArchive(const char *FileSpec, const ArchiveFormat fmt,
 							  const int compression, bool dosync, ArchiveMode mode,
-							  SetupWorkerPtrType setupDumpWorker);
+							  SetupWorkerPtrType setupDumpWorker,
+							  bool disableSeeking);
 
 /* The --list option */
 extern void PrintTOCSummary(Archive *AH);
diff --git src/bin/pg_dump/pg_backup_archiver.c src/bin/pg_dump/pg_backup_archiver.c
index 8f0b32ca17..0e5d4e0a60 100644
--- src/bin/pg_dump/pg_backup_archiver.c
+++ src/bin/pg_dump/pg_backup_archiver.c
@@ -69,7 +69,8 @@ typedef struct _parallelReadyList
 
 static ArchiveHandle *_allocAH(const char *FileSpec, const ArchiveFormat fmt,
 							   const int compression, bool dosync, ArchiveMode mode,
-							   SetupWorkerPtrType setupWorkerPtr);
+							   SetupWorkerPtrType setupWorkerPtr,
+							   bool disableSeeking);
 static void _getObjectDescription(PQExpBuffer buf, TocEntry *te,
 								  ArchiveHandle *AH);
 static void _printTocEntry(ArchiveHandle *AH, TocEntry *te, bool isData);
@@ -233,11 +234,12 @@ setupRestoreWorker(Archive *AHX)
 Archive *
 CreateArchive(const char *FileSpec, const ArchiveFormat fmt,
 			  const int compression, bool dosync, ArchiveMode mode,
-			  SetupWorkerPtrType setupDumpWorker)
+			  SetupWorkerPtrType setupDumpWorker,
+			  bool disableSeeking)
 
 {
 	ArchiveHandle *AH = _allocAH(FileSpec, fmt, compression, dosync,
-								 mode, setupDumpWorker);
+								 mode, setupDumpWorker, disableSeeking);
 
 	return (Archive *) AH;
 }
@@ -247,7 +249,7 @@ CreateArchive(const char *FileSpec, const ArchiveFormat fmt,
 Archive *
 OpenArchive(const char *FileSpec, const ArchiveFormat fmt)
 {
-	ArchiveHandle *AH = _allocAH(FileSpec, fmt, 0, true, archModeRead, setupRestoreWorker);
+	ArchiveHandle *AH = _allocAH(FileSpec, fmt, 0, true, archModeRead, setupRestoreWorker, false);
 
 	return (Archive *) AH;
 }
@@ -2274,7 +2276,8 @@ _discoverArchiveFormat(ArchiveHandle *AH)
 static ArchiveHandle *
 _allocAH(const char *FileSpec, const ArchiveFormat fmt,
 		 const int compression, bool dosync, ArchiveMode mode,
-		 SetupWorkerPtrType setupWorkerPtr)
+		 SetupWorkerPtrType setupWorkerPtr,
+		 bool disableSeeking)
 {
 	ArchiveHandle *AH;
 
@@ -2325,6 +2328,7 @@ _allocAH(const char *FileSpec, const ArchiveFormat fmt,
 	AH->mode = mode;
 	AH->compression = compression;
 	AH->dosync = dosync;
+	AH->disableSeeking = disableSeeking;
 
 	memset(&(AH->sqlparse), 0, sizeof(AH->sqlparse));
 
diff --git src/bin/pg_dump/pg_backup_archiver.h src/bin/pg_dump/pg_backup_archiver.h
index f9e6b42752..acf38c20da 100644
--- src/bin/pg_dump/pg_backup_archiver.h
+++ src/bin/pg_dump/pg_backup_archiver.h
@@ -340,6 +340,7 @@ struct _archiveHandle
 	bool		dosync;			/* data requested to be synced on sight */
 	ArchiveMode mode;			/* File mode - r or w */
 	void	   *formatData;		/* Header data specific to file format */
+	bool		disableSeeking;	/* Don't use fseeko() */
 
 	/* these vars track state to avoid sending redundant SET commands */
 	char	   *currUser;		/* current username, or NULL if unknown */
diff --git src/bin/pg_dump/pg_backup_custom.c src/bin/pg_dump/pg_backup_custom.c
index 8dfb6581d1..332c557616 100644
--- src/bin/pg_dump/pg_backup_custom.c
+++ src/bin/pg_dump/pg_backup_custom.c
@@ -145,6 +145,10 @@ InitArchiveFmt_Custom(ArchiveHandle *AH)
 	AH->lo_buf = (void *) pg_malloc(LOBBUFSIZE);
 
 	ctx->filePos = 0;
+	/* Question for reviewer: why is this int and not bool? Should I fix it? */
+	/* does int vs bool actually matter in the ctx struct? */
+	/* checkSeek() returns bool */
+	ctx->hasSeek = 0;
 
 	/*
 	 * Now open the file
@@ -164,7 +168,8 @@ InitArchiveFmt_Custom(ArchiveHandle *AH)
 				fatal("could not open output file: %m");
 		}
 
-		ctx->hasSeek = checkSeek(AH->FH);
+		if (!AH->disableSeeking)
+			ctx->hasSeek = checkSeek(AH->FH);
 	}
 	else
 	{
@@ -181,7 +186,8 @@ InitArchiveFmt_Custom(ArchiveHandle *AH)
 				fatal("could not open input file: %m");
 		}
 
-		ctx->hasSeek = checkSeek(AH->FH);
+		if (!AH->disableSeeking)
+			ctx->hasSeek = checkSeek(AH->FH);
 
 		ReadHead(AH);
 		ReadToc(AH);
diff --git src/bin/pg_dump/pg_dump.c src/bin/pg_dump/pg_dump.c
index a4e949c636..53357557c3 100644
--- src/bin/pg_dump/pg_dump.c
+++ src/bin/pg_dump/pg_dump.c
@@ -319,6 +319,7 @@ main(int argc, char **argv)
 	int			plainText = 0;
 	ArchiveFormat archiveFormat = archUnknown;
 	ArchiveMode archiveMode;
+	bool		disableSeeking = false;
 
 	static DumpOptions dopt;
 
@@ -360,6 +361,7 @@ main(int argc, char **argv)
 		{"binary-upgrade", no_argument, &dopt.binary_upgrade, 1},
 		{"column-inserts", no_argument, &dopt.column_inserts, 1},
 		{"disable-dollar-quoting", no_argument, &dopt.disable_dollar_quoting, 1},
+		{"disable-seeking", no_argument, &dopt.disable_seeking, 1},
 		{"disable-triggers", no_argument, &dopt.disable_triggers, 1},
 		{"enable-row-security", no_argument, &dopt.enable_row_security, 1},
 		{"exclude-table-data", required_argument, NULL, 4},
@@ -673,6 +675,8 @@ main(int argc, char **argv)
 	if (archiveFormat == archNull)
 		plainText = 1;
 
+	disableSeeking = !!dopt.disable_seeking;
+
 	/* Custom and directory formats are compressed by default, others not */
 	if (compressLevel == -1)
 	{
@@ -715,7 +719,7 @@ main(int argc, char **argv)
 
 	/* Open the output file */
 	fout = CreateArchive(filename, archiveFormat, compressLevel, dosync,
-						 archiveMode, setupDumpWorker);
+						 archiveMode, setupDumpWorker, disableSeeking);
 
 	/* Make dump options accessible right away */
 	SetArchiveOptions(fout, &dopt, NULL);
diff --git src/bin/pg_dump/t/002_pg_dump.pl src/bin/pg_dump/t/002_pg_dump.pl
index e116235769..e7b1355f1c 100644
--- src/bin/pg_dump/t/002_pg_dump.pl
+++ src/bin/pg_dump/t/002_pg_dump.pl
@@ -31,9 +31,20 @@ my $tempdir_short = TestLib::tempdir_short;
 # generate a text file to run through the tests from the
 # non-text file generated by pg_dump.
 #
-# TODO: Have pg_restore actually restore to an independent
-# database and then pg_dump *that* database (or something along
-# those lines) to validate that part of the process.
+# secondary_dump_cmd is an optional key that enables a
+#
+# 	pg_dump (dump_cmd) ->
+# 	pg_restore (restore_cmd, real database) ->
+#	pg_dump (secondary_dmp_cmd, SQL output (for matching))
+#
+# test process instead of the default
+#
+# 	pg_dump (dump_cmd) ->
+# 	pg_restore (restore_cmd, SQL output (for matching))
+#
+# test process. The secondary_dump database is created for
+# you and your restore_cmd and secondary_dmp_cmd must read
+# and write from it.
 
 my %pgdump_runs = (
 	binary_upgrade => {
@@ -140,6 +151,24 @@ my %pgdump_runs = (
 		],
 	},
 
+	defaults_custom_format_no_seek_parallel_restore => {
+		test_key => 'defaults',
+		dump_cmd => [
+			'pg_dump', '-Fc', '-Z6', '--no-sync', '--disable-seeking',
+			"--file=$tempdir/defaults_custom_format_no_seek_parallel_restore.dump", 'postgres',
+		],
+		restore_cmd => [
+			'pg_restore', '-Fc', '--jobs=2',
+			'--dbname=secondary_dump',
+			"$tempdir/defaults_custom_format_no_seek_parallel_restore.dump",
+		],
+		secondary_dump_cmd => [
+			'pg_dump', '-Fp', '--no-sync',
+			"--file=$tempdir/defaults_custom_format_no_seek_parallel_restore.sql",
+			'secondary_dump',
+		],
+	},
+
 	# Do not use --no-sync to give test coverage for data sync.
 	defaults_dir_format => {
 		test_key => 'defaults',
@@ -3298,6 +3327,27 @@ my %tests = (
 			%full_runs, %dump_test_schema_runs, section_pre_data => 1,
 		},
 		unlike => { exclude_dump_test_schema => 1 },
+	},
+
+	# This reliably produces the "possibly due to out-of-order restore request"
+	# when restoring with -j 2 when this was written but future changes to how
+	# pg_dump works could accidentally correctly order things as we're not really
+	# telling pg_dump how to do its on-disk layout.
+	'custom dump out-of-order' => {
+		create_sql   => '
+			CREATE TABLE ooo_parent0 (id integer primary key);
+			CREATE TABLE ooo_child0 (parent0 int references ooo_parent0 (id));
+			CREATE TABLE ooo_parent1 (id integer primary key);
+			CREATE TABLE ooo_child1 (parent0 int references ooo_parent1 (id));',
+		regexp => qr/^
+			 \QCREATE TABLE public.ooo_child0 (\E\n
+			 \s+\Qparent0 integer\E\n
+			 \Q);\E\n/xm,
+		like => {
+			%full_runs, section_pre_data => 1,
+			defaults_custom_format_no_seek => 1,
+			defaults_custom_format_no_seek_parallel_restore => 1,
+		},
 	});
 
 #########################################
@@ -3350,6 +3400,11 @@ foreach my $run (sort keys %pgdump_runs)
 		$num_tests++;
 	}
 
+	if ($pgdump_runs{$run}->{secondary_dump_cmd})
+	{
+		$num_tests++;
+	}
+
 	if ($pgdump_runs{$run}->{test_key})
 	{
 		$test_key = $pgdump_runs{$run}->{test_key};
@@ -3491,7 +3546,12 @@ command_fails_like(
 #########################################
 # Run all runs
 
-foreach my $run (sort keys %pgdump_runs)
+foreach my $run (
+	sort {
+		defined($pgdump_runs{$a}->{secondary_dump_cmd}) <=>
+		  defined($pgdump_runs{$b}->{secondary_dump_cmd})
+		  || $a cmp $b
+	} keys %pgdump_runs)
 {
 	my $test_key = $run;
 	my $run_db   = 'postgres';
@@ -3499,12 +3559,23 @@ foreach my $run (sort keys %pgdump_runs)
 	$node->command_ok(\@{ $pgdump_runs{$run}->{dump_cmd} },
 		"$run: pg_dump runs");
 
+	if ($pgdump_runs{$run}->{secondary_dump_cmd})
+	{
+		$node->safe_psql('postgres', 'create database secondary_dump;');
+	}
+
 	if ($pgdump_runs{$run}->{restore_cmd})
 	{
 		$node->command_ok(\@{ $pgdump_runs{$run}->{restore_cmd} },
 			"$run: pg_restore runs");
 	}
 
+	if ($pgdump_runs{$run}->{secondary_dump_cmd})
+	{
+		$node->command_ok(\@{ $pgdump_runs{$run}->{secondary_dump_cmd} },
+			"$run: secondary pg_dump runs");
+	}
+
 	if ($pgdump_runs{$run}->{test_key})
 	{
 		$test_key = $pgdump_runs{$run}->{test_key};
@@ -3561,6 +3632,14 @@ foreach my $run (sort keys %pgdump_runs)
 			}
 		}
 	}
+
+	if ($pgdump_runs{$run}->{secondary_dump_cmd})
+	{
+		$node->safe_psql('secondary_dump',
+			'alter subscription sub1 set (slot_name = NONE);');
+		$node->safe_psql('secondary_dump', 'drop subscription sub1;');
+		$node->safe_psql('postgres',       'drop database secondary_dump;');
+	}
 }
 
 #########################################
-- 
2.26.2

From 5ffc0420bc6040cc26b1a36ac522b696ef428464 Mon Sep 17 00:00:00 2001
From: David Gilman <davidgilman1@gmail.com>
Date: Fri, 22 May 2020 17:27:51 -0400
Subject: [PATCH 1/3] Remove unused seek check in tar dump format

---
 src/bin/pg_dump/pg_backup_tar.c | 5 -----
 1 file changed, 5 deletions(-)

diff --git src/bin/pg_dump/pg_backup_tar.c src/bin/pg_dump/pg_backup_tar.c
index d5bfa55646..bb5d3b1f45 100644
--- src/bin/pg_dump/pg_backup_tar.c
+++ src/bin/pg_dump/pg_backup_tar.c
@@ -82,7 +82,6 @@ typedef struct
 
 typedef struct
 {
-	int			hasSeek;
 	pgoff_t		filePos;
 	TAR_MEMBER *blobToc;
 	FILE	   *tarFH;
@@ -192,8 +191,6 @@ InitArchiveFmt_Tar(ArchiveHandle *AH)
 		 */
 		/* setvbuf(ctx->tarFH, NULL, _IONBF, 0); */
 
-		ctx->hasSeek = checkSeek(ctx->tarFH);
-
 		/*
 		 * We don't support compression because reading the files back is not
 		 * possible since gzdopen uses buffered IO which totally screws file
@@ -226,8 +223,6 @@ InitArchiveFmt_Tar(ArchiveHandle *AH)
 
 		ctx->tarFHpos = 0;
 
-		ctx->hasSeek = checkSeek(ctx->tarFH);
-
 		/*
 		 * Forcibly unmark the header as read since we use the lookahead
 		 * buffer
-- 
2.26.2

From 130b7614b4592ea2f0fee44b7a7b59703e80806e Mon Sep 17 00:00:00 2001
From: David Gilman <davidgilman1@gmail.com>
Date: Wed, 20 May 2020 22:49:28 -0400
Subject: [PATCH 2/3] Scan all TOCs when restoring a custom dump file without
 offsets

TOC requests are not guaranteed to come in disk order. If the custom
dump file was written with data offsets pg_restore can seek directly to
the data, making request order irrelevant. If there are no data offsets
pg_restore would only find the TOC if it happened to be immediately
after the current one on disk. 548e50976 changed how pg_restore's
parallel algorithm worked at the cost of greatly increasing out-of-order
TOC requests.

This patch changes pg_restore to scan through all TOCs to service a TOC
read request only when restoring a custom dump file without data offsets.
The odds of getting a successful parallel restore go way up at the cost
of a bunch of extra tiny reads when pg_restore starts up.

The pg_restore manpage now warns against running pg_dump with an
unseekable output file and suggests that if you plan on doing a parallel
restore of a custom dump you should run pg_dump with --file.
---
 doc/src/sgml/ref/pg_restore.sgml   |  9 +++++++++
 src/bin/pg_dump/pg_backup_custom.c | 20 ++++++++++++++++++--
 2 files changed, 27 insertions(+), 2 deletions(-)

diff --git doc/src/sgml/ref/pg_restore.sgml doc/src/sgml/ref/pg_restore.sgml
index 232f88024f..fd23d6720c 100644
--- doc/src/sgml/ref/pg_restore.sgml
+++ doc/src/sgml/ref/pg_restore.sgml
@@ -279,6 +279,15 @@ PostgreSQL documentation
         jobs cannot be used together with the
         option <option>--single-transaction</option>.
        </para>
+
+       <para>
+        The custom archive format may not work with the <option>-j</option>
+        option if the archive was originally created by writing the archive
+        to an unseekable output file. For the best concurrent restoration
+        performance with the custom archive format use
+        <application>pg_dump</application>'s <option>--file</option> option
+        to specify an output file.
+       </para>
       </listitem>
      </varlistentry>
 
diff --git src/bin/pg_dump/pg_backup_custom.c src/bin/pg_dump/pg_backup_custom.c
index 369dcea429..8dfb6581d1 100644
--- src/bin/pg_dump/pg_backup_custom.c
+++ src/bin/pg_dump/pg_backup_custom.c
@@ -423,9 +423,25 @@ _PrintTocData(ArchiveHandle *AH, TocEntry *te)
 	{
 		/*
 		 * We cannot seek directly to the desired block.  Instead, skip over
-		 * block headers until we find the one we want.  This could fail if we
-		 * are asked to restore items out-of-order.
+		 * block headers until we find the one we want.
 		 */
+
+		if (ctx->hasSeek)
+		{
+			/*
+			 * Start searching from the first block. If this is possible, we're
+			 * all but guaranteed to find the block, although at the cost of a bunch
+			 * of redundant, tiny reads. TOC requests aren't guaranteed to come in
+			 * disk order so this is a necessary evil.
+			 *
+			 * If the input file can't be seeked we're at the mercy of the
+			 * file's TOC layout on disk. An out-of-order restore request will
+			 * halt the restore.
+			 */
+			if (fseeko(AH->FH, ctx->dataStart, SEEK_SET) != 0)
+				fatal("error during file seek: %m");
+		}
+
 		_readBlockHeader(AH, &blkType, &id);
 
 		while (blkType != EOF && id != te->dumpId)
-- 
2.26.2

From 5ffc0420bc6040cc26b1a36ac522b696ef428464 Mon Sep 17 00:00:00 2001
From: David Gilman <davidgilman1@gmail.com>
Date: Fri, 22 May 2020 17:27:51 -0400
Subject: [PATCH 1/3] Remove unused seek check in tar dump format

---
 src/bin/pg_dump/pg_backup_tar.c | 5 -----
 1 file changed, 5 deletions(-)

diff --git src/bin/pg_dump/pg_backup_tar.c src/bin/pg_dump/pg_backup_tar.c
index d5bfa55646..bb5d3b1f45 100644
--- src/bin/pg_dump/pg_backup_tar.c
+++ src/bin/pg_dump/pg_backup_tar.c
@@ -82,7 +82,6 @@ typedef struct
 
 typedef struct
 {
-	int			hasSeek;
 	pgoff_t		filePos;
 	TAR_MEMBER *blobToc;
 	FILE	   *tarFH;
@@ -192,8 +191,6 @@ InitArchiveFmt_Tar(ArchiveHandle *AH)
 		 */
 		/* setvbuf(ctx->tarFH, NULL, _IONBF, 0); */
 
-		ctx->hasSeek = checkSeek(ctx->tarFH);
-
 		/*
 		 * We don't support compression because reading the files back is not
 		 * possible since gzdopen uses buffered IO which totally screws file
@@ -226,8 +223,6 @@ InitArchiveFmt_Tar(ArchiveHandle *AH)
 
 		ctx->tarFHpos = 0;
 
-		ctx->hasSeek = checkSeek(ctx->tarFH);
-
 		/*
 		 * Forcibly unmark the header as read since we use the lookahead
 		 * buffer
-- 
2.26.2

From 3e2a291103ea14f3abeab861197f76d59e7d30e0 Mon Sep 17 00:00:00 2001
From: David Gilman <davidgilman1@gmail.com>
Date: Wed, 20 May 2020 22:49:28 -0400
Subject: [PATCH 2/3] Scan all TOCs when restoring a custom dump file without
 offsets

TOC requests are not guaranteed to come in disk order. If the custom
dump file was written with data offsets, pg_restore can seek directly to
the data, making request order irrelevant. If there are no data offsets
pg_restore would never attempt to seek backwards, even when it was
possible, and would not find TOCs before the current read position in
the file. 548e50976 changed how pg_restore's parallel algorithm worked
at the cost of greatly increasing out-of-order TOC requests.

This patch changes pg_restore to scan through all TOCs to service a TOC
read request when restoring a custom dump file without data offsets.
The odds of getting a successful parallel restore go way up at the cost
of a bunch of extra tiny reads when pg_restore starts up.

The pg_restore manpage now warns against running pg_dump with an
unseekable output file and suggests that if you plan on doing a parallel
restore of a custom dump you should run pg_dump with --file.
---
 doc/src/sgml/ref/pg_restore.sgml   |  8 ++++++++
 src/bin/pg_dump/pg_backup_custom.c | 24 ++++++++++++++++++++++--
 2 files changed, 30 insertions(+), 2 deletions(-)

diff --git doc/src/sgml/ref/pg_restore.sgml doc/src/sgml/ref/pg_restore.sgml
index 232f88024f..a0bf64767b 100644
--- doc/src/sgml/ref/pg_restore.sgml
+++ doc/src/sgml/ref/pg_restore.sgml
@@ -279,6 +279,14 @@ PostgreSQL documentation
         jobs cannot be used together with the
         option <option>--single-transaction</option>.
        </para>
+
+       <para>
+        <application>pg_restore</application> with concurrent jobs may fail
+        when restoring a custom archive format dump written to an unseekable
+        output stream, like stdout. For the best performance when restoring
+        a custom archive format dump use <application>pg_dump</application>'s
+        <option>--file</option> option to specify an output file.
+       </para>
       </listitem>
      </varlistentry>
 
diff --git src/bin/pg_dump/pg_backup_custom.c src/bin/pg_dump/pg_backup_custom.c
index 369dcea429..91c60f7103 100644
--- src/bin/pg_dump/pg_backup_custom.c
+++ src/bin/pg_dump/pg_backup_custom.c
@@ -415,6 +415,7 @@ _PrintTocData(ArchiveHandle *AH, TocEntry *te)
 	lclTocEntry *tctx = (lclTocEntry *) te->formatData;
 	int			blkType;
 	int			id;
+	bool		initialScan = true;
 
 	if (tctx->dataState == K_OFFSET_NO_DATA)
 		return;
@@ -423,11 +424,19 @@ _PrintTocData(ArchiveHandle *AH, TocEntry *te)
 	{
 		/*
 		 * We cannot seek directly to the desired block.  Instead, skip over
-		 * block headers until we find the one we want.  This could fail if we
-		 * are asked to restore items out-of-order.
+		 * block headers until we find the one we want.
 		 */
+
 		_readBlockHeader(AH, &blkType, &id);
 
+		if (blkType == EOF && ctx->hasSeek) {
+			/* Started at the end of the file */
+			initialScan = false;
+			if (fseeko(AH->FH, ctx->dataStart, SEEK_SET) != 0)
+				fatal("error during file seek: %m");
+			_readBlockHeader(AH, &blkType, &id);
+		}
+
 		while (blkType != EOF && id != te->dumpId)
 		{
 			switch (blkType)
@@ -446,6 +455,17 @@ _PrintTocData(ArchiveHandle *AH, TocEntry *te)
 					break;
 			}
 			_readBlockHeader(AH, &blkType, &id);
+
+			if (blkType == EOF && ctx->hasSeek && initialScan) {
+				/*
+				 * This was possibly an out-of-order request.
+				 * Try one extra pass over the file to find it.
+				 */
+				initialScan = false;
+				if (fseeko(AH->FH, ctx->dataStart, SEEK_SET) != 0)
+					fatal("error during file seek: %m");
+				_readBlockHeader(AH, &blkType, &id);
+			}
 		}
 	}
 	else
-- 
2.26.2

From e1136ca7817e36f48346da8771ca1f0100158295 Mon Sep 17 00:00:00 2001
From: David Gilman <davidgilman1@gmail.com>
Date: Sat, 23 May 2020 10:49:33 -0400
Subject: [PATCH 3/3] Add integration test for out-of-order TOC requests in
 pg_restore

Add undocumented --disable-seeking argument to pg_dump to emulate
writing to an unseekable output file.
---
 src/bin/pg_dump/pg_backup.h          |  4 +-
 src/bin/pg_dump/pg_backup_archiver.c | 14 ++--
 src/bin/pg_dump/pg_backup_archiver.h |  1 +
 src/bin/pg_dump/pg_backup_custom.c   |  7 +-
 src/bin/pg_dump/pg_dump.c            |  6 +-
 src/bin/pg_dump/t/002_pg_dump.pl     | 97 +++++++++++++++++++++++++++-
 6 files changed, 117 insertions(+), 12 deletions(-)

diff --git src/bin/pg_dump/pg_backup.h src/bin/pg_dump/pg_backup.h
index 8c0cedcd98..160d705eb5 100644
--- src/bin/pg_dump/pg_backup.h
+++ src/bin/pg_dump/pg_backup.h
@@ -158,6 +158,7 @@ typedef struct _dumpOptions
 	int			use_setsessauth;
 	int			enable_row_security;
 	int			load_via_partition_root;
+	int			disable_seeking;
 
 	/* default, if no "inclusion" switches appear, is to dump everything */
 	bool		include_everything;
@@ -270,7 +271,8 @@ extern Archive *OpenArchive(const char *FileSpec, const ArchiveFormat fmt);
 /* Create a new archive */
 extern Archive *CreateArchive(const char *FileSpec, const ArchiveFormat fmt,
 							  const int compression, bool dosync, ArchiveMode mode,
-							  SetupWorkerPtrType setupDumpWorker);
+							  SetupWorkerPtrType setupDumpWorker,
+							  bool disableSeeking);
 
 /* The --list option */
 extern void PrintTOCSummary(Archive *AH);
diff --git src/bin/pg_dump/pg_backup_archiver.c src/bin/pg_dump/pg_backup_archiver.c
index 8f0b32ca17..0e5d4e0a60 100644
--- src/bin/pg_dump/pg_backup_archiver.c
+++ src/bin/pg_dump/pg_backup_archiver.c
@@ -69,7 +69,8 @@ typedef struct _parallelReadyList
 
 static ArchiveHandle *_allocAH(const char *FileSpec, const ArchiveFormat fmt,
 							   const int compression, bool dosync, ArchiveMode mode,
-							   SetupWorkerPtrType setupWorkerPtr);
+							   SetupWorkerPtrType setupWorkerPtr,
+							   bool disableSeeking);
 static void _getObjectDescription(PQExpBuffer buf, TocEntry *te,
 								  ArchiveHandle *AH);
 static void _printTocEntry(ArchiveHandle *AH, TocEntry *te, bool isData);
@@ -233,11 +234,12 @@ setupRestoreWorker(Archive *AHX)
 Archive *
 CreateArchive(const char *FileSpec, const ArchiveFormat fmt,
 			  const int compression, bool dosync, ArchiveMode mode,
-			  SetupWorkerPtrType setupDumpWorker)
+			  SetupWorkerPtrType setupDumpWorker,
+			  bool disableSeeking)
 
 {
 	ArchiveHandle *AH = _allocAH(FileSpec, fmt, compression, dosync,
-								 mode, setupDumpWorker);
+								 mode, setupDumpWorker, disableSeeking);
 
 	return (Archive *) AH;
 }
@@ -247,7 +249,7 @@ CreateArchive(const char *FileSpec, const ArchiveFormat fmt,
 Archive *
 OpenArchive(const char *FileSpec, const ArchiveFormat fmt)
 {
-	ArchiveHandle *AH = _allocAH(FileSpec, fmt, 0, true, archModeRead, setupRestoreWorker);
+	ArchiveHandle *AH = _allocAH(FileSpec, fmt, 0, true, archModeRead, setupRestoreWorker, false);
 
 	return (Archive *) AH;
 }
@@ -2274,7 +2276,8 @@ _discoverArchiveFormat(ArchiveHandle *AH)
 static ArchiveHandle *
 _allocAH(const char *FileSpec, const ArchiveFormat fmt,
 		 const int compression, bool dosync, ArchiveMode mode,
-		 SetupWorkerPtrType setupWorkerPtr)
+		 SetupWorkerPtrType setupWorkerPtr,
+		 bool disableSeeking)
 {
 	ArchiveHandle *AH;
 
@@ -2325,6 +2328,7 @@ _allocAH(const char *FileSpec, const ArchiveFormat fmt,
 	AH->mode = mode;
 	AH->compression = compression;
 	AH->dosync = dosync;
+	AH->disableSeeking = disableSeeking;
 
 	memset(&(AH->sqlparse), 0, sizeof(AH->sqlparse));
 
diff --git src/bin/pg_dump/pg_backup_archiver.h src/bin/pg_dump/pg_backup_archiver.h
index f9e6b42752..acf38c20da 100644
--- src/bin/pg_dump/pg_backup_archiver.h
+++ src/bin/pg_dump/pg_backup_archiver.h
@@ -340,6 +340,7 @@ struct _archiveHandle
 	bool		dosync;			/* data requested to be synced on sight */
 	ArchiveMode mode;			/* File mode - r or w */
 	void	   *formatData;		/* Header data specific to file format */
+	bool		disableSeeking;	/* Don't use fseeko() */
 
 	/* these vars track state to avoid sending redundant SET commands */
 	char	   *currUser;		/* current username, or NULL if unknown */
diff --git src/bin/pg_dump/pg_backup_custom.c src/bin/pg_dump/pg_backup_custom.c
index 91c60f7103..d471dfe267 100644
--- src/bin/pg_dump/pg_backup_custom.c
+++ src/bin/pg_dump/pg_backup_custom.c
@@ -145,6 +145,7 @@ InitArchiveFmt_Custom(ArchiveHandle *AH)
 	AH->lo_buf = (void *) pg_malloc(LOBBUFSIZE);
 
 	ctx->filePos = 0;
+	ctx->hasSeek = 0;
 
 	/*
 	 * Now open the file
@@ -164,7 +165,8 @@ InitArchiveFmt_Custom(ArchiveHandle *AH)
 				fatal("could not open output file: %m");
 		}
 
-		ctx->hasSeek = checkSeek(AH->FH);
+		if (!AH->disableSeeking)
+			ctx->hasSeek = checkSeek(AH->FH);
 	}
 	else
 	{
@@ -181,7 +183,8 @@ InitArchiveFmt_Custom(ArchiveHandle *AH)
 				fatal("could not open input file: %m");
 		}
 
-		ctx->hasSeek = checkSeek(AH->FH);
+		if (!AH->disableSeeking)
+			ctx->hasSeek = checkSeek(AH->FH);
 
 		ReadHead(AH);
 		ReadToc(AH);
diff --git src/bin/pg_dump/pg_dump.c src/bin/pg_dump/pg_dump.c
index a4e949c636..6ed45d319f 100644
--- src/bin/pg_dump/pg_dump.c
+++ src/bin/pg_dump/pg_dump.c
@@ -319,6 +319,7 @@ main(int argc, char **argv)
 	int			plainText = 0;
 	ArchiveFormat archiveFormat = archUnknown;
 	ArchiveMode archiveMode;
+	bool		disableSeeking = false;
 
 	static DumpOptions dopt;
 
@@ -360,6 +361,7 @@ main(int argc, char **argv)
 		{"binary-upgrade", no_argument, &dopt.binary_upgrade, 1},
 		{"column-inserts", no_argument, &dopt.column_inserts, 1},
 		{"disable-dollar-quoting", no_argument, &dopt.disable_dollar_quoting, 1},
+		{"disable-seeking", no_argument, &dopt.disable_seeking, 1},
 		{"disable-triggers", no_argument, &dopt.disable_triggers, 1},
 		{"enable-row-security", no_argument, &dopt.enable_row_security, 1},
 		{"exclude-table-data", required_argument, NULL, 4},
@@ -673,6 +675,8 @@ main(int argc, char **argv)
 	if (archiveFormat == archNull)
 		plainText = 1;
 
+	disableSeeking = (bool) dopt.disable_seeking;
+
 	/* Custom and directory formats are compressed by default, others not */
 	if (compressLevel == -1)
 	{
@@ -715,7 +719,7 @@ main(int argc, char **argv)
 
 	/* Open the output file */
 	fout = CreateArchive(filename, archiveFormat, compressLevel, dosync,
-						 archiveMode, setupDumpWorker);
+						 archiveMode, setupDumpWorker, disableSeeking);
 
 	/* Make dump options accessible right away */
 	SetArchiveOptions(fout, &dopt, NULL);
diff --git src/bin/pg_dump/t/002_pg_dump.pl src/bin/pg_dump/t/002_pg_dump.pl
index e116235769..2423ec5b1d 100644
--- src/bin/pg_dump/t/002_pg_dump.pl
+++ src/bin/pg_dump/t/002_pg_dump.pl
@@ -31,9 +31,20 @@ my $tempdir_short = TestLib::tempdir_short;
 # generate a text file to run through the tests from the
 # non-text file generated by pg_dump.
 #
-# TODO: Have pg_restore actually restore to an independent
-# database and then pg_dump *that* database (or something along
-# those lines) to validate that part of the process.
+# secondary_dump_cmd is an optional key that enables a
+#
+# 	pg_dump (dump_cmd) ->
+# 	pg_restore (restore_cmd, real database) ->
+#	pg_dump (secondary_dmp_cmd, SQL output (for matching))
+#
+# test process instead of the default
+#
+# 	pg_dump (dump_cmd) ->
+# 	pg_restore (restore_cmd, SQL output (for matching))
+#
+# test process. The secondary_dump database is created for
+# you and your restore_cmd and secondary_dmp_cmd must read
+# and write from it.
 
 my %pgdump_runs = (
 	binary_upgrade => {
@@ -139,6 +150,23 @@ my %pgdump_runs = (
 			"$tempdir/defaults_custom_format.dump",
 		],
 	},
+	defaults_custom_format_no_seek_parallel_restore => {
+		test_key => 'defaults',
+		dump_cmd => [
+			'pg_dump', '-Fc', '-Z6', '--no-sync', '--disable-seeking',
+			"--file=$tempdir/defaults_custom_format_no_seek_parallel_restore.dump", 'postgres',
+		],
+		restore_cmd => [
+			'pg_restore', '-Fc', '--jobs=2',
+			'--dbname=secondary_dump',
+			"$tempdir/defaults_custom_format_no_seek_parallel_restore.dump",
+		],
+		secondary_dump_cmd => [
+			'pg_dump', '-Fp', '--no-sync',
+			"--file=$tempdir/defaults_custom_format_no_seek_parallel_restore.sql",
+			'secondary_dump',
+		],
+	},
 
 	# Do not use --no-sync to give test coverage for data sync.
 	defaults_dir_format => {
@@ -153,6 +181,24 @@ my %pgdump_runs = (
 			"$tempdir/defaults_dir_format",
 		],
 	},
+	defaults_dir_format_parallel => {
+		test_key => 'defaults',
+		dump_cmd => [
+			'pg_dump',                                      '-Fd',
+			'--jobs=2',                                     '--no-sync',
+			"--file=$tempdir/defaults_dir_format_parallel", 'postgres',
+		],
+		restore_cmd => [
+			'pg_restore', '-Fd',
+			'--jobs=2', '--dbname=secondary_dump',
+			"$tempdir/defaults_dir_format_parallel",
+		],
+		secondary_dump_cmd => [
+			'pg_dump', '-Fp', '--no-sync',
+			"--file=$tempdir/defaults_dir_format_parallel.sql",
+			'secondary_dump',
+		],
+	},
 
 	# Do not use --no-sync to give test coverage for data sync.
 	defaults_parallel => {
@@ -3298,6 +3344,27 @@ my %tests = (
 			%full_runs, %dump_test_schema_runs, section_pre_data => 1,
 		},
 		unlike => { exclude_dump_test_schema => 1 },
+	},
+
+	# This reliably produces the "possibly due to out-of-order restore request"
+	# when restoring with -j 2 when this was written but future changes to how
+	# pg_dump works could accidentally correctly order things as we're not really
+	# telling pg_dump how to do its on-disk layout.
+	'custom dump out-of-order' => {
+		create_sql   => '
+			CREATE TABLE ooo_parent0 (id integer primary key);
+			CREATE TABLE ooo_child0 (parent0 int references ooo_parent0 (id));
+			CREATE TABLE ooo_parent1 (id integer primary key);
+			CREATE TABLE ooo_child1 (parent0 int references ooo_parent1 (id));',
+		regexp => qr/^
+			 \QCREATE TABLE public.ooo_child0 (\E\n
+			 \s+\Qparent0 integer\E\n
+			 \Q);\E\n/xm,
+		like => {
+			%full_runs, section_pre_data => 1,
+			defaults_custom_format_no_seek => 1,
+			defaults_custom_format_no_seek_parallel_restore => 1,
+		},
 	});
 
 #########################################
@@ -3350,6 +3417,11 @@ foreach my $run (sort keys %pgdump_runs)
 		$num_tests++;
 	}
 
+	if ($pgdump_runs{$run}->{secondary_dump_cmd})
+	{
+		$num_tests++;
+	}
+
 	if ($pgdump_runs{$run}->{test_key})
 	{
 		$test_key = $pgdump_runs{$run}->{test_key};
@@ -3499,12 +3571,23 @@ foreach my $run (sort keys %pgdump_runs)
 	$node->command_ok(\@{ $pgdump_runs{$run}->{dump_cmd} },
 		"$run: pg_dump runs");
 
+	if ($pgdump_runs{$run}->{secondary_dump_cmd})
+	{
+		$node->safe_psql('postgres', 'create database secondary_dump;');
+	}
+
 	if ($pgdump_runs{$run}->{restore_cmd})
 	{
 		$node->command_ok(\@{ $pgdump_runs{$run}->{restore_cmd} },
 			"$run: pg_restore runs");
 	}
 
+	if ($pgdump_runs{$run}->{secondary_dump_cmd})
+	{
+		$node->command_ok(\@{ $pgdump_runs{$run}->{secondary_dump_cmd} },
+			"$run: secondary pg_dump runs");
+	}
+
 	if ($pgdump_runs{$run}->{test_key})
 	{
 		$test_key = $pgdump_runs{$run}->{test_key};
@@ -3561,6 +3644,14 @@ foreach my $run (sort keys %pgdump_runs)
 			}
 		}
 	}
+
+	if ($pgdump_runs{$run}->{secondary_dump_cmd})
+	{
+		$node->safe_psql('secondary_dump',
+			'alter subscription sub1 set (slot_name = NONE);');
+		$node->safe_psql('secondary_dump', 'drop subscription sub1;');
+		$node->safe_psql('postgres',       'drop database secondary_dump;');
+	}
 }
 
 #########################################
-- 
2.26.2

From 5ffc0420bc6040cc26b1a36ac522b696ef428464 Mon Sep 17 00:00:00 2001
From: David Gilman <davidgilman1@gmail.com>
Date: Fri, 22 May 2020 17:27:51 -0400
Subject: [PATCH 1/4] Remove unused seek check in tar dump format

---
 src/bin/pg_dump/pg_backup_tar.c | 5 -----
 1 file changed, 5 deletions(-)

diff --git a/src/bin/pg_dump/pg_backup_tar.c b/src/bin/pg_dump/pg_backup_tar.c
index d5bfa55646..bb5d3b1f45 100644
--- a/src/bin/pg_dump/pg_backup_tar.c
+++ b/src/bin/pg_dump/pg_backup_tar.c
@@ -82,7 +82,6 @@ typedef struct
 
 typedef struct
 {
-	int			hasSeek;
 	pgoff_t		filePos;
 	TAR_MEMBER *blobToc;
 	FILE	   *tarFH;
@@ -192,8 +191,6 @@ InitArchiveFmt_Tar(ArchiveHandle *AH)
 		 */
 		/* setvbuf(ctx->tarFH, NULL, _IONBF, 0); */
 
-		ctx->hasSeek = checkSeek(ctx->tarFH);
-
 		/*
 		 * We don't support compression because reading the files back is not
 		 * possible since gzdopen uses buffered IO which totally screws file
@@ -226,8 +223,6 @@ InitArchiveFmt_Tar(ArchiveHandle *AH)
 
 		ctx->tarFHpos = 0;
 
-		ctx->hasSeek = checkSeek(ctx->tarFH);
-
 		/*
 		 * Forcibly unmark the header as read since we use the lookahead
 		 * buffer
-- 
2.26.2

From 8687d3947cab7ffadc0c7bdc61e19c4b8a94da2d Mon Sep 17 00:00:00 2001
From: David Gilman <davidgilman1@gmail.com>
Date: Mon, 25 May 2020 17:34:52 -0400
Subject: [PATCH 2/4] Skip tables in pg_restore by seeking instead of reading

---
 src/bin/pg_dump/pg_backup_custom.c | 28 ++++++++++++++++++----------
 1 file changed, 18 insertions(+), 10 deletions(-)

diff --git a/src/bin/pg_dump/pg_backup_custom.c b/src/bin/pg_dump/pg_backup_custom.c
index 369dcea429..8c84e3c611 100644
--- a/src/bin/pg_dump/pg_backup_custom.c
+++ b/src/bin/pg_dump/pg_backup_custom.c
@@ -562,19 +562,27 @@ _skipData(ArchiveHandle *AH)
 	blkLen = ReadInt(AH);
 	while (blkLen != 0)
 	{
-		if (blkLen > buflen)
+		if (ctx->hasSeek)
 		{
-			if (buf)
-				free(buf);
-			buf = (char *) pg_malloc(blkLen);
-			buflen = blkLen;
+			if (fseeko(AH->FH, blkLen, SEEK_CUR) != 0)
+				fatal("error during file seek: %m");
 		}
-		if ((cnt = fread(buf, 1, blkLen, AH->FH)) != blkLen)
+		else
 		{
-			if (feof(AH->FH))
-				fatal("could not read from input file: end of file");
-			else
-				fatal("could not read from input file: %m");
+			if (blkLen > buflen)
+			{
+				if (buf)
+					free(buf);
+				buf = (char *) pg_malloc(blkLen);
+				buflen = blkLen;
+			}
+			if ((cnt = fread(buf, 1, blkLen, AH->FH)) != blkLen)
+			{
+				if (feof(AH->FH))
+					fatal("could not read from input file: end of file");
+				else
+					fatal("could not read from input file: %m");
+			}
 		}
 
 		ctx->filePos += blkLen;
-- 
2.26.2

From ed106edbfd2eeeadec74289afb8d8bfe8f37fff1 Mon Sep 17 00:00:00 2001
From: David Gilman <davidgilman1@gmail.com>
Date: Wed, 20 May 2020 22:49:28 -0400
Subject: [PATCH 3/4] Scan all TOCs when restoring a custom dump file without
 offsets

TOC requests are not guaranteed to come in disk order. If the custom
dump file was written with data offsets, pg_restore can seek directly to
the data, making request order irrelevant. If there are no data offsets
pg_restore would never attempt to seek backwards, even when it was
possible, and would not find TOCs before the current read position in
the file. 548e50976 changed how pg_restore's parallel algorithm worked
at the cost of greatly increasing out-of-order TOC requests.

This patch changes pg_restore to scan through all TOCs to service a TOC
read request when restoring a custom dump file without data offsets.
The odds of getting a successful parallel restore go way up at the cost
of a bunch of extra tiny reads when pg_restore starts up.

The pg_restore manpage now warns against running pg_dump with an
unseekable output file and suggests that if you plan on doing a parallel
restore of a custom dump you should run pg_dump with --file.
---
 doc/src/sgml/ref/pg_restore.sgml   |  8 ++++++++
 src/bin/pg_dump/pg_backup_custom.c | 24 ++++++++++++++++++++++--
 2 files changed, 30 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml
index 232f88024f..a0bf64767b 100644
--- a/doc/src/sgml/ref/pg_restore.sgml
+++ b/doc/src/sgml/ref/pg_restore.sgml
@@ -279,6 +279,14 @@ PostgreSQL documentation
         jobs cannot be used together with the
         option <option>--single-transaction</option>.
        </para>
+
+       <para>
+        <application>pg_restore</application> with concurrent jobs may fail
+        when restoring a custom archive format dump written to an unseekable
+        output stream, like stdout. For the best performance when restoring
+        a custom archive format dump use <application>pg_dump</application>'s
+        <option>--file</option> option to specify an output file.
+       </para>
       </listitem>
      </varlistentry>
 
diff --git a/src/bin/pg_dump/pg_backup_custom.c b/src/bin/pg_dump/pg_backup_custom.c
index 8c84e3c611..851add4915 100644
--- a/src/bin/pg_dump/pg_backup_custom.c
+++ b/src/bin/pg_dump/pg_backup_custom.c
@@ -415,6 +415,7 @@ _PrintTocData(ArchiveHandle *AH, TocEntry *te)
 	lclTocEntry *tctx = (lclTocEntry *) te->formatData;
 	int			blkType;
 	int			id;
+	bool		initialScan = true;
 
 	if (tctx->dataState == K_OFFSET_NO_DATA)
 		return;
@@ -423,11 +424,19 @@ _PrintTocData(ArchiveHandle *AH, TocEntry *te)
 	{
 		/*
 		 * We cannot seek directly to the desired block.  Instead, skip over
-		 * block headers until we find the one we want.  This could fail if we
-		 * are asked to restore items out-of-order.
+		 * block headers until we find the one we want.
 		 */
+
 		_readBlockHeader(AH, &blkType, &id);
 
+		if (blkType == EOF && ctx->hasSeek) {
+			/* Started at the end of the file */
+			initialScan = false;
+			if (fseeko(AH->FH, ctx->dataStart, SEEK_SET) != 0)
+				fatal("error during file seek: %m");
+			_readBlockHeader(AH, &blkType, &id);
+		}
+
 		while (blkType != EOF && id != te->dumpId)
 		{
 			switch (blkType)
@@ -446,6 +455,17 @@ _PrintTocData(ArchiveHandle *AH, TocEntry *te)
 					break;
 			}
 			_readBlockHeader(AH, &blkType, &id);
+
+			if (blkType == EOF && ctx->hasSeek && initialScan) {
+				/*
+				 * This was possibly an out-of-order request.
+				 * Try one extra pass over the file to find it.
+				 */
+				initialScan = false;
+				if (fseeko(AH->FH, ctx->dataStart, SEEK_SET) != 0)
+					fatal("error during file seek: %m");
+				_readBlockHeader(AH, &blkType, &id);
+			}
 		}
 	}
 	else
-- 
2.26.2

From 69d7a9bafa342dc558c0d8efcee1af4b9e7e07d5 Mon Sep 17 00:00:00 2001
From: David Gilman <davidgilman1@gmail.com>
Date: Sat, 23 May 2020 10:49:33 -0400
Subject: [PATCH 4/4] Add integration test for out-of-order TOC requests in
 pg_restore

Add undocumented --disable-seeking argument to pg_dump to emulate
writing to an unseekable output file.
---
 src/bin/pg_dump/pg_backup.h          |  4 +-
 src/bin/pg_dump/pg_backup_archiver.c | 14 ++--
 src/bin/pg_dump/pg_backup_archiver.h |  1 +
 src/bin/pg_dump/pg_backup_custom.c   |  7 +-
 src/bin/pg_dump/pg_dump.c            |  6 +-
 src/bin/pg_dump/t/002_pg_dump.pl     | 97 +++++++++++++++++++++++++++-
 6 files changed, 117 insertions(+), 12 deletions(-)

diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index 8c0cedcd98..160d705eb5 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -158,6 +158,7 @@ typedef struct _dumpOptions
 	int			use_setsessauth;
 	int			enable_row_security;
 	int			load_via_partition_root;
+	int			disable_seeking;
 
 	/* default, if no "inclusion" switches appear, is to dump everything */
 	bool		include_everything;
@@ -270,7 +271,8 @@ extern Archive *OpenArchive(const char *FileSpec, const ArchiveFormat fmt);
 /* Create a new archive */
 extern Archive *CreateArchive(const char *FileSpec, const ArchiveFormat fmt,
 							  const int compression, bool dosync, ArchiveMode mode,
-							  SetupWorkerPtrType setupDumpWorker);
+							  SetupWorkerPtrType setupDumpWorker,
+							  bool disableSeeking);
 
 /* The --list option */
 extern void PrintTOCSummary(Archive *AH);
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 8f0b32ca17..0e5d4e0a60 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -69,7 +69,8 @@ typedef struct _parallelReadyList
 
 static ArchiveHandle *_allocAH(const char *FileSpec, const ArchiveFormat fmt,
 							   const int compression, bool dosync, ArchiveMode mode,
-							   SetupWorkerPtrType setupWorkerPtr);
+							   SetupWorkerPtrType setupWorkerPtr,
+							   bool disableSeeking);
 static void _getObjectDescription(PQExpBuffer buf, TocEntry *te,
 								  ArchiveHandle *AH);
 static void _printTocEntry(ArchiveHandle *AH, TocEntry *te, bool isData);
@@ -233,11 +234,12 @@ setupRestoreWorker(Archive *AHX)
 Archive *
 CreateArchive(const char *FileSpec, const ArchiveFormat fmt,
 			  const int compression, bool dosync, ArchiveMode mode,
-			  SetupWorkerPtrType setupDumpWorker)
+			  SetupWorkerPtrType setupDumpWorker,
+			  bool disableSeeking)
 
 {
 	ArchiveHandle *AH = _allocAH(FileSpec, fmt, compression, dosync,
-								 mode, setupDumpWorker);
+								 mode, setupDumpWorker, disableSeeking);
 
 	return (Archive *) AH;
 }
@@ -247,7 +249,7 @@ CreateArchive(const char *FileSpec, const ArchiveFormat fmt,
 Archive *
 OpenArchive(const char *FileSpec, const ArchiveFormat fmt)
 {
-	ArchiveHandle *AH = _allocAH(FileSpec, fmt, 0, true, archModeRead, setupRestoreWorker);
+	ArchiveHandle *AH = _allocAH(FileSpec, fmt, 0, true, archModeRead, setupRestoreWorker, false);
 
 	return (Archive *) AH;
 }
@@ -2274,7 +2276,8 @@ _discoverArchiveFormat(ArchiveHandle *AH)
 static ArchiveHandle *
 _allocAH(const char *FileSpec, const ArchiveFormat fmt,
 		 const int compression, bool dosync, ArchiveMode mode,
-		 SetupWorkerPtrType setupWorkerPtr)
+		 SetupWorkerPtrType setupWorkerPtr,
+		 bool disableSeeking)
 {
 	ArchiveHandle *AH;
 
@@ -2325,6 +2328,7 @@ _allocAH(const char *FileSpec, const ArchiveFormat fmt,
 	AH->mode = mode;
 	AH->compression = compression;
 	AH->dosync = dosync;
+	AH->disableSeeking = disableSeeking;
 
 	memset(&(AH->sqlparse), 0, sizeof(AH->sqlparse));
 
diff --git a/src/bin/pg_dump/pg_backup_archiver.h b/src/bin/pg_dump/pg_backup_archiver.h
index f9e6b42752..acf38c20da 100644
--- a/src/bin/pg_dump/pg_backup_archiver.h
+++ b/src/bin/pg_dump/pg_backup_archiver.h
@@ -340,6 +340,7 @@ struct _archiveHandle
 	bool		dosync;			/* data requested to be synced on sight */
 	ArchiveMode mode;			/* File mode - r or w */
 	void	   *formatData;		/* Header data specific to file format */
+	bool		disableSeeking;	/* Don't use fseeko() */
 
 	/* these vars track state to avoid sending redundant SET commands */
 	char	   *currUser;		/* current username, or NULL if unknown */
diff --git a/src/bin/pg_dump/pg_backup_custom.c b/src/bin/pg_dump/pg_backup_custom.c
index 851add4915..79445ac363 100644
--- a/src/bin/pg_dump/pg_backup_custom.c
+++ b/src/bin/pg_dump/pg_backup_custom.c
@@ -145,6 +145,7 @@ InitArchiveFmt_Custom(ArchiveHandle *AH)
 	AH->lo_buf = (void *) pg_malloc(LOBBUFSIZE);
 
 	ctx->filePos = 0;
+	ctx->hasSeek = 0;
 
 	/*
 	 * Now open the file
@@ -164,7 +165,8 @@ InitArchiveFmt_Custom(ArchiveHandle *AH)
 				fatal("could not open output file: %m");
 		}
 
-		ctx->hasSeek = checkSeek(AH->FH);
+		if (!AH->disableSeeking)
+			ctx->hasSeek = checkSeek(AH->FH);
 	}
 	else
 	{
@@ -181,7 +183,8 @@ InitArchiveFmt_Custom(ArchiveHandle *AH)
 				fatal("could not open input file: %m");
 		}
 
-		ctx->hasSeek = checkSeek(AH->FH);
+		if (!AH->disableSeeking)
+			ctx->hasSeek = checkSeek(AH->FH);
 
 		ReadHead(AH);
 		ReadToc(AH);
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index a4e949c636..6ed45d319f 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -319,6 +319,7 @@ main(int argc, char **argv)
 	int			plainText = 0;
 	ArchiveFormat archiveFormat = archUnknown;
 	ArchiveMode archiveMode;
+	bool		disableSeeking = false;
 
 	static DumpOptions dopt;
 
@@ -360,6 +361,7 @@ main(int argc, char **argv)
 		{"binary-upgrade", no_argument, &dopt.binary_upgrade, 1},
 		{"column-inserts", no_argument, &dopt.column_inserts, 1},
 		{"disable-dollar-quoting", no_argument, &dopt.disable_dollar_quoting, 1},
+		{"disable-seeking", no_argument, &dopt.disable_seeking, 1},
 		{"disable-triggers", no_argument, &dopt.disable_triggers, 1},
 		{"enable-row-security", no_argument, &dopt.enable_row_security, 1},
 		{"exclude-table-data", required_argument, NULL, 4},
@@ -673,6 +675,8 @@ main(int argc, char **argv)
 	if (archiveFormat == archNull)
 		plainText = 1;
 
+	disableSeeking = (bool) dopt.disable_seeking;
+
 	/* Custom and directory formats are compressed by default, others not */
 	if (compressLevel == -1)
 	{
@@ -715,7 +719,7 @@ main(int argc, char **argv)
 
 	/* Open the output file */
 	fout = CreateArchive(filename, archiveFormat, compressLevel, dosync,
-						 archiveMode, setupDumpWorker);
+						 archiveMode, setupDumpWorker, disableSeeking);
 
 	/* Make dump options accessible right away */
 	SetArchiveOptions(fout, &dopt, NULL);
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index e116235769..2423ec5b1d 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -31,9 +31,20 @@ my $tempdir_short = TestLib::tempdir_short;
 # generate a text file to run through the tests from the
 # non-text file generated by pg_dump.
 #
-# TODO: Have pg_restore actually restore to an independent
-# database and then pg_dump *that* database (or something along
-# those lines) to validate that part of the process.
+# secondary_dump_cmd is an optional key that enables a
+#
+# 	pg_dump (dump_cmd) ->
+# 	pg_restore (restore_cmd, real database) ->
+#	pg_dump (secondary_dmp_cmd, SQL output (for matching))
+#
+# test process instead of the default
+#
+# 	pg_dump (dump_cmd) ->
+# 	pg_restore (restore_cmd, SQL output (for matching))
+#
+# test process. The secondary_dump database is created for
+# you and your restore_cmd and secondary_dmp_cmd must read
+# and write from it.
 
 my %pgdump_runs = (
 	binary_upgrade => {
@@ -139,6 +150,23 @@ my %pgdump_runs = (
 			"$tempdir/defaults_custom_format.dump",
 		],
 	},
+	defaults_custom_format_no_seek_parallel_restore => {
+		test_key => 'defaults',
+		dump_cmd => [
+			'pg_dump', '-Fc', '-Z6', '--no-sync', '--disable-seeking',
+			"--file=$tempdir/defaults_custom_format_no_seek_parallel_restore.dump", 'postgres',
+		],
+		restore_cmd => [
+			'pg_restore', '-Fc', '--jobs=2',
+			'--dbname=secondary_dump',
+			"$tempdir/defaults_custom_format_no_seek_parallel_restore.dump",
+		],
+		secondary_dump_cmd => [
+			'pg_dump', '-Fp', '--no-sync',
+			"--file=$tempdir/defaults_custom_format_no_seek_parallel_restore.sql",
+			'secondary_dump',
+		],
+	},
 
 	# Do not use --no-sync to give test coverage for data sync.
 	defaults_dir_format => {
@@ -153,6 +181,24 @@ my %pgdump_runs = (
 			"$tempdir/defaults_dir_format",
 		],
 	},
+	defaults_dir_format_parallel => {
+		test_key => 'defaults',
+		dump_cmd => [
+			'pg_dump',                                      '-Fd',
+			'--jobs=2',                                     '--no-sync',
+			"--file=$tempdir/defaults_dir_format_parallel", 'postgres',
+		],
+		restore_cmd => [
+			'pg_restore', '-Fd',
+			'--jobs=2', '--dbname=secondary_dump',
+			"$tempdir/defaults_dir_format_parallel",
+		],
+		secondary_dump_cmd => [
+			'pg_dump', '-Fp', '--no-sync',
+			"--file=$tempdir/defaults_dir_format_parallel.sql",
+			'secondary_dump',
+		],
+	},
 
 	# Do not use --no-sync to give test coverage for data sync.
 	defaults_parallel => {
@@ -3298,6 +3344,27 @@ my %tests = (
 			%full_runs, %dump_test_schema_runs, section_pre_data => 1,
 		},
 		unlike => { exclude_dump_test_schema => 1 },
+	},
+
+	# This reliably produces the "possibly due to out-of-order restore request"
+	# when restoring with -j 2 when this was written but future changes to how
+	# pg_dump works could accidentally correctly order things as we're not really
+	# telling pg_dump how to do its on-disk layout.
+	'custom dump out-of-order' => {
+		create_sql   => '
+			CREATE TABLE ooo_parent0 (id integer primary key);
+			CREATE TABLE ooo_child0 (parent0 int references ooo_parent0 (id));
+			CREATE TABLE ooo_parent1 (id integer primary key);
+			CREATE TABLE ooo_child1 (parent0 int references ooo_parent1 (id));',
+		regexp => qr/^
+			 \QCREATE TABLE public.ooo_child0 (\E\n
+			 \s+\Qparent0 integer\E\n
+			 \Q);\E\n/xm,
+		like => {
+			%full_runs, section_pre_data => 1,
+			defaults_custom_format_no_seek => 1,
+			defaults_custom_format_no_seek_parallel_restore => 1,
+		},
 	});
 
 #########################################
@@ -3350,6 +3417,11 @@ foreach my $run (sort keys %pgdump_runs)
 		$num_tests++;
 	}
 
+	if ($pgdump_runs{$run}->{secondary_dump_cmd})
+	{
+		$num_tests++;
+	}
+
 	if ($pgdump_runs{$run}->{test_key})
 	{
 		$test_key = $pgdump_runs{$run}->{test_key};
@@ -3499,12 +3571,23 @@ foreach my $run (sort keys %pgdump_runs)
 	$node->command_ok(\@{ $pgdump_runs{$run}->{dump_cmd} },
 		"$run: pg_dump runs");
 
+	if ($pgdump_runs{$run}->{secondary_dump_cmd})
+	{
+		$node->safe_psql('postgres', 'create database secondary_dump;');
+	}
+
 	if ($pgdump_runs{$run}->{restore_cmd})
 	{
 		$node->command_ok(\@{ $pgdump_runs{$run}->{restore_cmd} },
 			"$run: pg_restore runs");
 	}
 
+	if ($pgdump_runs{$run}->{secondary_dump_cmd})
+	{
+		$node->command_ok(\@{ $pgdump_runs{$run}->{secondary_dump_cmd} },
+			"$run: secondary pg_dump runs");
+	}
+
 	if ($pgdump_runs{$run}->{test_key})
 	{
 		$test_key = $pgdump_runs{$run}->{test_key};
@@ -3561,6 +3644,14 @@ foreach my $run (sort keys %pgdump_runs)
 			}
 		}
 	}
+
+	if ($pgdump_runs{$run}->{secondary_dump_cmd})
+	{
+		$node->safe_psql('secondary_dump',
+			'alter subscription sub1 set (slot_name = NONE);');
+		$node->safe_psql('secondary_dump', 'drop subscription sub1;');
+		$node->safe_psql('postgres',       'drop database secondary_dump;');
+	}
 }
 
 #########################################
-- 
2.26.2

From 90e06cbb724f6f6a244dfc69f3d59ca2e7d29c01 Mon Sep 17 00:00:00 2001
From: David Gilman <davidgilman1@gmail.com>
Date: Wed, 20 May 2020 22:49:28 -0400
Subject: [PATCH 1/4] Scan all TOCs when restoring a custom dump file without
 offsets

TOC requests are not guaranteed to come in disk order. If the custom
dump file was written with data offsets, pg_restore can seek directly to
the data, making request order irrelevant. If there are no data offsets,
pg_restore would never attempt to seek backwards, even when seeking is
possible, and would be unable to find TOCs before the current read
position in the file. 548e50976 changed how pg_restore's parallel
algorithm worked at the cost of greatly increasing out-of-order TOC
requests.

This patch changes pg_restore to scan through all TOCs to service a TOC
read request when restoring a custom dump file without data offsets.
The odds of getting a successful parallel restore go way up at the cost
of a bunch of extra tiny reads when pg_restore starts up.

The pg_restore manpage now warns against running pg_dump with an
unseekable output file and suggests that if you plan on doing a parallel
restore of a custom dump, you should run pg_dump with --file.
---
 doc/src/sgml/ref/pg_restore.sgml   |  8 ++++++++
 src/bin/pg_dump/pg_backup_custom.c | 25 ++++++++++++++++++++-----
 2 files changed, 28 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml
index 232f88024f..23286bb076 100644
--- a/doc/src/sgml/ref/pg_restore.sgml
+++ b/doc/src/sgml/ref/pg_restore.sgml
@@ -279,6 +279,14 @@ PostgreSQL documentation
         jobs cannot be used together with the
         option <option>--single-transaction</option>.
        </para>
+
+       <para>
+        <application>pg_restore</application> with concurrent jobs may fail
+        when restoring a custom archive format dump written to an unseekable
+        output stream, like stdout. To allow for concurrent restoration of
+        a custom archive format dump, use <application>pg_dump</application>'s
+        <option>--file</option> option to specify an output file.
+       </para>
       </listitem>
      </varlistentry>
 
diff --git a/src/bin/pg_dump/pg_backup_custom.c b/src/bin/pg_dump/pg_backup_custom.c
index 369dcea429..5aa7ab33db 100644
--- a/src/bin/pg_dump/pg_backup_custom.c
+++ b/src/bin/pg_dump/pg_backup_custom.c
@@ -415,6 +415,7 @@ _PrintTocData(ArchiveHandle *AH, TocEntry *te)
 	lclTocEntry *tctx = (lclTocEntry *) te->formatData;
 	int			blkType;
 	int			id;
+	bool		initialScan = true;
 
 	if (tctx->dataState == K_OFFSET_NO_DATA)
 		return;
@@ -423,13 +424,28 @@ _PrintTocData(ArchiveHandle *AH, TocEntry *te)
 	{
 		/*
 		 * We cannot seek directly to the desired block.  Instead, skip over
-		 * block headers until we find the one we want.  This could fail if we
-		 * are asked to restore items out-of-order.
+		 * block headers until we find the one we want.
 		 */
-		_readBlockHeader(AH, &blkType, &id);
 
-		while (blkType != EOF && id != te->dumpId)
+		for (;;)
 		{
+			_readBlockHeader(AH, &blkType, &id);
+
+			if (blkType == EOF && ctx->hasSeek && initialScan)
+			{
+				/*
+				 * This was possibly an out-of-order request. Try one extra
+				 * pass over the file to find the TOC.
+				 */
+				initialScan = false;
+				if (fseeko(AH->FH, ctx->dataStart, SEEK_SET) != 0)
+					fatal("error during file seek: %m");
+				continue;
+			}
+
+			if (blkType == EOF || id == te->dumpId)
+				break;
+
 			switch (blkType)
 			{
 				case BLK_DATA:
@@ -445,7 +461,6 @@ _PrintTocData(ArchiveHandle *AH, TocEntry *te)
 						  blkType);
 					break;
 			}
-			_readBlockHeader(AH, &blkType, &id);
 		}
 	}
 	else
-- 
2.26.2

From 750958499b19e6295a15b01ccb3a9e3ce963af2c Mon Sep 17 00:00:00 2001
From: David Gilman <davidgilman1@gmail.com>
Date: Sat, 23 May 2020 10:49:33 -0400
Subject: [PATCH 2/4] Add integration test for out-of-order TOC requests in
 pg_restore

Add undocumented --disable-seeking argument to pg_dump to emulate
writing to an unseekable output file.
---
 src/bin/pg_dump/pg_backup.h          |  4 +-
 src/bin/pg_dump/pg_backup_archiver.c | 14 ++--
 src/bin/pg_dump/pg_backup_archiver.h |  1 +
 src/bin/pg_dump/pg_backup_custom.c   |  7 +-
 src/bin/pg_dump/pg_dump.c            |  6 +-
 src/bin/pg_dump/t/002_pg_dump.pl     | 97 +++++++++++++++++++++++++++-
 6 files changed, 117 insertions(+), 12 deletions(-)

diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index 8c0cedcd98..160d705eb5 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -158,6 +158,7 @@ typedef struct _dumpOptions
 	int			use_setsessauth;
 	int			enable_row_security;
 	int			load_via_partition_root;
+	int			disable_seeking;
 
 	/* default, if no "inclusion" switches appear, is to dump everything */
 	bool		include_everything;
@@ -270,7 +271,8 @@ extern Archive *OpenArchive(const char *FileSpec, const ArchiveFormat fmt);
 /* Create a new archive */
 extern Archive *CreateArchive(const char *FileSpec, const ArchiveFormat fmt,
 							  const int compression, bool dosync, ArchiveMode mode,
-							  SetupWorkerPtrType setupDumpWorker);
+							  SetupWorkerPtrType setupDumpWorker,
+							  bool disableSeeking);
 
 /* The --list option */
 extern void PrintTOCSummary(Archive *AH);
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 8f0b32ca17..0e5d4e0a60 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -69,7 +69,8 @@ typedef struct _parallelReadyList
 
 static ArchiveHandle *_allocAH(const char *FileSpec, const ArchiveFormat fmt,
 							   const int compression, bool dosync, ArchiveMode mode,
-							   SetupWorkerPtrType setupWorkerPtr);
+							   SetupWorkerPtrType setupWorkerPtr,
+							   bool disableSeeking);
 static void _getObjectDescription(PQExpBuffer buf, TocEntry *te,
 								  ArchiveHandle *AH);
 static void _printTocEntry(ArchiveHandle *AH, TocEntry *te, bool isData);
@@ -233,11 +234,12 @@ setupRestoreWorker(Archive *AHX)
 Archive *
 CreateArchive(const char *FileSpec, const ArchiveFormat fmt,
 			  const int compression, bool dosync, ArchiveMode mode,
-			  SetupWorkerPtrType setupDumpWorker)
+			  SetupWorkerPtrType setupDumpWorker,
+			  bool disableSeeking)
 
 {
 	ArchiveHandle *AH = _allocAH(FileSpec, fmt, compression, dosync,
-								 mode, setupDumpWorker);
+								 mode, setupDumpWorker, disableSeeking);
 
 	return (Archive *) AH;
 }
@@ -247,7 +249,7 @@ CreateArchive(const char *FileSpec, const ArchiveFormat fmt,
 Archive *
 OpenArchive(const char *FileSpec, const ArchiveFormat fmt)
 {
-	ArchiveHandle *AH = _allocAH(FileSpec, fmt, 0, true, archModeRead, setupRestoreWorker);
+	ArchiveHandle *AH = _allocAH(FileSpec, fmt, 0, true, archModeRead, setupRestoreWorker, false);
 
 	return (Archive *) AH;
 }
@@ -2274,7 +2276,8 @@ _discoverArchiveFormat(ArchiveHandle *AH)
 static ArchiveHandle *
 _allocAH(const char *FileSpec, const ArchiveFormat fmt,
 		 const int compression, bool dosync, ArchiveMode mode,
-		 SetupWorkerPtrType setupWorkerPtr)
+		 SetupWorkerPtrType setupWorkerPtr,
+		 bool disableSeeking)
 {
 	ArchiveHandle *AH;
 
@@ -2325,6 +2328,7 @@ _allocAH(const char *FileSpec, const ArchiveFormat fmt,
 	AH->mode = mode;
 	AH->compression = compression;
 	AH->dosync = dosync;
+	AH->disableSeeking = disableSeeking;
 
 	memset(&(AH->sqlparse), 0, sizeof(AH->sqlparse));
 
diff --git a/src/bin/pg_dump/pg_backup_archiver.h b/src/bin/pg_dump/pg_backup_archiver.h
index f9e6b42752..acf38c20da 100644
--- a/src/bin/pg_dump/pg_backup_archiver.h
+++ b/src/bin/pg_dump/pg_backup_archiver.h
@@ -340,6 +340,7 @@ struct _archiveHandle
 	bool		dosync;			/* data requested to be synced on sight */
 	ArchiveMode mode;			/* File mode - r or w */
 	void	   *formatData;		/* Header data specific to file format */
+	bool		disableSeeking;	/* Don't use fseeko() */
 
 	/* these vars track state to avoid sending redundant SET commands */
 	char	   *currUser;		/* current username, or NULL if unknown */
diff --git a/src/bin/pg_dump/pg_backup_custom.c b/src/bin/pg_dump/pg_backup_custom.c
index 5aa7ab33db..90a026e0f4 100644
--- a/src/bin/pg_dump/pg_backup_custom.c
+++ b/src/bin/pg_dump/pg_backup_custom.c
@@ -145,6 +145,7 @@ InitArchiveFmt_Custom(ArchiveHandle *AH)
 	AH->lo_buf = (void *) pg_malloc(LOBBUFSIZE);
 
 	ctx->filePos = 0;
+	ctx->hasSeek = 0;
 
 	/*
 	 * Now open the file
@@ -164,7 +165,8 @@ InitArchiveFmt_Custom(ArchiveHandle *AH)
 				fatal("could not open output file: %m");
 		}
 
-		ctx->hasSeek = checkSeek(AH->FH);
+		if (!AH->disableSeeking)
+			ctx->hasSeek = checkSeek(AH->FH);
 	}
 	else
 	{
@@ -181,7 +183,8 @@ InitArchiveFmt_Custom(ArchiveHandle *AH)
 				fatal("could not open input file: %m");
 		}
 
-		ctx->hasSeek = checkSeek(AH->FH);
+		if (!AH->disableSeeking)
+			ctx->hasSeek = checkSeek(AH->FH);
 
 		ReadHead(AH);
 		ReadToc(AH);
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index a4e949c636..6ed45d319f 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -319,6 +319,7 @@ main(int argc, char **argv)
 	int			plainText = 0;
 	ArchiveFormat archiveFormat = archUnknown;
 	ArchiveMode archiveMode;
+	bool		disableSeeking = false;
 
 	static DumpOptions dopt;
 
@@ -360,6 +361,7 @@ main(int argc, char **argv)
 		{"binary-upgrade", no_argument, &dopt.binary_upgrade, 1},
 		{"column-inserts", no_argument, &dopt.column_inserts, 1},
 		{"disable-dollar-quoting", no_argument, &dopt.disable_dollar_quoting, 1},
+		{"disable-seeking", no_argument, &dopt.disable_seeking, 1},
 		{"disable-triggers", no_argument, &dopt.disable_triggers, 1},
 		{"enable-row-security", no_argument, &dopt.enable_row_security, 1},
 		{"exclude-table-data", required_argument, NULL, 4},
@@ -673,6 +675,8 @@ main(int argc, char **argv)
 	if (archiveFormat == archNull)
 		plainText = 1;
 
+	disableSeeking = (bool) dopt.disable_seeking;
+
 	/* Custom and directory formats are compressed by default, others not */
 	if (compressLevel == -1)
 	{
@@ -715,7 +719,7 @@ main(int argc, char **argv)
 
 	/* Open the output file */
 	fout = CreateArchive(filename, archiveFormat, compressLevel, dosync,
-						 archiveMode, setupDumpWorker);
+						 archiveMode, setupDumpWorker, disableSeeking);
 
 	/* Make dump options accessible right away */
 	SetArchiveOptions(fout, &dopt, NULL);
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index e116235769..2423ec5b1d 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -31,9 +31,20 @@ my $tempdir_short = TestLib::tempdir_short;
 # generate a text file to run through the tests from the
 # non-text file generated by pg_dump.
 #
-# TODO: Have pg_restore actually restore to an independent
-# database and then pg_dump *that* database (or something along
-# those lines) to validate that part of the process.
+# secondary_dump_cmd is an optional key that enables a
+#
+# 	pg_dump (dump_cmd) ->
+# 	pg_restore (restore_cmd, real database) ->
+#	pg_dump (secondary_dmp_cmd, SQL output (for matching))
+#
+# test process instead of the default
+#
+# 	pg_dump (dump_cmd) ->
+# 	pg_restore (restore_cmd, SQL output (for matching))
+#
+# test process. The secondary_dump database is created for
+# you and your restore_cmd and secondary_dmp_cmd must read
+# and write from it.
 
 my %pgdump_runs = (
 	binary_upgrade => {
@@ -139,6 +150,23 @@ my %pgdump_runs = (
 			"$tempdir/defaults_custom_format.dump",
 		],
 	},
+	defaults_custom_format_no_seek_parallel_restore => {
+		test_key => 'defaults',
+		dump_cmd => [
+			'pg_dump', '-Fc', '-Z6', '--no-sync', '--disable-seeking',
+			"--file=$tempdir/defaults_custom_format_no_seek_parallel_restore.dump", 'postgres',
+		],
+		restore_cmd => [
+			'pg_restore', '-Fc', '--jobs=2',
+			'--dbname=secondary_dump',
+			"$tempdir/defaults_custom_format_no_seek_parallel_restore.dump",
+		],
+		secondary_dump_cmd => [
+			'pg_dump', '-Fp', '--no-sync',
+			"--file=$tempdir/defaults_custom_format_no_seek_parallel_restore.sql",
+			'secondary_dump',
+		],
+	},
 
 	# Do not use --no-sync to give test coverage for data sync.
 	defaults_dir_format => {
@@ -153,6 +181,24 @@ my %pgdump_runs = (
 			"$tempdir/defaults_dir_format",
 		],
 	},
+	defaults_dir_format_parallel => {
+		test_key => 'defaults',
+		dump_cmd => [
+			'pg_dump',                                      '-Fd',
+			'--jobs=2',                                     '--no-sync',
+			"--file=$tempdir/defaults_dir_format_parallel", 'postgres',
+		],
+		restore_cmd => [
+			'pg_restore', '-Fd',
+			'--jobs=2', '--dbname=secondary_dump',
+			"$tempdir/defaults_dir_format_parallel",
+		],
+		secondary_dump_cmd => [
+			'pg_dump', '-Fp', '--no-sync',
+			"--file=$tempdir/defaults_dir_format_parallel.sql",
+			'secondary_dump',
+		],
+	},
 
 	# Do not use --no-sync to give test coverage for data sync.
 	defaults_parallel => {
@@ -3298,6 +3344,27 @@ my %tests = (
 			%full_runs, %dump_test_schema_runs, section_pre_data => 1,
 		},
 		unlike => { exclude_dump_test_schema => 1 },
+	},
+
+	# This reliably produces the "possibly due to out-of-order restore request"
+	# when restoring with -j 2 when this was written but future changes to how
+	# pg_dump works could accidentally correctly order things as we're not really
+	# telling pg_dump how to do its on-disk layout.
+	'custom dump out-of-order' => {
+		create_sql   => '
+			CREATE TABLE ooo_parent0 (id integer primary key);
+			CREATE TABLE ooo_child0 (parent0 int references ooo_parent0 (id));
+			CREATE TABLE ooo_parent1 (id integer primary key);
+			CREATE TABLE ooo_child1 (parent0 int references ooo_parent1 (id));',
+		regexp => qr/^
+			 \QCREATE TABLE public.ooo_child0 (\E\n
+			 \s+\Qparent0 integer\E\n
+			 \Q);\E\n/xm,
+		like => {
+			%full_runs, section_pre_data => 1,
+			defaults_custom_format_no_seek => 1,
+			defaults_custom_format_no_seek_parallel_restore => 1,
+		},
 	});
 
 #########################################
@@ -3350,6 +3417,11 @@ foreach my $run (sort keys %pgdump_runs)
 		$num_tests++;
 	}
 
+	if ($pgdump_runs{$run}->{secondary_dump_cmd})
+	{
+		$num_tests++;
+	}
+
 	if ($pgdump_runs{$run}->{test_key})
 	{
 		$test_key = $pgdump_runs{$run}->{test_key};
@@ -3499,12 +3571,23 @@ foreach my $run (sort keys %pgdump_runs)
 	$node->command_ok(\@{ $pgdump_runs{$run}->{dump_cmd} },
 		"$run: pg_dump runs");
 
+	if ($pgdump_runs{$run}->{secondary_dump_cmd})
+	{
+		$node->safe_psql('postgres', 'create database secondary_dump;');
+	}
+
 	if ($pgdump_runs{$run}->{restore_cmd})
 	{
 		$node->command_ok(\@{ $pgdump_runs{$run}->{restore_cmd} },
 			"$run: pg_restore runs");
 	}
 
+	if ($pgdump_runs{$run}->{secondary_dump_cmd})
+	{
+		$node->command_ok(\@{ $pgdump_runs{$run}->{secondary_dump_cmd} },
+			"$run: secondary pg_dump runs");
+	}
+
 	if ($pgdump_runs{$run}->{test_key})
 	{
 		$test_key = $pgdump_runs{$run}->{test_key};
@@ -3561,6 +3644,14 @@ foreach my $run (sort keys %pgdump_runs)
 			}
 		}
 	}
+
+	if ($pgdump_runs{$run}->{secondary_dump_cmd})
+	{
+		$node->safe_psql('secondary_dump',
+			'alter subscription sub1 set (slot_name = NONE);');
+		$node->safe_psql('secondary_dump', 'drop subscription sub1;');
+		$node->safe_psql('postgres',       'drop database secondary_dump;');
+	}
 }
 
 #########################################
-- 
2.26.2

From 35d05669a0455bb928e51ea532a791e7f097987d Mon Sep 17 00:00:00 2001
From: David Gilman <davidgilman1@gmail.com>
Date: Fri, 22 May 2020 17:27:51 -0400
Subject: [PATCH 3/4] Remove unused seek check in tar dump format

---
 src/bin/pg_dump/pg_backup_tar.c | 5 -----
 1 file changed, 5 deletions(-)

diff --git a/src/bin/pg_dump/pg_backup_tar.c b/src/bin/pg_dump/pg_backup_tar.c
index d5bfa55646..bb5d3b1f45 100644
--- a/src/bin/pg_dump/pg_backup_tar.c
+++ b/src/bin/pg_dump/pg_backup_tar.c
@@ -82,7 +82,6 @@ typedef struct
 
 typedef struct
 {
-	int			hasSeek;
 	pgoff_t		filePos;
 	TAR_MEMBER *blobToc;
 	FILE	   *tarFH;
@@ -192,8 +191,6 @@ InitArchiveFmt_Tar(ArchiveHandle *AH)
 		 */
 		/* setvbuf(ctx->tarFH, NULL, _IONBF, 0); */
 
-		ctx->hasSeek = checkSeek(ctx->tarFH);
-
 		/*
 		 * We don't support compression because reading the files back is not
 		 * possible since gzdopen uses buffered IO which totally screws file
@@ -226,8 +223,6 @@ InitArchiveFmt_Tar(ArchiveHandle *AH)
 
 		ctx->tarFHpos = 0;
 
-		ctx->hasSeek = checkSeek(ctx->tarFH);
-
 		/*
 		 * Forcibly unmark the header as read since we use the lookahead
 		 * buffer
-- 
2.26.2

From aabcd1d843f063b7f9de2e44df7c6147226ad389 Mon Sep 17 00:00:00 2001
From: David Gilman <davidgilman1@gmail.com>
Date: Mon, 25 May 2020 17:34:52 -0400
Subject: [PATCH 4/4] Skip tables in pg_restore by seeking instead of reading

---
 src/bin/pg_dump/pg_backup_custom.c | 28 ++++++++++++++++++----------
 1 file changed, 18 insertions(+), 10 deletions(-)

diff --git a/src/bin/pg_dump/pg_backup_custom.c b/src/bin/pg_dump/pg_backup_custom.c
index 90a026e0f4..662e7a1793 100644
--- a/src/bin/pg_dump/pg_backup_custom.c
+++ b/src/bin/pg_dump/pg_backup_custom.c
@@ -580,19 +580,27 @@ _skipData(ArchiveHandle *AH)
 	blkLen = ReadInt(AH);
 	while (blkLen != 0)
 	{
-		if (blkLen > buflen)
+		if (ctx->hasSeek)
 		{
-			if (buf)
-				free(buf);
-			buf = (char *) pg_malloc(blkLen);
-			buflen = blkLen;
+			if (fseeko(AH->FH, blkLen, SEEK_CUR) != 0)
+				fatal("error during file seek: %m");
 		}
-		if ((cnt = fread(buf, 1, blkLen, AH->FH)) != blkLen)
+		else
 		{
-			if (feof(AH->FH))
-				fatal("could not read from input file: end of file");
-			else
-				fatal("could not read from input file: %m");
+			if (blkLen > buflen)
+			{
+				if (buf)
+					free(buf);
+				buf = (char *) pg_malloc(blkLen);
+				buflen = blkLen;
+			}
+			if ((cnt = fread(buf, 1, blkLen, AH->FH)) != blkLen)
+			{
+				if (feof(AH->FH))
+					fatal("could not read from input file: end of file");
+				else
+					fatal("could not read from input file: %m");
+			}
 		}
 
 		ctx->filePos += blkLen;
-- 
2.26.2

From a671dc328a7defa403ceb46b5107ed4ee5f53103 Mon Sep 17 00:00:00 2001
From: David Gilman <davidgilman1@gmail.com>
Date: Wed, 20 May 2020 22:49:28 -0400
Subject: [PATCH 1/5] Scan all TOCs when restoring a custom dump file without
 offsets

TOC requests are not guaranteed to come in disk order. If the custom
dump file was written with data offsets, pg_restore can seek directly to
the data, making request order irrelevant. If there are no data offsets,
pg_restore would never attempt to seek backwards, even when seeking is
possible, and would be unable to find TOCs before the current read
position in the file. 548e50976 changed how pg_restore's parallel
algorithm worked at the cost of greatly increasing out-of-order TOC
requests.

This patch changes pg_restore to scan through all TOCs to service a TOC
read request when restoring a custom dump file without data offsets.
The odds of getting a successful parallel restore go way up at the cost
of a bunch of extra tiny reads when pg_restore starts up.

The pg_restore manpage now warns against running pg_dump with an
unseekable output file and suggests that if you plan on doing a parallel
restore of a custom dump, you should run pg_dump with --file.
---
 doc/src/sgml/ref/pg_restore.sgml   |  8 ++++++++
 src/bin/pg_dump/pg_backup_custom.c | 25 ++++++++++++++++++++-----
 2 files changed, 28 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml
index 232f88024f..23286bb076 100644
--- a/doc/src/sgml/ref/pg_restore.sgml
+++ b/doc/src/sgml/ref/pg_restore.sgml
@@ -279,6 +279,14 @@ PostgreSQL documentation
         jobs cannot be used together with the
         option <option>--single-transaction</option>.
        </para>
+
+       <para>
+        <application>pg_restore</application> with concurrent jobs may fail
+        when restoring a custom archive format dump written to an unseekable
+        output stream, like stdout. To allow for concurrent restoration of
+        a custom archive format dump, use <application>pg_dump</application>'s
+        <option>--file</option> option to specify an output file.
+       </para>
       </listitem>
      </varlistentry>
 
diff --git a/src/bin/pg_dump/pg_backup_custom.c b/src/bin/pg_dump/pg_backup_custom.c
index 6ab122242c..a873ac3afa 100644
--- a/src/bin/pg_dump/pg_backup_custom.c
+++ b/src/bin/pg_dump/pg_backup_custom.c
@@ -413,6 +413,7 @@ _PrintTocData(ArchiveHandle *AH, TocEntry *te)
 	lclTocEntry *tctx = (lclTocEntry *) te->formatData;
 	int			blkType;
 	int			id;
+	bool		initialScan = true;
 
 	if (tctx->dataState == K_OFFSET_NO_DATA)
 		return;
@@ -421,13 +422,28 @@ _PrintTocData(ArchiveHandle *AH, TocEntry *te)
 	{
 		/*
 		 * We cannot seek directly to the desired block.  Instead, skip over
-		 * block headers until we find the one we want.  This could fail if we
-		 * are asked to restore items out-of-order.
+		 * block headers until we find the one we want.
 		 */
-		_readBlockHeader(AH, &blkType, &id);
 
-		while (blkType != EOF && id != te->dumpId)
+		for (;;)
 		{
+			_readBlockHeader(AH, &blkType, &id);
+
+			if (blkType == EOF && ctx->hasSeek && initialScan)
+			{
+				/*
+				 * This was possibly an out-of-order request. Try one extra
+				 * pass over the file to find the TOC.
+				 */
+				initialScan = false;
+				if (fseeko(AH->FH, ctx->dataStart, SEEK_SET) != 0)
+					fatal("error during file seek: %m");
+				continue;
+			}
+
+			if (blkType == EOF || id == te->dumpId)
+				break;
+
 			switch (blkType)
 			{
 				case BLK_DATA:
@@ -443,7 +459,6 @@ _PrintTocData(ArchiveHandle *AH, TocEntry *te)
 						  blkType);
 					break;
 			}
-			_readBlockHeader(AH, &blkType, &id);
 		}
 	}
 	else
-- 
2.27.0

From 6e8294021482fb76cb17d6c150ec6acb6d0cac37 Mon Sep 17 00:00:00 2001
From: David Gilman <davidgilman1@gmail.com>
Date: Sat, 23 May 2020 10:49:33 -0400
Subject: [PATCH 2/5] Add integration test for out-of-order TOC requests in
 pg_restore

---
 src/bin/pg_dump/t/002_pg_dump.pl | 107 ++++++++++++++++++++++++++++++-
 src/test/perl/TestLib.pm         |  12 +++-
 2 files changed, 114 insertions(+), 5 deletions(-)

diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index e116235769..d99e7a5d22 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -31,9 +31,20 @@ my $tempdir_short = TestLib::tempdir_short;
 # generate a text file to run through the tests from the
 # non-text file generated by pg_dump.
 #
-# TODO: Have pg_restore actually restore to an independent
-# database and then pg_dump *that* database (or something along
-# those lines) to validate that part of the process.
+# secondary_dump_cmd is an optional key that enables a
+#
+# 	pg_dump (dump_cmd) ->
+# 	pg_restore (restore_cmd, real database) ->
+#	pg_dump (secondary_dump_cmd, SQL output (for matching))
+#
+# test process instead of the default
+#
+# 	pg_dump (dump_cmd) ->
+# 	pg_restore (restore_cmd, SQL output (for matching))
+#
+# test process. The secondary_dump database is created for
+# you and your restore_cmd and secondary_dump_cmd must read
+# and write from it.
 
 my %pgdump_runs = (
 	binary_upgrade => {
@@ -139,6 +150,25 @@ my %pgdump_runs = (
 			"$tempdir/defaults_custom_format.dump",
 		],
 	},
+	defaults_custom_format_no_seek_parallel_restore => {
+		test_key => 'defaults',
+		dump_cmd => [
+			['pg_dump', '-Fc', '-Z6', '--no-sync', 'postgres'],
+			'|',
+			['perl', '-pe', ''],  # make pg_dump's stdout unseekable
+			">$tempdir/defaults_custom_format_no_seek_parallel_restore"
+		],
+		restore_cmd => [
+			'pg_restore', '-Fc', '--jobs=2',
+			'--dbname=secondary_dump',
+			"$tempdir/defaults_custom_format_no_seek_parallel_restore",
+		],
+		secondary_dump_cmd => [
+			'pg_dump', '-Fp', '--no-sync',
+			"--file=$tempdir/defaults_custom_format_no_seek_parallel_restore.sql",
+			'secondary_dump',
+		],
+	},
 
 	# Do not use --no-sync to give test coverage for data sync.
 	defaults_dir_format => {
@@ -153,6 +183,24 @@ my %pgdump_runs = (
 			"$tempdir/defaults_dir_format",
 		],
 	},
+	defaults_dir_format_parallel => {
+		test_key => 'defaults',
+		dump_cmd => [
+			'pg_dump',                                      '-Fd',
+			'--jobs=2',                                     '--no-sync',
+			"--file=$tempdir/defaults_dir_format_parallel", 'postgres',
+		],
+		restore_cmd => [
+			'pg_restore', '-Fd',
+			'--jobs=2', '--dbname=secondary_dump',
+			"$tempdir/defaults_dir_format_parallel",
+		],
+		secondary_dump_cmd => [
+			'pg_dump', '-Fp', '--no-sync',
+			"--file=$tempdir/defaults_dir_format_parallel.sql",
+			'secondary_dump',
+		],
+	},
 
 	# Do not use --no-sync to give test coverage for data sync.
 	defaults_parallel => {
@@ -3298,6 +3346,35 @@ my %tests = (
 			%full_runs, %dump_test_schema_runs, section_pre_data => 1,
 		},
 		unlike => { exclude_dump_test_schema => 1 },
+	},
+
+	# This reliably produces the "possibly due to out-of-order restore request"
+	# when restoring with -j 2 when this was written but future changes to how
+	# pg_dump works could accidentally correctly order things as we're not really
+	# telling pg_dump how to do its on-disk layout.
+	'custom dump out-of-order' => {
+		create_sql   => '
+			CREATE TABLE dump_test.ooo_parent0 (id int primary key);
+			CREATE TABLE dump_test.ooo_child0 (
+				parent0 int references dump_test.ooo_parent0 (id)
+			);
+			CREATE TABLE dump_test.ooo_parent1 (id int primary key);
+			CREATE TABLE dump_test.ooo_child1 (
+				parent0 int references dump_test.ooo_parent1 (id)
+			);',
+		regexp => qr/^
+			 \QCREATE TABLE dump_test.ooo_child0 (\E\n
+			 \s+\Qparent0 integer\E\n
+			 \Q);\E\n/xm,
+		like => {
+			%full_runs, %dump_test_schema_runs,
+			section_pre_data => 1,
+			defaults_custom_format_no_seek => 1,
+			defaults_custom_format_no_seek_parallel_restore => 1,
+		},
+		unlike => {
+			exclude_dump_test_schema => 1,
+		},
 	});
 
 #########################################
@@ -3350,6 +3427,11 @@ foreach my $run (sort keys %pgdump_runs)
 		$num_tests++;
 	}
 
+	if ($pgdump_runs{$run}->{secondary_dump_cmd})
+	{
+		$num_tests++;
+	}
+
 	if ($pgdump_runs{$run}->{test_key})
 	{
 		$test_key = $pgdump_runs{$run}->{test_key};
@@ -3499,12 +3581,23 @@ foreach my $run (sort keys %pgdump_runs)
 	$node->command_ok(\@{ $pgdump_runs{$run}->{dump_cmd} },
 		"$run: pg_dump runs");
 
+	if ($pgdump_runs{$run}->{secondary_dump_cmd})
+	{
+		$node->safe_psql('postgres', 'create database secondary_dump;');
+	}
+
 	if ($pgdump_runs{$run}->{restore_cmd})
 	{
 		$node->command_ok(\@{ $pgdump_runs{$run}->{restore_cmd} },
 			"$run: pg_restore runs");
 	}
 
+	if ($pgdump_runs{$run}->{secondary_dump_cmd})
+	{
+		$node->command_ok(\@{ $pgdump_runs{$run}->{secondary_dump_cmd} },
+			"$run: secondary pg_dump runs");
+	}
+
 	if ($pgdump_runs{$run}->{test_key})
 	{
 		$test_key = $pgdump_runs{$run}->{test_key};
@@ -3561,6 +3654,14 @@ foreach my $run (sort keys %pgdump_runs)
 			}
 		}
 	}
+
+	if ($pgdump_runs{$run}->{secondary_dump_cmd})
+	{
+		$node->safe_psql('secondary_dump',
+			'alter subscription sub1 set (slot_name = NONE);');
+		$node->safe_psql('secondary_dump', 'drop subscription sub1;');
+		$node->safe_psql('postgres',       'drop database secondary_dump;');
+	}
 }
 
 #########################################
diff --git a/src/test/perl/TestLib.pm b/src/test/perl/TestLib.pm
index d579d5c177..fb90d1abcc 100644
--- a/src/test/perl/TestLib.pm
+++ b/src/test/perl/TestLib.pm
@@ -338,8 +338,16 @@ The return value from the command is passed through.
 
 sub run_log
 {
-	print("# Running: " . join(" ", @{ $_[0] }) . "\n");
-	return IPC::Run::run(@_);
+	my $flatten = sub {
+		return map { ref eq 'ARRAY' ? @$_ : $_ } @_;
+	};
+	my @x = @{$_[0]};
+	print("# Running: " . join(" ", $flatten->(@x)) . "\n");
+	if (ref($x[0]) ne '') {
+		return IPC::Run::run(@x);
+	} else {
+		return IPC::Run::run(@_);
+	}
 }
 
 =pod
-- 
2.27.0

From fa48bd25d5f4a3c4a36a0f6ef09898f816d79286 Mon Sep 17 00:00:00 2001
From: David Gilman <davidgilman1@gmail.com>
Date: Fri, 22 May 2020 17:27:51 -0400
Subject: [PATCH 3/5] Remove unused seek check in tar dump format

---
 src/bin/pg_dump/pg_backup_tar.c | 5 -----
 1 file changed, 5 deletions(-)

diff --git a/src/bin/pg_dump/pg_backup_tar.c b/src/bin/pg_dump/pg_backup_tar.c
index b4f5942959..bc88c311c7 100644
--- a/src/bin/pg_dump/pg_backup_tar.c
+++ b/src/bin/pg_dump/pg_backup_tar.c
@@ -82,7 +82,6 @@ typedef struct
 
 typedef struct
 {
-	int			hasSeek;
 	pgoff_t		filePos;
 	TAR_MEMBER *blobToc;
 	FILE	   *tarFH;
@@ -192,8 +191,6 @@ InitArchiveFmt_Tar(ArchiveHandle *AH)
 		 */
 		/* setvbuf(ctx->tarFH, NULL, _IONBF, 0); */
 
-		ctx->hasSeek = checkSeek(ctx->tarFH);
-
 		/*
 		 * We don't support compression because reading the files back is not
 		 * possible since gzdopen uses buffered IO which totally screws file
@@ -226,8 +223,6 @@ InitArchiveFmt_Tar(ArchiveHandle *AH)
 
 		ctx->tarFHpos = 0;
 
-		ctx->hasSeek = checkSeek(ctx->tarFH);
-
 		/*
 		 * Forcibly unmark the header as read since we use the lookahead
 		 * buffer
-- 
2.27.0

From 881d5db123af6fca008eb479ac89624be1b75c50 Mon Sep 17 00:00:00 2001
From: David Gilman <davidgilman1@gmail.com>
Date: Mon, 25 May 2020 17:34:52 -0400
Subject: [PATCH 4/5] Skip tables in pg_restore by seeking instead of reading

---
 src/bin/pg_dump/pg_backup_custom.c | 28 ++++++++++++++++++----------
 1 file changed, 18 insertions(+), 10 deletions(-)

diff --git a/src/bin/pg_dump/pg_backup_custom.c b/src/bin/pg_dump/pg_backup_custom.c
index a873ac3afa..16414113af 100644
--- a/src/bin/pg_dump/pg_backup_custom.c
+++ b/src/bin/pg_dump/pg_backup_custom.c
@@ -575,19 +575,27 @@ _skipData(ArchiveHandle *AH)
 	blkLen = ReadInt(AH);
 	while (blkLen != 0)
 	{
-		if (blkLen > buflen)
+		if (ctx->hasSeek)
 		{
-			if (buf)
-				free(buf);
-			buf = (char *) pg_malloc(blkLen);
-			buflen = blkLen;
+			if (fseeko(AH->FH, blkLen, SEEK_CUR) != 0)
+				fatal("error during file seek: %m");
 		}
-		if ((cnt = fread(buf, 1, blkLen, AH->FH)) != blkLen)
+		else
 		{
-			if (feof(AH->FH))
-				fatal("could not read from input file: end of file");
-			else
-				fatal("could not read from input file: %m");
+			if (blkLen > buflen)
+			{
+				if (buf)
+					free(buf);
+				buf = (char *) pg_malloc(blkLen);
+				buflen = blkLen;
+			}
+			if ((cnt = fread(buf, 1, blkLen, AH->FH)) != blkLen)
+			{
+				if (feof(AH->FH))
+					fatal("could not read from input file: end of file");
+				else
+					fatal("could not read from input file: %m");
+			}
 		}
 
 		ctx->filePos += blkLen;
-- 
2.27.0

diff --git a/src/bin/pg_dump/pg_backup_custom.c b/src/bin/pg_dump/pg_backup_custom.c
index 6ab122242c..2659676fd9 100644
--- a/src/bin/pg_dump/pg_backup_custom.c
+++ b/src/bin/pg_dump/pg_backup_custom.c
@@ -421,13 +421,47 @@ _PrintTocData(ArchiveHandle *AH, TocEntry *te)
 	{
 		/*
 		 * We cannot seek directly to the desired block.  Instead, skip over
-		 * block headers until we find the one we want.  This could fail if we
-		 * are asked to restore items out-of-order.
+		 * block headers until we find the one we want.  Remember the
+		 * positions of skipped-over blocks, so that if we later decide we
+		 * need to read one, we'll be able to seek to it.
 		 */
-		_readBlockHeader(AH, &blkType, &id);
-
-		while (blkType != EOF && id != te->dumpId)
+		for (;;)
 		{
+			pgoff_t		thisBlkPos = _getFilePos(AH, ctx);
+			TocEntry   *otherte;
+
+			_readBlockHeader(AH, &blkType, &id);
+
+			if (blkType == EOF || id == te->dumpId)
+				break;
+
+			otherte = getTocEntryByDumpId(AH, id);
+			if (otherte && otherte->formatData)
+			{
+				lclTocEntry *othertctx = (lclTocEntry *) otherte->formatData;
+
+				/*
+				 * Note: on Windows, multiple threads might access/update the
+				 * same lclTocEntry concurrently, but that should be safe as
+				 * long as we update dataPos before dataState.  Ideally, we'd
+				 * use pg_write_barrier() to enforce that, but the needed
+				 * infrastructure doesn't exist in frontend code.  But Windows
+				 * only runs on machines with strong store ordering, so it
+				 * should be okay for now.
+				 */
+				if (othertctx->dataState == K_OFFSET_POS_NOT_SET)
+				{
+					othertctx->dataPos = thisBlkPos;
+					othertctx->dataState = K_OFFSET_POS_SET;
+				}
+				else if (othertctx->dataPos != thisBlkPos ||
+						 othertctx->dataState != K_OFFSET_POS_SET)
+				{
+					/* sanity check */
+					pg_log_warning("data block %d has wrong seek position", id);
+				}
+			}
+
 			switch (blkType)
 			{
 				case BLK_DATA:
@@ -443,7 +477,6 @@ _PrintTocData(ArchiveHandle *AH, TocEntry *te)
 						  blkType);
 					break;
 			}
-			_readBlockHeader(AH, &blkType, &id);
 		}
 	}
 	else
@@ -455,20 +488,18 @@ _PrintTocData(ArchiveHandle *AH, TocEntry *te)
 		_readBlockHeader(AH, &blkType, &id);
 	}
 
-	/* Produce suitable failure message if we fell off end of file */
+	/*
+	 * If we reached EOF without finding the block we want, then either it
+	 * doesn't exist, or it does but we lack the ability to seek back to it.
+	 */
 	if (blkType == EOF)
 	{
-		if (tctx->dataState == K_OFFSET_POS_NOT_SET)
-			fatal("could not find block ID %d in archive -- "
-				  "possibly due to out-of-order restore request, "
-				  "which cannot be handled due to lack of data offsets in archive",
-				  te->dumpId);
-		else if (!ctx->hasSeek)
+		if (!ctx->hasSeek)
 			fatal("could not find block ID %d in archive -- "
 				  "possibly due to out-of-order restore request, "
 				  "which cannot be handled due to non-seekable input file",
 				  te->dumpId);
-		else					/* huh, the dataPos led us to EOF? */
+		else
 			fatal("could not find block ID %d in archive -- "
 				  "possibly corrupt archive",
 				  te->dumpId);
@@ -560,19 +591,27 @@ _skipData(ArchiveHandle *AH)
 	blkLen = ReadInt(AH);
 	while (blkLen != 0)
 	{
-		if (blkLen > buflen)
+		if (ctx->hasSeek)
 		{
-			if (buf)
-				free(buf);
-			buf = (char *) pg_malloc(blkLen);
-			buflen = blkLen;
+			if (fseeko(AH->FH, blkLen, SEEK_CUR) != 0)
+				fatal("error during file seek: %m");
 		}
-		if ((cnt = fread(buf, 1, blkLen, AH->FH)) != blkLen)
+		else
 		{
-			if (feof(AH->FH))
-				fatal("could not read from input file: end of file");
-			else
-				fatal("could not read from input file: %m");
+			if (blkLen > buflen)
+			{
+				if (buf)
+					free(buf);
+				buf = (char *) pg_malloc(blkLen);
+				buflen = blkLen;
+			}
+			if ((cnt = fread(buf, 1, blkLen, AH->FH)) != blkLen)
+			{
+				if (feof(AH->FH))
+					fatal("could not read from input file: end of file");
+				else
+					fatal("could not read from input file: %m");
+			}
 		}
 
 		ctx->filePos += blkLen;
@@ -830,10 +869,13 @@ _Clone(ArchiveHandle *AH)
 		fatal("compressor active");
 
 	/*
+	 * We intentionally do not clone TOC-entry-local state: it's useful to
+	 * share knowledge about where the data blocks are across threads.
+	 * _PrintTocData has to be careful about the order of operations on that
+	 * state, though.
+	 *
 	 * Note: we do not make a local lo_buf because we expect at most one BLOBS
-	 * entry per archive, so no parallelism is possible.  Likewise,
-	 * TOC-entry-local state isn't an issue because any one TOC entry is
-	 * touched by just one worker child.
+	 * entry per archive, so no parallelism is possible.
 	 */
 }
 

>From 6e8294021482fb76cb17d6c150ec6acb6d0cac37 Mon Sep 17 00:00:00 2001
From: David Gilman <davidgilman1@gmail.com>
Date: Sat, 23 May 2020 10:49:33 -0400
Subject: [PATCH 2/5] Add integration test for out-of-order TOC requests in
 pg_restore

---
 src/bin/pg_dump/t/002_pg_dump.pl | 107 ++++++++++++++++++++++++++++++-
 src/test/perl/TestLib.pm         |  12 +++-
 2 files changed, 114 insertions(+), 5 deletions(-)

diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index e116235769..d99e7a5d22 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -31,9 +31,20 @@ my $tempdir_short = TestLib::tempdir_short;
 # generate a text file to run through the tests from the
 # non-text file generated by pg_dump.
 #
-# TODO: Have pg_restore actually restore to an independent
-# database and then pg_dump *that* database (or something along
-# those lines) to validate that part of the process.
+# secondary_dump_cmd is an optional key that enables a
+#
+# 	pg_dump (dump_cmd) ->
+# 	pg_restore (restore_cmd, real database) ->
+#	pg_dump (secondary_dump_cmd, SQL output (for matching))
+#
+# test process instead of the default
+#
+# 	pg_dump (dump_cmd) ->
+# 	pg_restore (restore_cmd, SQL output (for matching))
+#
+# test process. The secondary_dump database is created for
+# you and your restore_cmd and secondary_dump_cmd must read
+# and write from it.
 
 my %pgdump_runs = (
 	binary_upgrade => {
@@ -139,6 +150,25 @@ my %pgdump_runs = (
 			"$tempdir/defaults_custom_format.dump",
 		],
 	},
+	defaults_custom_format_no_seek_parallel_restore => {
+		test_key => 'defaults',
+		dump_cmd => [
+			['pg_dump', '-Fc', '-Z6', '--no-sync', 'postgres'],
+			'|',
+			['perl', '-pe', ''],  # make pg_dump's stdout unseekable
+			">$tempdir/defaults_custom_format_no_seek_parallel_restore"
+		],
+		restore_cmd => [
+			'pg_restore', '-Fc', '--jobs=2',
+			'--dbname=secondary_dump',
+			"$tempdir/defaults_custom_format_no_seek_parallel_restore",
+		],
+		secondary_dump_cmd => [
+			'pg_dump', '-Fp', '--no-sync',
+			"--file=$tempdir/defaults_custom_format_no_seek_parallel_restore.sql",
+			'secondary_dump',
+		],
+	},
 
 	# Do not use --no-sync to give test coverage for data sync.
 	defaults_dir_format => {
@@ -153,6 +183,24 @@ my %pgdump_runs = (
 			"$tempdir/defaults_dir_format",
 		],
 	},
+	defaults_dir_format_parallel => {
+		test_key => 'defaults',
+		dump_cmd => [
+			'pg_dump',                                      '-Fd',
+			'--jobs=2',                                     '--no-sync',
+			"--file=$tempdir/defaults_dir_format_parallel", 'postgres',
+		],
+		restore_cmd => [
+			'pg_restore', '-Fd',
+			'--jobs=2', '--dbname=secondary_dump',
+			"$tempdir/defaults_dir_format_parallel",
+		],
+		secondary_dump_cmd => [
+			'pg_dump', '-Fp', '--no-sync',
+			"--file=$tempdir/defaults_dir_format_parallel.sql",
+			'secondary_dump',
+		],
+	},
 
 	# Do not use --no-sync to give test coverage for data sync.
 	defaults_parallel => {
@@ -3298,6 +3346,35 @@ my %tests = (
 			%full_runs, %dump_test_schema_runs, section_pre_data => 1,
 		},
 		unlike => { exclude_dump_test_schema => 1 },
+	},
+
+	# This reliably produces the "possibly due to out-of-order restore request"
+	# when restoring with -j 2 when this was written but future changes to how
+	# pg_dump works could accidentally correctly order things as we're not really
+	# telling pg_dump how to do its on-disk layout.
+	'custom dump out-of-order' => {
+		create_sql   => '
+			CREATE TABLE dump_test.ooo_parent0 (id int primary key);
+			CREATE TABLE dump_test.ooo_child0 (
+				parent0 int references dump_test.ooo_parent0 (id)
+			);
+			CREATE TABLE dump_test.ooo_parent1 (id int primary key);
+			CREATE TABLE dump_test.ooo_child1 (
+				parent0 int references dump_test.ooo_parent1 (id)
+			);',
+		regexp => qr/^
+			 \QCREATE TABLE dump_test.ooo_child0 (\E\n
+			 \s+\Qparent0 integer\E\n
+			 \Q);\E\n/xm,
+		like => {
+			%full_runs, %dump_test_schema_runs,
+			section_pre_data => 1,
+			defaults_custom_format_no_seek => 1,
+			defaults_custom_format_no_seek_parallel_restore => 1,
+		},
+		unlike => {
+			exclude_dump_test_schema => 1,
+		},
 	});
 
 #########################################
@@ -3350,6 +3427,11 @@ foreach my $run (sort keys %pgdump_runs)
 		$num_tests++;
 	}
 
+	if ($pgdump_runs{$run}->{secondary_dump_cmd})
+	{
+		$num_tests++;
+	}
+
 	if ($pgdump_runs{$run}->{test_key})
 	{
 		$test_key = $pgdump_runs{$run}->{test_key};
@@ -3499,12 +3581,23 @@ foreach my $run (sort keys %pgdump_runs)
 	$node->command_ok(\@{ $pgdump_runs{$run}->{dump_cmd} },
 		"$run: pg_dump runs");
 
+	if ($pgdump_runs{$run}->{secondary_dump_cmd})
+	{
+		$node->safe_psql('postgres', 'create database secondary_dump;');
+	}
+
 	if ($pgdump_runs{$run}->{restore_cmd})
 	{
 		$node->command_ok(\@{ $pgdump_runs{$run}->{restore_cmd} },
 			"$run: pg_restore runs");
 	}
 
+	if ($pgdump_runs{$run}->{secondary_dump_cmd})
+	{
+		$node->command_ok(\@{ $pgdump_runs{$run}->{secondary_dump_cmd} },
+			"$run: secondary pg_dump runs");
+	}
+
 	if ($pgdump_runs{$run}->{test_key})
 	{
 		$test_key = $pgdump_runs{$run}->{test_key};
@@ -3561,6 +3654,14 @@ foreach my $run (sort keys %pgdump_runs)
 			}
 		}
 	}
+
+	if ($pgdump_runs{$run}->{secondary_dump_cmd})
+	{
+		$node->safe_psql('secondary_dump',
+			'alter subscription sub1 set (slot_name = NONE);');
+		$node->safe_psql('secondary_dump', 'drop subscription sub1;');
+		$node->safe_psql('postgres',       'drop database secondary_dump;');
+	}
 }
 
 #########################################
diff --git a/src/test/perl/TestLib.pm b/src/test/perl/TestLib.pm
index d579d5c177..fb90d1abcc 100644
--- a/src/test/perl/TestLib.pm
+++ b/src/test/perl/TestLib.pm
@@ -338,8 +338,16 @@ The return value from the command is passed through.
 
 sub run_log
 {
-	print("# Running: " . join(" ", @{ $_[0] }) . "\n");
-	return IPC::Run::run(@_);
+	my $flatten = sub {
+		return map { ref eq 'ARRAY' ? @$_ : $_ } @_;
+	};
+	my @x = @{$_[0]};
+	print("# Running: " . join(" ", $flatten->(@x)) . "\n");
+	if (ref($x[0]) ne '') {
+		return IPC::Run::run(@x);
+	} else {
+		return IPC::Run::run(@_);
+	}
 }
 
 =pod
-- 
2.27.0

diff --git a/src/bin/pg_dump/pg_backup_custom.c b/src/bin/pg_dump/pg_backup_custom.c
index 6ab122242c..3a9881d601 100644
--- a/src/bin/pg_dump/pg_backup_custom.c
+++ b/src/bin/pg_dump/pg_backup_custom.c
@@ -70,14 +70,12 @@ typedef struct
 {
 	CompressorState *cs;
 	int			hasSeek;
-	pgoff_t		filePos;
-	pgoff_t		dataStart;
 } lclContext;
 
 typedef struct
 {
 	int			dataState;
-	pgoff_t		dataPos;
+	pgoff_t		dataPos;		/* valid only if dataState=K_OFFSET_POS_SET */
 } lclTocEntry;
 
 
@@ -144,8 +142,6 @@ InitArchiveFmt_Custom(ArchiveHandle *AH)
 	AH->lo_buf_size = LOBBUFSIZE;
 	AH->lo_buf = (void *) pg_malloc(LOBBUFSIZE);
 
-	ctx->filePos = 0;
-
 	/*
 	 * Now open the file
 	 */
@@ -185,7 +181,6 @@ InitArchiveFmt_Custom(ArchiveHandle *AH)
 
 		ReadHead(AH);
 		ReadToc(AH);
-		ctx->dataStart = _getFilePos(AH, ctx);
 	}
 
 }
@@ -290,7 +285,8 @@ _StartData(ArchiveHandle *AH, TocEntry *te)
 	lclTocEntry *tctx = (lclTocEntry *) te->formatData;
 
 	tctx->dataPos = _getFilePos(AH, ctx);
-	tctx->dataState = K_OFFSET_POS_SET;
+	if (tctx->dataPos >= 0)
+		tctx->dataState = K_OFFSET_POS_SET;
 
 	_WriteByte(AH, BLK_DATA);	/* Block type */
 	WriteInt(AH, te->dumpId);	/* For sanity check */
@@ -350,7 +346,8 @@ _StartBlobs(ArchiveHandle *AH, TocEntry *te)
 	lclTocEntry *tctx = (lclTocEntry *) te->formatData;
 
 	tctx->dataPos = _getFilePos(AH, ctx);
-	tctx->dataState = K_OFFSET_POS_SET;
+	if (tctx->dataPos >= 0)
+		tctx->dataState = K_OFFSET_POS_SET;
 
 	_WriteByte(AH, BLK_BLOBS);	/* Block type */
 	WriteInt(AH, te->dumpId);	/* For sanity check */
@@ -551,7 +548,6 @@ _skipBlobs(ArchiveHandle *AH)
 static void
 _skipData(ArchiveHandle *AH)
 {
-	lclContext *ctx = (lclContext *) AH->formatData;
 	size_t		blkLen;
 	char	   *buf = NULL;
 	int			buflen = 0;
@@ -575,8 +571,6 @@ _skipData(ArchiveHandle *AH)
 				fatal("could not read from input file: %m");
 		}
 
-		ctx->filePos += blkLen;
-
 		blkLen = ReadInt(AH);
 	}
 
@@ -594,12 +588,10 @@ _skipData(ArchiveHandle *AH)
 static int
 _WriteByte(ArchiveHandle *AH, const int i)
 {
-	lclContext *ctx = (lclContext *) AH->formatData;
 	int			res;
 
 	if ((res = fputc(i, AH->FH)) == EOF)
 		WRITE_ERROR_EXIT;
-	ctx->filePos += 1;
 
 	return 1;
 }
@@ -615,13 +607,11 @@ _WriteByte(ArchiveHandle *AH, const int i)
 static int
 _ReadByte(ArchiveHandle *AH)
 {
-	lclContext *ctx = (lclContext *) AH->formatData;
 	int			res;
 
 	res = getc(AH->FH);
 	if (res == EOF)
 		READ_ERROR_EXIT(AH->FH);
-	ctx->filePos += 1;
 	return res;
 }
 
@@ -635,11 +625,8 @@ _ReadByte(ArchiveHandle *AH)
 static void
 _WriteBuf(ArchiveHandle *AH, const void *buf, size_t len)
 {
-	lclContext *ctx = (lclContext *) AH->formatData;
-
 	if (fwrite(buf, 1, len, AH->FH) != len)
 		WRITE_ERROR_EXIT;
-	ctx->filePos += len;
 }
 
 /*
@@ -652,11 +639,8 @@ _WriteBuf(ArchiveHandle *AH, const void *buf, size_t len)
 static void
 _ReadBuf(ArchiveHandle *AH, void *buf, size_t len)
 {
-	lclContext *ctx = (lclContext *) AH->formatData;
-
 	if (fread(buf, 1, len, AH->FH) != len)
 		READ_ERROR_EXIT(AH->FH);
-	ctx->filePos += len;
 }
 
 /*
@@ -688,7 +672,6 @@ _CloseArchive(ArchiveHandle *AH)
 		if (tpos < 0 && ctx->hasSeek)
 			fatal("could not determine seek position in archive file: %m");
 		WriteToc(AH);
-		ctx->dataStart = _getFilePos(AH, ctx);
 		WriteDataChunks(AH, NULL);
 
 		/*
@@ -862,30 +845,24 @@ _WorkerJobRestoreCustom(ArchiveHandle *AH, TocEntry *te)
 
 /*
  * Get the current position in the archive file.
+ *
+ * With a non-seekable archive file, we may not be able to obtain the
+ * file position.  If so, just return -1.  It's not too important in
+ * that case because we won't be able to rewrite the TOC to fill in
+ * data block offsets anyway.
  */
 static pgoff_t
 _getFilePos(ArchiveHandle *AH, lclContext *ctx)
 {
 	pgoff_t		pos;
 
-	if (ctx->hasSeek)
+	pos = ftello(AH->FH);
+	if (pos < 0)
 	{
-		/*
-		 * Prior to 1.7 (pg7.3) we relied on the internally maintained
-		 * pointer.  Now we rely on ftello() always, unless the file has been
-		 * found to not support it.  For debugging purposes, print a warning
-		 * if the internal pointer disagrees, so that we're more likely to
-		 * notice if something's broken about the internal position tracking.
-		 */
-		pos = ftello(AH->FH);
-		if (pos < 0)
+		/* Not expected if we found we can seek. */
+		if (ctx->hasSeek)
 			fatal("could not determine seek position in archive file: %m");
-
-		if (pos != ctx->filePos)
-			pg_log_warning("ftell mismatch with expected position -- ftell used");
 	}
-	else
-		pos = ctx->filePos;
 	return pos;
 }
 
@@ -897,7 +874,6 @@ _getFilePos(ArchiveHandle *AH, lclContext *ctx)
 static void
 _readBlockHeader(ArchiveHandle *AH, int *type, int *id)
 {
-	lclContext *ctx = (lclContext *) AH->formatData;
 	int			byt;
 
 	/*
@@ -918,7 +894,6 @@ _readBlockHeader(ArchiveHandle *AH, int *type, int *id)
 			*id = 0;			/* don't return an uninitialized value */
 			return;
 		}
-		ctx->filePos += 1;
 	}
 
 	*id = ReadInt(AH);

diff --git a/src/bin/pg_dump/pg_backup_custom.c b/src/bin/pg_dump/pg_backup_custom.c
index 3a9881d601..971e6adf48 100644
--- a/src/bin/pg_dump/pg_backup_custom.c
+++ b/src/bin/pg_dump/pg_backup_custom.c
@@ -70,6 +70,8 @@ typedef struct
 {
 	CompressorState *cs;
 	int			hasSeek;
+	/* lastFilePos is used only when reading, and may be invalid if !hasSeek */
+	pgoff_t		lastFilePos;	/* position after last data block we've read */
 } lclContext;
 
 typedef struct
@@ -181,8 +183,13 @@ InitArchiveFmt_Custom(ArchiveHandle *AH)
 
 		ReadHead(AH);
 		ReadToc(AH);
-	}
 
+		/*
+		 * Remember location of first data block (i.e., the point after TOC)
+		 * in case we have to search for desired data blocks.
+		 */
+		ctx->lastFilePos = _getFilePos(AH, ctx);
+	}
 }
 
 /*
@@ -418,13 +425,62 @@ _PrintTocData(ArchiveHandle *AH, TocEntry *te)
 	{
 		/*
 		 * We cannot seek directly to the desired block.  Instead, skip over
-		 * block headers until we find the one we want.  This could fail if we
-		 * are asked to restore items out-of-order.
+		 * block headers until we find the one we want.  Remember the
+		 * positions of skipped-over blocks, so that if we later decide we
+		 * need to read one, we'll be able to seek to it.
+		 *
+		 * When our input file is seekable, we can do the search starting from
+		 * the point after the last data block we scanned in previous
+		 * iterations of this function.
 		 */
-		_readBlockHeader(AH, &blkType, &id);
+		if (ctx->hasSeek)
+		{
+			if (fseeko(AH->FH, ctx->lastFilePos, SEEK_SET) != 0)
+				fatal("error during file seek: %m");
+		}
 
-		while (blkType != EOF && id != te->dumpId)
+		for (;;)
 		{
+			pgoff_t		thisBlkPos = _getFilePos(AH, ctx);
+
+			_readBlockHeader(AH, &blkType, &id);
+
+			if (blkType == EOF || id == te->dumpId)
+				break;
+
+			/* Remember the block position, if we got one */
+			if (thisBlkPos >= 0)
+			{
+				TocEntry   *otherte = getTocEntryByDumpId(AH, id);
+
+				if (otherte && otherte->formatData)
+				{
+					lclTocEntry *othertctx = (lclTocEntry *) otherte->formatData;
+
+					/*
+					 * Note: on Windows, multiple threads might access/update
+					 * the same lclTocEntry concurrently, but that should be
+					 * safe as long as we update dataPos before dataState.
+					 * Ideally, we'd use pg_write_barrier() to enforce that,
+					 * but the needed infrastructure doesn't exist in frontend
+					 * code.  But Windows only runs on machines with strong
+					 * store ordering, so it should be okay for now.
+					 */
+					if (othertctx->dataState == K_OFFSET_POS_NOT_SET)
+					{
+						othertctx->dataPos = thisBlkPos;
+						othertctx->dataState = K_OFFSET_POS_SET;
+					}
+					else if (othertctx->dataPos != thisBlkPos ||
+							 othertctx->dataState != K_OFFSET_POS_SET)
+					{
+						/* sanity check */
+						pg_log_warning("data block %d has wrong seek position",
+									   id);
+					}
+				}
+			}
+
 			switch (blkType)
 			{
 				case BLK_DATA:
@@ -440,7 +496,6 @@ _PrintTocData(ArchiveHandle *AH, TocEntry *te)
 						  blkType);
 					break;
 			}
-			_readBlockHeader(AH, &blkType, &id);
 		}
 	}
 	else
@@ -452,20 +507,18 @@ _PrintTocData(ArchiveHandle *AH, TocEntry *te)
 		_readBlockHeader(AH, &blkType, &id);
 	}
 
-	/* Produce suitable failure message if we fell off end of file */
+	/*
+	 * If we reached EOF without finding the block we want, then either it
+	 * doesn't exist, or it does but we lack the ability to seek back to it.
+	 */
 	if (blkType == EOF)
 	{
-		if (tctx->dataState == K_OFFSET_POS_NOT_SET)
-			fatal("could not find block ID %d in archive -- "
-				  "possibly due to out-of-order restore request, "
-				  "which cannot be handled due to lack of data offsets in archive",
-				  te->dumpId);
-		else if (!ctx->hasSeek)
+		if (!ctx->hasSeek)
 			fatal("could not find block ID %d in archive -- "
 				  "possibly due to out-of-order restore request, "
 				  "which cannot be handled due to non-seekable input file",
 				  te->dumpId);
-		else					/* huh, the dataPos led us to EOF? */
+		else
 			fatal("could not find block ID %d in archive -- "
 				  "possibly corrupt archive",
 				  te->dumpId);
@@ -491,6 +544,20 @@ _PrintTocData(ArchiveHandle *AH, TocEntry *te)
 				  blkType);
 			break;
 	}
+
+	/*
+	 * If our input file is seekable but lacks data offsets, update our
+	 * knowledge of where to start future searches from.  (Note that we did
+	 * not update the current TE's dataState/dataPos.  We could have, but
+	 * there is no point since it will not be visited again.)
+	 */
+	if (ctx->hasSeek && tctx->dataState == K_OFFSET_POS_NOT_SET)
+	{
+		pgoff_t		curPos = _getFilePos(AH, ctx);
+
+		if (curPos > ctx->lastFilePos)
+			ctx->lastFilePos = curPos;
+	}
 }
 
 /*
@@ -548,6 +615,7 @@ _skipBlobs(ArchiveHandle *AH)
 static void
 _skipData(ArchiveHandle *AH)
 {
+	lclContext *ctx = (lclContext *) AH->formatData;
 	size_t		blkLen;
 	char	   *buf = NULL;
 	int			buflen = 0;
@@ -556,19 +624,27 @@ _skipData(ArchiveHandle *AH)
 	blkLen = ReadInt(AH);
 	while (blkLen != 0)
 	{
-		if (blkLen > buflen)
+		if (ctx->hasSeek)
 		{
-			if (buf)
-				free(buf);
-			buf = (char *) pg_malloc(blkLen);
-			buflen = blkLen;
+			if (fseeko(AH->FH, blkLen, SEEK_CUR) != 0)
+				fatal("error during file seek: %m");
 		}
-		if ((cnt = fread(buf, 1, blkLen, AH->FH)) != blkLen)
+		else
 		{
-			if (feof(AH->FH))
-				fatal("could not read from input file: end of file");
-			else
-				fatal("could not read from input file: %m");
+			if (blkLen > buflen)
+			{
+				if (buf)
+					free(buf);
+				buf = (char *) pg_malloc(blkLen);
+				buflen = blkLen;
+			}
+			if ((cnt = fread(buf, 1, blkLen, AH->FH)) != blkLen)
+			{
+				if (feof(AH->FH))
+					fatal("could not read from input file: end of file");
+				else
+					fatal("could not read from input file: %m");
+			}
 		}
 
 		blkLen = ReadInt(AH);
@@ -804,6 +880,9 @@ _Clone(ArchiveHandle *AH)
 {
 	lclContext *ctx = (lclContext *) AH->formatData;
 
+	/*
+	 * Each thread must have private lclContext working state.
+	 */
 	AH->formatData = (lclContext *) pg_malloc(sizeof(lclContext));
 	memcpy(AH->formatData, ctx, sizeof(lclContext));
 	ctx = (lclContext *) AH->formatData;
@@ -813,10 +892,13 @@ _Clone(ArchiveHandle *AH)
 		fatal("compressor active");
 
 	/*
+	 * We intentionally do not clone TOC-entry-local state: it's useful to
+	 * share knowledge about where the data blocks are across threads.
+	 * _PrintTocData has to be careful about the order of operations on that
+	 * state, though.
+	 *
 	 * Note: we do not make a local lo_buf because we expect at most one BLOBS
-	 * entry per archive, so no parallelism is possible.  Likewise,
-	 * TOC-entry-local state isn't an issue because any one TOC entry is
-	 * touched by just one worker child.
+	 * entry per archive, so no parallelism is possible.
 	 */
 }