Re: PG_XLOG 27028 files running out of space

On Thu, Feb 14, 2013 at 3:01 AM, Ian Lawrence Barwick <barwick(at)gmail(dot)com>wrote:

> 2013/2/14 Tory M Blue <tmblue(at)gmail(dot)com>
>
>> My postgres db ran out of space. I have 27028 files in the pg_xlog
>> directory. I'm unclear what happened this has been running flawless for
>> years. I do have archiving turned on and run an archive command every 10
>> minutes.
>>
>> I'm not sure how to go about cleaning this up, I got the DB back up, but
>> I've only got 6gb free on this drive and it's going to blow up, if I can't
>> relieve some of the stress from this directory over 220gb.
>>
>> What are my options?
>>
>> Thanks
>>
>> Postgres 9.1.6
>> slon 2.1.2
>
>
> I can't give any advice right now, but I'd suggest posting more details of
> your
> setup, including as much of your postgresql.conf file as possible
> (especially
> the checkpoint_* and archive_* settings) and also the output of
> pg_controldata.
>
> Ian Barwick
>

Thanks Ian

I figured it out and figured out a way around it for now.

My archive destination had it's ownership changed and thus the archive
command could not write to the directory. I didn't catch this until well it
was too late. So 225GB, 27000 files later.

I found a few writeups on how to clear this up and use the command true in
the archive command to quickly and easily delete a bunch of wal files from
the pg_xlog directory in short order. So that worked and now since I know
what the cause was, I should be able to restore my pg_archive PITR configs
and be good to go.

This is definitely one of those bullets I would rather not of taken, but
the damage appears to be minimal (thank you postgres)

Thanks again
Tory