On Thu, Feb 14, 2013 at 3:08 AM, Heikki Linnakangas <hlinnakan...@vmware.com > wrote:
> 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 > Thanks Heikki, Yes I misspoke with the archive command, sorry, that was a timeout and in my haste/disorientation I misread/spoke. So I'm clear on that. I'm also over my issue after discovering the problem, but pg_controldata is something I could of used initially in my panic, so I've added that command to my toolbox and appreciate the response! Thanks Tory