Re: understanding minimum recovery ending location

On 30.12.2010 00:19, Robert Treat wrote:
> Howdy,
>
> I am hoping someone can help me better understand what the "minimum recovery
> ending location" of pg_controldata represents with regards to 9.0 hot
> standbys. When I look at any of our 8.4 (or lower) installs this number is
> almost always somewhere in the past of the xlog timeline (presuming the
> server has been up for any length of time), which makes sense because we've
> done enough replay that we're covered for consistent slave recovery
> purposes. However, on the hot standby machines the number seems to normally
> be at some point in the future. Below is the relevant bits of pg_controldata
> output from a hot standby I was looking at earlier today:
>
> Database cluster state:               in archive recovery
> pg_control last modified:             Wed 29 Dec 2010 04:54:34 PM GMT
> Latest checkpoint location:           56/21020CA8
> Prior checkpoint location:            56/1E36D780
> Latest checkpoint's REDO location:    56/1F599068
> Time of latest checkpoint:            Wed 29 Dec 2010 04:46:09 PM GMT
> Minimum recovery ending location:     56/24B88500
> Backup start location:                0/0
> Current wal_level setting:            hot_standby
>
> As you can see, the minimum recovery ending location is clearly ahead of the
> most recent checkpoint activity.

Minimum recovery ending location means: how far does the (standby) 
server need to replay WAL before it's safe to start it up. It is 
continuously updated as the recovery progresses, as data pages are 
flushed to disk. The reason is that if you kill the server during 
recovery, you have to recover up to the same point again, or the 
database wouldn't be consistent. Specifically, the WAL records for any 
data page changes that were already flushed to disk from the buffer 
cache before killing recovery must be re-replayed.

So in practice the minimum recovery ending location follows somewhat 
behind the last replayed WAL record.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com