Message-ID: <511CC5CA.2000403@vmware.com>
Date: Thu, 14 Feb 2013 13:08:58 +0200
From: Heikki Linnakangas <hlinnakangas@vmware.com>
Organization: VMware
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:10.0.12) Gecko/20130116 Icedove/10.0.12
MIME-Version: 1.0
To: Tory M Blue <tmblue@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: PG_XLOG 27028 files running out of space
References: 
 <CAEaSS0Ywu2gc5M1aowPxmnb_jfdjdicv0QGPmFdeDwnqV-zHGA@mail.gmail.com>
In-Reply-To: 
 <CAEaSS0Ywu2gc5M1aowPxmnb_jfdjdicv0QGPmFdeDwnqV-zHGA@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance