Re: time-delayed standbys

On Wed, Apr 20, 2011 at 10:19 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Greg Stark <gsstark(at)mit(dot)edu> writes:
>> On Wed, Apr 20, 2011 at 2:19 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> How would we keep track of the most recent timestamp received from the
>>> master without replaying the WAL records?
>
>> Well as we receive them we would have to peek at them to see the time.
>> Or we could have the master send its time to the slave as I believe
>> Tom has already proposed in the past but I believe didn't choose to
>> do?
>
> My idea of how to manage it would be to have walreceiver explicitly
> track the clock difference from the master, which it can do since
> walsender puts its current time into every message header.  You can use
> the slave's clock for comparisons so long as you add the appropriate
> offset.  (The theory here is that the skew won't change very fast ...)

I am a bit concerned about the reliability of this approach. If there
is some network lag, or some lag in processing from the master, we
could easily get the idea that there is time skew between the machines
when there really isn't. And our perception of the time skew could
easily bounce around from message to message, as the lag varies. I
think it would be tremendously ironic of the two machines were
actually synchronized to the microsecond, but by trying to be clever
about it we managed to make the lag-time accurate only to within
several seconds.

It looks like the MySQL version of this feature is spec'd to have some
sort of time skew compensation built into it, but I don't see anything
that explains how it actually works.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company