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
