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(at)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(at)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