Re: Problems with pg_upgrade after change of unix user running db.

On Tue, Nov 24, 2015 at 8:04 PM, Bruce Momjian <bruce(at)momjian(dot)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 <bruce(at)momjian(dot)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 +
>