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

<div dir="ltr">I just did the whole process over from the beginning.  here's the full output:<br /><br /><font
face="couriernew, monospace">-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<br /> Thu May  9
14:31:07EDT 2013<br />Performing Consistency Checks<br />-----------------------------<br />Checking current, bin, and
datadirectories                 ok<br />Checking cluster versions                                   ok<br /> Checking
databaseuser is a superuser                       ok<br />Checking for prepared transactions                        
 ok<br/>Checking for reg* system OID user data types                ok<br />Checking for contrib/isn with
bigint-passingmismatch       ok<br /> Creating catalog dump                                       ok<br />Checking for
presenceof required libraries                 ok<br />Checking database user is a superuser                       ok<br
/>Checkingfor prepared transactions                          ok<br /><br />If pg_upgrade fails after this point, you
mustre-initdb the<br />new cluster before continuing.<br /><br />Performing Upgrade<br />------------------<br
/>Analyzingall rows in the new cluster                       ok<br />Freezing all rows on the new cluster              
        ok<br /> Deleting files from new pg_clog                             ok<br />Copying old pg_clog to new server
                         ok<br />Setting next transaction ID for new cluster                 ok<br />Resetting WAL
archives                                     ok<br /> Setting frozenxid counters in new cluster                   ok<br
/>Creatingdatabases in the new cluster                       ok<br />Adding support functions to new cluster          
         ok<br />Restoring database schema to new cluster                    ok<br /> Removing support functions from
newcluster                 ok<br />Copying user relation files<br />  /var/lib/pgsql/9.1/data/base/16406/3016054      
          <br /><font color="#cc0000">Mismatch of relation OID in database "db": old OID 2938685, new OID 299749<br
/></font>Failure,exiting<br /><br />real 16m17.924s<br />user 1m34.334s<br />sys 1m27.519s<br />Thu May  9 14:47:25 EDT
2013<br/><br /></font>Here's the query of that OID:<br /><br /><font face="courier new, monospace">db=# SELECT relname,
relfilenode,relkind from pg_class where oid = 299749;<br />     relname     | relfilenode | relkind <br
/>----------------+-------------+---------<br/> pg_toast_17304 |      299749 | t<br />(1 row)<br /><br />db=#
</font><br/></div><div class="gmail_extra"><br /><br /><div class="gmail_quote">On Thu, May 9, 2013 at 10:45 AM, Bruce
Momjian<span dir="ltr"><<a href="mailto:bruce@momjian.us" target="_blank">bruce@momjian.us</a>></span> wrote:<br
/><blockquoteclass="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div
class="im">OnThu, May  9, 2013 at 10:20:12AM -0400, Evan D. Hoffman wrote:<br /> > >From the 9.1 cluster (port
5432):<br/> ><br /> ><br /> > db=# SELECT relname, relfilenode, relkind  from pg_class where oid = 2938685;<br
/>>         relname        | relfilenode | relkind<br /> > -----------------------+-------------+---------<br />
> substitutionlist_pkey |    21446253 | i<br /> > (1 row)<br /> ><br /> > db=#<br /><br /></div>OK, so it
isan index, interesting.<br /><div class="im"><br /> > >From the 9.2 cluster (port 5433):<br /> ><br /> >
db=#SELECT relname from pg_class where oid = 299721;<br /> >  relname<br /> > ---------<br /> > (0 rows)<br
/><br/></div>Is it possible that you mis-copied the "new" OID from the error message?<br /> It was at the end of the
line. If so, could you get the right number?<br /> The fact that old and new start with "29" but there are a
different<br/> number of digits in each number suggests it might be the wrong number.<br /><div class="im"><br /> >
>>Linking user relation files<br /> > >>   /var/lib/pgsql/9.1/data/base/16406/3016054<br /> >
>>Mismatch of relation OID in database "dbname": old OID 2938685, new OID 299721<br /> > >> Failure,
exiting<br/><br /></div>If that is the right number, I am confused because pg_upgrade thinks<br /> something has that
oidin pg_class in your new cluster.  It might help<br /> for you to look for that number in the pg_upgrade logs, and
youmight<br /> need to run a query from those logs to see where that number is coming<br /> from.<br /><div
class="im"><br/> > Assuming the relfilenode would be the filename on disk, it exists in<br /> > the 9.1 DB but
notin the 9.2:<br /> ><br /> > [root@dev-db2 16407]# ls -lh /var/lib/pgsql/9.1/data/base/16407/21446253<br />
>-rw------- 1 postgres postgres 16K May  7 12:04<br /> > /var/lib/pgsql/9.1/data/base/16407/21446253<br /> >
[root@dev-db216407]# ls -lh /var/lib/pgsql/9.2/data/base/16407/21446253<br /> > ls: cannot access
/var/lib/pgsql/9.2/data/base/16407/21446253:No such<br /> > file or directory<br /> > [root@dev-db2 16407]#<br
/><br/></div>Relfilenodes are not preserved, so I would not be surprised to see no<br /> match in the new cluster.<br
/><divclass="HOEnZb"><div class="h5"><br /> --<br />   Bruce Momjian  <<a
href="mailto:bruce@momjian.us">bruce@momjian.us</a>>       <a href="http://momjian.us"
target="_blank">http://momjian.us</a><br/>   EnterpriseDB                             <a href="http://enterprisedb.com"
target="_blank">http://enterprisedb.com</a><br/><br />   + It's impossible for everything to be true. +<br
/></div></div></blockquote></div><br/></div>