Re: synchronous replication: blocking commit on the master

I have specific needs for wanting synchronous replication instead of asynchronous replication, notwithstanding my desire to continue processing work on the master if there are no active slaves. I would like to use  replication for both HA and for query scaling. I'd like replication to be synchronous to ensure that any slaves are up to date, and I cannot afford even the small data potential loss implied by asynchronous replication.  However, should there be a situation where no slaves are alive (e.g. there is a single slave and it fails for whatever reason), I do not want to compromise the availability of the master while the slave is being restored. Instead, I'd like to be able to continue processing transactions on the master unimpeded until a slave can be brought back online. Once a slave is caught back up to the master I'd like to switch back to synchronous replication and again be able to use the slave to scale reads and as a failover target
should the master fail.

Does that make sense?

And thanks for the suggestion about switching individual transactions synchronous_commit = local or off. That could be of use in achieving my goal, albeit clumsily and with intervention on the client. I suppose a client could detect that a slave is no longer available, could then send an interrupt to all connections with pending work, and then set synchronous_commit=local on all connections before further work is submitted to the master.

Thanks.

________________________________
From: Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org; Jameison Martin <jameisonb(at)yahoo(dot)com>
Sent: Monday, February 27, 2012 5:52 PM
Subject: Re: [GENERAL] synchronous replication: blocking commit on the master

On Monday, February 27, 2012 4:36:26 pm Jameison Martin wrote:
> I have observed that a commit on a replication master hangs if there are no
> slaves to communicate with if synchronous replication is enabled. I
> believe I have seen a posting that this behavior is deliberate.
>
> In my environment I'd prefer to have the master continue processing
> transactions if there is a failure at the slave. Questions: * is there any
> way to allow the master proceed if the slave is unavailable (perhaps a
> configuration parameter I'm missing)?

http://www.postgresql.org/docs/9.1/interactive/runtime-config-wal.html#GUC-
SYNCHRONOUS-COMMIT

"
If synchronous_standby_names is set, this parameter also controls whether or not
transaction commit will wait for the transaction's WAL records to be flushed to
disk and replicated to the standby server. The commit wait will last until a
reply from the current synchronous standby indicates it has written the commit
record of the transaction to durable storage. If synchronous replication is in
use, it will normally be sensible either to wait both for WAL records to reach
both the local and remote disks, or to allow the transaction to commit
asynchronously. However, the special value local is available for transactions
that wish to wait for local flush to disk, but not synchronous replication.
"

>
>     * if not, is there any ideological objection to allowing the master to
> continue for users that would prefer that behavior? Thanks.

Seems to me to defeat the purpose of sync replication.  Though if you want,
beside the above:
"
Even when synchronous replication is enabled, individual transactions can be
configured not to wait for replication by setting the synchronous_commit
parameter to local or off.
"
Though it looks like you are really looking for async streaming replication.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com