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/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers