Re: Where art thou pg_clog?

Casey Duncan wrote:
>
> On Feb 15, 2007, at 5:21 PM, Alvaro Herrera wrote:
>
> >Casey Duncan wrote:

> >To fix the problem, set pg_database.datallowconn=true for template0,
> >then connect to it and do a VACUUM FREEZE. Then set
> >datallowconn=false
> >again.
>
> Do you mean to do this after upgrading to 8.1.8? If I try than in
> 8.1.5, I get (unsurprisingly):
>
> % psql -U postgres template0 -c "vacuum freeze"
> ERROR: could not access status of transaction 2565134864
> DETAIL: could not open file "pg_clog/098E": No such file or directory

Hum, yeah, I forgot to mention that you need to create the 098E pg_clog
segment for that to work at all :-) Fill it with byte 0x55 till the
needed position, which is the bit pattern for "all transactions
committed". I'd make sure to remove it manually after the freeze is
done, just in case! (I think the system would remove it at next
checkpoint, but anyway.)

You can do it either after or before upgrading; it's the same. The only
thing that changes in 8.1.7 is that an upcoming vacuum would not forget
the FREEZE.

> >>I'm curious how template0 got stomped on. Certainly nothing's been
> >>changing it. Of course it might just be some random bug so the fact
> >>it landed on a file for template0 could be completely arbitrary.
> >
> >The problem is that all databases are vacuumed every so many
> >transactions, to avoid Xid wraparound problems; even non connectable
> >databases. The problem is that a bug in autovacuum caused that vacuum
> >operation to neglect using the FREEZE flag; this negligence makes it
> >leave non-permanent Xids in the tables, leading to the problem you're
> >seeing.
>
> Ironically we were earlier bitten by the bug that autovacuum didn't
> do the cluster-wide vacuum until too late. Now we got bitten by the
> fact that did do the cluster-wide vacuum. Talk about damned-if-you-do-
> and-damned-if-you-don't! 8^)

Heh :-) Sorry, they are all my bugs. I guess you should be throwing
stones at me or something.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support