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

On Tue, May 20, 2014 at 03:25:00PM -0600, Jeff Ross wrote:
> 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
>
> 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.

OK, so we have code in the backend to force a toast table in the new
cluster if there isn't one in the old cluster, e.g.:

/*
* Check to see whether the table actually needs a TOAST table.
*
* If an update-in-place toast relfilenode is specified, force toast file
* creation even if it seems not to need one.
*/
if (!needs_toast_table(rel) &&
(!IsBinaryUpgrade ||
!OidIsValid(binary_upgrade_next_toast_pg_class_oid)))
return false;

What we never considered is case where the old cluster didn't have one
and the new one does.

What I would like you to do is to use the 9.3 pg_dump and create a
schema-only dump:

pg_dump --schema-only --binary-upgrade

Find the table that is getting the toast file on the new cluster but not
the old one, and run all the commands related to that table from the
dump --- you don't need to load any data, just the schema items.

Run that on the old cluster and the new cluster, then check if you are
getting the same case where there is no toast table on the old cluster
but one on the new cluster. Remember to only use the 9.3 pg_dump output
for both old and new clusters.

Thanks.

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

+ Everyone has their own god. +