Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4

I just did the whole process over from the beginning. here's the full
output:

-bash-4.1$ date ; time /usr/pgsql-9.2/bin/pg_upgrade -b /usr/pgsql-9.1/bin/
-B /usr/pgsql-9.2/bin/ -d /var/lib/pgsql/9.1/data/ -D
/var/lib/pgsql/9.2/data/ -p 50432 -P 50433 ; date
Thu May 9 14:31:07 EDT 2013
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
Checking for prepared transactions ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster ok
Freezing all rows on the new cluster ok
Deleting files from new pg_clog ok
Copying old pg_clog to new server ok
Setting next transaction ID for new cluster ok
Resetting WAL archives ok
Setting frozenxid counters in new cluster ok
Creating databases in the new cluster ok
Adding support functions to new cluster ok
Restoring database schema to new cluster ok
Removing support functions from new cluster ok
Copying user relation files
/var/lib/pgsql/9.1/data/base/16406/3016054
Mismatch of relation OID in database "db": old OID 2938685, new OID 299749
Failure, exiting

real 16m17.924s
user 1m34.334s
sys 1m27.519s
Thu May 9 14:47:25 EDT 2013

Here's the query of that OID:

db=# SELECT relname, relfilenode, relkind from pg_class where oid = 299749;
relname | relfilenode | relkind
----------------+-------------+---------
pg_toast_17304 | 299749 | t
(1 row)

db=#

On Thu, May 9, 2013 at 10:45 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Thu, May 9, 2013 at 10:20:12AM -0400, Evan D. Hoffman wrote:
> > >From the 9.1 cluster (port 5432):
> >
> >
> > db=# SELECT relname, relfilenode, relkind from pg_class where oid =
> 2938685;
> > relname | relfilenode | relkind
> > -----------------------+-------------+---------
> > substitutionlist_pkey | 21446253 | i
> > (1 row)
> >
> > db=#
>
> OK, so it is an index, interesting.
>
> > >From the 9.2 cluster (port 5433):
> >
> > db=# SELECT relname from pg_class where oid = 299721;
> > relname
> > ---------
> > (0 rows)
>
> Is it possible that you mis-copied the "new" OID from the error message?
> It was at the end of the line. If so, could you get the right number?
> The fact that old and new start with "29" but there are a different
> number of digits in each number suggests it might be the wrong number.
>
> > >> Linking user relation files
> > >> /var/lib/pgsql/9.1/data/base/16406/3016054
> > >> Mismatch of relation OID in database "dbname": old OID 2938685, new
> OID 299721
> > >> Failure, exiting
>
> If that is the right number, I am confused because pg_upgrade thinks
> something has that oid in pg_class in your new cluster. It might help
> for you to look for that number in the pg_upgrade logs, and you might
> need to run a query from those logs to see where that number is coming
> from.
>
> > Assuming the relfilenode would be the filename on disk, it exists in
> > the 9.1 DB but not in the 9.2:
> >
> > [root(at)dev-db2 16407]# ls -lh /var/lib/pgsql/9.1/data/base/16407/21446253
> > -rw------- 1 postgres postgres 16K May 7 12:04
> > /var/lib/pgsql/9.1/data/base/16407/21446253
> > [root(at)dev-db2 16407]# ls -lh /var/lib/pgsql/9.2/data/base/16407/21446253
> > ls: cannot access /var/lib/pgsql/9.2/data/base/16407/21446253: No such
> > file or directory
> > [root(at)dev-db2 16407]#
>
> Relfilenodes are not preserved, so I would not be surprised to see no
> match in the new cluster.
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +
>