Re: Re: [GENERAL] pg_upgrade from 9.0.7 to 9.1.3: duplicate key pg_authid_oid_index

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> On Fri, Jun 01, 2012 at 09:52:59AM -0400, Tom Lane wrote:
>> It seems that pg_upgrade needs a check to make sure that the bootstrap
>> superuser is named the same in old and new clusters.

> The attached patch adds checks to verify the the old/new servers have
> the same install-user oid.

That may or may not be a useful check to make, but it's got
approximately nothing to do with what I was complaining about.

In particular, supposing that the user has given you a username that
isn't the bootstrap superuser in the new cluster, this patch is not
going to stop the update script from failing. Because the script is
then going to try to replace the bootstrap superuser, and that is
certainly going to give an error.

I see the point of worrying about the install user as well as the
bootstrap superuser, but wouldn't it be best to insist they be the same?
Particularly in the new cluster, where if they aren't the same it means
the user has manually created at least one role in the new cluster,
which is likely to lead to OID conflicts or worse.

Furthermore, if the bootstrap superusers aren't named the same, your
patch fails to handle the original complaint. In the case the
OP mentioned, the old cluster had
OID 10: "ubuntu"
some user-defined OID: "postgres"
and the new cluster had
OID 10: "postgres"
If the user tells pg_upgrade to use username postgres, your check will
not fail AFAICS, but nonetheless things are going to be messed up after
the upgrade, because some objects and privileges that used to belong to
the bootstrap superuser will now belong to a non-default superuser,
whereas what used to belong to the non-default superuser will now belong
to the bootstrap superuser. That cannot be thought desirable. For one
reason, in the old installation the postgres role could have been
dropped (possibly after dropping a few non-builtin objects) whereas the
"ubuntu" role was pinned. In the new installation, "postgres" is pinned
and "ubuntu" won't be.

I think the checks that are actually needed here are (1) bootstrap
superusers are named the same, and (2) there are no roles other than the
bootstrap superuser in the new cluster.

regards, tom lane