Re: pg_xlog disk full error, i need help

Am Montag, 28. März 2005 18:06 schrieb Tom Lane:
> "Janning Vygen" <vygen@gmx.de> writes:
> > My disk was running full with 100 GB (!) of data/pg_xlog/ files.
>
> 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?

first of all i have 7.4 running. A "CLUSTER" was running which to me is
somewhat similiar to REINDEX, isn't it? And the night before i killed -9 my
nightly vacuum process which did not return after 6 hours or so. first i
tried to stop the postmaster with the init.d script, which didnt worked at
all. i think that killing this vacuum process was not a good idea. 24 hours
after killing this process this ugly xlog thing happend while executing
"CLUSTER". And the pg_dump right before "CLUSTER" did work fine.

Besides 100 GB of xlog, another strange thing that i had about 42 GB in
directory data/base. And it should be about 4 GB and  i vacuum an cluster
every night.

> Anyway, replaying that much log is gonna take awhile :-(.  I think you
> have only two choices:
> 1. Grin and bear it.

i tried for several hours.

> 2. Kill the replay process, then use pg_resetxlog to throw away the xlog.
>    Then pray you didn't lose anything critical by doing so.

i killed the process and used a database backup from just before the error
occurred.

> If you know that there was nothing going on except the supposed index
> build, then you can be pretty sure that #2 will lose nothing except the
> incomplete index, so it might be a workable alternative.

When it comes to trouble with postgresql i always have the feeling of not
knowing enough stuff which is NOT inside the docs. I had another ugly
situation a year ago and when in trouble it's very difficult to act calm.
Isnt' there more information about "Troubleshooting" than reading postgresql
code and archives? I am not an expert DBA (i wouldn't call me a DBA at all
besides the fact that i am actually doing the administration). But i am
willing to learn.

kind regards,
janning

--
PLANWERK 6 websolutions
Herzogstraße 85, 40215 Düsseldorf
Tel.: 0211-6015919  Fax: 0211-6015917
http://www.planwerk6.de