Re: Trimming transaction logs after extended WAL archive failures

On Thu, Mar 27, 2014 at 1:42 AM, Steven Schlansker <steven@likeness.com> wrote:
>
> On Mar 25, 2014, at 7:58 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
>> On 03/25/2014 04:52 PM, Steven Schlansker wrote:
>>>
>>
>>>> Some more questions, what happens when things begin to dawn on me:)
>>>>
>>>> You said the disk filled up entirely with log files yet currently the number(size) of logs is growing.
>>>
>>> It's holding stable now.  I tried to vacuum up to clean some space which turned out to generate more pg_xlog
activitythan it saved space, and (I assume) the archiver fell behind and that was the source of the growing log.  There
haven'tbeen any new segments since I stopped doing that. 
>>
>> Yea, vacuum just marks space as available for reuse it does not actually free space.
>>
>
> I even knew that.  Funny what you'll forget when the system is down and you're in a panic.
>
> This is actually something that has bit me on more than one occasion -- if you accidentally temporarily use too much
space,it is *very* hard to back out of the situation.  It seems that the only way to actually release space to the
systemare VACUUM FULL, CLUSTER, or to DROP objects.  None of these can be executed without severe disruption to a
runningdatabase.  A cluster operation on any of our tables that are large enough to matter can easily run through the
night.
Yep, depending on your application needs you could actually avoid any
periodic VACUUM FULL-like operations that need an exclusive lock on
the objects it is cleaning by making autovacuum more aggressive. This
makes your house cleaner by dropping the garbage at a higher
frequency.
--
Michael