Thread: Vacuum returns error
PG 7.2 and RH 7.2 Developer dropped ~30+ tables from 4 different DBs on the same server. Queiries began to fail: unable to locate file; /var/lib/pgsql/pg_clog/0006. Immediately VACUUMed DBs. Queiries began returning OK. Minutes later vacuumdb -z DB_1, returned OK. Attempted vacuumdb -z DB_2, failed with error similar to above. Where can I locate more comprehensive error message information? Any recommendations would be greatly appreciated. Thanks - Bill
(* NOTE: I realize that answering your own posts is like talking to yourself, please forgive me... *) On 1 Apr 2002 at 14:29, William Meloney wrote: > PG 7.2 and RH 7.2 > > Developer dropped ~30+ tables from 4 different DBs on the same > server. Queiries began to fail: unable to locate file; > /var/lib/pgsql/pg_clog/0006. > > Immediately VACUUMed DBs. Queiries began returning OK. > > Minutes later vacuumdb -z DB_1, returned OK. > > Attempted vacuumdb -z DB_2, failed with error similar to above. pg_dump DB_2 failed citing a specific table as the point of failure. A review of the dump file ( DB_2.txt.gz) gave the last record number of the table BEFORE it failed. We then executed a DELETE query and removed the offending record in the cited table. An immediate pg_dump completed successfully. Confidence is high that a vacuum will complete as well. > > Where can I locate more comprehensive error message > information? Any recommendations would be greatly appreciated. > > Thanks > > - Bill > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
"William Meloney" <bmeloney@mindspring.com> writes:
> PG 7.2 and RH 7.2
> Developer dropped ~30+ tables from 4 different DBs on the same
> server. Queiries began to fail: unable to locate file;
> /var/lib/pgsql/pg_clog/0006.
Hmm. What files do you actually have in /var/lib/pgsql/pg_clog?
(I'd like to see an ls -l of that directory if possible; the sizes
and mod dates might be useful clues.) Another useful bit of info
would be "select * from pg_database".
We've seen two or three reports of errors like this, which make me
think there's a corner-case bug or two lurking in the clog code.
Haven't been able to get enough data to track it down yet.
regards, tom lane
On 2 Apr 2002 at 2:09, Tom Lane wrote: > "William Meloney" <bmeloney@mindspring.com> writes: > > PG 7.2 and RH 7.2 > > Developer dropped ~30+ tables from 4 different DBs on the same > > server. Queiries began to fail: unable to locate file; > > /var/lib/pgsql/pg_clog/0006. > > Hmm. What files do you actually have in /var/lib/pgsql/pg_clog? > (I'd like to see an ls -l of that directory if possible; the sizes > and mod dates might be useful clues.) total 252 -rw------- 1 postgres postgres 253952 apr 3 13:47 0000 Another useful bit of info > would be "select * from pg_database". > database datdba encoding datistemplate datallowconn datlassysoid datvacuumxi qc 100 0 false true 16554 964425 plant1 100 0 false true 16554 964106 template1 1 0 true true 16554 1810 template0 1 0 true false 16554 49 plant2 100 0 false true 16554 964326 plant3 100 0 false true 16554 964264 Best of luck - William
"William Meloney" <bmeloney@mindspring.com> writes:
> Developer dropped ~30+ tables from 4 different DBs on the same
> server. Queiries began to fail: unable to locate file;
> /var/lib/pgsql/pg_clog/0006.
>>
>> Hmm. What files do you actually have in /var/lib/pgsql/pg_clog?
> [ info snipped ]
Okay. It seems quite clear that you've executed less than a million
transactions so far in this installation; so 0000 is the only clog
segment that does or should exist, and there's no valid reason
for anything to be trying to access segment 0006. I do not think
this could be a clog logic error as I first suspected; it's probably
more like a garden-variety data corruption problem, ie, something
dropped a bit and there's now an xmin or xmax field someplace that
contains a bogus transaction ID up in the six-million-something range.
My advice is to try to track down and get rid of the bad tuple.
regards, tom lane