Re: Replication & web apps - Mailing list pgsql-general

I just reread your post and see that you already understand that what
you are describing is async replication.  Duh.
That being said, what you are trying to do is really combine the idea of
load balancing and async replication....not too feasible I would think.
Usually when people are using async replication, the slave nodes are
only used as backups or for read-only reporting systems)

Jeff Amiel wrote:
> Well, you've hit upon the difference between synchronous replication
> and asynchronous replication (and touched on the idea of
> clustering/load balancing)
> In a synchronous replication scheme, the database update is made to
> BOTH master and slave simultaneously....the transactions must commit
> to both or it is rolled back.
> In asynchronous replication, there can indeed be a delay between when
> the master makes update and the slave receives the notification of it.
>
> pg_cluster is an example of a synchronous replication method (although
> it's really considered multi-master...not master-slave)
> Slony is an example of asynchronous.
>
> There are other techniques to balance the load of the database calls
> so that some go to one box and some to others, yet keep the data in
> synch...
> Continuent makes a commercial p/cluster product  as well as an open
> source product called Sequoia that sit in the JDBC layer and direct
> traffic and control the load balancing.
>
> Jeff Amiel
>
> Leonardo Francalanci wrote:
>> Hi,
>>
>> I still don't understand how replication can be used in web
>> applications.
>> Given this scenario:
>>
>> 1) user updates his profile -> update to the db (master)
>> 2) web app redirects to the "profile page" -> select from db (slave)
>>
>> Since (2) is a select it is issued to the slave.
>>
>> How can one be sure that the master propagates the update (1) to the
>> slave before data is requested from the slave (2)?
>> And: suppose there is a method to understand that the user made a
>> change to the db in the web request (as above) so that we have to
>> issue all queries of the same web request to the master, that is:
>>
>> 1) user updates his profile -> update to the db (master)
>> 2) web app redirects to the "profile page" -> select from db (master
>> again because in this web-request user made a change to the db)
>>
>> what if the user ask AGAIN for the "profile page" BEFORE write
>> propagates to the slave:
>>
>> 3) User ask for a refresh of the "profile page" -> select -> slave
>> (because user didn't make any change during THIS web request)
>>
>> ???
>>
>> In other words: how can asynchronous replication be used in an
>> application???
>>
>>
>>
>>
>>
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>>       choose an index scan if your joining column's datatypes do not
>>       match
>