Re: Trimming transaction logs after extended WAL archive failures

On 03/25/2014 04:17 PM, Steven Schlansker wrote:
>
> On Mar 25, 2014, at 4:02 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>
>> On 03/25/2014 03:54 PM, Steven Schlansker wrote:
>>>
>>> On Mar 25, 2014, at 3:52 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>>>
>>>> On 03/25/2014 01:56 PM, Steven Schlansker wrote:
>>>>> Hi everyone,
>>>>>
>>>>> I have a Postgres 9.3.3 database machine. Due to some intelligent work on the part of someone who shall remain nameless, the WAL archive command included a ‘> /dev/null 2>&1’ which masked archive failures until the disk entirely filled with 400GB of pg_xlog entries.
>>>>>
>>>>> I have fixed the archive command and can see WAL segments being shipped off of the server, however the xlog remains at a stable size and is not shrinking. In fact, it’s still growing at a (much slower) rate.
>>>>
>>>> So what is wal_keep_segments set at in postgresql.conf?
>>>>
>>>
>>> 5000. There are currently about 18000 WAL segments in pg_xlog.
>>
>> I guess what I should have also asked previously is what exactly are you doing, are you streaming as well as archiving?
>
> Yes, we have both enabled. Here’s some hopefully relevant configuration stanzas and information:
>

>
> I have verified that WAL segments are being archived to the archive destination, and that the slave is connected and receiving segments.

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.

So did you grow the disk, move the logs or find some way to reduce the
number?

What happened to the server when the disk filled up?
In other words do the log entries at the time show it recovered gracefully?
If not what did you do to get it running again?

The concern being that the server is actually fully recovered.

>
> Thanks!
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com