On Tue, Nov 24, 2015 at 8:04 PM, Bruce Momjian <br...@momjian.us> wrote:
> On Mon, Nov 23, 2015 at 11:12:25AM +0000, Benedikt Grundmann wrote: > > I got this error trying to upgrade one of our database clusters (happily > in > > testing) from 9.2 to 9.4: > > > > Old and new cluster install users have different values for pg_authid.oid > > > > Important background here is that we used to run the database as the > postgres > > unix user, but recently we had changed it to run as a different user > (because > > we have several different databases all running as the postgres user on > > different machines and we wanted each logically separate database to run > as a > > different extra for that purpose unix user -- this simplified internal > > administration management). > > > > We had done this by adding a new superuser to the database (with the > name of > > the unix user it will run as in the future). turning off the database, > chown -R > > <new-user> databasedir, starting the database > > Your description is very clear. In 9.4 and earlier, Postgres checks > that the user running upgrade has the same pg_authid.oid in the old and > new clusters. In 9.5 we check that the user is the > BOOTSTRAP_SUPERUSERID (10) on both the old and new cluster. > > Therefore, what I suggest you do, before running pg_upgrade, is to > rename the pg_authid.oid = 10 row to be your new install user instead of > 'postgres', and make your new user row equal 'postgres', e.g. something > like: > > -- You already did this first one > --> test=> create user my_new_install_user; > --> CREATE ROLE > > select oid from pg_authid where rolname = 'my_new_install_user'; > oid > ------- > 16385 > (1 row) > > select oid from pg_authid where rolname = 'postgres'; > oid > ----- > 10 > (1 row) > > -- 'XXX' prevents duplicate names > update pg_authid set rolname = 'XXX' where oid = 10; > UPDATE 1 > update pg_authid set rolname = 'postgres' where oid = 16385; > UPDATE 1 > update pg_authid set rolname = 'my_new_install_user' where oid = > 10; > UPDATE 1 > > What this does it to make your new install user the bootstrap user, > which is a requirement for 9.5 pg_upgrade. You would do this _before_ > running pg_upgrade as my_new_install_user. However, keep in mind that > once you do this, everthing owned by my_new_install_user and postgres > are now swapped. This is basically what you need to do after changing > the ownership of the Postgres file system files. > > You can see the 9.5 requirements in the pg_upgrade function > check_is_install_user(). You might as well just honor what that > requires as you will eventually be moving to 9.5. > Thanks I'll try this in one of the next days. Sorry for the radio silence in the last 2 days. We have been quite busy at work. I don't think I understand yet why this restriction exists (Neither the old nor the new). Is there some doc somewhere that explains what's going on? I tried to find something in the otherwise excellent postgres docs but failed. > > -- > Bruce Momjian <br...@momjian.us> http://momjian.us > EnterpriseDB http://enterprisedb.com > > + As you are, so once was I. As I am, so you will be. + > + Roman grave inscription + >
