Re: Help estimating database and WAL size

On 10/15/12 2:03 PM, Daniel Serodio (lists) wrote:
> John R Pierce wrote:
>> On 10/08/12 1:39 PM, Daniel Serodio (lists) wrote:
>>> 3) Estimate the size of the transaction log
>>> ** We've got no idea how to estimate this, need advice **
>>
>> postgres doesn't have a 'transaction log', it has the WAL
>> (Write-Ahead Logs). These are typically 16MB each. on databases
>> with a really heavy write load, I might bump the checkpoint_segments
>> as high as 60, which seems to result in about 120 of them being
>> created, 2GB total. these files get reused, unless you are archiving
>> them to implement a continuous realtime backup system (which enables
>> "PITR", Point in Time Recovery)
> Thanks, I was using the term "transaction log" as a synonym for WAL.
> We're planning on enabling PITR; how can we calculate the WAL size and
> the WAL archive size in this case?

its based on how much data you're writing to the database. Wheen you
write tuples (rows) to the database, they are stored in 8K pages/blocks
which are written to the current WAL file as they are committed, when
that WAL file fills up, or the checkpoint_timeout is reached (the
default is 30 seconds, I believe) , the WAL file is written to the archive.

To be able to utilize PITR, you need a complete base backup of the file
system, and /all/ the archived WAL files since that base backup was taken.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast