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.