Re: could not open file "pg_clog/0BFF"

On 8/22/07, Patrick Lindeman <patrick(at)my-mac(dot)nl> wrote:
> Hi,
>
> While checking the logfiles of our postgresql server I noticed the error
> mentioned in the subject:
>
> ERROR: could not access status of transaction 3221180546
> DETAIL: could not open file "pg_clog/0BFF": No such file or directory

What other files are there in the pg_clog directory?

> Searching on google told me that this could be solved by creating the file
> mentioned in the error using /dev/zero as input.
>
> After I have done that and taking another look at the login I also noticed
> that this error started to show up in the loggin since the 1st of August
> and from the same date the autovacuum was only vacuuming the 'template0'
> database.

OK, there's more going on here than what you're showing us so far.
template0 is normally frozen and set to not allow connections. so,
what's happened to the db to allow template0 to be connected to?

>
> Right now I have created the "pg_clog/0BFF" file and manually started a
> vacuum.
>
> And now my questions:
>
> Are those mentioned steps the appropriate steps or should we do anything
> else?
>
> What could have caused the error "could not access status of transaction
> 3221180546" and is it more than just coincidence that since that moment
> the vacuum stopped running?

Maybe transaction wraparound? I'm not really sure.

> Is there anything we can do to prevent this in future use?
>
> Besides we are using PostgreSQL 8.1.3.

Upgrading your postgresql version would not be a bad idea. 8.1 is up
to 8.1.9 or so by now. and I know there were some bugs in 8.1.3 or so
that could cause things like this to happen.
Not sure your exact problem is one of them, but you should really keep
up to date on patchs for your pg version.