Re: error while autovacuuming - Mailing list pgsql-general

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@postgresql.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