Thread: Problems with pg_upgrade.

Dear all,

I’m trying to upgrade from 9.0 to 9.2 with out any success. the pg_upgrade script fails with the message Old and new
clusterinstall users have different values for pg_authid.oid: 

geo-route-in:~ bror$ /opt/local//lib/postgresql92/bin/pg_upgrade -b /opt/local//lib/postgresql90/bin/ -B
/opt/local//lib/postgresql92/bin/-d /Volumes/ruoteRAID/db/postgresql90/defaultdb/ -D
/Volumes/ruoteRAID/db/postgresql92/defaultdb/-u bror 
Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories                 ok
Checking cluster versions                                   ok
Checking database user is a superuser                       ok
Checking for prepared transactions                          ok
Checking for reg* system OID user data types                ok
Checking for contrib/isn with bigint-passing mismatch       ok
Creating catalog dump                                       ok
Checking for presence of required libraries                 ok
Checking database user is a superuser                       ok

Old and new cluster install users have different values for pg_authid.oid.
Failure, exiting

The only information about this error I could find in in the source code:

> https://github.com/postgres/postgres/blob/master/contrib/pg_upgrade/check.c

…
    /*
     * We don't restore our own user, so both clusters must match have
     * matching install-user oids.
     */
    if (old_cluster.install_role_oid != new_cluster.install_role_oid)
        pg_fatal("Old and new cluster install users have different values for pg_authid.oid.\n");

    /*
     * 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.
     */
    if (new_cluster.role_count != 1)
        pg_fatal("Only the install user can be defined in the new cluster.\n");

    check_for_prepared_transactions(&new_cluster);
}
…

pg_authid in the old db looks as follows:

bror=# SELECT * FROM pg_authid;
 rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolconnlimit |
rolpassword| rolvaliduntil  

----------+----------+------------+---------------+-------------+--------------+-------------+--------------+-------------+---------------
 postgres | t        | t          | t             | t           | t            | t           |           -1 |
 |  
 bror     | t        | t          | t             | t           | t            | t           |           -1 |
 |  
 django   | f        | t          | f             | f           | f            | t           |           -1 |
 |  
(3 rows)

And in the new one:

SELECT * FROM pg_authid;
 rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication |
rolconnlimit| rolpassword | rolvaliduntil  

---------+----------+------------+---------------+-------------+--------------+-------------+----------------+--------------+-------------+---------------
 bror    | t        | t          | t             | t           | t            | t           | t              |
-1 |             |  
(1 row)

Any suggestion for how to fix this?


Many thanks!

/Bror Jonsson

Bror Jonsson <brorfred@gmail.com> writes:
> I�m trying to upgrade from 9.0 to 9.2 with out any success. the pg_upgrade script fails with the message Old and new
clusterinstall users have different values for pg_authid.oid: 

> geo-route-in:~ bror$ /opt/local//lib/postgresql92/bin/pg_upgrade -b /opt/local//lib/postgresql90/bin/ -B
/opt/local//lib/postgresql92/bin/-d /Volumes/ruoteRAID/db/postgresql90/defaultdb/ -D
/Volumes/ruoteRAID/db/postgresql92/defaultdb/-u bror 

At a guess, "bror" is not the original superuser in the old cluster
(ie, the one who ran initdb).  You need to use the name of that
superuser, not just any superuser.

            regards, tom lane

Thanks!

Is there any way to figure out which the original superuser was?

:-)B


On Jul 29, 2014, at 12:41, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Bror Jonsson <brorfred@gmail.com> writes:
>> I’m trying to upgrade from 9.0 to 9.2 with out any success. the pg_upgrade script fails with the message Old and new
clusterinstall users have different values for pg_authid.oid: 
>
>> geo-route-in:~ bror$ /opt/local//lib/postgresql92/bin/pg_upgrade -b /opt/local//lib/postgresql90/bin/ -B
/opt/local//lib/postgresql92/bin/-d /Volumes/ruoteRAID/db/postgresql90/defaultdb/ -D
/Volumes/ruoteRAID/db/postgresql92/defaultdb/-u bror 
>
> At a guess, "bror" is not the original superuser in the old cluster
> (ie, the one who ran initdb).  You need to use the name of that
> superuser, not just any superuser.
>
>             regards, tom lane

Bror Jonsson <brorfred@gmail.com> writes:

> I’m trying to upgrade from 9.0 to 9.2 with out any success. the pg_upgrade script fails with the message Old and new cluster install users have different values for pg_authid.oid:

> geo-route-in:~ bror$ /opt/local//lib/postgresql92/bin/pg_upgrade -b /opt/local//lib/postgresql90/bin/ -B /opt/local//lib/postgresql92/bin/ -d /Volumes/ruoteRAID/db/postgresql90/defaultdb/ -D  /Volumes/ruoteRAID/db/postgresql92/defaultdb/ -u bror

At a guess, "bror" is not the original superuser in the old cluster
(ie, the one who ran initdb).  You need to use the name of that
superuser, not just any superuser.

to clarify, the old cluster's install_user is postgres, while the new is bror.  they must be the same.  stop the new cluster, wipe the data dir out, and re-run initdb as the postgres user this time, THEN run the upgrade script with -u postgres

-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast

Bror Jonsson <brorfred@gmail.com> writes:
> Is there any way to figure out which the original superuser was?

You got a surplus of superusers?

Anyway, it should be the one with OID 10.

select * from pg_authid where oid = 10;

            regards, tom lane