Обсуждение: When I set the synchronous_standby_names and synchronous_commit to off, the replication query state also show in sync mode.
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.
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
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.
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).
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
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.