Re: Synchronous replication and read consistency

Ravi Krishna <sravikrishna3(at)gmail(dot)com> wrote:

> As per this:
>
> http://www.postgresql.org/docs/current/static/warm-standby.html#SYNCHRONOUS-REPLICATION
>
> "When requesting synchronous replication, each commit of a write
> transaction will wait until confirmation is received that the commit
> has been written to the transaction log on disk of both the primary
> and standby server."
>
> Does it mean that, on the standby, when PG writes the transaction log
> on the disk, it also updates the data buffers to make the transaction
> visible for all sessions.

No, it means that if the primary is hit by a meteor and you promote
the standby, the data will not have been lost. The time between
the successful return of the commit on the primary and the time at
which the change becomes visible on the standby is normally quite
small; you may have trouble running into a case where you notice
it, but it can happen.

> Eg:
> On the primary
> A big transaction committed
> Now if I issue a select on the primary looking for the transaction I
> committed above, I will get what I want.
> Will I get the same result if instead of primary I issue the select on
> the standby.

Not necessarily. There has been discussion of adding a new mode
which will delay the commit on the primary until it is visible on a
synchronous standby, but I don't recall where that left off. One
of the issues is that with the current guarantee you need multiple
replicas to prevent a failure of a standby from stalling the
primary indefinitely, and you don't have an easy way to know
*which* replica succeeded in persisting the transaction without
doing a lot of work.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company