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

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.

--
  Bruce Momjian  <bruce@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                             +