Re: WAL segments pile up during standalone mode

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(at)commandprompt(dot)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(at)alvh(dot)no-ip(dot)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?

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support