Обсуждение: Synchronous replication and read consistency
Does sync replication guarantee that any inserted data on primary is immediately visible for read on standbys with no lag.
> Does sync replication guarantee that any inserted data on primary is > immediately visible for read on standbys with no lag. Basically yes. Of course there is *some* latency, at the very least from the network. If I run a process on a standby machine that displays a value every 0.1 sec and update the value on the master, I see the standby updating with a lag that feels less than 0.2 sec or so. You might have lag, however, in situations where you have so much write into the master that the network or standby is not able to catch up. After the write burst is over, the stanby will catch up as it quickly as possible, though. Also, you use the word "consistency", that would be something else... Of course you always get consistent data, lag or not. This is Postgres after all :) Bye, Chris.
Chris/Joshua I would like to know more details. 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. 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. Hope it is clear. On Wed, Jul 29, 2015 at 2:20 PM, Chris Mair <chris@1006.org> wrote: >> Does sync replication guarantee that any inserted data on primary is >> immediately visible for read on standbys with no lag. > > Basically yes. Of course there is *some* latency, at the very least > from the network. > > If I run a process on a standby machine that displays a value every > 0.1 sec and update the value on the master, I see the standby updating > with a lag that feels less than 0.2 sec or so. > > You might have lag, however, in situations where you have so much > write into the master that the network or standby is not able to > catch up. After the write burst is over, the stanby will catch up > as it quickly as possible, though. > > Also, you use the word "consistency", that would be something else... > Of course you always get consistent data, lag or not. This is Postgres > after all :) > > Bye, > Chris. > > > >
> Chris/Joshua > > I would like to know more details. > > 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." > Ah, sorry I misread sync replication as streaming replication... > 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. > > 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. > > Hope it is clear. Synchronous replication is slower by nature. It will slow down the master as well because each commit has to wait for a standby to ack it. The answer to your question is still yes, you will get the same result on the standby. You will actually see less lag than with normal streaming replication in the sense that the standby lagging several transactions behind due to a commit/write burst on the master is not possible anymore. This is of course at the expense of master-performance. Bye, Chris.
Ravi Krishna <sravikrishna3@gmail.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
"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. " Joshua: THis essentially contradicts your statement to me. On Wed, Jul 29, 2015 at 5:10 PM, Kevin Grittner <kgrittn@ymail.com> wrote: > Ravi Krishna <sravikrishna3@gmail.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
On 07/29/2015 02:27 PM, Ravi Krishna wrote: > > "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. " > > Joshua: THis essentially contradicts your statement to me. I would trust Kevin with this particular information. What he wrote was new to me as well. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing "I'm offended" is basically telling the world you can't control your own emotions, so everyone else should do it for you.
Kevin Grittner schrieb am 29.07.2015 um 23:10: > 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. It's actually not that hard to run into. We encountered this when we were running unit tests against a master/slave setup with pgPool: http://postgresql.nabble.com/Synchronous-replication-pgPool-not-all-transactions-immediately-visible-on-standby-tp5820275.html Regards Thomas