Skip site navigation (1) Skip section navigation (2)

Mismatch of relation names: pg_toast.pg_toast_nnn during pg_upgrade from 8.4 to 9.1

From: Jamie Fox <jfox(at)directcommerce(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Mismatch of relation names: pg_toast.pg_toast_nnn during pg_upgrade from 8.4 to 9.1
Date: 2011-09-27 23:19:38
Message-ID: CAANAXLLGxc4HhpBcPbamZRgDhMum2SpzwV6XQpGk=TEphbk6CA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi -

I've had no problem upgrading copies our qa databases (which are
backed up and restored with pg_dump/pg_restore)  but have run into the
same problem each time I try to upgrade a copy of our production
database (backed up and restored via PITR).  After verifying a
successful restore and vacuum analyze, I ran a successful pg_upgrade
check run with:

    pg_upgrade -b /usr/local/pgsql-8.4.2/bin -B
/usr/local/pgsql-9.1.0/bin -c -d /data/pgsql/prod-84 -D
/data/pgsql/prod-91 -G /home/postgres/pg_upgrade_prod.check.debug -k
-l /home/postgres/pg_upgrade_prod.check.log -p 5435 -P 5436 -v

Then when I ran the actual pg_upgrade:

    pg_upgrade -b /usr/local/pgsql-8.4.2/bin -B
/usr/local/pgsql-9.1.0/bin -d /data/pgsql/prod-84 -D
/data/pgsql/prod-91 -G /home/postgres/pg_upgrade_prod.debug -k -l
/home/postgres/pg_upgrade_prod.log -p 5435 -P 5436 -v

It fails at this stage:

    Restoring user relation files
    linking /data/pgsql/prod-84/base/11564/2613 to
/data/pgsql/prod-91/base/12698/12570
    linking /data/pgsql/prod-84/base/11564/2683 to
/data/pgsql/prod-91/base/12698/12572
    Mismatch of relation names: database "prod1", old rel
pg_toast.pg_toast_54542379, new rel pg_toast.pg_toast_16735
    Failure, exiting

These are the log files generated:

    -rw-rw-r-- 1 postgres postgres      0 Sep 27 12:47
pg_upgrade_prod.check.debug
    -rw-rw-r-- 1 postgres postgres   8524 Sep 27 13:27 pg_upgrade_prod.check.log
    -rw-rw-r-- 1 postgres postgres      0 Sep 27 13:31 pg_upgrade_prod.debug
    -rw-rw-r-- 1 postgres postgres   2374 Sep 27 14:12
pg_upgrade_dump_globals.sql
    -rw-rw-r-- 1 postgres postgres 257696 Sep 27 14:12 pg_upgrade_dump_db.sql
    -rw-rw-r-- 1 postgres postgres 260092 Sep 27 14:12 pg_upgrade_dump_all.sql
    -rw-rw-r-- 1 postgres postgres  75413 Sep 27 14:12 pg_upgrade_prod.log

I don't see any helpful (to me) explanation within them, but after
some searching ran across this comment in the relevant section of
source:

    00059         /*
    00060          * In pre-8.4, TOAST table names change during
CLUSTER;  in >= 8.4
    00061          * TOAST relation names always use heap table oids,
hence we cannot
    00062          * check relation names when upgrading from pre-8.4.
    00063          */

At this point I still don't understand the underlying issue.  Our
databases were migrated in place (linked) with pg_migrator from 8.3 to
8.4 could that be the cause or related?  Where would you recommend I
look to discover a cause and hopefully a solution?

Thanks in advance,

Jamie

Responses

pgsql-hackers by date

Next:From: Will LeinweberDate: 2011-09-27 23:57:52
Subject: feature request: auto savepoint for interactive psql when in transaction.
Previous:From: Steve CrawfordDate: 2011-09-27 23:13:41
Subject: pg_upgrade - add config directory setting

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group