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
>
