Re: error while autovacuuming

On Fri, Aug 27, 2010 at 3:21 PM, tamanna madaan <
tamanna(dot)madan(at)globallogic(dot)com> wrote:

> Hi All
>
> Any idea about this problem ??
>
> Thanks..
> Tamanna
>
>
> -----Original Message-----
> From: tamanna madaan
> Sent: Fri 8/20/2010 11:54 AM
> To: pgsql-general(at)postgresql(dot)org
> Subject: error while autovacuuming
>
> Hi All
>
> I am using a cluster setup with two nodes in it . postgres version is 8.1.2
> and slony 1.1.5 is being used for replication.
> I have autovacuum enabled. But it was giving the following error while
> vacuuming template0 database.
>
> 2010-08-18 18:36:14 UTC LOG: autovacuum: processing database "template0"
> 2010-08-18 18:36:14 UTC ERROR: could not access status of transaction
> 3222599682
> 2010-08-18 18:36:14 UTC DETAIL: could not open file "pg_clog/0C01": No such
> file or directory
>
> Because of this error autovacuum is not able to process other databses in
> postgres including my databse 'my_database'.
> which is causing database to slow down.
>
> I googled about this problem and found that this may be because of some
> data corruption. But I don't see any source of data corruption
> in my database. Can anyone please let me know what could be causing this
> error. We generally don't use template0 database.
> So what could be causing this data corruption.
>
> Following below steps have solved the problem for now :
>
> 1. set 'datallowconn' to true for template0 database in pg_database
> table.
> 2. Stop postgres
> 3. create a zero filled 0C01 file in /var/lib/pgsql/data/pg_clog folder.
> 4. start postgres
> 5. Execute `vacuum freeze` for all the databases i.e template0 ,
> template1, postgres and my_database
> 6. set 'datallowconn' to false for template0 database in pg_database
> table.
>
> But I doubt if the problem has been solved permanently or it can reapper
> again. If it has been solved permanenlty then how.
> I just created a zero filled 0C01 file of 256 K size. It doesn't have any
> meaningful data. How this can resolve the problem permanently??
>
> Please reply ...
>
> Thanks...
> Tamanna
>
> Any chance you can upgrade your version to 8.4.x series? If I am not
wrong, there has been a significant change in autovaccum between the
versions. In case you cannot, I would still suggest you to update your
version to 8.1.21 which should atleast cover any known bugs.

With regards

Amitabh