X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id AB3D49DC82B for ; Mon, 26 Dec 2005 12:02:57 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 86108-05 for ; Mon, 26 Dec 2005 11:02:55 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id CD2079DC80B for ; Mon, 26 Dec 2005 12:02:54 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id jBQG2qYd009823; Mon, 26 Dec 2005 11:02:53 -0500 (EST) To: Gourish Singbal cc: "pgsql-admin@postgresql.org" Subject: Re: vacuuming template0 gave ERROR In-reply-to: <674d1f8a0512260334g3b72c635m7d46ab457a8f2694@mail.gmail.com> References: <674d1f8a0512260334g3b72c635m7d46ab457a8f2694@mail.gmail.com> Comments: In-reply-to Gourish Singbal message dated "Mon, 26 Dec 2005 17:04:28 +0530" Date: Mon, 26 Dec 2005 11:02:52 -0500 Message-ID: <9822.1135612972@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.045 required=5 tests=[AWL=0.045] X-Spam-Score: 0.045 X-Spam-Level: X-Archive-Number: 200512/312 X-Sequence-Number: 20032 Gourish Singbal writes: > Got the following ERROR when i was vacuuming the template0 database. Why were you doing that in the first place? template0 shouldn't ever be touched. > postgresql server version is 7.4.5 The underlying cause is likely related to this 7.4.6 bug fix: 2004-10-13 18:22 tgl * contrib/pgstattuple/pgstattuple.c, src/backend/access/heap/heapam.c, src/backend/utils/adt/ri_triggers.c (REL7_4_STABLE): Repair possible failure to update hint bits back to disk, per http://archives.postgresql.org/pgsql-hackers/2004-10/msg00464.php. I plan a more permanent fix in HEAD, but for the back branches it seems best to just touch the places that actually have a problem. > INFO: vacuuming "pg_catalog.pg_statistic" > ERROR: could not access status of transaction 1107341112 > DETAIL: could not open file "/home/postgres/data/pg_clog/0420": No such > file or directory Fortunately for you, pg_statistic doesn't contain any irreplaceable data. So you could get out of this via TRUNCATE pg_statistic; VACUUM ANALYZE; -- rebuild contents of pg_statistic VACUUM FREEZE; -- make sure template0 needs no further vacuuming Then reset template0's datallowconn to false, and get rid of that code to override it. And then update to a more recent release ;-) (I don't recall exactly what rules 7.4 uses, but likely you'll find that you need to run a standalone backend with -O switch to perform TRUNCATE on a system catalog.) regards, tom lane