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

Thanks for your replies so far, it seems like the problem has been solved,
for now!

> 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?
>

There are lots of files in the pg_clog directory running from 01FF until
0250.

>> 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?
>
I dont know what happened, but one of the solutions posted in the reply
from Jeff pointed out that I should 'vacuum freeze' template0. While
trying to connect I got the error that I couldn't connect since
"template0" was not accepting connections. After manually executing the
query:
UPDATE pg_database SET datallowconn = true where datname='template0';
I was able to connect and execute 'vacuum freeze;'

It seems like vacuum is running on all databases again so it seems like
the fix works for now.

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

We already had in mind that we needed to upgrade to 8.1.9 or even 8.2.
This problem just made this upgrade more urgent.

Thank you both again !

Regards,
Patrick Lindeman