Re: BUG #11090: Unclear error message in pg_upgrade

Tom Lane-2 wrote
> Bruce Momjian <

> bruce@

> > writes:
>> 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.
>
> I think you missed my point.  I'm suggesting actually restricting what
> pg_upgrade will accept, so that the error conditions become simpler to
> understand.
>
> Furthermore, if your description of the restrictions is accurate, then
> restricting to "the user must be the bootstrap superuser in both clusters"
> is actually not an extra restriction.  You said:
>
>> o  only one user can be defined in the new cluster (most likely the
>> install user)
>
> If there is only one user in the new cluster, it *is* the bootstrap
> superuser, because that user cannot be deleted.
>
>> o  the oids of the two users must be the same (likely the install user
>> on the old cluster as well)
>
> If the OIDs are the same, then the old-cluster user is also the bootstrap
> superuser, because the bootstrap superuser's hard-wired OID is 10.  QED.
>
> So I think you should get rid of the existing error check and simplify it
> to "user must be bootstrap superuser (ie, OID 10) in both clusters".
> The existing approach adds only confusion, not flexibility.
>
>> 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 don't think you actually can, and if you could, I frankly would not
> trust the results.

For the archives - the "original" complaint with detail is here:

http://www.postgresql.org/message-id/2B69ABB0-014A-4296-8D34-D2E83A84FA77@gmail.com

Basically:
1) Original database created with default "postgres" user
2) DBA decides it would be better if "bob" was the superuser
3) Create "bob" in original database (as opposed to doing a rename)
4) <assume> REASSIGN OWNED FROM postgres TO bob;
5) pg_upgrade time - decide "bob" should be doing this (totally forgot about
postgres by now)
6) DBA sees error

Note the missing piece was, for me, the fact that you cannot delete the
"postgres" user in the original database.  If you could then there would be
no possible way to get oid 10 in the new cluster to match the now deleted
oid 10 in the original cluster.  Once that is "frozen" then as Tom said all
of the other rules amount to the same thing - "postgres" must be the name of
the user in the new database.

NOTE: had the DBA done an "ALTER USER postgres RENAME TO bob" instead of
simply adding "bob" then everything supposedly would have worked correctly.


> "Old and new cluster must be created by the same user. Try to re-init the
> new database with the flag "-U %s" \n"

The OP's proposed message seems good to me though s/database/cluster/?  Or
is there some unspoken assumption we can make at this point in the process.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/BUG-11090-Unclear-error-message-in-pg-upgrade-tp5813198p5813246.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.