Re: pg_upgrade failing for 200+ million Large Objects

On Wed, Mar 24, 2021 at 12:05:27PM -0400, Jan Wieck wrote:
> On 3/24/21 12:04 PM, Jan Wieck wrote:
> > In any case I changed the options so that they behave the same way, the
> > existing -o and -O (for old/new postmaster options) work. I don't think
> > it would be wise to have option forwarding work differently between
> > options for postmaster and options for pg_dump/pg_restore.
> 
> Attaching the actual diff might help.

I think the original issue with XIDs was fixed by 74cf7d46a.

Are you still planning to progress the patches addressing huge memory use of
pg_restore?

Note this other, old thread on -general, which I believe has variations on the
same patches.
https://www.postgresql.org/message-id/flat/7bf19bf2-e6b7-01a7-1d96-f0607c728c49@wi3ck.info

There was discussion about using pg_restore --single.  Note that that was used
at some point in the past: see 12ee6ec71 and 861ad67bd.

The immediate problem is that --single conflicts with --create.
I cleaned up a patch I'd written to work around that.  It preserves DB settings
and passes pg_upgrade's test.  It's probably not portable as written, but if need be
could pass an empty file instead of /dev/null...

diff --git a/src/bin/pg_upgrade/pg_upgrade.c b/src/bin/pg_upgrade/pg_upgrade.c
index 3628bd74a7..9c504aff79 100644
--- a/src/bin/pg_upgrade/pg_upgrade.c
+++ b/src/bin/pg_upgrade/pg_upgrade.c
@@ -364,6 +364,16 @@ create_new_objects(void)
 		DbInfo	   *old_db = &old_cluster.dbarr.dbs[dbnum];
 		const char *create_opts;
 
+		PQExpBufferData connstr,
+				escaped_connstr;
+
+		initPQExpBuffer(&connstr);
+		initPQExpBuffer(&escaped_connstr);
+		appendPQExpBufferStr(&connstr, "dbname=");
+		appendConnStrVal(&connstr, old_db->db_name);
+		appendShellString(&escaped_connstr, connstr.data);
+		termPQExpBuffer(&connstr);
+
 		/* Skip template1 in this pass */
 		if (strcmp(old_db->db_name, "template1") == 0)
 			continue;
@@ -378,18 +388,31 @@ create_new_objects(void)
 		 * propagate its database-level properties.
 		 */
 		if (strcmp(old_db->db_name, "postgres") == 0)
-			create_opts = "--clean --create";
+			create_opts = "--clean";
 		else
-			create_opts = "--create";
+			create_opts = "";
 
+		/* Create the DB but exclude all objects */
 		parallel_exec_prog(log_file_name,
 						   NULL,
 						   "\"%s/pg_restore\" %s %s --exit-on-error --verbose "
+							"--create -L /dev/null "
 						   "--dbname template1 \"%s\"",
 						   new_cluster.bindir,
 						   cluster_conn_opts(&new_cluster),
 						   create_opts,
 						   sql_file_name);
+
+		parallel_exec_prog(log_file_name,
+						   NULL,
+						   "\"%s/pg_restore\" %s %s --exit-on-error --verbose --single "
+						   "--dbname=%s \"%s\"",
+						   new_cluster.bindir,
+						   cluster_conn_opts(&new_cluster),
+						   create_opts,
+							escaped_connstr.data,
+						   sql_file_name);
+
 	}
 
 	/* reap all children */