Re: Configuring synchronous replication

On 24/09/10 01:11, Simon Riggs wrote:
>> But that's not what I call synchronous replication, it doesn't give
>> you the guarantees that
>> textbook synchronous replication does.
>
> Which textbook?

I was using that word metaphorically, but for example:

Wikipedia
  http://en.wikipedia.org/wiki/Replication_%28computer_science%29
  (includes a caveat that many commercial systems skimp on it)

Oracle docs
 
http://download.oracle.com/docs/cd/B10500_01/server.920/a96567/repoverview.htm
  Scroll to "Synchronous Replication"

Googling for "synchronous replication textbook" also turns up this 
actual textbook:
   Database Management Systems by R. Ramakrishnan & others
which uses synchronous replication with this meaning, although in the 
context of multi-master replication.

Interestingly, "Transaction Processing: Concepts and techniques" by 
Grey, Reuter, chapter 12.6.3, defines three levels:

1-safe - what we call asynchronous
2-safe - commit is acknowledged after the slave acknowledges it, but if 
the slave is down, fall back to asynchronous mode.
3-safe - commit is acknowledged only after slave acknowledges it. If it 
is down, refuse to commit

In the context of multi-master replication, "eager replication" seems to 
be commonly used to mean synchronous replication.


If we just want *something* that's useful, and want to avoid the hassle 
of registration and all that, I proposed a while back 
(http://archives.postgresql.org/message-id/4C7E29BC.3020902@enterprisedb.com) 
that we could aim for behavior that would be useful for distributing 
read-only load to slaves.

The use case is specifically that you have one master and one or more 
hot standby servers. You also have something like pgpool that 
distributes all read-only queries across all the nodes, and routes 
updates to the master server.

In this scenario, you want that the master node does not acknowledge a 
commit to the client until all currently connected standby servers have 
replayed the commit. Furthermore, you want a standby server to stop 
accepting queries if it loses connection to the master, to avoid giving 
out-of-date responses. With suitable timeouts in the master and the 
standby, it seems possible to guarantee that you can connect to any node 
in the system and get an up-to-date result.

It does not give zero data loss like synchronous replication does, but 
it keeps hot standby servers trustworthy for queries.


It bothers me that no-one seems to have a clear use case in mind. People 
want "synchronous replication", but don't seem to care much what 
guarantees it should provide. I wish the terminology was better 
standardized in this area.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com