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

In-short, I would like to understand if i am achieving the same
asynchronous streaming replication by putting synchronous_commit='local' -

I understand that streaming replication is record based log-shipping.

Below is what shows up on our primary test server where we are testing
synchronous replication -

*1. Synchronous setup enabled with synchronous_commit='local'*

postgres=# select * from pg_stat_replication ;

procpid | usesysid | usename | application_name | client_addr |
client_hostname | client_port | backend_start | state |
sent_location | write_locat
ion | flush_location | replay_location | sync_priority | sync_state
---------+----------+----------+------------------+-------------+-----------------+-------------+------------------------------+-----------+---------------+------------
----+----------------+-----------------+---------------+------------
24099 | 10 | postgres | walreceiver | <ip-address> |
| 56432 | 2012-02-15 12:55:39.65663+03 | streaming |
0/E000078 | 0/E000078
| 0/E000078 | 0/E000078 | 1 | *sync*
(1 row)

postgres=# show synchronous_commit ;

synchronous_commit
--------------------
* local*
(1 row)

postgres=# show synchronous_standby_names ;

synchronous_standby_names
---------------------------------------------
*
(1 row)

Does this mean that the system is still replicating synchronously ? If yes,
by what means ?

*Below is our actual production setup in 9.1.1 with asynchronous
replication setup -*

*2. Asynchronous enabled with synchronous_commit='on'*

psql (9.1.1)
Type "help" for help.

postgres=# select * from pg_stat_replication;

procpid | usesysid | usename | application_name | client_addr |
client_hostname | client_port | backend_start | state
| sent_location | write
_location | flush_location | replay_location | sync_priority | sync_state
---------+----------+----------+------------------+-------------+----------------------+-------------+-------------------------------+-----------+---------------+------
----------+----------------+-----------------+---------------+------------
3159 | 10 | postgres | walreceiver | <ipaddress> |
<hostname> | 40165 | 2012-02-08 12:41:51.858897+03 | streaming |
1/86F83B50 | 1/86F
83B50 | 1/86F83B50 | 1/86F83B50 | 0 | *async*

(1 row)

postgres=# show synchronous_commit ;
synchronous_commit
--------------------
on
(1 row)

postgres=# show synchronous_standby_names ;

synchronous_standby_names
---------------------------

(1 row)

Operation wise, I am not seeing much difference by inserting few 1000 rows.
Its almost the same behavior both in asynch and sync rep.

Thanks,
VB

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

> 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
>