Thread: autovacuum: could not access status of transaction
Hello!
PostgreSQL 8.1.1 on x86_64-pc-linux-gnu
I've been running a server with autovacuum enabled for quite a while now (months) without problems. But recently the server slowed down and after investigation I found the following repeated error messsage in the log:
LOG: autovacuum: processing database "template0"
ERROR: could not access status of transaction 3541181801
DETAIL: could not open file "pg_clog/0D31": No such file or directory
ERROR: could not access status of transaction 3541181801
DETAIL: could not open file "pg_clog/0D31": No such file or directory
I assume that the avac-process halts at this point which means no vacuum and/or analyze for the other databases? Which would explain the slowdown.
What is the best way to proceed with this? Stop the postmaster, create a zero-filled pg_clog/0D31 and restart?
Regards
Nichlas
On Tuesday 28 March 2006 14:21, Nichlas Löfdahl wrote: > Hello! > > PostgreSQL 8.1.1 on x86_64-pc-linux-gnu > > I've been running a server with autovacuum enabled for quite a while now > (months) without problems. But recently the server slowed down and after > investigation I found the following repeated error messsage in the log: > > LOG: autovacuum: processing database "template0" > ERROR: could not access status of transaction 3541181801 > DETAIL: could not open file "pg_clog/0D31": No such file or directory > > I assume that the avac-process halts at this point which means no vacuum > and/or analyze for the other databases? Which would explain the slowdown. > > What is the best way to proceed with this? Stop the postmaster, create a > zero-filled pg_clog/0D31 and restart? > Sorry, I am not really of any help with this, however I am finding it odd that this message is occuring while trying to process template0. Have you fuddled with your system catalogs to make template0 connectable? Else ISTM autovacuum should never be trying to process template0. Anyone else want to weigh in here? -- Robert Treat Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Robert Treat wrote: > On Tuesday 28 March 2006 14:21, Nichlas Löfdahl wrote: > > I've been running a server with autovacuum enabled for quite a while now > > (months) without problems. But recently the server slowed down and after > > investigation I found the following repeated error messsage in the log: > > > > LOG: autovacuum: processing database "template0" > > ERROR: could not access status of transaction 3541181801 > > DETAIL: could not open file "pg_clog/0D31": No such file or directory > > Sorry, I am not really of any help with this, however I am finding it odd that > this message is occuring while trying to process template0. Have you fuddled > with your system catalogs to make template0 connectable? Else ISTM autovacuum > should never be trying to process template0. Anyone else want to weigh in > here? Hmm ... all databases, including those marked not connectable, are vacuumed eventually, even if it's only for doing a no-op VACUUM FREEZE. Maybe the database had the datistemplate bit reset, then it was modified and not frozen again before setting the datistemplate bit again. While I don't see why anyone would modify template0, it's certainly possible. Another possibility is that there is data corruption, but I would think that it would present in other ways as well. > > I assume that the avac-process halts at this point which means no vacuum > > and/or analyze for the other databases? Which would explain the slowdown. Yup. Autovacuum would detect that template0 is the database in most need of an autovacuum processing, then fail and die. Subsequent autovac iterations would do the same. > > What is the best way to proceed with this? Stop the postmaster, create a > > zero-filled pg_clog/0D31 and restart? Yeah, I'd do that and VACUUM FREEZE template0 right away. What files are actually in pg_clog anyway? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support