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

Re: [GENERAL] pg_upgrade problem

From: hubert depesz lubaczewski <depesz(at)depesz(dot)com>
To: Bruce Momjian <bruce(at)momjian(dot)us>
Cc: PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org>
Subject: Re: [GENERAL] pg_upgrade problem
Date: 2011-08-29 17:49:24
Message-ID: 20110829174924.GA14101@depesz.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
On Mon, Aug 29, 2011 at 06:54:41PM +0200, hubert depesz lubaczewski wrote:
> On Fri, Aug 26, 2011 at 05:28:35PM +0200, hubert depesz lubaczewski wrote:
> > On Fri, Aug 26, 2011 at 12:18:55AM -0400, Bruce Momjian wrote:
> > > 
> > > OK, this was very helpful.  I found out that there is a bug in current
> > > 9.0.X, 9.1.X, and HEAD that I introduced recently when I excluded temp
> > > tables.  (The bug is not in any released version of pg_upgrade.)  The
> > > attached, applied patches should fix it for you.  I assume you are
> > > running 9.0.X, and not 9.0.4.
> > 
> > pg_upgrade worked. Now I'm doing reindex and later on vacuumdb -az.
> 
> vacuumdb failed. The fail looks very similar to the one I had on 9.0.4.
> 
> After long vacuum I got:
> INFO:  vacuuming "pg_toast.pg_toast_106668498"
> vacuumdb: vacuuming of database "etsy_v2" failed: ERROR:  could not access status of transaction 3429738606
> DETAIL:  Could not open file "pg_clog/0CC6": No such file or directory.
> 
> Unfortunately at the moment, I no longer have the old (8.3) setup, but I do
> have the 9.0.X and will be happy to provide any info you might need to help me
> debug/fix the problem.

this pg_toast is related to table "transactions", which was vacuumed
like this:

INFO:  vacuuming "public.transactions"
INFO:  index "transaction_id_pkey" now contains 50141303 row versions in 144437 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.08s/0.13u sec elapsed 173.04 sec.
INFO:  index "transactions_creation_tsz_idx" now contains 50141303 row versions in 162634 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.19s/0.23u sec elapsed 77.45 sec.
INFO:  index "fki_transactions_xxxxxxxxxx_fkey" now contains 50141303 row versions in 163466 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.13s/0.29u sec elapsed 65.45 sec.
INFO:  index "fki_transactions_xxxxxxxx_fkey" now contains 50141303 row versions in 146528 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.15s/0.24u sec elapsed 50.28 sec.
INFO:  index "fki_transactions_xxxxxxxxxxxxx_fkey" now contains 50141303 row versions in 190914 pages
DETAIL:  0 index row versions were removed.
5 index pages have been deleted, 0 are currently reusable.
CPU 1.49s/0.17u sec elapsed 67.95 sec.
INFO:  index "transactions_xxxxxxxxxxxxxxxxxxxxxxxxxx_id" now contains 50141303 row versions in 164669 pages
DETAIL:  0 index row versions were removed.
2 index pages have been deleted, 0 are currently reusable.
CPU 1.36s/0.18u sec elapsed 62.83 sec.
INFO:  "transactions": found 0 removable, 39644831 nonremovable row versions in 5978240 out of 7312036 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 8209452 unused item pointers.
0 pages are entirely empty.
CPU 75.75s/18.57u sec elapsed 9268.19 sec.
INFO:  vacuuming "pg_toast.pg_toast_106668498"
vacuumdb: vacuuming of database "etsy_v2" failed: ERROR:  could not access status of transaction 3429738606
DETAIL:  Could not open file "pg_clog/0CC6": No such file or directory.

Interestingly.

In old dir there is pg_clog directory with files:
0AC0 .. 0DAF (including 0CC6, size 262144)
but new pg_clog has only:
0D2F .. 0DB0

File content - nearly all files that exist in both places are the same, with exception of 2 newest ones in new datadir:
3c5122f3e80851735c19522065a2d12a  0DAF
8651fc2b9fa3d27cfb5b496165cead68  0DB0

0DB0 doesn't exist in old, and 0DAF has different md5sum: 7d48996c762d6a10f8eda88ae766c5dd

one more thing. I did select count(*) from transactions and it worked.

that's about it. I can probably copy over files from old datadir to new (in
pg_clog/), and will be happy to do it, but I'll wait for your call - retry with
copies files might destroy some evidence.

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

In response to

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2011-08-29 17:57:07
Subject: Re: spinlocks on HP-UX
Previous:From: Dean RasheedDate: 2011-08-29 17:40:07
Subject: Re: timestamptz parsing bug?

pgsql-general by date

Next:From: Martín MarquésDate: 2011-08-29 18:24:55
Subject: Re: passing cursors from one PL function to another
Previous:From: hubert depesz lubaczewskiDate: 2011-08-29 16:54:41
Subject: Re: [GENERAL] pg_upgrade problem

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