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.bal...@verse.in>wrote:


> On Wed, Feb 15, 2012 at 11:01 AM, Venkat Balaji <venkat.bal...@verse.in>wrote:
>
>>
>> On Tue, Feb 14, 2012 at 8:09 PM, Adrian Klaver 
>> <adrian.kla...@gmail.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
>

Reply via email to