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@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
