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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
