Re: BUG #11090: Unclear error message in pg_upgrade

On Tue, Jul 29, 2014 at 05:31:47PM -0400, Tom Lane wrote:
> Having said that, I think the actual implementation restriction is not
> that they have the same *name*, but that they have the same *OID*.
> And the OID of the bootstrap superuser is always gonna be 10. As long
> as the new cluster's bootstrap superuser name doesn't collide with
> any other usernames in the old cluster, it could in principle be
> different. The only difficulty is that pg_upgrade has but one -U
> switch to specify both names ... and it's not exactly clear that it's
> worth the complication to have two such switches.
>
> I agree that it'd be better if the error message said something like
> "you have to use the bootstrap superuser, which is 'foo' in this cluster".
> That would be overconstraining the user of pg_upgrade, but not by much,
> and it would be a lot easier to understand than the current situation.

Basically, here are the pg_upgrade restrictions:

o same user must exist in old and new clusters

o only one user can be defined in the new cluster (most likely the
install user)

o the oids of the two users must be the same (likely the install user
on the old cluster as well)

The error message generated:

"Old and new cluster install users have different values for
pg_authid.oid.\n"

basically is failing on the last check. Odds are the user specified by
-U existed in both the old and new clusters, and was the sole/install
user in the new cluster. What failed was that the old user was not the
install user, so didn't have the install oid (10) in the old cluster.

I am not sure we can improve that error message. I don't think we can
assume anything more than an oid mismatch. The oids might not match
because of the case above, or the new cluster might have created a new
user and then dropped the install user, and in that case the new user
oid would be the wrong one.

The basic logic is outlined in this C comment:

* We only allow the install user in the new cluster because other defined
* users might match users defined in the old cluster and generate an
* error during pg_dump restore.

What I think you actually can do is to run pg_upgrade with a user that
is not the install user in either cluster, as long as the oids match. I
am afraid any more specific message could technically be wrong in some
rare cases.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +