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

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=#

From the 9.2 cluster (port 5433):

db=# SELECT relname from pg_class where oid = 299721;relname
---------
(0 rows)

db=#

Assuming the relfilenode would be the filename on disk, it exists in
the 9.1 DB but not in the 9.2:

[root@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@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@dev-db2 16407]#


On Wed, May 8, 2013 at 5:35 PM, Bruce Momjian <bruce@momjian.us> wrote:
> On Wed, May  8, 2013 at 02:27:18PM -0400, Evan D. Hoffman wrote:
>> If you want to start the old cluster, you will need to remove
>> the ".old" suffix from /var/lib/pgsql/9.1/data/global/pg_control.old.
>> Because "link" mode was used, the old cluster cannot be safely
>> started once the new cluster has been started.
>>
>> 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
>
> [ Moved to hackers ]
>
> OK, that is odd.  We preserve old/new OIDs, (not relfilenode, as someone
> suggested in this thread);  FYI:
>
>  *  FYI, while pg_class.oid and pg_class.relfilenode are initially the same
>  *  in a cluster, but they can diverge due to CLUSTER, REINDEX, or VACUUM
>  *  FULL.  The new cluster will have matching pg_class.oid and
>  *  pg_class.relfilenode values and be based on the old oid value.  This can
>  *  cause the old and new pg_class.relfilenode values to differ.  In summary,
>  *  old and new pg_class.oid and new pg_class.relfilenode will have the
>  *  same value, and old pg_class.relfilenode might differ.
>
> The problem reported is that pg_dump was not able to preserve the
> old/new oids between clusters.  Can you get the answer for this query on
> the old cluster:
>
>         SELECT relname from pg_class where oid = 2938685;
>
> and on the new cluster, assuming you used 'copy' mode so you can start
> the old/new clusters indepdendently:
>
>         SELECT relname from pg_class where oid = 299721;
>
> I think we will find that there is something in pg_dump related to this
> table that isn't preserving the oids.
>
> --
>   Bruce Momjian  <bruce@momjian.us>        http://momjian.us
>   EnterpriseDB                             http://enterprisedb.com
>
>   + It's impossible for everything to be true. +