Re: Old pg_xlog files

I am running postgres 7.4.9 on an LFS system , and had a long outstanding
pg_checkpoint that seemed to prevent the DB from being cleanly shutdown.
The box was power cycled (not my choice...) and when it came up Postgres did
it's recovery and seemed to be fine.

Checkpoints appear to be up to date (via pg_controldata), but the pg_xlog
directory has 95 files, of which 90 are dated before midnight yesterday or
earlier.

Can I just delete them safely? Or is there some recovery method I can do?

Thanks.

On Mar 22, 2006, at 1:37 PM, Tass Chapman wrote:

> Checkpoints appear to be up to date (via pg_controldata), but the
> pg_xlog directory has 95 files, of which 90 are dated before
> midnight yesterday or earlier.
>
> Can I just delete them safely? Or is there some recovery method I
> can do?
>

no, do not delete them. they will eventually be re-used. check back
in a few days and see how old the various files are...

Vivek Khera <vivek(at)khera(dot)org> writes:
> On Mar 22, 2006, at 1:37 PM, Tass Chapman wrote:
>> Checkpoints appear to be up to date (via pg_controldata), but the
>> pg_xlog directory has 95 files, of which 90 are dated before
>> midnight yesterday or earlier.
>>
>> Can I just delete them safely? Or is there some recovery method I
>> can do?

> no, do not delete them. they will eventually be re-used. check back
> in a few days and see how old the various files are...

If there are more than about 2*checkpoint_segments of them, the extras
will be removed by the next successful checkpoint. So there's really no
reason to touch them manually.

regards, tom lane

Thanks all. It is finally begining to reuse them.

Any suggestions on what I can look at to see why the checkpoint was so far
outstanding and why it hung on in a Zombie state when we tried a soft boot?

On 3/22/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Vivek Khera <vivek(at)khera(dot)org> writes:
> > On Mar 22, 2006, at 1:37 PM, Tass Chapman wrote:
> >> Checkpoints appear to be up to date (via pg_controldata), but the
> >> pg_xlog directory has 95 files, of which 90 are dated before
> >> midnight yesterday or earlier.
> >>
> >> Can I just delete them safely? Or is there some recovery method I
> >> can do?
>
> > no, do not delete them. they will eventually be re-used. check back
> > in a few days and see how old the various files are...
>
> If there are more than about 2*checkpoint_segments of them, the extras
> will be removed by the next successful checkpoint. So there's really no
> reason to touch them manually.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

"Tass Chapman" <tasseh(dot)postgres(at)gmail(dot)com> writes:
> Any suggestions on what I can look at to see why the checkpoint was so far
> outstanding and why it hung on in a Zombie state when we tried a soft boot?

If it was in a funny process state, I'd guess that there was some
hardware problem that caused a disk write request to hang up. You
can get stuck in uninterruptible disk wait for quite a long time if
the disk doesn't respond.

regards, tom lane

Thanks.
I'll see if I can get a scheduled maintenance check on this.

The disks seem to be good as they are a 0+1 RAID and all internal tests
show them to be in good health along with the controller. Would memory be a
good suspect?

On 3/22/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "Tass Chapman" <tasseh(dot)postgres(at)gmail(dot)com> writes:
> > Any suggestions on what I can look at to see why the checkpoint was so
> far
> > outstanding and why it hung on in a Zombie state when we tried a soft
> boot?
>
> If it was in a funny process state, I'd guess that there was some
> hardware problem that caused a disk write request to hang up. You
> can get stuck in uninterruptible disk wait for quite a long time if
> the disk doesn't respond.
>
> regards, tom lane
>