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

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.

We need to get this setup live on production shortly.

Thanks
VB

On Fri, Feb 10, 2012 at 4:47 PM, Venkat Balaji <venkat(dot)balaji(at)verse(dot)in>wrote:

>
> This issue stays resolved !!!
>
> The statements are no more hanging on production now :)
>
> The suspected problem was -
>
> Our brand new production server did not have the port 5432 open.
>
> I had opened the port using "iptables" command and everything started
> working.
>
> synchronous replication is fast and awesome.
>
> Thanks
> VB
>
>
> On Fri, Feb 3, 2012 at 9:45 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>wrote:
>
>> On Thursday, February 02, 2012 10:21:28 pm Venkat Balaji wrote:
>>
>> >
>> > Connection is working fine between primary and standby, ping is working
>> > fine and wal archive file transfer is working without any issues.
>> >
>> > I tried CREATE TABLE and CREATE DATABASE, both were hanging.
>> >
>> > Apart from regular streaming replication settings, I did the following
>> on
>> > primary to enable synchronous replication -
>> >
>> > synchronous_standby_names='*'
>> >
>> > Commands started hanging after that. Is there anything else i need to
>> do.
>>
>> From here:
>>
>> http://www.postgresql.org/docs/9.1/interactive/runtime-config-replication.html
>>
>> "
>> synchronous_standby_names (string)
>> ... The synchronous standby will be the first standby named in this list
>> that is
>> both currently connected and streaming data in real-time (as shown by a
>> state of
>> streaming in the pg_stat_replication view). Other standby servers
>> appearing
>> later in this list represent potential synchronous standbys....
>>
>> The name of a standby server for this purpose is the application_name
>> setting of
>> the standby, as set in the primary_conninfo of the standby's walreceiver.
>> There
>> is no mechanism to enforce uniqueness. In case of duplicates one of the
>> matching
>> standbys will be chosen to be the synchronous standby, though exactly
>> which one
>> is indeterminate. The special entry * matches any application_name,
>> including
>> the default application name of walreceiver.
>>
>> "
>>
>> So I would check the pg_stat_replication view to see if Postgres is
>> seeing the
>> standby as streaming.
>>
>>
>> >
>> > Thanks
>> > VB
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)gmail(dot)com
>>
>
>