Re: pg_upgrade fails: Mismatch of relation OID in database 8.4 -> 9.3

On 5/20/14, 2:22 PM, Bruce Momjian wrote:
>
> On Tue, May 20, 2014 at 12:59:31PM -0600, Jeff Ross wrote:
>> Removing support functions from new cluster                 ok
>> Copying user relation files
>>    /var/lib/postgresql/8.4/main/base/4275487/4278965
>> Mismatch of relation OID in database "FNBooking": old OID 4279499,
>> new OID 19792
>> Failure, exiting
>
> OK, those numbers are supposed to match.  The array is ordered by OID
> and pg_upgrade expects a 1-to-1 mapping.

Ah, so I misunderstood the error message--thanks for clearing that up.
>
>> On 8.4.21, here's that OID:
>>
>> postgres=# \c "FNBooking"
>> psql (9.3.4, server 8.4.21)
>> You are now connected to database "FNBooking" as user "postgres".
>> FNBooking=# SELECT relname, relfilenode, relkind from pg_class where
>> oid = 4279499;
>>      relname    | relfilenode | relkind
>> ---------------+-------------+---------
>>   abandone_conv |     4279499 | r
>> (1 row)
>>
>> and on 9.3.4 it is the same:
>>
>> postgres@vdev1commandprompt2:~$ psql "FNBooking"
>> psql (9.3.4)
>> Type "help" for help.
>>
>> FNBooking=# SELECT relname, relfilenode, relkind from pg_class where
>> oid = 4279499;
>>      relname    | relfilenode | relkind
>> ---------------+-------------+---------
>>   abandone_conv |     4279499 | r
>> (1 row)
>
> Yes, they are supposed to match.
>
>> On 8.4.21, the new OID doesn't exist:
>>
>> FNBooking=# SELECT relname, relfilenode, relkind from pg_class where
>> oid = 19792;
>>   relname | relfilenode | relkind
>> ---------+-------------+---------
>> (0 rows)
>>
>> and on 9.3.4 it is this:
>>
>> FNBooking=# SELECT relname, relfilenode, relkind from pg_class where
>> oid = 19792;
>>       relname      | relfilenode | relkind
>> ------------------+-------------+---------
>>   pg_toast_4279527 |       19792 | t
>> (1 row)
>>
>> Just to check, I did a pg_dump of the 8.4.21 FNBooking database and
>> it restored with psql to 9.3.4 with no issues but the overall
>> migration will really be too big to go this route.
>
> So the problem is that some table in the new cluster got a low-numbered
> toast file and the version of the table in the old cluster probably
> doesn't have a toast file.
>
> Can you track down details on what table owns that toast file?  Can you
> check on the table's layout to see what might have caused the toast
> table creation?  Were columns added/removed?  If you remove that table,
> does pg_upgrade then work?  I am guessing it would.
>


Here's a sample from a different database that failed with the same problem.

Error:  Mismatch of relation OID in database "UDB": old OID 1163225, new 
OID 22588
postgres@vdev1commandprompt2:~$ psql "UDB"
psql (9.3.4)
Type "help" for help.

UDB=# \x
Expanded display is on.
UDB=# select * from pg_class where reltoastrelid = 22588;
-[ RECORD 1 
]--+--------------------------------------------------------------------------------------------------
relname        | contact_email
relnamespace   | 2200
reltype        | 17262
reloftype      | 0
relowner       | 10
relam          | 0
relfilenode    | 17260
reltablespace  | 0
relpages       | 0
reltuples      | 0
relallvisible  | 0
reltoastrelid  | 22588
reltoastidxid  | 0
relhasindex    | t
relisshared    | f
relpersistence | p
relkind        | r
relnatts       | 5
relchecks      | 0
relhasoids     | f
relhaspkey     | t
relhasrules    | f
relhastriggers | t
relhassubclass | f
relispopulated | t
relfrozenxid   | 1944
relminmxid     | 2
relacl         | 
{postgres=arwdDxt/postgres,fnv2=arwd/postgres,webv2=arwd/postgres,krish=r/postgres,fm=r/postgres}
reloptions     |

UDB=# \d+ contact_email                                                       Table 
"public.contact_email" Column |            Type             | 
Modifiers                          | Storage  | Stats target | Description

--------+-----------------------------+------------------------------------------------------------+----------+--------------+-------------
id    | integer                     | not null default 
 
nextval('contact_email_id_seq'::regclass) | plain    |              | email1 | character varying(255)      | not null
                         | extended |              | email2 | character varying(255)      |
|extended |              | time   | timestamp without time zone | not null default now()                             |
plain   |              | source | email_source                | not null                             | plain    |
      |
 
Indexes:    "contact_email_pkey" PRIMARY KEY, btree (id)    "idx_contact_email_email1" btree (lower(email1::text) 
varchar_pattern_ops)    "idx_contact_email_email2" btree (lower(email2::text) 
varchar_pattern_ops)
Referenced by:    TABLE "abandoned_master_booking" CONSTRAINT 
"abandoned_master_booking_contact_email_id_fkey" FOREIGN KEY 
(contact_email_id) REFERENCES contact_email(id)    TABLE "contact" CONSTRAINT "contact_contact_email_id_fkey" FOREIGN 
KEY (contact_email_id) REFERENCES contact_email(id)    TABLE "eticketaccesslog" CONSTRAINT 
"eticketaccesslog_contact_email_id_fkey" FOREIGN KEY (contact_email_id) 
REFERENCES contact_email(id)
Has OIDs: no

UDB=# \dT+ email_source
List of data types
-[ RECORD 1 ]-----+-------------
Schema            | public
Name              | email_source
Internal name     | email_source
Size              | 4
Elements          | Booking                  | User Profile                  | UserProfile
Access privileges | =U/postgres
Description       |

I do not know if columns were added or removed.

Dropping the table from the last database that caused pg_upgrade to fail 
let pg_upgrade proceed on through many more before it failed again on 
the UDB database, so that's progress!

If there is anything else I can provide, let me know.

Thanks, Bruce!

Jeff