On Mon, Sep 27, 2010 at 1:51 AM, Yang Zhang <yanghates...@gmail.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.gurj...@{ gmail | yahoo }.com Twitter/Skype: singh_gurjeet Mail sent from my BlackLaptop device
