Re: No control over max.num. WAL files

On Wed, May 25, 2011 at 6:47 AM, Andrew Sullivan <ajs(at)crankycanuck(dot)ca> wrote:
> On Wed, May 25, 2011 at 01:37:47PM +0100, Simon Riggs wrote:
>>
>> That's the way SQLServer and Oracle work, but not PostgreSQL. We can
>> clear down WAL files even during a long running transaction.
>>
>> For us, "unneeded" means prior to the second-to-last checkpoint record.
>
> Well, they're obviously not getting cleared down, so they must be
> needed.  I know how Postgres is supposed to work in these cases, but
> in my experience you cannot rely on the OP's calculation to provide
> you with a true maximum.  Pathological conditions result in a lot of
> WAL segments hanging around.
>
> What I really suspect is that this has to do with the way I/O
> scheduling works, particularly in the presence of the bgwriter.  But I
> don't feel comfortable suggesting particular reasons for what I've
> experienced in production.  What I _can_ tell you is that, when I've
> had to do large restores like this, I wanted plenty of overhead for
> WAL.  ISTR dedicating 40G to WAL one time for a case like this.

I have one db server on a SAN right now and it's got 20G allocated for
WAL and 500G for the data/base dir, and have no problems with my WAL
ever coming close to filling up. But if I did, I'd just shut down the
db, move pg_xlog back to the data/base dir on the 500G drive set,
restart, restore, shut down, move pg_xlog back, restart and go.