Re: pg_xlog disk full error, i need help

Janning Vygen <vygen(at)gmx(dot)de> writes:
> Am Montag, 28. Mrz 2005 18:06 schrieb Tom Lane:
>> The only way for pg_xlog to bloat vastly beyond what it's supposed to be
>> (which is to say, about twice your checkpoint_segments setting) is if
>> checkpoints are somehow blocked from happening. The only mechanism I
>> know about for that is that in 7.4.* (maybe 7.3.* too) a very large
>> btree CREATE INDEX or REINDEX operation can block checkpoints until it
>> completes. Did you have something like that going on?

> It looks like something/someone wrote so much data in my table that the
> nightly clustering process just didn't succeed because of disk full failure
> after writing too many pg_xlog files. The writing of so many pg_xlog files
> now makes more sense to me when clustering 68 GByte of data.

Yeah, CLUSTER proceeds by rewriting the table and then invoking REINDEX
on each index, so the checkpoint lockout problem will apply. Tell you
the truth, the best and perhaps only answer for you is to update to 8.0
where that problem is solved.

> How do i get the tablename using this filenode? (restarting the database is
> not an option)

Sure it is. pg_resetxlog will allow you to restart ... possibly you
will lose some transactions, but if the only thing going on was the
CLUSTER, nothing of value will be lost.

What I would expect to be happening in a CLUSTER is that there would be
an "old" file plus a "new" file of similar size, for both the table
itself and each index that's been processed (up to the point of failure,
where you will have a partially-written new index). After restart with
this method, you will find only the "old" files listed in pg_class.
You'll want to manually delete the unreferenced "new" files.

regards, tom lane