On Wed, May 25, 2011 at 6:47 AM, Andrew Sullivan <ajs@xxxxxxxxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
