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

On Wednesday, February 15, 2012 2:15:34 am Venkat Balaji wrote:
> 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.

First sync replication is just an advanced form of streaming replication.

http://www.postgresql.org/docs/9.1/interactive/warm-standby.html#STREAMING-
REPLICATION
"
Streaming replication allows a standby server to stay more up-to-date than is
possible with file-based log shipping. The standby connects to the primary, which
streams WAL records to the standby as they're generated, without waiting for the
WAL file to be filled.
"

In both cases WAL information is being used. Though just one record at a time,
so the entire WAL file does not have to be shipped over. In the case of sync
replication a commit on the master is not complete until it also completes on
the standby. This is for the default case where synchronous_commit=on and
synchronous_standby_names has valid names. In your case you are using
synchronous_commit=local  and per:

http://www.postgresql.org/docs/9.1/interactive/runtime-config-wal.html#RUNTIME-
CONFIG-WAL-SETTINGS

synchronous_commit

....
"However, the special value local is available for transactions that wish to
wait for local flush to disk, but not synchronous replication."


So in this case you are not waiting for confirmation of the commit being flushed
to disk on the standby.  It that case you are bypassing the primary reason for
sync replication. The plus is transactions on the master will complete faster
and do so in the absence of the standby. The minus is that you are in sort of an
in between state.

Personally, I take sync replication to be basically an all or nothing
proposition. By setting it up you are saying you want, at minimum, two database
clusters to be in sync at any point in time all the time (except for start up).
If that is not possible then you are really looking for async replication.

>
> Thanks,
> VB


--
Adrian Klaver
adrian.klaver@gmail.com