Re: WAL segments pile up during standalone mode

On Thu, Mar 3, 2011 at 10:16 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> Excerpts from Robert Haas's message of jue mar 03 11:18:38 -0300 2011:
>> On Thu, Mar 3, 2011 at 9:15 AM, Alvaro Herrera
>> <alvherre@commandprompt.com> wrote:
>> > Excerpts from Fujii Masao's message of mié mar 02 22:44:45 -0300 2011:
>> >> On Thu, Mar 3, 2011 at 3:22 AM, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
>> >> > I noticed that in standalone mode, WAL segments don't seem to be
>> >> > recycled.  This could get problematic if you're forced to vacuum large
>> >> > tables in that mode and space for WAL is short.
>> >>
>> >> Checkpoint is required to recycle old WAL segments. Can checkpoint
>> >> be executed in standalone mode? even during VACUUM FULL?
>> >
>> > Hmm, I guess it would violate POLA that the standalone server would
>> > decide to run checkpoint in the middle of vacuum.  I imagine that in
>> > some cases the only option would be to process the tables manually, with
>> > the ALTER TABLE/SET TYPE trick or similar (VACUUM FULL in 9.0+).
>> >
>> > So I can see that there is no good fix for this problem, yet it is a
>> > very inconvenient situation to be in.
>>
>> I don't think it would violate the POLA for a standalone backend to
>> checkpoint periodically, but I have to admit I can count the number of
>> times I've run a standalone backend on one hand.  Does this come up
>> much?
>
> I admit I have no idea why these guys seem to run into wraparound
> problems so much.
>
> On the other hand, I'm not sure that it would work to try to checkpoint
> "during" vacuum, because the backend is in a transaction.  Maybe it
> would work to force a checkpoint after each command, and between tables
> in a multi-table vacuum (which is presumably a common thing to do in a
> standalone backend) or something like that?

I doubt it's necessary to force a checkpoint after each command - I
assume that if you want one, you can just execute the CHECKPOINT
command explicitly.  The multi-table VACUUM case could be handled
similarly - VACUUM each table, then checkpoint, and so on.   It'd
probably be more worthwhile to pursue the approach of allowing the
system to be brought up in multi-user mode, but allow only super-users
to log in and don't allow them to do anything except VACUUM until some
semblance of sanity is achieved.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company