Thread: BUG #11090: Unclear error message in pg_upgrade
The following bug has been logged on the website: Bug reference: 11090 Logged by: Bror Jonsson Email address: brorfred@gmail.com PostgreSQL version: 9.2.0 Operating system: Mac OS X Description: pg_upgrade requires that the old and new database has the same install superuser (superuser that ran initdb). If this requirement isn't fulfilled, you'll get the following error: "Old and new cluster install users have different values for pg_authid.oid.\n" (https://github.com/postgres/postgres/blob/master/contrib/pg_upgrade/check.c, line 168) This was very confusing to me. A suggested rephrase would be: "Old and new cluster must be created by the same user. Try to re-init the new database with the flag "-U %s" \n" Where %s is the the install user in the old cluster.
brorfred@gmail.com wrote: > pg_upgrade requires that the old and new database has the same install > superuser (superuser that ran initdb). If this requirement isn't fulfilled, > you'll get the following error: > > "Old and new cluster install users have different values for > pg_authid.oid.\n" To me, the bug is that we required both superusers to be named the same in the first place. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On 7/29/2014 11:58 AM, Alvaro Herrera wrote: > brorfred@gmail.com wrote: > >> >pg_upgrade requires that the old and new database has the same install >> >superuser (superuser that ran initdb). If this requirement isn't fulfilled, >> >you'll get the following error: >> > >> >"Old and new cluster install users have different values for >> >pg_authid.oid.\n" > To me, the bug is that we required both superusers to be named the same > in the first place. so, if its enhanced with a -U newsuperuser flag, any objects owned by 'postgres' on the original cluster are now owned by 'newsuperuser' on the new cluster? -- john r pierce 37N 122W somewhere on the middle of the left coast
Alvaro Herrera-9 wrote > brorfred@ > wrote: > >> pg_upgrade requires that the old and new database has the same install >> superuser (superuser that ran initdb). If this requirement isn't >> fulfilled, >> you'll get the following error: >> >> "Old and new cluster install users have different values for >> pg_authid.oid.\n" > > To me, the bug is that we required both superusers to be named the same > in the first place. It seems a reasonable limitation for something the simply purports to "upgrade". The data should not be changed in the process. My question: is there a way to change the name of the assigned install user as long as you can manage to ensure they have the same oid? The error implies there is. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-11090-Unclear-error-message-in-pg-upgrade-tp5813198p5813220.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
John R Pierce wrote > On 7/29/2014 11:58 AM, Alvaro Herrera wrote: >> > brorfred@ > wrote: >> >>> >pg_upgrade requires that the old and new database has the same install >>> >superuser (superuser that ran initdb). If this requirement isn't >>> fulfilled, >>> >you'll get the following error: >>> > >>> >"Old and new cluster install users have different values for >>> >pg_authid.oid.\n" >> To me, the bug is that we required both superusers to be named the same >> in the first place. > > so, if its enhanced with a -U newsuperuser flag, any objects owned by > 'postgres' on the original cluster are now owned by 'newsuperuser' on > the new cluster? Please no. I'm not a pg_upgrade user but this just seems like asking for trouble. There has been one complaint and while I can see why the user tried this it is not a very convincing use case. Rewording the error is sufficient. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-11090-Unclear-error-message-in-pg-upgrade-tp5813198p5813221.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
On Tue, Jul 29, 2014 at 1:44 PM, David G Johnston < david.g.johnston@gmail.com> wrote: > John R Pierce wrote > > On 7/29/2014 11:58 AM, Alvaro Herrera wrote: > >> > > > brorfred@ > > > wrote: > >> > >>> >pg_upgrade requires that the old and new database has the same install > >>> >superuser (superuser that ran initdb). If this requirement isn't > >>> fulfilled, > >>> >you'll get the following error: > >>> > > >>> >"Old and new cluster install users have different values for > >>> >pg_authid.oid.\n" > >> To me, the bug is that we required both superusers to be named the same > >> in the first place. > > > > so, if its enhanced with a -U newsuperuser flag, any objects owned by > > 'postgres' on the original cluster are now owned by 'newsuperuser' on > > the new cluster? > > Please no. I'm not a pg_upgrade user but this just seems like asking for > trouble. There has been one complaint and while I can see why the user > tried this it is not a very convincing use case. Rewording the error is > sufficient. > I have one database with a retrospectively poor choice of initdb -U. I certainly wish there was a way, short of dump and restore, to change the name of that user. Although I don't think pg_upgrade would be the ideal way to do that. Cheers, Jeff
Jeff Janes wrote: > I have one database with a retrospectively poor choice of initdb -U. I > certainly wish there was a way, short of dump and restore, to change the > name of that user. Although I don't think pg_upgrade would be the ideal > way to do that. Have you tried an UPDATE pg_authid? -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> Jeff Janes wrote:
>> I have one database with a retrospectively poor choice of initdb -U. I
>> certainly wish there was a way, short of dump and restore, to change the
>> name of that user. Although I don't think pg_upgrade would be the ideal
>> way to do that.
> Have you tried an UPDATE pg_authid?
Or even just ALTER USER RENAME?
regards, tom lane
David G Johnston <david.g.johnston@gmail.com> writes:
> Alvaro Herrera-9 wrote
>> To me, the bug is that we required both superusers to be named the same
>> in the first place.
> It seems a reasonable limitation for something the simply purports to
> "upgrade". The data should not be changed in the process.
Yeah, I wouldn't really expect pg_upgrade to deal with such a case.
For one thing, what if the new cluster's superuser name conflicts with
some non-superuser in the old cluster?
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.
regards, tom lane
On Tue, Jul 29, 2014 at 2:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Alvaro Herrera <alvherre@2ndquadrant.com> writes: > > Jeff Janes wrote: > >> I have one database with a retrospectively poor choice of initdb -U. I > >> certainly wish there was a way, short of dump and restore, to change the > >> name of that user. Although I don't think pg_upgrade would be the ideal > >> way to do that. > > > Have you tried an UPDATE pg_authid? > No. Updating the system catalog directly sounds like one of those 'you get to keep both pieces' situations, and it wasn't that important to me. > > Or even just ALTER USER RENAME? > I thought I tried that. On a test system, I see that it works once I create yet another superuser to be logged in as as I do the renaming. I think the problem I was having before I gave up is that the desired new role already existed and owned some inconsequential objects. I tried to REASSIGN OWNED the rest of the objects to it, which wouldn't work. What I should have done is REASSIGNED OWNED the few stray objects from the desired role back to the -U role (or to a completely different role), drop the desired one, and then rename the -U user to the desired one. So yeah, I think there is a path to do this, it is just quite convoluted. Cheers, Jeff
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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
Bruce Momjian <bruce@momjian.us> 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.
regards, tom lane
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.
On Tue, Jul 29, 2014 at 10:33:17PM -0400, Tom Lane wrote: > 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. OK, I had never made this logical conclusion that it had to be the install user. Since you can't remove the install user, and the new cluster can only have one user (the install user), and because we preserve pg_authid.oid, the old cluster has to be the install user too. I have developed the 9.5-only patch which simplifies these checks and error messages; attached. > > 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. Agreed. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
Attachment
On Wed, Jul 30, 2014 at 10:58:03AM -0400, Bruce Momjian wrote: > OK, I had never made this logical conclusion that it had to be the > install user. Since you can't remove the install user, and the new > cluster can only have one user (the install user), and because we > preserve pg_authid.oid, the old cluster has to be the install user too. > > I have developed the 9.5-only patch which simplifies these checks and > error messages; attached. Patch applied, with doc addition. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +