Re: [GENERAL] pg_upgrade problem

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/