Re: [HACKERS] Mismatch of relation names: pg_toast.pg_toast_nnn during pg_upgrade from 8.4 to 9.1

2011-09-30 Thread Jamie Fox
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 br...@momjian.us 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 pete...@gmx.net 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  br...@momjian.us        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

2011-09-28 Thread Jamie Fox
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 pete...@gmx.net 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

2011-09-27 Thread Jamie Fox
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

2009-07-15 Thread Jamie Fox
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
alvhe...@commandprompt.comwrote:

 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

2009-07-14 Thread Jamie Fox
On Mon, Jul 13, 2009 at 8:03 PM, Bruce Momjian br...@momjian.us 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


Re: [HACKERS] [GENERAL] large object does not exist after pg_migrator

2009-07-14 Thread Jamie Fox
  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