Re: 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