Re: standby questions - Mailing list pgsql-general

On Sat, 9 Feb 2008, Roberto Scattini wrote:

> -a "WAL segment file" is the same that a "log file segment"?

Yes:  WAL="write-ahead log".

> -how often a new WAL file is generated? this depends on the server load?

These are the WAL segment files that are produced by the database, the
ones you're copying to the standby server.  A new one comes out whenever
the current one is filled.  While it's not really exact, if you think of
it as new one appearing after every 16MB of changes to the database that's
the right general idea.

> -what are the "log file segments" referenced by checkpoint_segments?
> are the "binary logs" where postgres stores lasts transactions, the
> ones in $DATA/pg_xlog dir? if this is true, then:
> what means "Maximum distance between automatic WAL checkpoints"???
> this is how often, in "log file segments", postgres will perform a
> checkpoint, generating a special checkpoint record from which to start
> the redo operation, dont?

A checkpoint happens after one of a few things have occurred:

-The checkpoint_timeout has passed
-checkpoint_segments worth of WAL files have been output since the last
one
-One is required to support a command (server shutdown for example)

Because the 2nd of those depends on activity as mentioned above, that's
why the "distance" between checkpoints can vary a bit.

The checkpoint will re-use up to 2*checkpoint_segments+1 files on the
primary server as part of its cleanup.

> -in one WAL file i could have one, more than one and even an
> incomplete "transaction"?? (where is the doc i need to read?!)
> -if i have incomplete transactions on a WAL, how the standby server
> processes that? what if the rest of the transaction never reaches the
> standby server?

Transactions that haven't been completed for whatever reason are rolled
back as part of bringing the standby server up.

> -how do i know exactly at which point in time (in transactions) my
> standby server is, if i have to switch to primary role?

You can run pg_controldata on the standby to see where it's at.

> -what is the "restartpoint" named in the "Warm Standby Servers for
> High Availability" page?

I started to answer this one but realized I don't have a clear enough
description here for you.  Hopefully someone will chime in (and by someone
I mean Simon) with more detail about how restartpoints are tracked and
what that implies for setting -k in pg_standby for 8.2.

Good questions, I've been collecting notes for a FAQ on this subject and
I'll add some of these to it.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD