Re: Checking for stale reads on hot standby

On Mon, Sep 27, 2010 at 1:51 AM, Yang Zhang <yanghatespam(at)gmail(dot)com> wrote:

> Say you have an application using PG asynchronous streaming
> replication to some hot standbys, to distribute the read load. The
> application itself is a typical web application consisting of multiple
> servers, serving a number of sessions (perhaps belonging to different
> users), and the workload is OLTP-ish, with each session continually
> issuing a bunch of transactions. To guarantee session timeline
> consistency for clients of the application, you want to make sure that
> they can read data that's at least as new as anything they've
> read/written previously, never traveling back in time.
>
> With asynchronous replication, after seeing a new version of the data
> from one standby, you may see an older version from a subsequent query
> to another standby. The question: what are some ways to provide this
> form of consistency in the context of PG asynchronous replication?
>
> Is the standard/recommended approach to use a sequence representing
> the global database version? Here, the application is responsible for
> incrementing this from update transactions. In read transactions,
> check that the sequence value is >= the session's highest-seen-value,
> and raise the latter if necessary.
>
>
See the nuggets hidden in section 25.2.5.2. "Monitoring" at
http://www.postgresql.org/docs/9.0/static/warm-standby.html#STREAMING-REPLICATION

After an UPDATE, your application can cache the info from
'pg_current_xlog_location()' result on the primary and then compare that
with the result of 'pg_last_xlog_receive_location()' on the standby to see
if it is seeing fresh enough data.

Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com

singh(dot)gurjeet(at){ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device