Postgres Pro
Downloads
Home   >   mailing lists

Re: [GENERA]: Postgresql-9.1.1 synchronous replication issue - Mailing list pgsql-general

From Venkat Balaji
Subject Re: [GENERA]: Postgresql-9.1.1 synchronous replication issue
Date
Msg-id CAFrxt0i21CT7eErcjxC3fQJ3EhhaQ1HgQiginzEzY2HQv4Qm4g@mail.gmail.com
Whole thread Raw
In response to Re: [GENERA]: Postgresql-9.1.1 synchronous replication issue  (Venkat Balaji <venkat.balaji@verse.in>)
Responses Re: [GENERA]: Postgresql-9.1.1 synchronous replication issue
List pgsql-general
Tree view

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.balaji@verse.in> wrote:
 
On Wed, Feb 15, 2012 at 11:01 AM, Venkat Balaji <venkat.balaji@verse.in> wrote:

On Tue, Feb 14, 2012 at 8:09 PM, Adrian Klaver <adrian.klaver@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

pgsql-general by date:

Previous
From: Philip Couling
Date:
Subject: Re: Easy form of "insert if it isn't already there"?
Next
From: "Tomas Vondra"
Date:
Subject: Re: High checkpoint_segments