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

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. +