Re: [GENERA]: Postgresql-9.1.1 synchronous replication issue

On Wed, Feb 15, 2012 at 11:01 AM, Venkat Balaji <venkat(dot)balaji(at)verse(dot)in>wrote:

>
> On Tue, Feb 14, 2012 at 8:09 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>wrote:
>
>> On Tuesday, February 14, 2012 4:21:22 am Venkat Balaji wrote:
>> > Hello,
>> >
>> > Disaster Recovery testing for Synchronous replication setup -
>> >
>> > When the standby site is down, transactions at the production site
>> started
>> > hanging (this is after the successful setup of synchronous replication).
>> >
>> > We changed synchronous_commit to 'local' to over-come this situation.
>> >
>> > - No transactions are hanging at the production site even when the
>> standby
>> > is down
>> > - Standby is automatically getting synced when it is back up again.
>> >
>> > Can someone let us know if there are any "-ve" effects of putting
>> > synchronous_commit='local' ??
>> >
>> > I am assuming that this as good as putting "synchronous_commit=on" on an
>> > stand-alone system.
>>
>> It would seem you are really after streaming replication(which is
>> asynchronous)
>> more than synchronous replication. I have not used synchronous replication
>> enough to be sure, but I think by setting synchronous_commit='local' you
>> are
>> basically turning the system into a straight streaming(asynchronous)
>> system
>> anyway.
>>
>
> Sorry. Ignore my earlier message -

Yeah. Its a kind of asynchronous ( at the transaction level, NOT WAL based
). All i wanted to achieve is as follows -

1. Synchronous replication - which would perform transactions
simultaneously on production and standby.
2. Ideally, if the commit does not occur at the standby site, then it would
not commit at the production as well, which will cause production site
to hang. I do not want production site to hang if the standby site is
down or not accessible.
3. I would need the commit to occur on production and the production apps
should not be disturbed if the standby fails to respond. To achieve this,
I have set synchronous_commit='local' to ensure that transactions are
committed at production site first.

We do have streaming replication (of PG-9.0) setup on our other production
boxes, which is asynchronous and is WAL based.

Thanks
VB