Re: error while autovacuuming

Hi All

Can a postgres patch for the fix ( provided in postgres-8.1.6 for
autovacuum error ) be included with the postgres version I am using i.e
postgres 8.1.2 so that I don't have to upgrade to 8.1.21 ??

Please reply

Thanks...
Tamanna

-----Original Message-----
From: tamanna madaan
Sent: Friday, August 27, 2010 3:21 PM
To: pgsql-general(at)postgresql(dot)org
Subject: RE: error while autovacuuming

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