Thread: PG_XLOG 27028 files running out of space

On 14.02.2013 12:49, Tory M Blue wrote:
> 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?

You'll need to delete some of the oldest xlog files to release disk
space. But first you need to make sure you don't delete any files that
are still needed, and what got you into this situation in the first place.

You say that you "run an archive command every 10 minutes". What do you
mean by that? archive_command specified in postgresql.conf is executed
automatically by the system, so you don't need to and should not run
that manually. After archive_command has run successfully, and the
system doesn't need the WAL file for recovery anymore (ie. after the
next checkpoint), the system will delete the archived file to release
disk space. Clearly that hasn't been working in your system for some
reason. If archive_command doesn't succeed, ie. it returns a non-zero
return code, the system will keep retrying forever until it succeeds,
without deleting the file. Have you checked the logs for any
archive_command errors?

To get out of the immediate trouble, run "pg_controldata", and make note
of this line:

Latest checkpoint's REDO WAL file:    000000010000000000000001

Anything older than that file is not needed for recovery. You can delete
those, if you have them safely archived.

- Heikki

Tory M Blue wrote:
> 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.

> Postgres 9.1.6
> slon 2.1.2

Are there any messages in the log file?
Are you sure that archiving works, i.e. do WAL files
show up in your archive location?

The most likely explanation for what you observe is that
archive_command returns a non-zero result (fails).
That would lead to a message in the log.

Yours,
Laurenz Albe