Re: Horribly slow pg_upgrade performance with many Large Objects

This is what the opening comment in pg_upgrade says

I think we do preserve role oids

/*
* To simplify the upgrade process, we force certain system values to be
* identical between old and new clusters:
*
* We control all assignments of pg_class.oid (and relfilenode) so toast
* oids are the same between old and new clusters. This is important
* because toast oids are stored as toast pointers in user tables.
*
* While pg_class.oid and pg_class.relfilenode are initially the same in a
* cluster, they can diverge due to CLUSTER, REINDEX, or VACUUM FULL. We
* control assignments of pg_class.relfilenode because we want the filenames
* to match between the old and new cluster.
*
* We control assignment of pg_tablespace.oid because we want the oid to match
* between the old and new cluster.
*
* We control all assignments of pg_type.oid because these oids are stored
* in user composite type values.
*
* We control all assignments of pg_enum.oid because these oids are stored
* in user tables as enum values.
*
* We control all assignments of pg_authid.oid for historical reasons (the
* oids used to be stored in pg_largeobject_metadata, which is now copied via
* SQL commands), that might change at some point in the future.
*/

On Tue, Apr 8, 2025 at 6:13 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Nathan Bossart <nathandbossart(at)gmail(dot)com> writes:
> > On Tue, Apr 08, 2025 at 09:35:24AM +0200, Hannu Krosing wrote:
> >> Changing the LO export to dumping pg_largeobject_metadata content
> >> instead of creating the LOs should be a nice small change confined to
> >> pg_dump --binary-upgrade only so perhaps we could squeeze it in v18
> >> still.
>
> > Feature freeze for v18 was ~4 hours ago, so unfortunately this is v19
> > material at this point.
>
> Yeah, even if we had a patch in hand, it's too late for v18. However
> there are additional problems with this idea:
>
> 1. The idea requires role OIDs to match across the upgrade.
> I don't believe that pg_upgrade tries to preserve role OIDs --- and
> doing so would be problematic, because what if the new cluster's
> bootstrap superuser is named differently in the old and new clusters?
>
> It might be possible to work around that with some casting to/from
> regrole, but I don't think a simple COPY into pg_largeobject_metadata
> will play along with that.
>
> 2. If you just do the equivalent of an INSERT or COPY into
> pg_largeobject_metadata, you could create entries that look right,
> but they are actually not right because there should be pg_shdepend
> entries backing each ownership or permission reference (for non-pinned
> roles) and there won't be.
>
> I guess you could think of also manually inserting rows into
> pg_shdepend, but (a) ugh and (b) the claimed speedup is kind
> of vanishing into the distance at this point.
>
> regards, tom lane