Re: xlogdump fixups and WAL log question.

On Oct 21, 2006, at 3:12 PM, Simon Riggs wrote:

> On Sat, 2006-10-21 at 09:00 -0400, Theo Schlossnagle wrote:
>> On Oct 21, 2006, at 6:08 AM, Martijn van Oosterhout wrote:
>>
>>> On Sat, Oct 21, 2006 at 10:37:51AM +0100, Simon Riggs wrote:
>>>> Turning off WAL is a difficult topic. Without it you have no crash
>>>> recovery, which IMHO everybody says they don't care about until  
>>>> they
>>>> crash, then they realise. It's hard to be selective about  
>>>> writing WAL
>>>> for specific operations also.
>>>
>>> It's been discussed before. One idea is to declare tables without
>>> logging. The idea being that during recovery those tables and  
>>> related
>>> indexes are simply truncated. No foreign keys allowed. Obviously  
>>> they
>>> will not be saved via PITR either.
>>>
>>> Put another way, the table structure is saved in WAL, but the data
>>> isn't.
>>
>> This is exactly what I'd like.  Simon suggested turning off WAL
>> during the loads as a possible hack solution.  The reason this won't
>> work is that we snap all the time, lots of tables.  We have between
>> 2000 and 4000 snapshot operations per day (throughout).  At the same
>> time we have reporting queries running (that create and/or populate
>> other tables) that last from 5 minutes to 18 hours.  It is important
>> that we run everything but the snapshots with WAL on (as we must have
>> PITR -- sans snapshots)
>
> These tables are loaded once then read-only, yes?

No, they are loaded, and then reloaded, and then reloaded. Queries  
that use them will get the most recently loaded version of them.  It  
meets a business rule like: table foo on the warehouse should be  
representative of version of table foo on OLTP no older than 30 minutes.

// Theo Schlossnagle
// CTO -- http://www.omniti.com/~jesus/
// OmniTI Computer Consulting, Inc. -- http://www.omniti.com/