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(at)wi3ck(dot)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 */