Re: Help estimating database and WAL size

Am 2012-10-15 23:13, schrieb John R Pierce:
> 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.

In huge number of cases you will also write these files to some kind of
network storage via e.g. CIFS or NFS so you have access to them via your
warm-standby-machines. I want to say: this is taken some storage but can
be reviewed kind of independent from database itself.

Cheers,
Frank