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

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.

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

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +