测试1:synchronous_standby_names=* 是否等同于synchronous_standby_names=any1(s1,s2,s3)
结论:不等同
ANY 2(s1,s2):quorum,quorum,async
ANY 1(s1,s2,s3):quorum,quorum,quorum
*:potential,potential,sync
ANY 2(s1,s2,s3):quorum,quorum,quorum
FIRST 2(s1,s2,s3):sync,sync,potential
postgres=# select application_name ,client_addr,state,sent_lsn,write_lsn,flush_lsn,write_lag,sync_state from pg_stat_replication ;
application_name | client_addr | state | sent_lsn | write_lsn | flush_lsn | write_lag | sync_state
--------------------+--------------+-----------+------------+------------+------------+-----------+------------
172.16.16.2:11011 | 172.16.16.2 | streaming | 3/20000000 | 3/20000000 | 3/20000000 | | quorum
172.16.16.15:11009 | 172.16.16.15 | streaming | 3/20000000 | 3/20000000 | 3/20000000 | | quorum
172.16.16.16:11000 | 172.16.16.16 | streaming | 3/20000000 | 3/20000000 | 3/20000000 | | quorum
(3 rows)
postgres=# show synchronous_standby_names ;
synchronous_standby_names
----------------------------------------------------------------
ANY 2(172.16.16.2:11011,172.16.16.15:11009,172.16.16.16:11000)
(1 row)
postgres=# select application_name ,client_addr,state,sent_lsn,write_lsn,flush_lsn,write_lag,sync_state from pg_stat_replication ;
application_name | client_addr | state | sent_lsn | write_lsn | flush_lsn | write_lag | sync_state
--------------------+--------------+-----------+------------+------------+------------+-----------+------------
172.16.16.2:11011 | 172.16.16.2 | streaming | 3/21000000 | 3/21000000 | 3/21000000 | | sync
172.16.16.15:11009 | 172.16.16.15 | streaming | 3/21000000 | 3/21000000 | 3/21000000 | | sync
172.16.16.16:11000 | 172.16.16.16 | streaming | 3/21000000 | 3/21000000 | 3/21000000 | | potential
(3 rows)
postgres=# show synchronous_commit ;
synchronous_commit
--------------------
off
(1 row)
postgres=# show synchronous_standby_names ;
synchronous_standby_names
------------------------------------------------------------------
FIRST 2(172.16.16.2:11011,172.16.16.15:11009,172.16.16.16:11000)
(1 row)
postgres=# show synchronous_standby_names ;
synchronous_standby_names
----------------------------------------------
ANY 2(172.16.16.15:11009,172.16.16.16:11000)
(1 row)
postgres=# select * from pg_stat_replication ;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state
| sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state
------+----------+---------+--------------------+--------------+-----------------+-------------+-----------------------------------+--------------+----------
-+------------+------------+------------+------------+-----------+-----------+------------+---------------+------------
3338 | 10 | tbase | 172.16.16.16:11000 | 172.16.16.16 | | 59810 | 2022-10-13 17:58:23.965964 +08:00 | | streaming
| 3/20000000 | 3/20000000 | 3/20000000 | 3/20000000 | | | | 1 | quorum
3318 | 10 | tbase | 172.16.16.15:11009 | 172.16.16.15 | | 60350 | 2022-10-13 17:58:23.834684 +08:00 | | streaming
| 3/20000000 | 3/20000000 | 3/20000000 | 3/20000000 | | | | 1 | quorum
3298 | 10 | tbase | 172.16.16.2:11011 | 172.16.16.2 | | 58738 | 2022-10-13 17:58:23.774640 +08:00 | | streaming
| 3/20000000 | 3/20000000 | 3/20000000 | 3/20000000 | | | | 0 | async
(3 rows)
postgres=# select * from pg_stat_replication ;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state
| sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state
------+----------+---------+--------------------+--------------+-----------------+-------------+-----------------------------------+--------------+----------
-+------------+------------+------------+------------+-----------+-----------+------------+---------------+------------
3338 | 10 | tbase | 172.16.16.16:11000 | 172.16.16.16 | | 59810 | 2022-10-13 17:58:23.965964 +08:00 | | streaming
| 3/20000000 | 3/20000000 | 3/20000000 | 3/20000000 | | | | 1 | quorum
3318 | 10 | tbase | 172.16.16.15:11009 | 172.16.16.15 | | 60350 | 2022-10-13 17:58:23.834684 +08:00 | | streaming
| 3/20000000 | 3/20000000 | 3/20000000 | 3/20000000 | | | | 1 | quorum
3298 | 10 | tbase | 172.16.16.2:11011 | 172.16.16.2 | | 58738 | 2022-10-13 17:58:23.774640 +08:00 | | streaming
| 3/20000000 | 3/20000000 | 3/20000000 | 3/20000000 | | | | 1 | quorum
(3 rows)
postgres=# show synchronous_standby_names ;
synchronous_standby_names
----------------------------------------------------------------
ANY 1(172.16.16.2:11011,172.16.16.15:11009,172.16.16.16:11000)
(1 row)
postgres=# select * from pg_stat_replication ;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state
| sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state
-------+----------+---------+--------------------+--------------+-----------------+-------------+-----------------------------------+--------------+---------
--+------------+------------+------------+------------+-----------+-----------+------------+---------------+------------
31590 | 10 | tbase | 172.16.16.15:11009 | 172.16.16.15 | | 41754 | 2022-10-13 17:02:21.581610 +08:00 | | streamin
g | 3/1C0000E0 | 3/1C0000E0 | 3/1C0000E0 | 3/1C0000E0 | | | | 1 | potential
31589 | 10 | tbase | 172.16.16.16:11000 | 172.16.16.16 | | 64470 | 2022-10-13 17:02:21.540937 +08:00 | | streamin
g | 3/1C0000E0 | 3/1C0000E0 | 3/1C0000E0 | 3/1C0000E0 | | | | 1 | potential
31588 | 10 | tbase | 172.16.16.2:11011 | 172.16.16.2 | | 42160 | 2022-10-13 17:02:21.447645 +08:00 | | streamin
g | 3/1C0000E0 | 3/1C0000E0 | 3/1C0000E0 | 3/1C0000E0 | | | | 1 | sync
(3 rows)
postgres=# show synchronous_standby_names ;
synchronous_standby_names
---------------------------
*
(1 row)
postgres=# show synchronous_commit ;
synchronous_commit
--------------------
local
(1 row)
postgres=#
测试2:synchronous_standby_names和synchronous_commit的优先级
如果synchronous_standby_names=*,synchronous_commit=off的一主三备环境,是如何实现的主备同步状态
结论:
如果 synchronous_standby_names 参数不指定值,同步复制不会启用。
即使同步复制被启用,通过设置 synchronous_commit 配置参数为 local 或者 off,单个事务可以被配置为不等待 standby 端的 wal 被应用。
所以可以简单理解synchronous_standby_names 优先级大于synchronous_commit。
标签:10,同步,postgresql,lsn,synchronous,standby,主备,172.16,20000000 From: https://www.cnblogs.com/ddlearning/p/16790565.html