Thread: When I set the synchronous_standby_names and synchronous_commit to off, the replication query state also show in sync mode.
When I set the synchronous_standby_names and synchronous_commit to off, the replication query state also show in sync mode.
From
厚积薄发
Date:
When I use the sync replation in PG9.6, I find a question about the query state display.The following is the testing steps: 1) set the postgresql.conf parameter synchronous_standby_names = '5(*)' synchronous_commit = on 2) start the standby node ,and query the replication state It shows that the replication in the sync mode. postgres=# select * from pg_stat_replication; -[ RECORD 1 ]----+------------------------------ pid | 2650 usesysid | 10 usename | houjibofa application_name | walreceiver client_addr | 127.0.0.1 client_hostname | client_port | 50816 backend_start | 2016-10-07 15:32:22.179723+08 backend_xmin | state | streaming sent_location | 0/70000F4 write_location | 0/70000F4 flush_location | 0/70000F4 replay_location | 0/70000F4 sync_priority | 1 sync_state | sync postgres=# show synchronous_commit; -[ RECORD 1 ]------+--- synchronous_commit | on 4) set the synchronous_commit to off, using the pg_ctl reload method postgres=# show synchronous_commit; -[ RECORD 1 ]------+---- synchronous_commit | off postgres=# select * from pg_stat_replication; -[ RECORD 1 ]----+------------------------------ pid | 2650 usesysid | 10 usename | houjibofa application_name | walreceiver client_addr | 127.0.0.1 client_hostname | client_port | 50816 backend_start | 2016-10-07 15:32:22.179723+08 backend_xmin | state | streaming sent_location | 0/70000F4 write_location | 0/70000F4 flush_location | 0/70000F4 replay_location | 0/70000F4 sync_priority | 1 sync_state | sync It also shows in the sync replication mode, however, it is in the async mode. And I can create a table in the primary mode. postgres=# show synchronous_standby_names; -[ RECORD 1 ]-------------+----- synchronous_standby_names | 5(*) postgres=# create table test(a int); CREATE TABLE The PG version is follows. postgres=# select version(); version ------------------------------------------------------------------------------------- -------------------PostgreSQL 9.6.0 on i686-pc-linux-gnu, compiled by gcc (Ubuntu 5.3.1-14ubuntu2) 5.3. 1 20160413, 32-bit (1 row) I think the reason is that when get the replication priority in the function SyncRepGetStandbyPriority, it does not consider the parameter synchronous_commit. So if we set the synchronous_standby_names parameter and the standby fit the setting, no matter whether sync or not, it's priority will not equal to 0. -- View this message in context: http://postgresql.nabble.com/When-I-set-the-synchronous-standby-names-and-synchronous-commit-to-off-the-replication-query-state-a-tp5924787.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
Re: When I set the synchronous_standby_names and synchronous_commit to off, the replication query state also show in sync mode.
From
Michael Paquier
Date:
On Fri, Oct 7, 2016 at 9:00 AM, =E5=8E=9A=E7=A7=AF=E8=96=84=E5=8F=91 <postg= resql_2016@163.com> wrote: > It also shows in the sync replication mode, however, it is in the async > mode. And I can create a table in the primary mode. This is not a bug, but a design behavior. synchronous_commit can be used to enforce synchronous replication checks not to happen for certain transactions. Take for example transactions where temporary or unlogged tables are created, you don't need to care about the WAL they generated to be replicated before going back to the client. --=20 Michael
Re: When I set the synchronous_standby_names and synchronous_commit to off, the replication query state also show in sync mode.
From
厚积薄发
Date:
I know that. My question is when I use the reload method to change the synchronous_commit(from on to off), but the query replication state seems not change. Although it is in the async mode, it shows in the sync mode. -- View this message in context: http://postgresql.nabble.com/When-I-set-the-synchronous-standby-names-and-synchronous-commit-to-off-the-replication-query-state-a-tp5924787p5924790.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
Re: Re: When I set the synchronous_standby_names and synchronous_commit to off, the replication query state also show in sync mode.
From
Andres Freund
Date:
On 2016-10-06 17:15:30 -0700, å积èå wrote: > I know that. My question is when I use the reload method to change the > synchronous_commit(from on to off), but the query replication state seems > not change. Although it is in the async mode, it shows in the sync mode. Read Michael's mail again. He explains precisely this. synchronous_standby_names (per cluster) is the relevant bit, not synchronous_commit (per session).
Re: Re: When I set the synchronous_standby_names and synchronous_commit to off, the replication query state also show in sync mode.
From
Michael Paquier
Date:
On Fri, Oct 7, 2016 at 9:15 AM, =E5=8E=9A=E7=A7=AF=E8=96=84=E5=8F=91 <postg= resql_2016@163.com> wrote: > I know that. My question is when I use the reload method to change the > synchronous_commit(from on to off), but the query replication state seems > not change. Although it is in the async mode, it shows in the sync mode. And you didn't get my point :) synchronous_commit has no effect on what is showing in pg_stat_replication or in the way priorities are calculated for each standby connected. It just influences the local status of a syncrep wait. --=20 Michael
Re: When I set the synchronous_standby_names and synchronous_commit to off, the replication query state also show in sync mode.
From
厚积薄发
Date:
I know your meanings. As the parameter synchronous_commit can change in the every session, so when calculating the priority if consider it no meanings. But the sync_state result seems confuse me, when I set the synchronous_commit to off or local which is not in the sync mode. Thanks for all replies. -- View this message in context: http://postgresql.nabble.com/When-I-set-the-synchronous-standby-names-and-synchronous-commit-to-off-the-replication-query-state-a-tp5924787p5924794.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.