Re: [HACKERS] Mismatch of relation names: pg_toast.pg_toast_nnn during pg_upgrade from 8.4 to 9.1
I regret that as a part-timer recently brought back on here I didn't get an opportunity to test this earlier. The upgrade with the patch worked fine on my first attempt. Thanks again, Jamie On Wed, Sep 28, 2011 at 7:32 PM, Bruce Momjian wrote: > Jamie Fox wrote: >> Thanks, I'm following the thread "pg_upgrade automatic testing" and >> will try the patch just detailed there. > > I have applied the patch to head and 9.1.X. We still have a win32 bug > to fix. It is a shame I was not able to fix these before 9.1.1 was > released. :-( > > --- > >> >> >> On Wed, Sep 28, 2011 at 12:50 AM, Peter Eisentraut wrote: >> > On tis, 2011-09-27 at 16:19 -0700, Jamie Fox wrote: >> >> >> >> 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 >> > >> > This issue is known and a fix is currently being discussed. >> > >> > >> >> -- >> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-hackers > > -- > Bruce Momjian http://momjian.us > EnterpriseDB http://enterprisedb.com > > + It's impossible for everything to be true. + > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Mismatch of relation names: pg_toast.pg_toast_nnn during pg_upgrade from 8.4 to 9.1
Thanks, I'm following the thread "pg_upgrade automatic testing" and will try the patch just detailed there. Jamie On Wed, Sep 28, 2011 at 12:50 AM, Peter Eisentraut wrote: > On tis, 2011-09-27 at 16:19 -0700, Jamie Fox wrote: >> >> 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 > > This issue is known and a fix is currently being discussed. > > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Mismatch of relation names: pg_toast.pg_toast_nnn during pg_upgrade from 8.4 to 9.1
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] large object does not exist after pg_migrator
Worked great, vacuumlo finished, a vacuum -full finished amazingly quickly, very exciting. We're pointing qa apps at it now for testing. For some reason though, that index has to be rebuilt after running pg_migrator. I'll be testing on our 100GB+ prod copy shortly and will let you know if you want. Thanks, Jamie On Wed, Jul 15, 2009 at 9:28 AM, Alvaro Herrera wrote: > Jamie Fox wrote: > > > Hi - > > REINDEX INDEX pg_largeobject_loid_pn_index; > > > > This seems to have fixed the problem, lo_open of lob data is working > again - > > now to see how vacuumlo likes it. > > So did it work? > > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. >
Re: [HACKERS] [GENERAL] large object does not exist after pg_migrator
> > Here's what I have found that got broken during pg_migrate: In two side > by > > side databases (an 8.3.7 copy and 8.4.0 migrated with pg_migrator) the > > pg_largeobject table has the same number of rows. However, in the 8.4 > > database any select for an loid in pg_largeobject returns zero rows. If > I > > select all loids to a file, and compare to select all loids from 8.3.7 > > they're the same. When I select != an loid it seems to exclude the one > and > > return the rest, but all other comparisons <, > or = return zero rows. > Or > > I'm completely batty. Dereferencing via lo_open of blob_data (an oid) in > > other tables fails in the 8.4 database with 'large object id does not > > exist'. > > Oh, so maybe it's pg_largeobject's index that's borked ... Did you try > reindexing it? > > How are we transferring pg_largeobject, and are we transferring its > index too? Hi - REINDEX INDEX pg_largeobject_loid_pn_index; This seems to have fixed the problem, lo_open of lob data is working again - now to see how vacuumlo likes it. Thanks, Jamie
Re: [HACKERS] [GENERAL] large object does not exist after pg_migrator
On Mon, Jul 13, 2009 at 8:03 PM, Bruce Momjian wrote: > Alvaro Herrera wrote: > > Bruce Momjian wrote: > > > Alvaro Herrera wrote: > > > > Bruce Momjian wrote: > > > > > Jamie Fox wrote: > > > > > > > > > > I can also see that the pg_largeobject table is different, in the > pg_restore > > > > > > version the Rows (estimated) is 316286 and Rows (counted) is the > same, in > > > > > > the pg_migrator version the Rows (counted) is only 180507. > > > > > > > > > Wow, I didn't test large objects specifically, and I am confused > why > > > > > there would be a count discrepancy. I will need to do some research > > > > > unless someone else can guess about the cause. > > > > > > > > Maybe pg_largeobject is not getting frozen? > > > > > > That would explain the change in count, but I thought we froze > > > _everything_, and had to. > > > > After a quick chat with Bruce it was determined that we don't freeze > > anything (it would be horrid for downtime if we did so in pg_migrator; > > and it would be useless if ran anywhere else). What we do is migrate > > pg_clog from the old cluster to the new. So never mind that hypothesis. > > > > Bruce noticed that the pg_dump/pg_migrator combo is failing to restore > > pg_largeobject's relfrozenxid. We're not sure how this is causing the > > errors Jamie is seeing, because what I think should happen is that scans > > of the table should fail with failures to open pg_clog files > > such-and-such, but not missing tuples ... > > Jamie, is it possible for you to apply the attached patch to the 8.4 > server, install the new pg_dump, and run the test again to see if > pg_largeobject is fixed? This patch properly sets the relfrozenxid in > the system tables for each database. > Sorry for the confusion, an addendum meant to be helpful fell out of this thread during the move from -general. I will try this patch now, but to be clear - the number of rows in pg_largeobject changed after I ran vacuumlo (that eventually failed). Here's what I have found that got broken during pg_migrate: In two side by side databases (an 8.3.7 copy and 8.4.0 migrated with pg_migrator) the pg_largeobject table has the same number of rows. However, in the 8.4 database any select for an loid in pg_largeobject returns zero rows. If I select all loids to a file, and compare to select all loids from 8.3.7 they're the same. When I select != an loid it seems to exclude the one and return the rest, but all other comparisons <, > or = return zero rows. Or I'm completely batty. Dereferencing via lo_open of blob_data (an oid) in other tables fails in the 8.4 database with 'large object id does not exist'. Thanks again, Jamie
