Re: PG_XLOG 27028 files running out of space

[Tory M Blue <tmblue@xxxxxxxxx>]

On Thu, Feb 14, 2013 at 3:01 AM, Ian Lawrence Barwick <barwick@xxxxxxxxx> wrote:

2013/2/14 Tory M Blue <tmblue@xxxxxxxxx>

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